Is there a way to track what code has been executed using dbms_sql.execute?
For example if I run this query:
DECLARE
cursor_name INTEGER;
rows_processed INTEGER;
BEGIN
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'SELECT * FROM dual WHERE 2 = :x' ,DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_name, ':x', 2);
rows_processed := DBMS_SQL.EXECUTE(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END;
I should be able to find the result:
SELECT * FROM dual WHERE 2 = 2
I've tried looking in v$sql/v$sql_bind_capture and dba_hist_sqltext/dba_hist_sqlbind but it seems unreliable since running the same sql with different bind variables causes the sql with the same sql_id to be overwritten.
The only way to capture all bind variables is to perform hard-parse on each execution. The easy way to achieve this is to purge the cursor from heap after it has been executed as following:
-- get sqlAddr and hashVal from v$sqlarea
SYS.dbms_shared_pool.purge(SqlAddr||', '||hashVal,'c',127); -- 127 is a bitmask for heaps 0~7 to be freed
http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_shared_pool.htm#CHDCBEBB
After that you can rely on v$sql_bind_capture for mapping bind variables.