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 ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Monday, July 21, 2003 5:24 PM
> 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: <[EMAIL PROTECTED] > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: Ryan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).