Ошибка pls 00201

I executed a PL/SQL script that created the following table

TABLE_NAME VARCHAR2(30) := 'B2BOWNER.SSC_Page_Map';

I made an insert function for this table using arguments

CREATE OR REPLACE FUNCTION F_SSC_Page_Map_Insert(
         p_page_id   IN B2BOWNER.SSC_Page_Map.Page_ID_NBR%TYPE, 
         p_page_type IN B2BOWNER.SSC_Page_Map.Page_Type%TYPE, 
         p_page_dcpn IN B2BOWNER.SSC_Page_Map.Page_Dcpn%TYPE)

I was notified I had to declare B2BOWNER.SSC_Page_Map prior to it appearing as an argument to my function. Why am I getting this error?

EDIT: Actual error

Warning: compiled but with compilation errors
Errors for FUNCTION F_SSC_PAGE_MAP_INSERT

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
2/48     PLS-00201: identifier 'SSC_PAGE_MAP.PAGE_ID_NBR' must be declared
0/0      PL/SQL: Compilation unit analysis terminated 

EDIT: Complete PL/SQL Function

RETURN INTEGER
IS
   TABLE_DOES_NOT_EXIST exception;  
   PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942

BEGIN

   INSERT INTO 
       B2BOWNER.SSC_Page_Map VALUES(
           p_page_id, 
           p_page_type, 
           p_page_dcpn);

   RETURN 0;

   EXCEPTION
       WHEN TABLE_DOES_NOT_EXIST THEN
           RETURN -1;
       WHEN DUP_VAL_ON_INDEX THEN
           RETURN -2;
       WHEN INVALID_NUMBER THEN
           RETURN -3;
       WHEN OTHERS THEN
           RETURN -4;
END;

SHOW ERRORS PROCEDURE F_SSC_Page_Map_Insert;

GRANT EXECUTE ON F_SSC_Page_Map_Insert TO B2B_USER_DBROLE; 
RETURN INTEGER

EDIT: I change the arguments and received a new error related to the insert command

CREATE OR REPLACE FUNCTION F_SSC_Page_Map_Insert(
                            p_page_id   IN INTEGER, 
                            p_page_type IN VARCHAR2, 
                            p_page_dcpn IN VARCHAR2)

RETURN INTEGER
IS

TABLE_DOES_NOT_EXIST exception;  
PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942); -- ORA-00942

BEGIN

INSERT INTO 
    B2BOWNER.SSC_Page_Map VALUES(
        p_page_id, 
        p_page_type, 
        p_page_dcpn);

The error

Errors for FUNCTION F_SSC_PAGE_MAP_INSERT

LINE/COL ERROR                                                            
-------- -----------------------------------------------------------------
17/18    PL/SQL: ORA-00942: table or view does not exist                  
16/5     PL/SQL: SQL Statement ignored                                    

The tables has been verified within the correct schema and with the correct attribute names and types

EDIT: I executed the following command to check if I have access

DECLARE
    count_this INTEGER;

BEGIN

select count(*) into count_this 
from all_tables 
where owner = 'B2BOWNER' 
and table_name = 'SSC_PAGE_MAP';

DBMS_OUTPUT.PUT_LINE(count_this);

END;

The output I received is

1
PL/SQL procedure successfully completed.

I have access to the table.

EDIT:

So I finally conducted an insert into the table via the schema using PL/SQL and it worked fine. It appears I simply do not have authority to create functions but that is an assumption.

EDIT:

Actual table DDL statement

 v_create := 'CREATE TABLE ' ||  TABLE_NAME || ' (
                PAGE_ID_NBR   NUMERIC(10)   NOT NULL Check(Page_ID_NBR > 0),
                PAGE_TYPE     VARCHAR2(50)  NOT NULL, 
                PAGE_DCPN     VARCHAR2(100) NOT NULL,
                PRIMARY KEY(Page_ID_NBR, Page_Type))';

EXECUTE IMMEDIATE v_create; 

COMMIT WORK;

COMMIT COMMENT 'Create Table'; 

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.

An example to show one cause of a «PLS-00201: identifier must be declared» error (Doc ID 391047.1)

Last updated on MARCH 04, 2022

Applies to:

Purpose

The «PLS-00201: identifier ‘XYZ’ must be declared» error is a fairly common yet misunderstood error due to how Oracle handles privileges granted directly and privileges granted via a Role in conjunction to Stored Procedures.

This Note should help illustrate the difference.

Scope

The example provided within creates two users where User1 owns the table and stored procedure and USER2 attempts to execute the procedure within User1’s schema.

The stored procedure execute a SELECT from two tables.

User2 attempts to access User1’s procedure which in turn Selects from User1’s table.

Referencing of the procedure fails when the privileges are granted via a Role, but works when granted directly.

Details

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

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.

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.

Output

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.

Output

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.

Exception

Solution

Output

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.

Exception

Solution

Output

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.

Источник

Error: PLS-00201: identifier ‘DBMS_CRYPTO’ must be declared.

I have a strange problem. My oracle version is 10.2.0.4.0 on linux server. I have to use ‘DBMS_CRYPTO’ oracle supplied package for one requirement. Here, first I wrote one anonymous PLSQL block,

DECLARE
INPUT_STRING VARCHAR2(200);
OUTPUT_STRING VARCHAR2(200);
ENCRYPTED_RAW RAW(2000);
DECRYPTED_RAW RAW(2000);
NUM_KEY_BYTES NUMBER := 256 / 8;
KEY_BYTES_RAW RAW(32);
ENCRYPTION_TYPE PLS_INTEGER := DBMS_CRYPTO.ENCRYPT_AES256 DBMS_CRYPTO.CHAIN_CBC DBMS_CRYPTO.PAD_PKCS5;
BEGIN

DBMS_OUTPUT.PUT_LINE(‘Original String Input: ‘ || INPUT_STRING);

ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => UTL_I18N.STRING_TO_RAW(INPUT_STRING,’AL32UTF8′),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW);

DBMS_OUTPUT.PUT_LINE(‘Encrypted String: ‘ || ENCRYPTED_RAW);

DECRYPTED_RAW := DBMS_CRYPTO.DECRYPT(SRC => ENCRYPTED_RAW,
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW);

OUTPUT_STRING := UTL_I18N.RAW_TO_CHAR(DECRYPTED_RAW, ‘AL32UTF8’);

DBMS_OUTPUT.PUT_LINE(‘Decrypted String Output: ‘ || OUTPUT_STRING);

IF INPUT_STRING=OUTPUT_STRING THEN
DBMS_OUTPUT.PUT_LINE(‘Encryption and decryption has happened in a expected manner.’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Encryption and decryption has not happened properly.’);
END IF;
END;

It is compiling without any error.
Same I wrote it in Procedure, but im getting the following error.

CREATE OR REPLACE PROCEDURE encrypt_hema
/********************************************************************************************
* Procedure Name : ENCRYPT_HEMA
* Parameters : p_input_text,p_encrypted_value,p_decrypted_value,p_err_msg
* Purpose : To encrypt a given text
* Author : Hemakumar for Q3 Enh 2012.
* Created : 22-OCT-12
********************************************************************************************/
(
p_input_text IN VARCHAR2,
p_encrypted_value OUT RAW,
p_decrypted_value OUT RAW,
p_err_msg OUT VARCHAR2)
IS

chk_exception EXCEPTION;
v_encrypted_text VARCHAR2(4000);
v_decrypted_text VARCHAR2(4000);
ENCRYPTED_RAW RAW(2000);
DECRYPTED_RAW RAW(2000);
NUM_KEY_BYTES NUMBER := 256 / 8;
KEY_BYTES_RAW RAW(32);
ENCRYPTION_TYPE PLS_INTEGER := sys.dbms_crypto.ENCRYPT_AES256 DBMS_CRYPTO.CHAIN_CBC DBMS_CRYPTO.PAD_PKCS5;

IF p_input_text IS NULL THEN
p_err_msg := exc_handler.display_warning(8414);
RAISE chk_exception;
END IF;

IF p_input_text IS NOT NULL THEN

ENCRYPTED_RAW := DBMS_CRYPTO.ENCRYPT(SRC => UTL_I18N.STRING_TO_RAW(p_input_text,’AL32UTF8′),
TYP => ENCRYPTION_TYPE,
KEY => KEY_BYTES_RAW);

v_encrypted_text := ENCRYPTED_RAW;
p_encrypted_value:=v_encrypted_text;

Источник

PLS-00201 error calling function in package

I have a package defined that’s exhibiting odd behavior. Called PKGAPPLICATIONS, it contains a number of stored procedures, one of which, fGetReportTitle, returns with the above error when I call it through my application code.

The Specification and Body code compiles cleanly, I can successfully execute the function in TOAD’s Procedure Editor, and I can see the function when I view the package in TOAD’s Schema Browser. I can also successfully call other Stored Procs in the same package.

I’ve checked the spelling, the parameter data types, lengths, execution rights, etc, but still get the PLS-00201 error when I execute the application. Anybody have any ideas??

Thanks in advance.

Thanks for your reply.

Yes, the function is included in the package spec.
No, the method is not overloaded.
There’s a proc in the same package with a similar name. The two names are fGetReportName and ssp_GetReportName. Could they be conflicting?
My DBA tells me that we’re not executing via a role because the application signs on as the database owner and therefore has execution rights. The fact that the other SPs in the package are available to the application supports that assertion, I think.

PLS-00201: identifier ‘string’ must be declared
Cause: You tried to reference either an undeclared variable, exception, procedure, or other item, or an item to which no privilege was granted or an item to which privilege was granted only through a role.
Action: 1) Check your spelling and declaration of the referenced name. 2) Verify that the declaration for the referenced item is placed correctly in the block structure. 3) If the referenced item is indeed declared but you don’t have privileges to refer to that item, for security reasons, you will be notified only that the item is not declared. 4) If the referenced item is indeed declared and you believe that you have privileges to refer to that item, check the privileges; if the privileges were granted only via a role, then this is expected and documented behavior. Stored objects (packages, procedures, functions, triggers, views) run in the security domain of the object owner with no roles enabled except PUBLIC. Again, you will be notified only that the item was not declared.

Did you really check all called/procedures in all the possible calling stacks? Including the standard packages that you may call ?
You can also use USER_DEPENDENCIES to find used objects.

Because Oracle unfortunately does not give the object name that triggers the error, you might be forced to debug your code until you find the object that triggers this error.

Who is the owner of the package and who is calling? if the owner is different from who is calling then create a public synonym fo the package and the grant execute on the package to the caller.

You’re asking about a month too late. I would assume that the OP has already figured it out by now.

Hi,
I recently installed ERWin connect through ERWin4.1 and when I am trying to connect using userid/password and connection string for my database it is giving error :
oracle vers. 8.xxorcle9i10g error 6550 sevierty 16
‘PLS-00201 : identifier ‘MM_USER_HASHPROFILE2’ must be declared.

Could someone please guild me through that error.

I went through your post regarding the PLS-00201 error.

I faced the same problem while selecting the data from a table using a cursor which
was written within a PL/SQL custom function. Oracle could not resolve the table name
referenced in the select statement and hence it was throwing the PLS-00201 error.

I tried selecting the data from the referenced table using a select statement. The table existed in the database and i had select priviliges granted on the table through a role which was actually the cause of the error.

All i mean to say is select priviliges should not be granted through a role but should be directly granted to the user for the select statements on tables written within procedures/functions to work. This will avoid the PLS-00201 error.

SQL> grant programmer to user123;

Say programmer is a role containing select priviliges on a table A belonging to a schema. When a select statement is written within a procedure/function being executed by user123, it will lead to the error PLS-00201.

This can be avoided by:

SQL> grant select on schema.A to user123;

SQL> grant select any table to user123;

Assigning direct priviliges to a user rather than assigning priviliges therough a role
will avoid such errors.

I verified the solution practically and the code is working fine in my case.. I confirmed
the same on Oracle Metalink.

Hope this helps you.. Please revert back to me for any further clarifications.

Источник

strange error..PLS-00201: identifier ‘PLITBLM’ must be declared

could not find any such errors.

SQL> set serveroutput on size 10000
SQL> DECLARE
2 TYPE CUSTOMER_RECORD IS RECORD (CUSTOMER_ACCT_ID NUMBER,CUSTOMER_NAME VARCHAR2(2000));
3 TYPE CUSTOMER_REC IS TABLE OF CUSTOMER_RECORD INDEX BY BINARY_INTEGER;
4 LREC_CUSTOMER_RECORD CUSTOMER_RECORD;
5 LT_CUSTOMER_REC CUSTOMER_REC;
6 BEGIN
7 LREC_CUSTOMER_RECORD.CUSTOMER_ACCT_ID:=10;
8 LREC_CUSTOMER_RECORD.CUSTOMER_NAME:=’BHAGAT’;
9 LT_CUSTOMER_REC(1):=LREC_CUSTOMER_RECORD;
10 DBMS_OUTPUT.PUT_LINE(LT_CUSTOMER_REC(1).CUSTOMER_ACCT_ID);
11 DBMS_OUTPUT.PUT_LINE(LT_CUSTOMER_REC(1).CUSTOMER_NAME);
12 end;
13 /
10
BHAGAT

PL/SQL procedure successfully completed.

Check the contents you copied before applying in sqlplus. Try to exit the current session and run the code in new session.

The PL/SQL Engine uses several system (SYS user) packages. The error is likely caused by:
— a missing package
— an invalidated package

Run the following SQL as user SYS:
SELECT object_name,status,owner from DBA_OBJECTS where status = ‘INVALID’

Try and recompile these invalid packages, e.g.
ALTER PACKAGE

In the case of a missing package, the problem could be a tad more serious. What is the db version? Is this a new install? If not, what changes were recently made?

Thanks for the reply.
It works perfectly fine in my system.
However my colleague is encountering this error in his PC,with exactly the same piece of code.I wonder if version problems or something of that sort could attribute to this error?

Источник

PLS-00201 means that the identifier you specified in the statement has never 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

A. 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.

B. 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.

C. 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.

Let’s Develop in Oracle

ORA-06550: line n, column n

ORA-06550: line string, column string: string
Cause: Usually a PL/SQL compilation error.
Action: none

ORA-06550 is a very simple exception, and occurs when we try to execute a invalid pl/sql block like stored procedure. ORA-06550 is basically a PL/SQL compilation error. Lets check the following example to generate ORA-06550:

Here we create a stored procedure «myproc» which has some compilation errors and when we tried to execute it, ORA-06550 was thrown by the Oracle database. To debug ORA-06550 we can use «show error» statement as:

Now we know variable SAL is not defined and must be written as c.sal. So we will need to make corrections in «myproc» as

Hi every one!
Can anyone give me some idea about PRAGMA INLINE?

Pragma inline is compiler directive to replace its call with its definition, like we have #define in C language
check this link out: http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/inline_pragma.htm

i have created the package
create or replace package maths
as
procedure addition(a in number, b in number,c in out number);
function subtraction(a in number,b in number,c out number) return number;
procedure multiplication(a in number,b in number,c out number);
function division(a in number,b in number,c out number) return number;
end maths;
And i created package body,
create or replace package body maths
as
procedure addition(a in number,b in number,c in out number)
is
begin
c:=a+b;
end addition;
function subtraction(a in number,b in number,c out number) return number
is
begin
c:=a-b;
return c;
end subtraction;
procedure multiplication(a in number,b in number,c out number)
is
begin
c:=a*b;
end multiplication;
function division(a in number,b in number,c out number) return number
is
begin
c:=a/b;
return c;
end division;
end maths;
And then i called the procedure by using the code
set serveroutput on
declare
x number;
y number;
z number;
begin
x:=10;
y:=20;
addition(x,y,z);
dbms_output.put_line(z);
end;
but i am getting the below error:

Error starting at line 148 in command:
declare
x number;
y number;
z number;
begin
x:=10;
y:=20;
addition(x,y,z);
dbms_output.put_line(z);
end;
Error report:
ORA-06550: line 8, column 1:
PLS-00905: object SATYA.ADDITION is invalid
ORA-06550: line 8, column 1:
PL/SQL: Statement ignored
06550. 00000 — «line %s, column %s:n%s»
*Cause: Usually a PL/SQL compilation error.
*Action:

HOW CAN I RESOLVE THIS ERROR CAN ANY ONE PLZ HELP ME:

Источник

Accessing TABLE From READ ONLY DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905 (Doc ID 358697.1)

Last updated on JANUARY 29, 2022

Applies to:

Symptoms

Accessing TABLE From READ ONLY (STANDBY) DATABASE Using DATABASE LINK Within PL/SQL Fails With ORA-06550 ORA-04063 or PLS-00905

To reproduce in remote read only Database:

In local database:

drop database link ora102;
create database link ora102 using ‘ora102’;

declare
i number;
begin
select count(*) into i from x@ora102;
end;
/

If local and remote database have version between Oracle9i 9.2.0 to Oracle 11.2 or later it fails with:

If local database have versions between Oracle8 8.0.6 to Oracle8i 8.1.7 it fails with :

SVRMGR> declare
2> i number;
3> begin
4> select count(*) into i from x@ora817;
5> end;
6> /
select count(*) into i from x@ora817;

*
ORA-06550: line 4, column 33:
PLS-00905: object SCOTT.X@ORA817.WORLD is invalid
ORA-06550: line 4, column 5:
PL/SQL: SQL Statement ignored

If Local Database has a version higher than Oracle9i 9.2.0 and remote Database a version lower than Oracle9i 9.0.1 then it could fails with ORA-00600 [17069]

Work OK When Accessing TABLE Using SQL From READ ONLY (STANDBY) DATABASE Using DATABASE LINK

Cause

To view full details, sign in with your My Oracle Support account.

Don’t have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | | | | Legal Notices | Terms of Use

Источник

ORA-06550 and PLS-00201 identifier

must be declared

I am attempting to execute a sql script in SQLplus, but I am getting this error:
ORA-06550: line 18, column 7
PLS-00201: identifier ‘IPFMODHISTORY.ALLUSERSALLTABLES’ must be declared
PL/SQL: statement ignored

I don’t understand what the problem is. I have granted EXECUTE privileges on the package to the schema owner. I also tried granting EXECUTE privileges on the package to the user. I know the package is there and the ALLUSERSALLTABLES function exists.

Maybe the allusersalltables function is only declared in the package body, not in the specification of ipfmodhistory package. This way the function cannot be called from outside.

I do have the function declared in the spec as well.

I tried something else now. I tried preceeding the package name with the name of the schema, such as: SCHEMA_NAME.ipfModHistory.AllUsersAllTables,
I get this error: ORA-00942: table or view does not exist

what does following query return?

select * from all_objects where object_name = ‘IPFMODHISTORY’;

Why do you have same package under different schemas? It is not recommended to have your own objects under SYS schema.

From which user was the EXECUTE grant given?

Since there is no package body under SYS schema, I presume it would be for PANTOS schema only?

I don’t know why the package is listed under both the SYS schema and the PANTOS schema. And yes, it is for the PANTOS schema only.
Logged in as the user who is trying to execute this package, I did a:
SELECT * FROM USER_TAB_PRIVS_RECD;
and found that PANTOS granted the user EXECUTE privileges on the package.

Thank you,
Laura

Message was edited by:
The Fabulous LB

I tried creating a synonym, but that didn’t help either. I still get the same error message.

Logged in as PANTOS, I tried your example, CREATE SYNONYM ipfModHistory FOR PANTOS.ipfModHistory, and got this error:
ORA-01471: cannot create a synonym with same name as object

So I tried this:
CREATE SYNONYM modhistory FOR PANTOS.ipfModHistory, and the synonym was created.

Logged in as PANTOS, EXECUTE privileges were granted to the user who will actually be executing the SQL script:
GRANT EXECUTE on ipfModHistory TO user2;
Grant succeeded.

Then I modified the SQL script to call the package & function with the synonym. Maybe this is where I am going wrong? And I get the same type of error as before:
PLS-00201: identifier ‘MODHISTORY.ALLUSERSALLTABLES’ must be declared.
PLS-00201: identifier ‘MODHISTORY.ALLUSERSSINGLETABLE’ must be declared.
. and so on.

What am I missing here?

Can you successfully run ipfmodhistory.allusersalltables as the pantos user? From the error message, it appears that the function is trying to access a table that does not exist, or that pantos does not have directly granted privileges on. If any of the tables used in the function are not owned by pantos, you will either need to make a private synonym in the pantos schema, or prefix the table name wih the table owners name.

When i try to execute this script logged in as PANTOS, I get this error:
ORA-00942: table or view does not exist
ORA-06512: at «PANTOS.IPFMODHISTORY», line 73

I am trying to do a select statement inside my function that selects from
v$xml_audit_trail view and the audit_actions table.

So I verified who the owner of this view/table are:
select owner, table_name from dba_tables where table_name = ‘AUDIT_ACTIONS’;
select owner, object_name from all_objects where object_name = ‘V$XML_AUDIT_TRAIL’;

Источник

Ora 06550 error in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

We are currently experiencing issues trying to connect to Oracle stored procedures from Microsoft SQL Server Reporting Services 2014, receiving the following error anytime we try and run any stored procedure via a report connecting to an Oracle stored procedure:

ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘ ‘ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

These stored procedures return an “OUT” SYS_REFCURSOR parameter as required to return a data set to SSRS, and it seems to be that parameter that is causing the issue for all our reports.

We are running SSRS on a Windows Server 2012 R2 Standard OS, 64 bit, and SSRS is running under a 64 bit configuration. We have installed the ODAC 64 bit components (64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64) for Windows (downloaded from https://www.oracle.com/technetwork/database/windows/downloads/index-090165.html) and also registered the ODP.NET DLLs on the server into the GAC as our reports use this connection method.

From SSRS on the server, we can successfully make a connection to the Oracle datasource using the SSRS Report Manager. And from our development machines, where we installed the 32 bit ODAC / ODT Tools for Visual Studio (ODAC 12.2c Release 1 and Oracle Developer Tools for Visual Studio (12.2.0.1.1)) (because Visual Studio uses the 32-bit ODAC components), we can successfully connect to the Oracle database and execute the reports without the error we are receiving on the server.

We have already validated that we have the correct parameters in the report, and we have validated that we can connect and execute the stored procedures successfully via SQL Plus and also on our local development machines from the SSRS report.

We are trying to connect to an Oracle 11.2.0.4 database.

We have already tried following the advice and procedures from a number of articles, including those listed in other posts on this site such as «https://social.technet.microsoft.com/Forums/en-US/424f750e-7f58-49e3-bd4a-51e0acdd99a4/not-able-to-use-oracle-sp-in-ssrsgetting-an-error?forum=sqlreportingservices» and «https://social.technet.microsoft.com/Forums/en-US/626c9c6c-1c99-4718-9cb1-054a102701cd/ssrs-calling-a-stored-procedure-error-pls00306-wrong-number-or-types-of-arg?forum=sqlreportingservices&ppud=4». But as far as we can tell, the ODAC version we have installed on the server (12.2c Release 1) can connect to an Oracle database version 10g or higher (according to https://www.oracle.com/technetwork/topics/dotnet/install122010-3711118.html), and our database is 11.2.0.4 so we should be good, correct? Or is the Oracle documentation wrong, and in order to connect to an Oracle 11.x database we need the ODAC 11.2.0.3.0 components on the server (even though the ODAC 12.2c components installed on our development machines allow us to run the reports successfully from Visual Studio)?

Anyone have any thoughts?

Thanks in advance.

According to your description , seems you could check in the following aspects.

• Do not use the stored procedure directly, try to use a simple query check if the query would runs ok in ssrs. If the simple query runs correct , seems it is an issue about the query (stored procedure ), if not seems it is an issue about the connection provider driver.
• For the query problem .
  1. Check if you have the correct parameter type.
  2. Do you have enough permission to access the stored procedure and the correspond temp table space.
  3. Check your stored procedure again.
  4. Any custom datatype or just mistype.

• For the provider driver.
  1. Make sure you have correct install the correspond provider driver .(both 32 bit and 64 bit ,and both user level and system level)
  2. The multiple connection driver ‘s crash , try to make sure you have a clean environment .see: Connection error after upgrading from ODAC 9i to 11.2

You could also offer the correspond ssrs log or the oracle log information to us for more further research.

Hope it can help you.

Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread.

Thanks for the response. Following your suggestions, I took the (very simple) PL-SQL out of the stored procedure, and built a new SSRS report to run directly against the SQL, and as expected this worked both on my development machine, and from the server.

However, I don’t necessarily agree that this points to an issue with the stored procedure. I say that because I can successfully use the stored procedure, using the same database and user and connection string, from my development machine and have it work. If there was an issue with the stored proc, then it shouldn’t work anywhere I try and use it, correct?

And the PL-SQL / stored procedure we are trying to get working is extremely simple. Here is the PL-SQL:

WHERE TRUNC (DATECOLUMN) =

TRUNC (TO_DATE (‘2018-01-01’, ‘yyyy-mm-dd’))

And as a stored procedure, it is pretty much just as simple, except that we have defined a single input parameter for the date, and the necessary output parameter to hold the dataset to pass back to the report:

CREATE OR REPLACE PROCEDURE BLAH.spParameterTest (

param1 IN VARCHAR,

Results OUT SYS_REFCURSOR)

OPEN Results FOR

WHERE TRUNC (DATECOLUMN) =

TRUNC (TO_DATE (param1, ‘yyyy-mm-dd’));

I have double (and triple :)) checked the stored procedure, it’s parameters and data types, and permissions, and all seem good (again, we can successfully use it from our development machines). I had actually seen that first article you reference previously and validated all of that.

Regarding the second article — I don’t believe we have that issue either, as we also thought this could be an issue and removed all ODAC installations on the server, then installed the singular ODAC 64 bit components (64-bit ODAC 12.2c Release 1 (12.2.0.1.1) for Windows x64) for Windows component. One item you mentioned peaked my interest though, and that was:

1. Make sure you have correct install the correspond provider driver .(both 32 bit and 64 bit ,and both user level and system level)

Is the driver not automatically installed where necessary by the ODAC installation? And why would I need the 32 bit driver since I’m using all 64 bit software and OS? And how do I install at a user vs. system level?

Finally, regarding your suggestion to post the related SSRS log, here is an excerpt that we get when we attempt to run the report from the SSRS Report Manager:

• An error has occurred during report processing. (rsProcessingAborted)
  • Query execution failed for dataset ‘DataSet1’. (rsErrorExecutingCommand)
    • ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to ‘BLAH’ ORA-06550: line 1, column 7: PL/SQL: Statement ignored

And in the SSRS log file we get (sorry for the length :)):

processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: , Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: i INFO: DataPrefetch abort handler called for Report with Aborting data sources .
processing!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: , Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.OnDemandProcessingContext.AbortHelper.ThrowAbortException(String uniqueName)
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.FetchData()
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.PrefetchData(ReportInstance reportInstance, ParameterInfoCollection parameters, Boolean mergeTran)
at Microsoft.ReportingServices.OnDemandProcessing.Merge.FetchData(ReportInstance reportInstance, Boolean mergeTransaction)
at Microsoft.ReportingServices.ReportProcessing.Execution.ProcessReportOdpInitial.PreProcessSnapshot(OnDemandProcessingContext odpContext, Merge
webserver!ReportServer_0-1!12d8!01/08/2019-16:35:34:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. —> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset ‘DataSet1’. —> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to ‘BLAH’
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.DataExtensions.OracleCommandWrapperExtension.ExecuteReader(CommandBehavior behavior)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunEmbeddedQuery(Boolean& readerExtensionsSupported, Boolean& readerFieldProperties, List`1 queryParams, Object[] paramValues)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.RunDataSetQueryAndProcessAsIRowConsumer(Boolean processAsIRowConsumer)
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeDataSet.InitializeAndRunLiveQuery()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.InitializeRowSourceAndProcessRows()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.Process()
at Microsoft.ReportingServices.OnDemandProcessing.RuntimeAtomicDataSet.ProcessConcurrent(Object threadSet)
— End of inner exception stack trace —
at Microsoft.ReportingServices.OnDemandProcessing.OnDemandProcessingContext.AbortHelper.ThrowAbortException(String uniqueName)
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.FetchData()
at Microsoft.ReportingServices.OnDemandProcessing.RetrievalManager.PrefetchData(ReportInstance reportInstance, ParameterInfoCollection parameters, Boolean mergeTran)
at Microsoft.ReportingServices.OnDemandProcessing.Merge.FetchData(ReportInstance reportInstance, Boolean mergeTransaction)
at Microsoft.ReportingServic

So, does any of the above give you any clues that might be wrong?

Источник

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.