RE: Bind values missing from SQL trace

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 16 Aug 2007 10:19:56 -0700
Message-ID: <04DDF147ED3A0D42B48A48A18D574C45071FE2D9@NT15.oneneck.corp>

Okay, I tested it out and here is what I found:

1. Yes, dty=96 for CHAR & NCHAR, dty=1 for VARCHAR2 & NVARHCAR2 (in my 10.2.0.2 trace file it's reported as oacdty)
2. In 10.2.0.2, the same problem exists - NCHAR and NVARCHAR2 bind variable values are not shown in the 10046 trace file
3. The bind variable values ARE captured in v$sql_bind_capture

The details of my testing are shown below.

Regards,
Brandon

SQL> create table t (ch char(10), nch nchar(10), varch varchar2(10), nvarch nvarchar2(10));

Table created.

SQL> insert into t values ('a','a','a','a');

1 row created.

SQL> var ch char(10);
SQL> var nch nchar(10);
SQL> var varch varchar2(10);
SQL> var nvarch nvarchar2(10);
SQL> exec :ch := 'a';

PL/SQL procedure successfully completed.

SQL> exec :nch := 'a';

PL/SQL procedure successfully completed.

SQL> exec :varch := 'a';

PL/SQL procedure successfully completed.

SQL> exec :nvarch := 'a';

PL/SQL procedure successfully completed.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> select * from t where ch=:ch and nch=:nch and varch=:varch and nvarch=:nvarch;

CH NCH VARCH NVARCH
---------- ---------- ---------- ---------- a a a a

BINDS #8:
kkscoacd
 Bind#0

  oacdty=96 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=128 off=0
  kxsbbbfp=1104b2350  bln=32  avl=10  flg=05
  value="a         "

 Bind#1
  oacdty=96 mxl=32(20) mxlc=10 mal=00 scl=00 pre=00   oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=32   kxsbbbfp=1104b2370 bln=32 avl=20 flg=01   value=""
 Bind#2

  oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=31 siz=0 off=64
  kxsbbbfp=1104b2390  bln=32  avl=01  flg=01

  value="a"
 Bind#3
  oacdty=01 mxl=32(20) mxlc=10 mal=00 scl=00 pre=00   oacflg=03 fl2=1000010 frm=02 csi=2000 siz=0 off=96   kxsbbbfp=1104b23b0 bln=32 avl=02 flg=01   value=""

SQL> select name, position, datatype, datatype_string, max_length, value_string from v$sql_bind_capture where sql_id = 'cms29hwt5ga23';

NAME                             POSITION   DATATYPE DATATYPE_STRING

MAX_LENGTH VALUE_STRI

------------------------------ ---------- ---------- ---------------
---------- ----------


:CH                                     1         96 CHAR(32)

32 a


:NCH                                    2         96 NCHAR(32)

32 a


:VARCH                                  3          1 VARCHAR2(32)

32 a


:NVARCH                                 4          1 NVARCHAR2(32)

32 a

-----Original Message-----
From: Allen, Brandon
Sent: Thursday, August 16, 2007 9:34 AM
To: 'norman.dunbar_at_environment-agency.gov.uk'; Rich Jesse; oracle-l_at_freelists.org
Subject: RE: Bind values missing from SQL trace

Great work Norman, thanks. I haven't actually tested this yet, but after reading Jonathan's case including all the comments, it looks like both varchar & nvarchar will be reported as dty=1 in the trace file and both char and nchar will be reported as dty=96.

Regards,
Brandon

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Dunbar, Norman

Interestingly, the trace shows both binds as having the same data type (dty=1)

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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

Received on Thu Aug 16 2007 - 12:19:56 CDT