Friday, July 13, 2012

How to See the Value of a PL/SQL Bind Variable

You can see the value of a PL/SQL bind variable like this:

Add the following line to your database’s parameter file:
event='10046 trace name context forever, level 4'

Bounce the database.

Run some PL/SQL which uses a bind variable:

SQL> set serveroutput on
SQL> variable bind_variable varchar2(3)
SQL> exec :bind_variable := 'SYS'

PL/SQL procedure successfully completed.

SQL> declare
  2   counter number;
  3  begin
  4   select count(*) into counter
  5    from dba_tables
  6    where owner = :bind_variable;
  7   dbms_output.put_line('Tables owned by SYS = '||counter);
  8  end;
  9  /
Tables owned by SYS = 322

PL/SQL procedure successfully completed.

SQL>

Look at the appropriate lines in the trace file to see the value of the bind variable you have just used (click on your browser's zoom button if necessary as the text is quite small):

=====================
PARSING IN CURSOR #1 len=173 dep=0 uid=22 oct=47 lid=22 tim=46193203462144 hv=1143714290 ad='2b36288'
declare
counter number;
begin
select count(*) into counter
  from dba_tables
  where owner = :bind_variable;
dbms_output.put_line('Tables owned by SYS = '||counter);
end;
END OF STMT
PARSE #1:c=0,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=2,tim=46193203462144
BINDS #1:
bind 0: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=32 offset=0
   bfp=1404b4520 bln=32 avl=03 flg=05
   value="SYS"
=====================
PARSING IN CURSOR #2 len=50 dep=1 uid=22 oct=3 lid=22 tim=46193203463168 hv=2267380128 ad='2ba90d0'
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER = :B1
END OF STMT
PARSE #2:c=0,e=1024,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=46193203463168
BINDS #2:
bind 0: dty=1 mxl=32(03) mal=00 scl=00 pre=00 oacflg=13 oacfl2=1 size=32 offset=0
   bfp=1404b48a0 bln=32 avl=03 flg=05
   value="SYS"
EXEC #2:c=0,e=0,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=2,tim=46193203463168
FETCH #2:c=50000,e=60416,p=0,cr=702,cu=0,mis=0,r=1,dep=1,og=2,tim=46193203523584
EXEC #1:c=50000,e=61440,p=0,cr=702,cu=0,mis=0,r=1,dep=0,og=2,tim=46193203523584
=====================

No comments: