2 Replies Latest reply: Feb 12, 2010 2:07 AM by 730428 RSS

    v$sql, v$sqlarea and v$sqltext

    Rich V
      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
          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
            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