Ora 06550 ошибка pls 00201

I’m connecting to an Oracle database as user1. Within the database, user2 exists and has a package pack1 which contains two stored procedures, proc1 and proc2.

I’m trying to call those procedures, but I’m getting the aforementioned error. The error mentions ‘type1’ that is defined in Types.

After some research, it was suggested to check privileges, however those seem to be fine. I’m using Oracle SQL Developer and when I click «grants» on the pack1 and type1, my user has both EXECUTE and DEBUG privileges.

I thought it was an error in the code, but that would throw a different error. I thought about creaing an synonym for the package as I have read that those can help, but I don’t have neccesary right to do so and before I ask, I wanted to exhaust all my options.

I have tried to use both my prepared call and a default one created by SQL Developer when I try to run those stored procedures. Interestingly, when I run my code, mentioned error is thrown. When I run the «default» code I get «Relative path in absolute URI» error.

Is there anything I could have missed?

This is the code I’m using to call the procedure.

DECLARE
ClientData type1;
BEGIN
pack1.proc1(param1,param2,ClientData);
FOR i IN 1..ClientData.LAST LOOP
DBMS_OUTPUT.PUT_LINE(ClientData(i).kid ||' '...;
--DBMS_OUTPUT.PUT_LINE(ClientData(i).portfolioName);
--DBMS_OUTPUT.PUT_LINE(ClientData(i).clientCategory);
--DBMS_OUTPUT.PUT_LINE(ClientData(i).typ_pf);
--DBMS_OUTPUT.PUT_LINE(ClientData(i). ptf_ccy);
END LOOP;
END;

EDIT 1: here is the full error:

ORA-06550: row 2, column 14:
PLS-00201: identifier 'SYSADMIN(user2).CLIENTDATAINSTRESB_A(type1)' must be declared
ORA-06550: row 2, column 14:
PL/SQL: Item ignored
ORA-06550: row 4, column 53:
PLS-00320: the declaration of the type of this expression is     incomplete or malformed
ORA-06550: row 4, column 1:
PL/SQL: Statement ignored
ORA-06550: row 5, column 13:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: row 5, column 1:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Under normal circumstances, I would include the body of the procedure, however, it is business and therefore something I cannot share.
From what I understand, on the beginning of the call, declaration failes and therefore it results in the rest of the errors, since those correspod to the places where the ‘type1’ is called.
To further explain what ‘type1’ is, it is a refference to a table (‘type2’). It is created by following code:

create or replace TYPE type1 IS TABLE OF type2;

EDIT 2
as @Alex Poole suggested, I’ve modified my declaration:

DECLARE
ClientData user2.type1;

however, it reproduced the same error:

PLS-00201: identifier 'user2.type1' must be declared

EDIT 3
The incorrect names were caused by me forgeting to sterilize the code, it was fixed.

The packages and the both types are owned by the user2. When I connect to the database, I don’t see anything from my perspective. No tables, packages or types. Only when I browse ,,Other users» and look into perspective of user2, I can see the required procedures.
Also, I am positive that I have privileges (EXECUTE & DEBUG) to all required objects. That is packages, and defined types.
When talking about how the privileges were granted, is there a way to check that? I used command

SELECT * FROM USER_TAB_PRIVS;

That lists user2 as a Grantor and Owner and user1 as a Grantee
EDIT 4
This is the outcome of SELECT * FROM USER_TAB_PRIVS;

GRANTEE OWNER   TABLE_NAME  GRANTOR PRIVILEGE   GRANTABLE  HIERARCHY    COMMON  TYPE
USER1   USER2   TYPE2       USER2   DEBUG       NO         NO           NO  TYPE
USER1   USER2   TYPE2       USER2   EXECUTE     NO         NO           NO  TYPE
USER1   USER2   TYPE1       USER2   DEBUG       NO         NO           NO  TYPE
USER1   USER2   TYPE1       USER2   EXECUTE     NO         NO           NO  TYPE
USER1   USER2   PACK1       USER2   DEBUG       NO         NO           NO  PACKAGE
USER1   USER2   PACK1       USER2   EXECUTE     NO         NO           NO  PACKAGE
PUBLIC  SYS     USER1       USER1   INHERIT PRIVILEGES  NO  NO  NO  USER

EDIT 5: Fixed the outcome table from lower-case to upper-case. As suggested however, I double-checked the names of the package and the Types, they are indeed in upper case.

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

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;

Then we run the programming unit again.

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