This discussion is archived
2 Replies Latest reply: Feb 12, 2010 12:07 AM by 730428 RSS

v$sql, v$sqlarea and v$sqltext

rv1234 Newbie
Currently Being Moderated
Hello,

Any idea how to isolate PLSQL objects calls from SQL statement in following views?

v$sql,
v$sqlarea,
v$sqltext


For e.g. I ran following query but it would also include SELECT statements. Whereas I only need PLSQL Calls.

select * from v$sql where upper(sql_text) like 'BEGIN%.%'

Is there any better way?

Thank you in advance for reading this post.
R
  • 1. Re: v$sql, v$sqlarea and v$sqltext
    641320 Explorer
    Currently Being Moderated
    r using any third party tool...?

    I am using sql developer and F8 to see the sql query executed. we can filter it by searching the word BEGIN.

    is there any othere way..?

    experts pls answer..?

    S
  • 2. Re: v$sql, v$sqlarea and v$sqltext
    730428 Guru
    Currently Being Moderated
    A select statement can include a pl/sql function call so you cannot exclude selects and search only the begin keyword.
    You could join with DBA_OBJECTS, but is not very precise:
    select * 
    from v$sql, dba_objects  
    where upper(sql_text) like '%'||object_name||'%'
    and object_type in ('PACKAGE','PROCEDURE','FUNCTION');
    Max
    http://oracleitalia.wordpress.com

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points