Oraclue

Oracle internals, debugging and undocumented features

Looking at Oracle cursor from oposite side

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 —–

Advertisements

2 responses to “Looking at Oracle cursor from oposite side

  1. coskan March 18, 2009 at 10:03 pm

    this is awesome Miladin. thank you for sharing this

  2. Brian Foroud June 18, 2009 at 9:26 pm

    Hey Miladin, I was doing a cursor search and accidentally stumbled over your blog. So how is it going?
    I called your house once, and left a message. anyhow, send me a note once in a while at foroudb@yahoo.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: