How to retrieve sql text of cursor?

database-cursor, oracle

Solution

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

Problem

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.).

Original source