From oracle-l-bounce@freelists.org Fri Mar 19 15:34:41 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2JLYft15295 for ; Fri, 19 Mar 2004 15:34:41 -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 i2JLYeo15289 for ; Fri, 19 Mar 2004 15:34:41 -0600 Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 59CE1390BFF; Fri, 19 Mar 2004 16:32:55 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 19 Mar 2004 16:31:49 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from mail2.quallaby.com (mail2.quallaby.com [12.159.134.103]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 10212390977 for ; Fri, 19 Mar 2004 16:31:49 -0500 (EST) Received: from quallaby.com (dhcp68.quallaby.com [192.168.64.68]) by mail2.quallaby.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2653.13) id GPV1SJGL; Fri, 19 Mar 2004 16:39:00 -0500 Message-ID: <405B683B.70908@quallaby.com> Date: Fri, 19 Mar 2004 16:38:03 -0500 From: Tim Johnston User-Agent: Mozilla Thunderbird 0.5 (Windows/20040207) X-Accept-Language: en-us, en MIME-Version: 1.0 To: oracle-l@freelists.org Subject: Re: Dynamic SQL question References: <03d101c40df9$c52683f0$9b2c6880@cets.local> In-Reply-To: <03d101c40df9$c52683f0$9b2c6880@cets.local> Content-Type: text/plain; charset=us-ascii; format=flowed X-archive-position: 1316 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: tjohnston@quallaby.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l What's the definition of the v_update field? Deanna Schneider wrote: >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 >----------------------------------------------------------------- > > -- Regards, Tim Johnston Tel: 978-322-4226 Fax: 978-322-4100 ---------------------------------------------------------------- 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 -----------------------------------------------------------------