From Julian Dyke

CREATE TABLE t101 (c1 NUMBER);
CREATE TABLE t102 (c1 NUMBER);
 
SET SERVEROUTPUT ON
 
DECLARE
  l_sc INTEGER;
  l_sid INTEGER;
  l_sqlid VARCHAR2(13);
  l_statement VARCHAR2(100);
BEGIN
  l_statement := 'INSERT INTO t102 SELECT * FROM t101';
 
  l_sc := DBMS_SQL.OPEN_CURSOR;
 
  DBMS_OUTPUT.PUT_LINE ('Cursor = '||TO_CHAR(l_sc));
 
  DBMS_SQL.PARSE (l_sc,l_statement,DBMS_SQL.V7);
 
  SELECT sid INTO l_sid FROM v$mystat WHERE ROWNUM = 1; 
 
  DBMS_OUTPUT.PUT_LINE ('SID = '||TO_NUMBER (l_sid));
 
  SELECT sql_id INTO l_sqlid FROM v$open_cursor
  WHERE sid = l_sid
  AND sql_text LIKE l_statement||'%';
 
  DBMS_OUTPUT.PUT_LINE ('SQLID = '||l_sqlid);
 
  DBMS_SQL.CLOSE_CURSOR (l_sc);
END;
/