OUT bind variable content in a 10046 level 12 trace?

From: Kim Berg Hansen <kibeha_at_gmail.com>
Date: Tue, 31 Jan 2017 10:37:16 +0100
Message-ID: <CA+S=qd0xEW_DsLhpyct86dwJMxTMP_xiYG8RM=+OY2Y6u21B3Q_at_mail.gmail.com>

Hi, List

I have an insert in PL/SQL:

declare

   v_deptno dept.deptno%type;
   v_dname  dept.dname%type := 'Development';
   v_loc    dept.loc%type   := 'Hamburg';

begin

   insert into dept (deptno, dname, loc)    values (dept_seq.nextval, v_dname, v_loc)    returning deptno into v_deptno;
end;
/

In my 10046 trace I find these lines for the INSERT cursor:

PARSING IN CURSOR #139843088636704 len=102 dep=1 uid=109 oct=2 lid=109 tim=48270699525 hv=324646403 ad='773726c8' sqlid='f9mwj2w9pmdh3' INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES (DEPT_SEQ.NEXTVAL, :B2 , :B1 ) RETURNING DEPTNO INTO :O0
END OF STMT
PARSE
#139843088636704:c=0,e=256,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=48270699524

BINDS #139843088636704:
 Bind#0
  oacdty=01 mxl=32(14) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=01 csi=873 siz=32 off=0   kxsbbbfp=7f2fc6b35dc0 bln=32 avl=11 flg=09   value="Development"
 Bind#1
  oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=01 csi=873 siz=32 off=0   kxsbbbfp=7f2fc6b35df8 bln=32 avl=07 flg=09   value="Hamburg"
 Bind#2
  oacdty=02 mxl=22(21) mxlc=00 mal=00 scl=00 pre=00   oacflg=13 fl2=206001 frm=00 csi=00 siz=24 off=0   kxsbbbfp=7f2fc6b35e38 bln=21 avl=00 flg=09

WAIT #139843088636704: nam='db file sequential read' ela= 872 file#=10
block#=129 blocks=1 obj#=92476 tim=48270727211
WAIT #139843088636704: nam='db file sequential read' ela= 778 file#=10
block#=128 blocks=1 obj#=92476 tim=48270728060
WAIT #139843088636704: nam='db file sequential read' ela= 12 file#=10
block#=139 blocks=1 obj#=92477 tim=48270728253

EXEC
#139843088636704:c=26000,e=28749,p=7,cr=258,cu=9,mis=1,r=1,dep=1,og=1,plh=830852307,tim=48270728323
STAT #139843088636704 id=1 cnt=1 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL DEPT (cr=35 pr=3 pw=0 time=5036 us)'

STAT #139843088636704 id=2 cnt=1 pid=1 pos=1 obj=112383 op='SEQUENCE
 DEPT_SEQ (cr=34 pr=0 pw=0 time=2855 us)'
CLOSE #139843088636704:c=0,e=2,dep=1,type=3,tim=48270728451

In the BINDS section Bind#2 (:O0) hasn't got any value yet, of course, it is an OUT bind variable and won't be assigned a value until EXEC time. In the STAT lines after EXEC I can see that it did access the sequence during the insert, but not what value it got.

*Question:*

Is it possible to get the value of the OUT bind variable (the RETURNING clause) written to the trace file somehow? Maybe using a different trace event than 10046?

Thanks.

Regards

Kim Berg Hansen

http://www.kibeha.dk
kibeha_at_kibeha.dk
_at_kibeha <http://twitter.com/kibeha>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jan 31 2017 - 10:37:16 CET