RE: 10046 trace.. bind question
Date: Tue, 26 Aug 2008 16:51:17 +0200
Message-ID: <000601c9078b$319f6cb0$d100000a@iconos.be>
Ric,
FYI: the cause that oracle is not showing the values for the nvarchar2/nchar datatype bind variables is Bug 6358382. See Note:6358382.8. After applying the patch (only available on limited platforms) the value should be shown in hex format.
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer
-----Original Message-----
From: Ric Van Dyke [mailto:ric.van.dyke_at_hotsos.com]
Sent: zondag 24 augustus 2008 17:23
To: D'Hooge Freek; contactarul_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: 10046 trace.. bind question
Roger that on the nvarchr2 or varchar2, like you I have seen the "n" data types don't get a value printed in the trace so I figured that the data is being interpreted as a varchar2 not an nvarchar2.
It would be interesting to see the SQL, the explain plan and the stat lines of the SQL being run, it may well be that a data type conversion is taking place, although it seems odd since Oracle by default always converts the SRTING type (varchar2 in this case) to a NUMBER and not a NUMBER to a STRING. So I don't think this is an implicit conversion. But with out the code we are guessing.
- Ric
-----Original Message-----
From: freek D'Hooge [mailto:freek.dhooge_at_uptime.be]
Sent: Sunday, August 24, 2008 6:25 AM
To: Ric Van Dyke; contactarul_at_gmail.com; oracle-l_at_freelists.org
Subject: RE: 10046 trace.. bind question
Hi,
oacdty=01 could mean varchar2 or nvarchar2. There is a huge difference between the two as if a bind value is passed as a nvarchar2 and the column datatype is a varchar2 then a implicit conversion is done, meaning that an index will not be used.
I have found that (at least on 9i) in case of a nvarchar2 datatype the bind value was not shown in the trace file (but the value length was).
Regards,
Freek D'Hooge
Uptime
Oracle Database Administrator
email: freek.dhooge_at_uptime.be
tel +32(0)3 451 23 82
http://www.uptime.be
disclaimer
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ric Van Dyke
Sent: zondag 24 augustus 2008 3:24
To: contactarul_at_gmail.com; oracle-l_at_freelists.org Subject: RE: 10046 trace.. bind question
Yes a 1 is a varchar2 data type. You can see these data types in the:
OracleR Call Interface Programmer's Guide,
10g Release 2 (10.2)
Part Number B14250-02
Chapter 3, Data Types, table 3-1.
What ever program is sending that in is not sending it as a number.
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Aug 26 2008 - 09:51:17 CDT