How to use the dbms_sql to get the analyze for insert into statement

cursorsoracleplsql

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.

Best Answer

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

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

The MSDN/BOL topic for DECLARE CURSOR contains example syntax for cursor usage. That said, as I had 5 minutes to spare on the train this morning...

-- Create temporary table for Business Units (replace with the "real" table)
DECLARE @BusinessUnit TABLE (BU NVARCHAR(MAX))

INSERT @BusinessUnit
SELECT 'India' UNION SELECT 'International' UNION SELECT 'US' UNION SELECT 'UK'

DECLARE @K TABLE (BU NVARCHAR(MAX),K NVARCHAR(MAX),Y MONEY,A MONEY,D MONEY,YP MONEY)
DECLARE @FY INT
DECLARE @BU NVARCHAR(MAX)

SET @FY = 2012

DECLARE BU_cursor CURSOR FAST_FORWARD FOR
SELECT BU FROM @BusinessUnit

OPEN BU_cursor
FETCH NEXT FROM BU_cursor INTO @BU

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @K (BU, K, Y, A, D, YP)
    EXEC dbo.SP_Report @FY, @BU

    FETCH NEXT FROM BU_cursor INTO @BU
END

CLOSE BU_cursor
DEALLOCATE BU_cursor

SELECT * FROM @K

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

It wouldn't perform as well, but to meet your requirements given your limitations, you could use a pipelined result set. Both your original procedure and your new procedure could then use the function. The original one would return the entire results, and the new procedure would wrap the function in a larger query that does the GROUP BY. In this way the core query code would not be duplicated. Here is a pipelined result set example along with two queries using it:

create or replace PACKAGE Example AUTHID Definer AS

Type tRow Is Record (
     F1 Date
   , F2 Date
   );
Type tTable Is Table of tRow;
Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined;

END Example;
/


create or replace PACKAGE BODY Example AS

Function PipelineResult(pDate In Date DEFAULT sysdate) Return tTable Pipelined AS
   vYesterday Date := trunc(sysdate-1);
Begin
  For vRow IN (
      SELECT pDate, vYesterday FROM DUAL
      UNION ALL
      SELECT pDate, vYesterday-1 FROM DUAL
      UNION ALL
      SELECT pDate+1, vYesterday FROM DUAL
  )
  Loop
    Pipe Row(vRow);
  End Loop;
End;

END Example;
/


SELECT * FROM TABLE(Example.PipelineResult);

SELECT F1, count(*) FROM TABLE(Example.PipelineResult) GROUP BY F1;

Best Answer

Related Solutions

Sql-server – Declaring cursor to loop over some values in SQL Server 2008 R2

How to embed a sub-prodecure call in a SELECT statement in an Oracle 11g PL/SQL stored procedure

Related Question