"Identifier Must Be Declaired" error on Package Procedure
For some reason the code below doesnt compile. Its almost as if I dont have access to the table, except that if I run the blocks of (sql select statements) I get a result set back. Im at a lost and was hoping for some alternate ideas.
<code>
CREATE OR REPLACE PACKAGE "PKG_HRREPORTS"
AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE "USP_401KMATCHING" (IO_CURSOR IN OUT T_CURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY "PKG_HRREPORTS" AS
PROCEDURE "USP_401KMATCHING" (IO_CURSOR IN OUT T_CURSOR)
IS
CURSOR c1 IS
SELECT e.EMPLOYEE
FROM BENEFIT b, EMPLOYEE, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.PLAN_CODE = '401K'
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
GROUP BY e.EMPLOYEE
HAVING COUNT(b.START_DATE)>1;
CURSOR c2 IS
SELECT e.EMPLOYEE, b.START_DATE, b.STOP_DATE, b.EMP_PRE_CONT, c.COMP_AMOUNT, e.DATE_HIRED, PROCESS_LEVEL, DEPARTMENT
FROM BENEFIT b, EMPLOYEE e, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.PLAN_CODE = '401K'
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
AND e.EMPLOYEE IN (SELECT EMPLOYEE FROM c1)
ORDER BY e.EMPLOYEE, b.START_DATE;
CURSOR c3 IS
SELECT e.EMPLOYEE, b.START_DATE, b.STOP_DATE, e.DATE_HIRED
FROM BENEFIT b, EMPLOYEE e, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.START_DATE >= e.DATE_HIRED + 90
AND b.PLAN_CODE = '401K'
AND EXTRACT(YEAR FROM TRUNC(e.DATE_HIRED)) = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
ORDER BY e.EMPLOYEE;
TYPE tmp_results IS TABLE OF c2%ROWTYPE;
TYPE tmp_GapComp IS TABLE OF c2%ROWTYPE;
LAST_EMPLOYEE BENEFIT.EMPLOYEE%TYPE;
LAST_STOPDATE BENEFIT.STOP_DATE%TYPE;
V_CURSOR T_CURSOR;
BEGIN
<do insert . . . >
FOR i1 IN c2
LOOP
IF...THEN
.........
ELSE
.........
END IF;
END LOOP;
COMMIT;
<do second insert....>
IO_CURSOR := V_CURSOR;
END "USP_401KMATCHING";
END "PKG_HRREPORTS";
/
</code>
<error>
PLS-00201: identifier 'TESTHR.BENEFIT' must be declared, line 5
....<more errors on 'TESTHR.BENEFIT' >
</error>
I should mention, Ive set up Synonyms in my schema that point to TESTHR.BENEFIT when calling BENEFIT. Any ideas? Any obvioius syntax errors? This is all running on Oracle 8.1.7
Thanks
Matt
<code>
CREATE OR REPLACE PACKAGE "PKG_HRREPORTS"
AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE "USP_401KMATCHING" (IO_CURSOR IN OUT T_CURSOR);
END;
/
CREATE OR REPLACE PACKAGE BODY "PKG_HRREPORTS" AS
PROCEDURE "USP_401KMATCHING" (IO_CURSOR IN OUT T_CURSOR)
IS
CURSOR c1 IS
SELECT e.EMPLOYEE
FROM BENEFIT b, EMPLOYEE, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.PLAN_CODE = '401K'
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
GROUP BY e.EMPLOYEE
HAVING COUNT(b.START_DATE)>1;
CURSOR c2 IS
SELECT e.EMPLOYEE, b.START_DATE, b.STOP_DATE, b.EMP_PRE_CONT, c.COMP_AMOUNT, e.DATE_HIRED, PROCESS_LEVEL, DEPARTMENT
FROM BENEFIT b, EMPLOYEE e, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.PLAN_CODE = '401K'
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
AND e.EMPLOYEE IN (SELECT EMPLOYEE FROM c1)
ORDER BY e.EMPLOYEE, b.START_DATE;
CURSOR c3 IS
SELECT e.EMPLOYEE, b.START_DATE, b.STOP_DATE, e.DATE_HIRED
FROM BENEFIT b, EMPLOYEE e, COMPHIST c
WHERE b.COMPANY = e.COMPANY
AND b.EMPLOYEE = e.EMPLOYEE
AND b.PLAN_TYPE = c.PLAN_TYPE
AND b.PLAN_CODE = c.PLAN_CODE
AND b.COMPANY = c.COMPANY
AND b.EMPLOYEE = c.EMPLOYEE
AND b.START_DATE >= e.DATE_HIRED + 90
AND b.PLAN_CODE = '401K'
AND EXTRACT(YEAR FROM TRUNC(e.DATE_HIRED)) = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(SYSDATE))
AND c.PLAN_YEAR = EXTRACT(YEAR FROM TRUNC(b.START_DATE))
ORDER BY e.EMPLOYEE;
TYPE tmp_results IS TABLE OF c2%ROWTYPE;
TYPE tmp_GapComp IS TABLE OF c2%ROWTYPE;
LAST_EMPLOYEE BENEFIT.EMPLOYEE%TYPE;
LAST_STOPDATE BENEFIT.STOP_DATE%TYPE;
V_CURSOR T_CURSOR;
BEGIN
<do insert . . . >
FOR i1 IN c2
LOOP
IF...THEN
.........
ELSE
.........
END IF;
END LOOP;
COMMIT;
<do second insert....>
IO_CURSOR := V_CURSOR;
END "USP_401KMATCHING";
END "PKG_HRREPORTS";
/
</code>
<error>
PLS-00201: identifier 'TESTHR.BENEFIT' must be declared, line 5
....<more errors on 'TESTHR.BENEFIT' >
</error>
I should mention, Ive set up Synonyms in my schema that point to TESTHR.BENEFIT when calling BENEFIT. Any ideas? Any obvioius syntax errors? This is all running on Oracle 8.1.7
Thanks
Matt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look at example a) from this Metalink article:
Doc ID:
Note:269973.1
Subject:
How to Find out the Reason for
PLS-00201 in PL/SQL
Type:
BULLETIN
Status:
PUBLISHED
Content
Type:
TEXT/PLAIN
Creation
Date:
23-APR-2004
Last Revision
Date:
09-AUG-
2004
***
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
***
PURPOSE
-------
The purpose of this document is to give information on the most common
causes of the errors PLS-00201 and when developing with PL/SQL
code and collect the most common notes written about this.
SCOPE & APPLICATION
-------------------
A common problem for PL/SQL developers are receiving errors on
tables/views
used in PL/SQL code when there is no problem to use this objects from
SQL*PLUS. This document will explain the expected behavors of getting
the following error.
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.
Some basic rules that will help avoiding problems
==========================
a) Differences in Name Resolution Between SQL and PL/SQL
When the PL/SQL compiler processes a SQL statement, such as a DML
statement,
it uses the same name-resolution rules as SQL. For example, for a name
such as
SCOTT.FOO, SQL matches objects in the SCOTT schema first, then
packages, types,
tables, and views in the current schema.
PL/SQL uses a different order to resolve names in PL/SQL statements
such as
assignments and procedure calls. In the case of a name SCOTT.FOO,
PL/SQL
searches first for packages, types, tables, and views named SCOTT in
the
current schema, then for objects in the SCOTT schema.
b) Scope and Visibility of PL/SQL identifiers.
References to an identifier are resolved according to its scope and
visibility.
The scope of an identifier is that region of a program unit
(block, subprogram, or package)from which you can reference the
identifier.
An identifier is visible only in the regions from which you can
reference
the identifier using an unqualified name.
c) Roles versus explicit Grants.
Managing and controlling privileges is made easier by using roles,
which are named groups of related privileges that you grant, as a
group,
to users or other roles. Within a database, each role name must be
unique,
different from all user names and all other role names. Unlike schema
objects,
roles are not contained in any schema. Therefore, a user who creates a
role
can be dropped with no effect on the role.
Roles ease the administration of end-user system and schema object
privileges.
However, roles are not meant to be used by application developers,
because the privileges to access schema objects within stored
programmatic
constructs must be granted directly.
Some general rules about debugging PLS-201 errors
==========================
a) First make sure that the object you reference really do exists.
This can be done by checking the error code we get during
compilation
Error during compilation of procedure 'BOGUS'
Example:
2/13 PLS-00201: identifier 'SCOTT.FOO' must be declared
2/13 PL/SQL: Item ignored
4/5 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression
is
incomplete or malformed
The error stack states that on line 2 position 13 we have a un-
declared
identifier
Note:
We can use the same method for checking the error
4/26 PL/SQL: ORA-00942: table or view does not exist
Since the source is stored in the database we could use the table
USER_SOURCE
to find out more about the error
SQL> SELECT text FROM USER_SOURCE
2 WHERE NAME = 'BOGUS'
3 AND LINE = 2;
TEXT
--------------------------
my_values scott.foo.id%TYPE;
SQL>
We can use the same method for checking the error
4/26 PL/SQL: ORA-00942: table or view does not exist
This means there is a reference to the id column of the foo object
in the scott schema
So check the scott schema if the object do exists. If not then the
error expected
since we reference an object that do not exists.
If the object exists then check that the scott.fo object has been
explicitly
granted to the owner of the 'BOGUS' procedure.
Reference:
[NOTE:168168.1] Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in
PL/SQL,
works in SQL*Plus
Example of common situation where PLS-201 is throwned as error
==========================
a) ROLES versus EXPLICIT GRANT
--------------------------
This code will fail even though the select will work the
creation of the procedure will fail.
===================== code begins here ======================
connect / as sysdba
grant create role to scott;
connect scott/tiger
drop role foobar;
drop table foo;
create table foo (id number);
insert into foo values(1);
commit;
create role foobar;
grant all on foo to foobar;
grant foobar to testcase;
connect testcase/manager
drop procedure bogus;
select * from scott.foo;
create procedure bogus is
my_values scott.foo.id%TYPE;
cursor c1 is
select id from scott.foo;
begin
open c1;
fetch c1 into my_values;
close c1;
end;
/
show error
===================== code ends here ======================
Example output:
Warning: Procedure created with compilation errors.
SQL> Errors for PROCEDURE BOGUS:
LINE/COL ERROR
-------- --------------------------
---
2/13 PLS-00201: identifier 'SCOTT.FOO' must be declared
2/13 PL/SQL: Item ignored
4/5 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
SQL>
Solution:
=========
As scott explicit grant the necessary privilige to testcase schema
Roles are *not* used for PL/SQL code.
Example:
connect scott/tiger
grant all on foo to testcase;
connect testcase/manager
alter procedure bogus compile;
exec bogus;
Example output:
SQL> Connected.
SQL> Grant succeeded.
SQL> Connected.
SQL> Procedure altered.
PL/SQL procedure successfully completed.
SQL>
[NOTE:27287.1] OERR:PLS.201
===
b) PLS-201 WHEN WORKING WITH DYNAMIC VIEWS OR OTHER DICTIONARY BASED
OBJECTS.
--------------------------
------
Another common scenario is using dynamic dictionary views like
v$session.
Since those objects are views we cannot grant them directly.
Use following reference Note:
[NOTE:1013570.102] PLS-201 WHEN USING V$SESSION OR OTHER DYNAMIC VIEW
Also note that any priviliges granted by SYS are lost if you do a full
export and import of a database. Like moving a database using export
and import. Therefore it is recommended to save all your necessary
grant steps in a SQL script for re-usability
Reference:
[NOTE:97902.1] After Full Import all Object Privileges Granted by SYS
are Lost
===
c) PLS-00201 when invoking system packages like DBMS_*
--------------------------
Solution:
Explicitly grant execute permission on the system package to the owner
of the
PL/SQL package/procedure/function
d) PLS-00201 when invoking system packages like DBMS_LOCK after
migrating from 8i to 9i
--------------------------
----------------
Solution:
---------
Explicitly grant execute permission the system package to the owner of
the
PL/SQL package/procedure/function
Reference:
[BUG:2784411] GETTING PLS-201 WHEN EXECUTING DBMS_LOCK PACKAGE
[NOTE:47316.1] PARAMETER:O7_DICTIONARY_AC
e) PLS-00201 when referencing remote objects over database links
--------------------------
There are numerous reasons for this error to occur but the following
issues are
frequently reasons.
Reference:
[BUG:2324247] COMPILATION ERRORS WHEN DBLINK NAME IS THE SAME THAN
REMOTE DB INSTANCE NAME
[NOTE:208065.1] Accessing a Loopback DBLINK in PL/SQL Fails With
PLS-00201
[NOTE:1029662.6] PLS-000201 ERROR WHEN EXECUTING A REMOTE STORED
PROCEDURED THOUGH GATEWAY
f) PLS-00201 when using the wrap utility.
--------------------------
This following piece of code will work if compiled normally
Example: nvl2.sql
create or replace procedure test_nvl2 is
my_last_name varchar2(30);
my_salary number(10);
my_income number(10);
cursor c1 is
SELECT ename, sal, NVL2(comm,
sal + (sal * comm), sal) income
FROM emp WHERE ename like 'S%'
ORDER BY ename;
begin
open c1;
loop
fetch c1 into my_last_name,my_salary,my_
dbms_output.put_line(my_la
exit when c1%NOTFOUND;
end loop;
close c1;
end;
/
However, using this with wrap in 9iR2 and compile the code will
generate
an PLS-00201 error like
wrap iname=nvl2.sql
SQL> show error
Errors for PROCEDURE TEST_NVL2:
LINE/COL ERROR
-------- --------------------------
8/1 PL/SQL: SQL Statement ignored
8/20 PLS-00201: identifier 'NVL2' must be declared
SQL>
The explanation for this is documented as
Some recent SQL syntax is not supported by the wrap utility by default.
To enable the support for all SQL syntax, specify the option
edebug=wrap_new_sql (with no dash).
Workaround:
-----------
wrap edebug=wrap_new_sql iname=nvl2.sql
Reference:
[NOTE:250949.1] how to wrap new sql using wrap utility
RELATED DOCUMENTS
-----------------
[BUG:2324247] COMPILATION ERRORS WHEN DBLINK NAME IS THE SAME THAN
REMOTE DB INSTANCE NAME
[BUG:2784411] GETTING PLS-201 WHEN EXECUTING DBMS_LOCK PACKAGE
[NOTE:27287.1] OERR:PLS.201
[NOTE:168168.1] Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111
in PL/SQL, works in SQL*Plus
[NOTE:1013570.102] PLS-201 WHEN USING V$SESSION OR OTHER DYNAMIC VIEW
[NOTE:13615.1] Roles and Privileges Administration and Restrictions
[NOTE:97902.1] After Full Import all Object Privileges Granted by
SYS are Lost
[NOTE:100056.1] How to Find a Package's Privilege and Grantee User
Information
[NOTE:47316.1] PARAMETER:O7_DICTIONARY_AC
[NOTE:208065.1] Accessing a Loopback DBLINK in PL/SQL Fails With
PLS-00201
[NOTE:1029662.6] PLS-000201 ERROR WHEN EXECUTING A REMOTE STORED
PROCEDURED THOUGH GATEWAY
[NOTE:250949.1] How to wrap new sql using wrap utility
SQL Language Reference
Oracle Database Concepts
PL/SQL Users Guide and Reference
Doc ID:
Note:269973.1
Subject:
How to Find out the Reason for
PLS-00201 in PL/SQL
Type:
BULLETIN
Status:
PUBLISHED
Content
Type:
TEXT/PLAIN
Creation
Date:
23-APR-2004
Last Revision
Date:
09-AUG-
2004
***
This article is being delivered in Draft form and may contain
errors. Please use the MetaLink "Feedback" button to advise
Oracle of any issues related to this article.
***
PURPOSE
-------
The purpose of this document is to give information on the most common
causes of the errors PLS-00201 and when developing with PL/SQL
code and collect the most common notes written about this.
SCOPE & APPLICATION
-------------------
A common problem for PL/SQL developers are receiving errors on
tables/views
used in PL/SQL code when there is no problem to use this objects from
SQL*PLUS. This document will explain the expected behavors of getting
the following error.
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.
Some basic rules that will help avoiding problems
==========================
a) Differences in Name Resolution Between SQL and PL/SQL
When the PL/SQL compiler processes a SQL statement, such as a DML
statement,
it uses the same name-resolution rules as SQL. For example, for a name
such as
SCOTT.FOO, SQL matches objects in the SCOTT schema first, then
packages, types,
tables, and views in the current schema.
PL/SQL uses a different order to resolve names in PL/SQL statements
such as
assignments and procedure calls. In the case of a name SCOTT.FOO,
PL/SQL
searches first for packages, types, tables, and views named SCOTT in
the
current schema, then for objects in the SCOTT schema.
b) Scope and Visibility of PL/SQL identifiers.
References to an identifier are resolved according to its scope and
visibility.
The scope of an identifier is that region of a program unit
(block, subprogram, or package)from which you can reference the
identifier.
An identifier is visible only in the regions from which you can
reference
the identifier using an unqualified name.
c) Roles versus explicit Grants.
Managing and controlling privileges is made easier by using roles,
which are named groups of related privileges that you grant, as a
group,
to users or other roles. Within a database, each role name must be
unique,
different from all user names and all other role names. Unlike schema
objects,
roles are not contained in any schema. Therefore, a user who creates a
role
can be dropped with no effect on the role.
Roles ease the administration of end-user system and schema object
privileges.
However, roles are not meant to be used by application developers,
because the privileges to access schema objects within stored
programmatic
constructs must be granted directly.
Some general rules about debugging PLS-201 errors
==========================
a) First make sure that the object you reference really do exists.
This can be done by checking the error code we get during
compilation
Error during compilation of procedure 'BOGUS'
Example:
2/13 PLS-00201: identifier 'SCOTT.FOO' must be declared
2/13 PL/SQL: Item ignored
4/5 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression
is
incomplete or malformed
The error stack states that on line 2 position 13 we have a un-
declared
identifier
Note:
We can use the same method for checking the error
4/26 PL/SQL: ORA-00942: table or view does not exist
Since the source is stored in the database we could use the table
USER_SOURCE
to find out more about the error
SQL> SELECT text FROM USER_SOURCE
2 WHERE NAME = 'BOGUS'
3 AND LINE = 2;
TEXT
--------------------------
my_values scott.foo.id%TYPE;
SQL>
We can use the same method for checking the error
4/26 PL/SQL: ORA-00942: table or view does not exist
This means there is a reference to the id column of the foo object
in the scott schema
So check the scott schema if the object do exists. If not then the
error expected
since we reference an object that do not exists.
If the object exists then check that the scott.fo object has been
explicitly
granted to the owner of the 'BOGUS' procedure.
Reference:
[NOTE:168168.1] Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111 in
PL/SQL,
works in SQL*Plus
Example of common situation where PLS-201 is throwned as error
==========================
a) ROLES versus EXPLICIT GRANT
--------------------------
This code will fail even though the select will work the
creation of the procedure will fail.
===================== code begins here ======================
connect / as sysdba
grant create role to scott;
connect scott/tiger
drop role foobar;
drop table foo;
create table foo (id number);
insert into foo values(1);
commit;
create role foobar;
grant all on foo to foobar;
grant foobar to testcase;
connect testcase/manager
drop procedure bogus;
select * from scott.foo;
create procedure bogus is
my_values scott.foo.id%TYPE;
cursor c1 is
select id from scott.foo;
begin
open c1;
fetch c1 into my_values;
close c1;
end;
/
show error
===================== code ends here ======================
Example output:
Warning: Procedure created with compilation errors.
SQL> Errors for PROCEDURE BOGUS:
LINE/COL ERROR
-------- --------------------------
---
2/13 PLS-00201: identifier 'SCOTT.FOO' must be declared
2/13 PL/SQL: Item ignored
4/5 PL/SQL: SQL Statement ignored
4/26 PL/SQL: ORA-00942: table or view does not exist
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
SQL>
Solution:
=========
As scott explicit grant the necessary privilige to testcase schema
Roles are *not* used for PL/SQL code.
Example:
connect scott/tiger
grant all on foo to testcase;
connect testcase/manager
alter procedure bogus compile;
exec bogus;
Example output:
SQL> Connected.
SQL> Grant succeeded.
SQL> Connected.
SQL> Procedure altered.
PL/SQL procedure successfully completed.
SQL>
[NOTE:27287.1] OERR:PLS.201
===
b) PLS-201 WHEN WORKING WITH DYNAMIC VIEWS OR OTHER DICTIONARY BASED
OBJECTS.
--------------------------
------
Another common scenario is using dynamic dictionary views like
v$session.
Since those objects are views we cannot grant them directly.
Use following reference Note:
[NOTE:1013570.102] PLS-201 WHEN USING V$SESSION OR OTHER DYNAMIC VIEW
Also note that any priviliges granted by SYS are lost if you do a full
export and import of a database. Like moving a database using export
and import. Therefore it is recommended to save all your necessary
grant steps in a SQL script for re-usability
Reference:
[NOTE:97902.1] After Full Import all Object Privileges Granted by SYS
are Lost
===
c) PLS-00201 when invoking system packages like DBMS_*
--------------------------
Solution:
Explicitly grant execute permission on the system package to the owner
of the
PL/SQL package/procedure/function
d) PLS-00201 when invoking system packages like DBMS_LOCK after
migrating from 8i to 9i
--------------------------
----------------
Solution:
---------
Explicitly grant execute permission the system package to the owner of
the
PL/SQL package/procedure/function
Reference:
[BUG:2784411] GETTING PLS-201 WHEN EXECUTING DBMS_LOCK PACKAGE
[NOTE:47316.1] PARAMETER:O7_DICTIONARY_AC
e) PLS-00201 when referencing remote objects over database links
--------------------------
There are numerous reasons for this error to occur but the following
issues are
frequently reasons.
Reference:
[BUG:2324247] COMPILATION ERRORS WHEN DBLINK NAME IS THE SAME THAN
REMOTE DB INSTANCE NAME
[NOTE:208065.1] Accessing a Loopback DBLINK in PL/SQL Fails With
PLS-00201
[NOTE:1029662.6] PLS-000201 ERROR WHEN EXECUTING A REMOTE STORED
PROCEDURED THOUGH GATEWAY
f) PLS-00201 when using the wrap utility.
--------------------------
This following piece of code will work if compiled normally
Example: nvl2.sql
create or replace procedure test_nvl2 is
my_last_name varchar2(30);
my_salary number(10);
my_income number(10);
cursor c1 is
SELECT ename, sal, NVL2(comm,
sal + (sal * comm), sal) income
FROM emp WHERE ename like 'S%'
ORDER BY ename;
begin
open c1;
loop
fetch c1 into my_last_name,my_salary,my_
dbms_output.put_line(my_la
exit when c1%NOTFOUND;
end loop;
close c1;
end;
/
However, using this with wrap in 9iR2 and compile the code will
generate
an PLS-00201 error like
wrap iname=nvl2.sql
SQL> show error
Errors for PROCEDURE TEST_NVL2:
LINE/COL ERROR
-------- --------------------------
8/1 PL/SQL: SQL Statement ignored
8/20 PLS-00201: identifier 'NVL2' must be declared
SQL>
The explanation for this is documented as
Some recent SQL syntax is not supported by the wrap utility by default.
To enable the support for all SQL syntax, specify the option
edebug=wrap_new_sql (with no dash).
Workaround:
-----------
wrap edebug=wrap_new_sql iname=nvl2.sql
Reference:
[NOTE:250949.1] how to wrap new sql using wrap utility
RELATED DOCUMENTS
-----------------
[BUG:2324247] COMPILATION ERRORS WHEN DBLINK NAME IS THE SAME THAN
REMOTE DB INSTANCE NAME
[BUG:2784411] GETTING PLS-201 WHEN EXECUTING DBMS_LOCK PACKAGE
[NOTE:27287.1] OERR:PLS.201
[NOTE:168168.1] Getting ORA-942 or ORA-1031 and PLS-201 or ORA-28111
in PL/SQL, works in SQL*Plus
[NOTE:1013570.102] PLS-201 WHEN USING V$SESSION OR OTHER DYNAMIC VIEW
[NOTE:13615.1] Roles and Privileges Administration and Restrictions
[NOTE:97902.1] After Full Import all Object Privileges Granted by
SYS are Lost
[NOTE:100056.1] How to Find a Package's Privilege and Grantee User
Information
[NOTE:47316.1] PARAMETER:O7_DICTIONARY_AC
[NOTE:208065.1] Accessing a Loopback DBLINK in PL/SQL Fails With
PLS-00201
[NOTE:1029662.6] PLS-000201 ERROR WHEN EXECUTING A REMOTE STORED
PROCEDURED THOUGH GATEWAY
[NOTE:250949.1] How to wrap new sql using wrap utility
SQL Language Reference
Oracle Database Concepts
PL/SQL Users Guide and Reference
ASKER
ok so, i checked with our Jr. DBA and he said that database wasnt set up to allow explicit grants on objects and is only avialiablr via roles. however, im not sure that the explict grant is the answer since im still able to execute basic select statements and get results back. any other thoughts on that?
chedgey,
reading your post now.
thanks,
matt
chedgey,
reading your post now.
thanks,
matt
mnye:
---->however, im not sure that the explict grant is the answer since im still able to execute basic select statements and get results back.
I can bet it's the ROLE problem.
Oracle ROLE does not apply in PLSQL procedure.
SO in this case, you have to explicitly grant on object level.
---->however, im not sure that the explict grant is the answer since im still able to execute basic select statements and get results back.
I can bet it's the ROLE problem.
Oracle ROLE does not apply in PLSQL procedure.
SO in this case, you have to explicitly grant on object level.
If explicit grants cannot be given to the user you cannot create stored procedures or packages using tables in other schemas. There is NO way of doing it via roles.
mnye,
If you are using access through roles then I agree with SeaZodiac that it is almost certainly the roles causing the problem. In the Metalink article that I pasted in for you read the section about differences in name resolution between SQL and PL/SQL for the answer.
Regards
Chedgey
If you are using access through roles then I agree with SeaZodiac that it is almost certainly the roles causing the problem. In the Metalink article that I pasted in for you read the section about differences in name resolution between SQL and PL/SQL for the answer.
Regards
Chedgey
ASKER
my Sr. DBA will be back tommorow, so i will see if he can grant the explicit access to these tables. thanks for the help on this and ill be back tommorrow to let you know how it goes.
thanks,
matt
thanks,
matt
ASKER
thanks guys that solved the problem!
mnye
mnye
kindly give a suggestion for solving this problem..
this will work on sql
this is migrate from the sql ..
by executing the stored procedure in oracle 11g this error will come .
PLS000201 :identifier'utils.incremen
thanks in advance
this will work on sql
this is migrate from the sql ..
by executing the stored procedure in oracle 11g this error will come .
PLS000201 :identifier'utils.incremen
thanks in advance
ASKER
Thanks!