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" <dofreeman@xxxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> 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@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Deanna Schneider > Sent: Friday, March 19, 2004 4:28 PM > To: oracle-l@xxxxxxxxxxxxx > 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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //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@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------