IT Tutorial IT Tutorial | Oracle DBA | SQL Server, Goldengate, Exadata, Big Data, Data ScienceTutorial
I got ” SP2-0552 Bind variable not declared ” error in Oracle database.
SP2-0552 Bind variable not declared
Details of error are as follows.
SP2-0552: Bind variable not declared. Cause: The specified bind variable was not declared. Action: Run the VARIABLE command to check that the bind variables you used in your SQL statement exist. Before running a SQL statement with bind variables, you must use the VARIABLE command to declare each variable.
SQL> declare mgr varchar2(3); begin :mgr := '001'; end; / SP2-0552: Bind variable "MGR" not declared. Scenario 2: SQL> declare mgr varchar2(5); begin select '002' into mgr from dual; end; / PL/SQL procedure successfully completed. Although the above does not produce an error, the following command will when an attempt to display the value of the mgr variable is done. SQL> print mgr; SP2-0552: Bind variable "MGR" not declared.
Bind variable not declared.
This SP2-0552 error is related to the specified bind variable was not declared.
Run the VARIABLE command to check that the bind variables you used in your SQL statement exist. Before running a SQL statement with bind variables, you must use the VARIABLE command to declare each variable.
Before explaining the cause of the error, here is background information on bind variables per the SQL*Plus manual.
Bind variables are variables you create in SQL*Plus and then reference in PL/SQL or SQL. If you create a bind variable in SQL*Plus, you can use the variable as you would a declared variable in your PL/SQL subprogram and then access the variable from SQL*Plus. You can use bind variables for such things as storing return codes or debugging your PL/SQL subprograms.
Creating Bind Variables
You create bind variables in SQL*Plus with the VARIABLE command. For example
VARIABLE ret_val NUMBER
This command creates a bind variable named ret_val with a datatype of NUMBER. See the VARIABLE command for more information. (To list all bind variables created in a session, type VARIABLE without any arguments.)
Referencing Bind Variables
You reference bind variables in PL/SQL by typing a colon (:) followed immediately by the name of the variable. For example
:ret_val := 1;
Displaying Bind Variables
To display the value of a bind variable in SQL*Plus, you use the SQL*Plus PRINT command. For example:
PRINT RET_VAL
In scenario 2, the anonymous PL/SQL block completed successfully meaning no error was returned. This is because the variable MGR being referenced is the locally defined variable and not the bind variable as no colon (:) is used. After the code successfully completed, the error was visible when executing the PRINT command. This is because the PRINT command is used to display bind variables and not valid to use in an attempt to display locally defined variables.
In both scenarios, the error was returned when reference to the MGR bind variable was made which is because the creation of the bind variable MGR was never done. Per the above information from the SQL*Plus manual to define a bind variable the VARIABLE command is used which was never done in any of the two scenarios.
The following will re-execute the two scenarios correctly, but before doing so, the bind variable MGR will need to be created.
SQL> VARIABLE MGR VARCHAR2(10) Scenario 1: SQL> declare mgr varchar2(3); begin :mgr := '001'; end; / PL/SQL procedure successfully completed.
Now that the anonymous block worked, the MGR bind variable value can be displayed.
SQL> PRINT MGR MGR -------------------------------- 001
Scenario 2: was changed to reference the bind variable instead of the locally defined variable as per the added colon (:).
SQL> declare mgr varchar2(5); begin select '002' into :mgr from dual; end; / PL/SQL procedure successfully completed.
Using the PRINT command now displays the new value.
SQL> PRINT MGR MGR -------------------------------- 002
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
