Oracle pl/sql - Retrieve sql_id from dbms_sql

Question

Using dbms_sql.execute, is it possible to programatically retrieve the sql_id of the query being executed?

Here is my failed attempt:

declare
  v_sql_text varchar2(128) := 'select 123 from dual';
  v_cursor INTEGER;
  v_ret number;

  v_sql_id varchar2(13) := null;
BEGIN
  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, v_sql_text, dbms_sql.native);   

  -- ==================================================
  -- attempt 1 - after parse
  select prev_sql_id
    into v_sql_id
    from v$session
    where audsid = sys_context('userenv', 'sessionid');

  dbms_output.put_line('after parse: ' || v_sql_id);
  -- ==================================================

  v_ret := dbms_sql.execute(v_cursor);

  -- ==================================================
  -- attempt 2 - after execute - this doesn't seem to work either
  --select prev_sql_id
  --  into v_sql_id
  --  from v$session
  --  where audsid = sys_context('userenv', 'sessionid');

  --dbms_output.put_line('after execute: ' || v_sql_id);
  -- ==================================================

  dbms_sql.close_cursor(v_cursor);
END;
/

1) Is this even possible?

2) Is there a better approach?

Additional Information - 2/17/2014

I'm really after the sql_id and child_number.

We use a combination of dbms_scheduler and dbms_sql to run reports "in the background". My intent is to capture and store two pieces of information - sql_id and child_number - with each report run. I'd like to capture this information for diagnostic purposes (example - it is possible to display the cached execution plan of a specific query run by using dbms_xplan.display_cursor(sql_id, sql_child)). This way we would not be reliant on Enterprise Manager, nor would we have to copy/paste the query text into sqlplus to see the plan [and very likely end up with a hard parse and potentially different plan].

One thing that adds complication is our use of bind variables. For the same sql_id, there could be many different "sub-plans" depending on bind variable peeking / histograms. For this reason, I'm hesitant to query directly against v$sql to get this information. I would rather try and find some kind of hard link using v$session, dbms_sql, or some other Tom Kyte-style sorcery.

Update 2/21/2014

Working on a solution based on information from the following sources:

https://dba.stackexchange.com/questions/55609/how-to-use-the-dbms-sql-to-get-the-analyze-for-insert-into-statement http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:40832696008013

I will post a concise example once it is cleaned up. Thanks for the replies.

Answer 1

This works for me.

declare
  v_sql_text varchar2(128) := 'select 112233 from dual';
  v_cursor INTEGER;
  v_ret number;

  v_sql_id varchar2(13) := null;
BEGIN
  v_cursor := dbms_sql.open_cursor;
  dbms_sql.parse(v_cursor, v_sql_text, dbms_sql.native);   
  v_ret := dbms_sql.execute(v_cursor);
  dbms_sql.close_cursor(v_cursor);

  --To save time lets only look 5 minutes back
  --last_load_time is a varchar that's why we have to use to_date
  select sql_id into v_sql_id from v$sql
  where dbms_lob.compare(sql_fulltext, v_sql_text) = 0
  and to_date(last_load_time,'YYYY-MM-DD/HH24:MI:SS') > sysdate - (5/1440);

  dbms_output.put_line('v_sql_id: ' || v_sql_id);

END;
/