PLS-00201: identifier must be declared

The PLS-00201: identifier must be declared error happens when the identifier is used without being declared in the PL/SQL code. Oracle Variables and other identifiers must either be declared or made available before they’ve been used. The variable is used in the code, but it isn’t declared in the database or hasn’t been given permission to use it. It throws an error PLS-00201: identifier must be declared while calling the database identifier.

In the declaration block, the oracle variables should be declared. In PL/SQL code, the variable can be used. The variable cannot be used by PL/SQL code if it is not declared. The variable is not available. The value can neither assign to the variable nor read from the variable. The identifier is not declared and is used in the PL/SQL code, so Oracle will throw an error PLS-00201: identifier must be declared.



Exception

The stack trace for the PLS-00201: identifier must be declared error will look like this. The oracle error would show the name of the identifier that it could not locate in the database, was inaccessible, or did not have authorization to execute.

Error report -
ORA-06550: line 3, column 26:
PLS-00201: identifier 'EMPNAME' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"


Cause

The identifier cannot be used if it has not been declared in the Oracle database. The memory needed to store and retrieve the value will be created by the identifier declaration. Value cannot assign or retrieve from the variable if the identifier is not declared. The error would be thrown if you use a variable that is not declared or defined in the Oracle database.



Problem

If an identifier is used without being declared in the PL/SQL code, the identifier would not be available in the database. Until the identifier is declared, it can not be used in the PL/SQL code. Otherwise, the identifier would throw an error, prompting you to declare it.

declare
begin
    dbms_output.put_line(empname);
end;

Output

declare
begin
    dbms_output.put_line(empname);
end;
Error report -
ORA-06550: line 3, column 26:
PLS-00201: identifier 'EMPNAME' must be declared
ORA-06550: line 3, column 5:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Solution 1

The identifier in the code may be misspelled. If the variable is declared, the identifier is misspelled. The spelling of the identifier should be corrected. If the identifier is not found in the declaration, it must be declared. If it hasn’t already been declared, the identifier must be declared.

declare
    empname varchar2(10) :='yawin';
begin
    dbms_output.put_line(empname);
end;

Output

Yawin
PL/SQL procedure successfully completed.


Solution 2

It’s likely that the thing you’re searching for isn’t available, or that it’s misspelled. The error will be thrown when you call the members of the referenced identifier. The error would be thrown if the system packages are misspelled or not visible. The system package’s spelling needs to be changed.

declare
    empname varchar2(10) :='yawin';
begin
    dbms_ooutput.put_line(empname);
end;

Exception

declare
    empname varchar2(10) :='yawin';
begin
    dbms_ooutput.put_line(empname);
end;
Error report -
ORA-06550: line 4, column 5:
PLS-00201: identifier 'DBMS_OOUTPUT.PUT_LINE' must be declared
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

Solution

declare
    empname varchar2(10) :='yawin';
begin
    dbms_output.put_line(empname);
end;

Output

Yawin
PL/SQL procedure successfully completed.


Solution 3

It’s likely that the referring identifier object isn’t accessible. When the identifier is run, it is unable to locate the identifier’s definition. It is possible that the identifier will not be created or that it will be deleted. It’s likely that the identifier name is misspelled. Check that the identifier has been created and is usable. The name of the identifier reference should be right.

exec printmyname;

Exception

BEGIN printmyname; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PRINTMYNAME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution

create procedure printmyname as
begin
dbms_output.put_line('yawin');
end;
set serveroutput on
exec printmyname;

Output

Yawin
PL/SQL procedure successfully completed.


Solution 4

It’s possible that the identifier object in the Oracle database doesn’t have permission to run. Permission needs to be granted. To receive this authorization, you may need to contact your database administrator.

create procedure myproject.printmyname as
begin
dbms_output.put_line('yawin');
end;
set serveroutput on
exec printmyname;

Exception

BEGIN printmyname; END;
Error report -
ORA-06550: line 1, column 7:
PLS-00201: identifier 'PRINTMYNAME' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Solution

grant execute on myproject.printmyname to yawin; 
set serveroutput on
exec myproject.printmyname;

Output

Yawin
PL/SQL procedure successfully completed.



Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *