From oracle-l-bounce@freelists.org Fri Mar 19 15:26:58 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JLQwE14613 for ; Fri, 19 Mar 2004 15:26:58 -0600 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 i2JLQwo14608 for ; Fri, 19 Mar 2004 15:26:58 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E26C7390A62; Fri, 19 Mar 2004 16:25:12 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 16:24:09 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from enhbgsmtp03.state.pa.us (ENHBGSMTP03.state.pa.us [206.224.21.17]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B890F390A1B for ; Fri, 19 Mar 2004 16:24:08 -0500 (EST) Received: from ENHBGPRI11.PA.LCL ([206.224.21.35]) by enhbgsmtp03.state.pa.us with Microsoft SMTPSVC(5.0.2195.6713); Fri, 19 Mar 2004 16:30:29 -0500 X-MimeOLE: Produced By Microsoft Exchange V6.0.6487.1 content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis Subject: RE: Dynamic SQL question Date: Fri, 19 Mar 2004 16:30:29 -0500 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Dynamic SQL question Thread-Index: AcQN+VnneAknKfm+SKmGxS3C5qH3HwAAAINQ From: "Freeman, Donald" To: X-OriginalArrivalTime: 19 Mar 2004 21:30:29.0402 (UTC) FILETIME=[66AF9FA0:01C40DF9] X-archive-position: 1312 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dofreeman@state.pa.us Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l Whoops, you're right. Wild stab missed! Sorry! -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Deanna Schneider Sent: Friday, March 19, 2004 4:33 PM To: oracle-l@freelists.org Subject: Re: Dynamic SQL question Nope. It doesn't even compile then. It's not "inside" v_update, it's ending the assignment block of v_update. ----- Original Message ----- From: "Freeman, Donald" To: Sent: Friday, March 19, 2004 3:26 PM Subject: RE: Dynamic SQL question > I don't think there is supposed to be a ; inside v_update. Take it out and see if it runs. > > -----Original Message----- > From: oracle-l-bounce@freelists.org > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Deanna Schneider > Sent: Friday, March 19, 2004 4:28 PM > To: oracle-l@freelists.org > Subject: Dynamic SQL question > > > Hello, > I'm trying to write a procedure that includes dynamic sql (8.17). If I don't > use the dynamic sql, the query runs fine. If I do, it fails with a numeric > or value error (ORA-06512). > > Can anyone see what I'm missing? > This works: > UPDATE resourcegroup > SET lft = DECODE( (SIGN((lft/drop_left)-1) + SIGN((lft/drop_right)-1)), > 0, lft-1, 2, lft-2, lft), > rgt = DECODE( (SIGN((rgt/drop_left)-1) + SIGN((rgt/drop_right)-1)), > 0, rgt-1, 2, rgt-2, lft) > WHERE lft > drop_left; > > > This fails: > v_update := 'UPDATE '|| in_tablename || > 'SET lft = DECODE( (SIGN((lft/:1)-1) + SIGN((lft/:2)-1)), 0, > lft-1, 2, lft-2, lft), '|| > 'rgt = DECODE( (SIGN((rgt/:3)-1) + SIGN((rgt/:4)-1)), 0, rgt-1, > 2, rgt-2, lft) ' || > 'WHERE lft > :5'; > > EXECUTE IMMEDIATE v_update USING drop_left, drop_right, drop_left, > drop_right, drop_left; > > I'm just using the linewrap concantenation for use of readability for > testing. It fails when it's all on one line as well. > > Thanks. > -Deanna > > > ---------------------------------------------------------------- > 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 > ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------