All geek questions in one place

"Crawl" table or view does not exist "in package compilation

There are two schemas in the Oracle database.

MYSCHEMA , which is controlled by me.

OTHERSCHEMA , which is not controlled by me.

I just know that I can get the result from select * from OTHERSCHEMA.OTHEROBJECT . However, OTHEROBJECT is synonymous with .

In my package I have an operator like

 insert into MYSCHEMA.MYTABLE(COL1) select COL1 from OTHERSCHEMA.OTHEROBJECT;

But that gave me Table or view does not exist.

How can I solve or work around this problem? Thanks!

+6
source share
1 answer

I assume that you have received the privilege to select from otherschema.otherobject using a role, as shown in a direct grant (for example, grant all on otherschema.otherobject to myschema ). If so, privileges in this role will not be used to determine what rights you have in the PL / SQL block.

See also How Roles Work in PL / SQL Blocks (Oracle Docu, where indicated in Roles Used in Named Blocks with Definer Rights

All roles are disabled in any named PL / SQL block (stored procedure, function, or trigger) that runs with qualifier rights. Roles are not used to verify privileges, and you cannot set roles as part of the rights determination procedure.)

+11
source

Source: https://habr.com/ru/post/904288/

More articles:


All Articles