-2

I have created two identical procedures in two schemas. I have then granted execute on each procedure to a third schema. I call each procedure using the qualified object name. When calling the procedure in one schema there is no issue, when calling the second I get PLS-00302: component must be declared.

Why?

Here's the code I'm running, actual usernames/passwords have been obscured. Firstly the procedures are created in USER1 and USER2 and execute is granted to USER3

connect user1/password@db/db
-- */
create or replace procedure blah is
begin
  null;
end;
/

Procedure created.

grant execute on blah to user3;

Grant succeeded.

connect user2/password@db/db
-- */
create or replace procedure blah is
begin
  null;
end;
/

Procedure created.

grant execute on blah to user3;

Grant succeeded.

Then I connect to USER3 and attempt to execute the procedures

connect user3/password@db/db
-- */
exec user1.blah

PL/SQL procedure successfully completed.

exec user2.blah

BEGIN user2.blah; END;

           *
ERROR at line 1:
ORA-06550: line 1, column 12:
PLS-00302: component 'BLAH' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

As far as I can tell this doesn't appear to be a permissions issue (though it obviously is). The system privileges of the 2 users are identical, the following queries return no results:

select privilege, admin_option, common from dba_sys_privs where grantee = 'USER1'
 minus
select privilege, admin_option, common from dba_sys_privs where grantee = 'USER2';

select privilege, admin_option, common from dba_sys_privs where grantee = 'USER2'
 minus
select privilege, admin_option, common from dba_sys_privs where grantee = 'USER1';

Querying USER_TAB_PRIVS_RECD in USER3 returns the following, no differences

select * from user_tab_privs_recd where table_name = 'BLAH';

OWNER TABLE_NAME GRANTOR PRIVILEGE GRANTABLE HIERARCHY COMMON TYPE
----- ---------- ------- --------- --------- --------- ------ ---------
USER1 BLAH       USER1   EXECUTE   NO        NO        NO     PROCEDURE
USER2 BLAH       USER2   EXECUTE   NO        NO        NO     PROCEDURE

closed as off-topic by Philᵀᴹ, mustaccio, RLF, JSapkota, Tom V Nov 16 '16 at 14:26

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – mustaccio, RLF, JSapkota, Tom V
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Exact same code is working fine for me as expected. You might be connected to different instance of database or something else. – JSapkota Nov 15 '16 at 14:52
  • I'd have mentioned if it was a RAC @JSapkota. There's only one instance, and this was run locally. – Ben Nov 15 '16 at 14:58
  • I'm voting to close this question as off-topic because it cannot be reproduced independently – Philᵀᴹ Nov 15 '16 at 16:03
  • That is kind of the point of the question @Phil, it's true. If I could reproduce it I wouldn't be asking the question... – Ben Nov 15 '16 at 16:13
  • @Ben if you can't even reproduce it yourself, then there's something missing in your question, and we're unlikely able to help – Philᵀᴹ Nov 15 '16 at 16:30
2

Closest way I can reproduce this is if there's a package in user3 with the same name as user2. It tries to find the procedure BLAH in the package user2 rather than in the schema user2.

So query SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='USER2'

  • You, sir, are a miracle worker - that's the answer. – Ben Nov 16 '16 at 8:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.