1
declare 
 v_cursor number;
 stmt varchar2(2000);
 s varchar2(40);
 cursor cc1 is select voc.SQL_ID from v$open_cursor voc left join v$sql vs on voc.SQL_ID=vs.SQL_ID  where voc.SID in(select sid from v$mystat) and to_char(substr(vs.SQL_FULLTEXT,1,2000)=stmt;
begin
delete from table1;
v_cursor:=dbms_sql.open_cursor;
stmt:='inset into table2 (column1,column2) values(''a'',''b'')';
dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
insert into table1 select * from tables(dbms_xplan.display
open cc1;
loop
fetch cc1 into s;
exit;
end loop;
close cc1;
insert into table1 select * from(dbms_xplan.display_cursor(s,0,'ALL'));

I execute this and select from the table1 but the record in table1 is tells me:

it could also be that the plan is no longer in cursor cache(check v$sql_plan).

But if I change the stmt to update statement and execute, the record in table1 is correct. So i want to know where can I get the analyze about the insert statement.

1 Answer 1

1

The child_number argument to dbms_xplan.display_cursor needs to be taken from v$sql as well, it's not necessarily zero. (I'm not exactly sure what influences that number, but dropping and recreating table2 in your example gives me a new child_number each time).

Here's a modified version of your procedure (fixed a few typoes in what you have above):

DECLARE
  v_cursor NUMBER;
  stmt     VARCHAR2(2000);
  s        VARCHAR2(40);
  cn       NUMBER;
  CURSOR cc1
  IS
    SELECT voc.SQL_ID, vs.child_number
    FROM v$open_cursor voc
    LEFT JOIN v$sql vs
    ON voc.SQL_ID  =vs.SQL_ID
    WHERE voc.SID IN (SELECT sid FROM v$mystat)
      AND TO_CHAR(SUBSTR(vs.SQL_FULLTEXT,1,2000))=stmt;
BEGIN
  DELETE FROM table1;
  v_cursor:=dbms_sql.open_cursor;
  stmt    :='insert into table2 values (''a'',''b'')';
  dbms_sql.parse(v_cursor,stmt,dbms_sql.native);
  OPEN cc1;
  LOOP
    FETCH cc1 INTO s, cn;
    EXIT;
  END LOOP;
  CLOSE cc1;
  -- To inspect the child number:
  -- dbms_output.put_line(s || ' '|| cn); 
  INSERT INTO table1 SELECT * FROM table(dbms_xplan.display_cursor(s,cn,'ALL'));
END;
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.