Home » SQL & PL/SQL » Client Tools » SP2-0552 error
SP2-0552 error [message #388602] Wed, 25 February 2009 10:55 Go to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
in the script below I keep getting error "SP2-0552 Bind variable &sv_num3 not declared"
SQL Plus does not allow me to input sv_num1 - 3 variables and I do not understand why. As a result I get an error that I did not declare &sv_num3...
Also,, why sv_num3 and not sv_num2 and sv_num, after all the server does not give me a chance to declare any of them???

thank you Smile
=====
set serveroutput on
declare
v_num1 NUMBER := &sv_num1;
v_num2 NUMBER := &sv_num2;
v_num3 NUMBER := &sv_num3;
v_result NUMBER;

begin
v_result := CASE
WHEN v_num1 IS NOT NULL THEN v_num1
ELSE
CASE WHEN v_num2 IS NOT NULL THEN v_num2
ELSE v_num3
END
END;
DBMS_OUTPUT.PUT_LINE ('Result: ' ||v_result);
END;
/
===========
Re: SP2-0552 error [message #388607 is a reply to message #388602] Wed, 25 February 2009 11:39 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
i figured it out.
the solution was to SET DEFINE ON
in SQl PLUS.
i just installed this Oracle XE 10 and it must have been SET DEFINE OFF by default which means it it did not regognise the '&' sign and that is why iwas not requested to input any of the variables to begin with!
Re: SP2-0552 error [message #388613 is a reply to message #388602] Wed, 25 February 2009 12:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste your execution like following (note the format):
SQL> declare
  2  v_num1 NUMBER := &sv_num1;
  3  v_num2 NUMBER := &sv_num2;
  4  v_num3 NUMBER := &sv_num3;
  5  v_result NUMBER;
  6  
  7  begin
  8  v_result := CASE
  9  WHEN v_num1 IS NOT NULL THEN v_num1
 10  ELSE
 11  CASE WHEN v_num2 IS NOT NULL THEN v_num2
 12  ELSE v_num3
 13  END
 14  END;
 15  DBMS_OUTPUT.PUT_LINE ('Result: ' ||v_result);
 16  END;
 17  /
Enter value for sv_num1: 1
Enter value for sv_num2: 1
Enter value for sv_num3: 1
Result: 1

PL/SQL procedure successfully completed.

Regards
Michel
Re: SP2-0552 error [message #388650 is a reply to message #388613] Wed, 25 February 2009 18:56 Go to previous messageGo to next message
happyjoshua777
Messages: 33
Registered: February 2009
Location: United States
Member
forgive me if i do not understand something, but i really cannot see the difference between you code and mine...
i made mind code work by 1st doing: SET DEFINE ON
that reset everything to its defaults and not '&' is recongnised (at least i think that this is why it works).
please let me know what your thoughts are Smile
Re: SP2-0552 error [message #388690 is a reply to message #388650] Wed, 25 February 2009 23:08 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no difference between codes and just copy and paste it and I asked you to post the same.

Regards
Michel
Previous Topic: Can't initialize OCI. Error -1
Next Topic: Way to identify DB connection in SQL developer script?
Goto Forum:
  


Current Time: Sun Dec 11 02:42:25 CST 2016

Total time taken to generate the page: 0.07642 seconds