You could try executing something like: ALTER SESSION SET EVENTS='6512 trace name errorstack forever,level 10'; that should give you not only the statement that failed, but also the bind variables, which should tell you where the problem is. On 03/19/2004 04:27:54 PM, Deanna Schneider wrote: > 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 -----------------------------------------------------------------