QUESTION:
Troubleshooting (Describe the reason why can not execute procedure? )
I have two user "pccxx" and "pccback" and one role "role_back" in my oracle databse.
First.
I grant the select table privilegs to the role "role_back" by below script.
GRANT SELECT ON PCC_ACCEPTM TO ROLE_BACKUP;
(give select PCCXX.PCC_ACCEPTM ==> "role_back" )
Second.
I grant the role "role_back" to another user "pccback" by below script.
GRANT "role_back" TO "pccback";
("role_back" ==> pccback" )
I can select pccxx.pcc_acceptm table by user "pccback";
SQL> connect pccback/pccback@luxpccdb
SQL> SELECT ACCP_NO FROM PCCXX.PCC_ACCEPTM WHERE ACCP_NO = 'W021125001';
ACCP_NO
--- ------------
W021125001
but I can not execute procedure by below script ,
oracle server return ORA-00942 table or view does not exist ,
--------------------------------------------------------------------------------
PROCEDURE pro_test
IS
query_str VARCHAR2(200);
BEGIN
query_str := 'INSERT INTO PCC_ACCEPTM_2002(FACT_NO,YYMM,ACCP_NO) SELECT ACCP_NO FROM PCCXX.PCC_ACCEPTM WHERE ACCP_NO = ''W021125001''';
EXECUTE IMMEDIATE query_str ;
COMMIT WORK ;
END;
--------------------------------------------------------------------------------
SQL> EXEC PRO_TEST
begin PRO_TEST; end;
ERROR 在行 1:
ORA-00942 table or view does not exist
ORA-06512: "BACKUP.PRO_TEST", line 6
ORA-06512: line 1
Please tell me the reason why can not execute procedure?
