Miladin Modrakovic's Blog: Oraclue

We are all familiar with cursor flow:open ,parse, binds, define,execute, fetch,variable, close..

I have used Oracle errorstack at level 3 (level 2 + context area ) to dump cursor at different life stages…

This are only top part of cursors dump but you can produce your own traces and play with it.

First task is to have an open cursor. I will use  OPEN_CURSOR function.
SQL> oradebug setmypid
Statement processed.
SQL> variable a1 number
SQL> execute :a1  :=dbms_sql.open_cursor;

PL/SQL procedure successfully completed.
Now I am dumping a cursor:

SQL> oradebug dump errorstack 3
Statement processed.

 

I have opened trace file and looking for a string “Cursor Dump”
We can see that cursor in null state ( NULL=1 or state=NULL ) with other basic cursors information ( cursor’s number ,flag etc.)

—– Session Cursor Dump —–
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 1(0, 1, 64, 300)
 NULL=1 SYNTAX=0 PARSE=0 BOUND=0 FETCH=0 ROW=0
Cached frame pages(total, free):
 4k(3, 3), 8k(0, 0), 16k(0, 0), 32k(0, 0)
—– Session Open Cursors —–

—————————————-
Cursor#2(0x2aaaac081998) state=NULL curiob=(nil)
 curflg=45 fl2=0 par=(nil) ses=0xbf4fba90
Every SQL statement must be parsed by calling the PARSE Procedures.
Parsing the statement checks the statement’s syntax and associates it with the cursor in your program.

 

SQL> variable b1 varchar2(20)
SQL> execute dbms_sql.parse ( :a1 ,’select * from scott.emp where ename = :b1′,dbms_sql.native);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.
Go back to trace file.  PARSE=1 or state=PARSE
—– Session Cursor Dump —–
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 1(0, 1, 64, 300)
 NULL=0 SYNTAX=0 PARSE=1 BOUND=0 FETCH=0 ROW=0
Cached frame pages(total, free):
 4k(3, 3), 8k(0, 0), 16k(0, 0), 32k(0, 0)
—– Session Open Cursors —–

—————————————-
Cursor#2(0x2aaaac081998) state=PARSE curiob=0x2aaaac0a13f8
 curflg=45 fl2=0 par=(nil) ses=0xbf4fba90

 

I am suppliyng input data ( bind variables ) at runtime using BIND_VARIABLE Procedures
 
SQL> execute :b1 :=’SCOTT’

PL/SQL procedure successfully completed.

SQL> execute dbms_sql.bind_variable (:a1,’:b1′,:b1);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.

 

—– Session Cursor Dump —–
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 1(0, 1, 64, 300)
 NULL=0 SYNTAX=0 PARSE=0 BOUND=1 FETCH=0 ROW=0
Cached frame pages(total, free):
 4k(3, 3), 8k(0, 0), 16k(0, 0), 32k(0, 0)
—– Session Open Cursors —–

—————————————-
Cursor#2(0x2aaaac081998) state=BOUND curiob=0x2aaaac0a13f8
 curflg=4d fl2=0 par=(nil) ses=0xbf4fba90
Execute function will run sql statement:
SQL> variable d number
SQL> execute :d := dbms_sql.execute (:a1);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.
rows that satisfy the query are retreived

—– Session Cursor Dump —–
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 1(0, 1, 64, 300)
 NULL=0 SYNTAX=0 PARSE=0 BOUND=0 FETCH=1 ROW=0
Cached frame pages(total, free):
 4k(3, 1), 8k(0, 0), 16k(0, 0), 32k(0, 0)
—– Session Open Cursors —–

—————————————-
Cursor#2(0x2aaaac081998) state=FETCH curiob=0x2aaaac0a13f8
 curflg=4f fl2=0 par=(nil) ses=0xbf4fba90

 
and finaly close cursor

SQL> execute dbms_sql.close_cursor(:a1);

PL/SQL procedure successfully completed.

SQL> oradebug dump errorstack 3
Statement processed.
—– Session Cursor Dump —–
Current cursor: 0, pgadep=0

Open cursors(pls, sys, hwm, max): 0(0, 0, 64, 300)
 NULL=0 SYNTAX=0 PARSE=0 BOUND=0 FETCH=0 ROW=0
Cached frame pages(total, free):
 4k(8, 8), 8k(0, 0), 16k(0, 0), 32k(0, 0)
—– Session Open Cursors —–