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

=====================