SP2-0552 Bind variable not declared

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.

Using Bind Variables
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 1, although the variable MGR is defined “mgr varchar2(3);” after the DECLARE statement, it is defined as a local variable within the anonymous PL/SQL block and the variable being set by the “:mgr := ‘001’;” statement is actually a bind variable as dictated by the colon (:).

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 )

 

 189 views last month,  4 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply