Geek asks and answers

Oracle pl / sql - Get sql_id from dbms_sql

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

Here is my unsuccessful 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 it possible?

2) Is there a better approach?

Additional information - 2/17/2014

I am really after sql_id and child_number.

dbms_scheduler dbms_sql " ". - sql_id child_number - . ( - , dbms_xplan.display_cursor (sql_id, sql_child)). , Enterprise Manager, / sqlplus, [, , ].

, , - . sql_id "" /. v $sql, . - , v $session, dbms_sql - Tom Kyte.

2/21/2014

:

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

. .

+3
1

.

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 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;
/
+2

More articles:


All Articles