Ошибка ora 06550 pls 00201

Проблемы

«ORA-06550: строка 1, столбец 7: PLS-00201: идентификатор» Диаграмма должна быть объявлена как ORA-06550: строка 1, столбец 7 PL/SQL: оператор проигнорирован «Эта ошибка возникает при настройке компании FRx в качестве значения по умолчанию.

Причина

Таблицы индексов на стороне сервера (также называемые таблицами FRL) не заполнены.

Решение

Чтобы проверить это, выполните следующий запрос для базы данных Ross: SELECT * FROM frl_acct_code если запрос не возвращает какие-либо записи (таблица пуста), необходимо заполнить таблицы индексов на стороне сервера. Запустите сценарий FRX_Conversion. DML. Этот сценарий должен выполняться с помощью Gembase. Для получения дополнительной помощи по этому процессу обратитесь в службу поддержки Ross.

Ссылки

I have two databases; one is in local server;

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
"CORE   11.1.0.7.0  Production"
TNS for 64-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

The other one is a VM :

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
"CORE   11.2.0.4.0  Production"
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

Everything else about these servers are identical.
I am DBA for both of the servers. All the tables and stored procedures and are working fine in both databases but one stored procedure is not working when Asp.net tries to connect to it.

When I run my Asp.net app that is connecting to the VM database I get the following error telling me that stored procedure must be declared :

Message: Sys.WebForms.PageRequestManagerServerErrorException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'GETINFO' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I open my SQL Developer and debug the procedure on the VM database and I get the desired values.
It is only happening in .Net app if I try to get that particular stored procedure. Everything else, being all the other stored procedures, are working fine.

What I have done to fix the issue;

1. Changed the name of the procedure (didnt work)
2. Checked the schema to make sure all the tables and stored procedures belongs to correct user (they do belong to correct user)
3. I granted access to user even though stored procedure created by that user.
4. I dropped and recreated stored procedure
5. I used Myuser.storedprocedure name notation it didnt work

What I don’t understand is that exact same code is working on local network, and also that I can debug the stored procedure via Oracle SQL Developer.

I can share the stored product here but it is really, really long.

How can I fix this?

PLS-00201 means that the identifier you specified in the statement has never been declared, so it cannot be used by the stored procedure.

In this post, we’ll talk about some error patterns of PLS-00201.

1. Undeclared Variable
2. DBMS_SQL
3. DBMS_LOCK

Undeclared Variable

What is undeclared variable? Let’s see an example to make it clear.

SQL> set serveroutput on;
SQL> begin
  2    select first_name into v_fn from employees where last_name = 'Chen';
  3    dbms_output.put_line('The first name is: ' || v_fn);
  4  end;
  5  /
  select first_name into v_fn from employees where last_name = 'Chen';
                         *
ERROR at line 2:
ORA-06550: line 2, column 26:
PLS-00201: identifier 'V_FN' must be declared
ORA-06550: line 2, column 31:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 3, column 49:
PLS-00201: identifier 'V_FN' must be declared
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored

In the example, it found an identifier which is not declare anywhere in the programming unit.

Solution

In fact, the identifier is a local variable, we just forgot to declare it before using. Let’s declare the variable as a string.

SQL> declare
  2    v_fn varchar2(25);
  3  begin
  4    select first_name into v_fn from employees where last_name = 'Chen';
  5    dbms_output.put_line('The first name is: ' || v_fn);
  6  end;
  7  /
The first name is: John

PL/SQL procedure successfully completed.

The final result has been successful output.

DBMS_SQL

Some SYS’s packages are very common to PUBLIC to EXECUTE, such as DBMS_SQL, DBMS_LOB or UTL_FILE.

PLS-00201: identifier 'DBMS_SQL' must be declared

Solution

In such case, the right privileges may be gone or revoked from PUBLIC. I have provided the solution in the post: How to Resolve PLS-00201: identifier ‘DBMS_SQL’ must be declared.

DBMS_LOCK

DBMS_LOCK does not open to PUBLIC, it should be granted to specific user to execute whenever required.

SQL> begin
  2    dbms_lock.sleep(10);
  3  end;
  4  /
  dbms_lock.sleep(10);
  *
ERROR at line 2:
ORA-06550: line 2, column 3:
PLS-00201: identifier 'DBMS_LOCK' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored

Solution

We should grant EXECUTE privilege on the package to the user by SYS.

SQL> show user
USER is "SYS"
SQL> grant execute on dbms_lock to hr;

Grant succeeded.

Then we run the programming unit again.

SQL> begin
  2    dbms_lock.sleep(10);
  3  end;
  4  /

PL/SQL procedure successfully completed.

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.