How to retrieve sql text of cursor?

If I have a cursor and would like to log the text of the cursor during execution is it possible to use the cursor name in some way and retrieve the SQL?

For example,

OPEN cursor_1 for 
    SELECT ...

I'd like to believe I can do something like cursor_1%NAME much like I can use the other attributes of a cursor. (%ISOPEN, %FOUND, etc.).

AFAIK, there's no way of linking a cursor name to it's SQL text being parsed, but joining V$open_cursor with v$sql using SQL_ID, you should be able to pull out & log the details of the SQL statement being logged for those cursors which are open.

SELECT sql_id,
       user_name,
       sid,
       saddrsql_fulltext
FROM   v$sql
       join v$open_cursor USING (sql_id)

Perhaps you could correlate the SQL text with cursor, but that's going to have to be a manual process

How to retrieve sql text of cursor?

Related

Recent Posts