that was just a mock up of what I wrote. I dont have '||' in the real
version. Its jut a typo here. Anyone know what is going on?
----- Original Message -----
From: "Rudy Zung" <rzung_at_printcafe.com>
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
Sent: Monday, July 21, 2003 5:24 PM
Subject: RE: need help with execute immediate from a script
> Your "using v_var1,v_var2" shouldn't be appended to the
> string that represents the dynamic SQL to execute;
> the "using..." is part of the syntac for execute immediate.
> An Oracle error message being what they are, they don't
> always highlight the correct cause of fault, but will
> identify some other error located close to where the real
> fault lies.
>
> For your solution, remove the "||" that occurs after
> ":2"
>
> It is also unlikely that you can select "*" into
> TOTAL, which has been declared as a NUMBER. Consider
> "count(*)" perhaps?
>
> -----Original Message-----
> Sent: Monday, July 21, 2003 4:59 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Im calling a script that uses dynamic sql. Im passing in a value as well.
I
> keep getting
>
> SP2-0552: Bind variable "2" not declared.
>
> Here is a code snipped
>
> declare
> v_var1 Varchar2(30) := 'TEST';
> v_var2 VARCHAR2(30) := '&1';
> total number;
> begin
>
> execute immediate ' Select * ' ||
> ' from user_objects '||
> ' where object_name = :1 '||
> ' or object_name = :2 '||
> using v_var1,v_var2
> into total;
>
> end;
> /
>
> I call it as follows:
>
> @script HELLO
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: <rgaffuri_at_cox.net
> INET: rgaffuri_at_cox.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Rudy Zung
> INET: rzung_at_printcafe.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Mon Jul 21 2003 - 15:34:24 CDT
