From oracle-l-bounce@freelists.org Wed May 5 14:14:44 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i45JETk14057 for ; Wed, 5 May 2004 14:14:39 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i45JEJ614018 for ; Wed, 5 May 2004 14:14:29 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6909572D255; Wed, 5 May 2004 14:05:10 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21596-35; Wed, 5 May 2004 14:05:10 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A63CF72D0AD; Wed, 5 May 2004 14:05:09 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Wed, 05 May 2004 14:03:56 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 85C0572D14D for ; Wed, 5 May 2004 14:03:55 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21691-05 for ; Wed, 5 May 2004 14:03:55 -0500 (EST) Received: from deet.btinternet.com (deet.btinternet.com [194.73.73.29]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 33E1D72D114 for ; Wed, 5 May 2004 14:03:55 -0500 (EST) Received: from [217.42.79.77] (helo=Primary) by deet.btinternet.com with smtp (Exim 3.22 #25) id 1BLRto-0003el-00 for oracle-l@freelists.org; Wed, 05 May 2004 20:17:44 +0100 Message-ID: <016001c432d5$a52bcaf0$7102a8c0@Primary> From: "Jonathan Lewis" To: References: <6.1.0.6.2.20040505130500.02a28558@pop.centrexcc.com> Subject: Re: Wrong results using decode when db upgraded to 9205 Date: Wed, 5 May 2004 20:17:45 +0100 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 4489 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: jonathan@jlcomp.demon.co.uk Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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" To: 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@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@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 -----------------------------------------------------------------