CODEWITHSUNDEEP
oracle-databaseplsqlprivilegesuser-defined-types
bbaley
bbaley

Reputation: 309

PLS-00201 identifier must be declared referencing user defined type as SYS

I am having a problem with certain defined TYPEs in my package create scripts -

I receive the following for certain TYPEs

PLS-00201 identifier must be declared

For instance;

Error(7,23): PLS-00201: identifier 'another_schema.some_table_type' must be declared
Error(8,23): PLS-00201: identifier 'another_schema.some_object_type' must be declared

I should note that I am changing these scripts - which ran fine before - where previously they were run as the script owner, which now is a container schema/user only - and the create scripts are being executed as SYS. (it is a dev database and they have not created a user yet for me to use to do this other than SYS so far)

(The packages compiled previously -when run as the schema owner with appropriate privileges.)

This package (and various other objects, views procedures and functions) access both objects in its schema owns as well as those in another schema.

In this case - the issue is with SOME defined TYPEs (and tables of type) that are in the referenced schema - where I get the PLS-00201 error.

In the code below, when compiling the error is generated for the references to;

      l_tc_data       another_schema.some_table_type := another_schema.some_table_type();
      l_tc_obj        another_schema.some_object_type;

but not;

      l_loc_t        another_schema.location_ref_t;

which are in the same "other" schema.

create or replace PACKAGE BODY  example_schema.example_pkg 
AS
function getSomeData (p_project in varchar2, p_start_date in date, p_end_date in date, p_timezone in varchar2 := 'UTC')
return fancydatatabletype is
      l_cursor        SYS_REFCURSOR;
      l_tsdata        fancydatatabletype := fancydatatabletype();

      l_tc_data       another_schema.some_table_type := another_schema.some_table_type();
      l_tc_obj        another_schema.some_object_type;

      t_data          fancydatatype;
      l_project       VARCHAR2(20);
      l_timezone      VARCHAR2(40);
      l_start         DATE;
      l_end           DATE;

      l_loc_t        another_schema.location_ref_t; 
BEGIN
    l_tc_obj := another_schema.some_object_type(null, 1, 2, 3, 'test');

    -- .... more stuff
END getSomeData;

So my questions are; 1. could it possibly be an issue with privileges ? (running as SYS) 2. what other things should I look at that could produce this error ? (since they do exist)

I can successfully query the TYPES, so they do exist, are named the same and the user (SYS in this case) can "see" them;

select * from ALL_TYPES where type_name like 'some_table_type%';

I know I am being dumb, blind or just plain at the limits of my Oracle skills - so hoping someone can help me figure it out and learn for the next time !

Upvotes: 0

Views: 10152

Answers (1)

APC
APC

Reputation: 146339

The referenced objects are owned by ANOTHER_SCHEMA. You are attempting to use them in PL/SQL owned by EXAMPLE_SCHEMA. So the most likely explanation is the grants between the two schemas.

In order to use them in code, ANOTHER_SCHEMA must have granted privileges directly to EXAMPLE_SCHEMA, that is by name and not through a role. This is due to the way Oracle's security model works.

You can check the extant grants like this:

select table_name as object_name
       , privilege
       , grantor
from dba_tab_privs
where grantee = 'EXAMPLE_SCHEMA'
and table_owner = 'ANOTHER_SCHEMA'
/

Upvotes: 2

Related Questions