Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Dynamic SQL question

Re: Dynamic SQL question

From: Tim Johnston <tjohnston_at_quallaby.com>
Date: Fri, 19 Mar 2004 16:44:47 -0500
Message-ID: <405B69CF.4080103@quallaby.com>


My last response was sparse so I decided to let you know what I was thinking... I'm guessing that the size of your v_update field is too small to store the string you are building... For example...

SQL> Create Table Tim ( Col1 Varchar2(10));

Table created.

SQL> Insert Into Tim Values ('test');

1 row created.

SQL> Commit;

Commit complete.

SQL> Declare
  2 v_update varchar2(20);
  3 v_table varchar2(20) := 'tim';
  4 Begin
  5 v_update := 'update '||v_table||' set col1 = ''value'' where col1 = ''test''';
  6 execute immediate v_update;
  7 End;
  8 /
Declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error ORA-06512: at line 5

SQL> Tim

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_at_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_at_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
-----------------------------------------------------------------
Received on Fri Mar 19 2004 - 15:41:13 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US