Problem Resolution For roles and Stored Procedures.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:961430030094
http://asktom.oracle.com/~tkyte/Misc/RolesAndProcedures.html
As per http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:961430030094 with tom kyte, he has mentioned that we cannot compile this unless we give direct object privs to Owner of procedure, as at compile time AUTHID DEFINER and AUTHID CURRENT_USERS behave same. They both compile as per the definers privs.
We can resolve this issue by using NDS, as NDS does not validate object existance.. As following..
execute immediate ‘SELECT max(EFF_DATE) FROM CORPORATE.DIRECT_CAD’ INTO DtCurr_RepDate;
This will bypass compile time object validations. See following work-around.
12:16:57 WEBP18FSCRIPTS@WEBP18F:-> create or replace procedure pvm_1
12:16:57 2 AUTHID CURRENT_USER
12:16:57 3 as
12:16:57 4 Myint number;
12:16:57 5 begin
12:16:57 6 execute immediate ‘select count(1) from browse_channel’ into myint;
12:16:57 7 dbms_output.put_line (‘myint = ‘ || myint);
12:16:57 8 end;
12:16:57 9 /
Procedure created.
Elapsed: 00:00:00.05
12:16:57 WEBP18FSCRIPTS@WEBP18F:-> exec pvm_1
myint = 19
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.01
12:16:59 WEBP18FSCRIPTS@WEBP18F:->
http://www.adp-gmbh.ch/ora/plsql/authid.html