ORACLE11g, table or view does not exist while creating procedure

Question

There're two schema in db, I create a materialized view -- 'MV1' and grant it successfully，selecting from it in other schema is ok.

GRANT select ON schemaA.MV_CA_REVENU_MS_GEO TO read;
/
GRANT select ON schemaA.MV_CA_REVENU_MS_GEO TO write;
/
GRANT update ON schemaA.MV_CA_REVENU_MS_GEO TO write;
/

But while compiling the procedure, there's error message says 'table or view does not exist' for 'MV1'. The procedure code is:

create or replace 
PROCEDURE                     SP_NAME (args ... ) is
  .
  .
begin
    INSERT INTO tableName(
        .
        .
      ) SELECT ...
          FROM (SELECT ...
                  FROM MV1  -- **schemaA.MV1 doesn't work either** 
                 WHERE 
end SP_NAME;
/
GRANT EXECUTE ON schemaB.SP_NAME TO read;
GRANT DEBUG ON schemaB.SP_NAME TO read;
GRANT EXECUTE ON schemaB.SP_NAME TO write;
GRANT DEBUG ON schemaB.SP_NAME TO write;
/
CREATE or replace PUBLIC SYNONYM SP_NAME FOR schemaB.SP_NAME;
/

I try to add schemaA in front of MV1, it doesn't work. Is there any other step should I take a check?

I assume this is the same issue as http://stackoverflow.com/questions/8599393/bypass-table-or-view-does-not-exist-in-package-compilation . So just check solution there. You need to use direct grants instead of roles. — Nickolay Komar, Apr 24 '14 at 08:19

score 0 · Answer 1 · answered Apr 24 '14 at 07:50

0

Are "read" and "write" schema names or role names? Permissions in Oracle granted indirectly via roles are not available when compiling stored procedures, functions, and packages

answered Apr 24 '14 at 07:50

Rene

10,391
5
33
46

ORACLE11g, table or view does not exist while creating procedure

1 Answers1