Re: Wrong results using decode when db upgraded to 9205

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 5 May 2004 20:17:45 +0100
Message-ID: <016001c432d5$a52bcaf0$7102a8c0@Primary>

Quick test:

alter session set cursor_sharing = force;

select count(decode(n1,3,1,2,null,4,1,6,null,null)) from t1
where decode(n1,6,3,null) = 2;

Text in v$sql
select
count(decode(n1,:"SYS_B_0",:"SYS_B_1",:"SYS_B_2",null,:"SYS_B_3",:"SYS_B_4", :"SYS_B_5",null,null)) from t1 where
decode(n1,:"SYS_B_6",:"SYS_B_7",null) = :"SYS_B_8"

9.2.0.1

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Optimising Oracle Seminar
http://www.jlcomp.demon.co.uk/seminar.html

June 2004 UK Manchester
July 2004 Iceland
July 2004 USA California
Aug 2004 USA North Carolina
Sept 2004 UK Manchester
Sept 2004 USA NYC
Oct 2004 USA Boston

Original Message ----- From: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Wednesday, May 05, 2004 8:05 PM Subject: RE: Wrong results using decode when db upgraded to 9205

I don't have a 9.2.0.5 instance to test this, but in testing on 9.2.0.4 on Windows2000, I did not get the rewrite to bind variables inside the decode function when setting cursor_sharing to force or similar on a session level. Maybe Oracle "fixed" that in 9.2.0.5 but it is not workin.

Can you check in your 9.2.0.2 system if you get the bind variable substitution with cursor_sharing=force/similar ?

At 10:59 AM 5/5/2004, you wrote:
>Wolfgang ...
>
>It looks like this ...=20
>
>decode(:"SYS_B_08", :"SYS_B_09", :"SYS_B_10", :"SYS_B_11") =3D =
>:"SYS_B_12"
regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed May 05 2004 - 14:14:44 CDT