Solved

"Identifier Must Be Declaired" error on Package Procedure

Posted on 2004-10-07
11
7,860 Views
Last Modified: 2012-06-20
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
0
Comment
Question by:mnye
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 10

Assisted Solution

by:SDutta
SDutta earned 125 total points
ID: 12249762
Did you connect as TESTHR and GRANT SELECT ON BENEFIT TO <your package owner>

That is the most obvious starting point. Even without the above grant you can create a synonym but cannot use it. The grant has to be given explicitly to the schema owner and not via a ROLE.


0
 
LVL 6

Accepted Solution

by:
chedgey earned 75 total points
ID: 12249776
If i remember back to 8i days there was a problem with compiling PL/SQL accessing non-local objects if privilege was derived through a role.

Does the user you are running this creation script through have directly granted privileges on TESTHR.BENEFITS or do they derive them through a role? If the latter then have the TESTHR schema grant the privilegges explicitly to the user.

Regards

Chedgey
0
 
LVL 7

Author Comment

by:mnye
ID: 12249973
ok, I know I have "god" access to all schemas in this db (its test env) but i believe it is via roles.  I will go check with my dbas on getting explicit access and post back in a bit.  

Thanks!
0
 
LVL 6

Expert Comment

by:chedgey
ID: 12250060
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 owner
 
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 owner
 
Reference:
 
[BUG:2784411]  GETTING PLS-201 WHEN EXECUTING DBMS_LOCK PACKAGE
[NOTE:47316.1] PARAMETER:O7_DICTIONARY_ACCESSIBILITY
 
 
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_income;
    dbms_output.put_line(my_last_name||' '||my_salary||' '||my_income);
    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_ACCESSIBILITY
[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
 

0
 
LVL 7

Author Comment

by:mnye
ID: 12250279
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 23

Expert Comment

by:seazodiac
ID: 12250312
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.
0
 
LVL 10

Expert Comment

by:SDutta
ID: 12250335
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.

0
 
LVL 6

Expert Comment

by:chedgey
ID: 12250353
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
0
 
LVL 7

Author Comment

by:mnye
ID: 12250445
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
0
 
LVL 7

Author Comment

by:mnye
ID: 12259534
thanks guys that solved the problem!

mnye
0
 

Expert Comment

by:cartheees
ID: 38107674
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.incrementTrancount' must be declared.

thanks in advance
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now