This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Mar 5, 2013 3:57 AM by CP RSS

Calling a Procedure from one schema to other

CP Newbie
Currently Being Moderated
I have 2 procedures.

Procedure1 is in Schema1. Procedure2 is in Schema2.
create or replace procedure schema2.procedure2
begin
--
logic
--
Schema1.Procedure1(input);
--
end;
I am not able to execute this proc. Getting an error 'table or view does not exist'. If I execute in Schema1, there is no error.

Procedure2 is being executed with SYS privileges.
  • 1. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    1) Did you grant schema 2 EXECUTE access on schema1.procedure1? If not, you'll need to do that.
    2) What, exactly, do you mean when you say "executed with SYS privileges"? If you mean that schema2 has been granted the SYSDBA role, that role has no bearing on a definer's rights stored procedure.

    Justin
  • 2. Re: Calling a Procedure from one schema to other
    rp0428 Guru
    Currently Being Moderated
    >
    I am not able to execute this proc. Getting an error 'table or view does not exist'. If I execute in Schema1, there is no error.
    >
    Roles are disabled in PL/SQL.

    The user of the procedure needs to have priviliges on the tables/objects granted directly to them; not thru a role.
  • 3. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    >
    Justin Cave wrote:

    1) Did you grant schema 2 EXECUTE access on schema1.procedure1? If not, you'll need to do that.
    2) What, exactly, do you mean when you say "executed with SYS privileges"? If you mean that schema2 has been granted the SYSDBA role, that role has no bearing on a definer's rights stored procedure.
    >

    schema2.procedure2 (which has schema1.procedure1) is being called from an OAQ. So the user which is logged for executing schema1.procedure1 is SYS (should be SYSDBA role).

    Should I give access to SYS role to execute schema1.procedure1 or I should give privileges to schema2?
  • 4. Re: Calling a Procedure from one schema to other
    rp0428 Guru
    Currently Being Moderated
    >
    Should I give access to SYS role to execute schema1.procedure1 or I should give privileges to schema2?
    >
    Neither. Reread your error message
    >
    Getting an error 'table or view does not exist'
    >
    There is a missing table/view privilege.
  • 5. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    Does OAQ stand for Oracle Advanced Queue? Or something else?

    In a definer's rights stored procedure, which appears to be what schema2.procedure2 is, the identity of the caller is irrelevant. The owner of the stored procedure (schema2) must be given EXECUTE access on schema1.procedure1 directly not via a role. Granting EXECUTE access on procedure1 to the SYSDBA role won't change anything even if the SYSDBA role has been granted to schema2 since, as we've said a couple times, a definer's rights stored procedure does not have access to privileges granted to the owner via roles.

    Justin
  • 6. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    >
    rp0428 wrote:
    There is a missing table/view privilege.
    >

    Table has Select, Insert, Update and Delete Privilege to the Role. Should I give access to the user directly for all the tables ?
  • 7. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    >
    Justin Cave wrote:

    Does OAQ stand for Oracle Advanced Queue? Or something else?
    >

    Yes. It stands for Oracle Advanced Queue.

    >
    In a definer's rights stored procedure, which appears to be what schema2.procedure2 is, the identity of the caller is irrelevant. The owner of the stored procedure (schema2) must be given EXECUTE access on schema1.procedure1 directly not via a role. Granting EXECUTE access on procedure1 to the SYSDBA role won't change anything even if the SYSDBA role has been granted to schema2 since, as we've said a couple times, a definer's rights stored procedure does not have access to privileges granted to the owner via roles.
    >

    Tried giving access to the User directly as well. Schema2 has Debug and Execute privileges on schema1.procedure1. Still same Issue.
  • 8. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    CP wrote:
    Tried giving access to the User directly as well. Schema2 has Debug and Execute privileges on schema1.procedure1. Still same Issue.
    Why do you believe that it is the call to schema1.procedure1 that is failing? If you are actually getting an ORA-00942 error, rather than a more generic `PROCEDURE1` is not a procedure or is undefined, what makes you believe that the problem is the call to schema1.procedure1?

    Justin
  • 9. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    >
    Why do you believe that it is the call to schema1.procedure1 that is failing? If you are actually getting an ORA-00942 error, rather than a more generic `PROCEDURE1` is not a procedure or is undefined, what makes you believe that the problem is the call to schema1.procedure1?
    >

    Because, I am able to execute the same procedure with same inputs successfully without any error when I am executing that in schema1.
  • 10. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    What is the exact error and the exact error stack that you are getting?

    Justin
  • 11. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    Error Message:
    ORA-00942: table or view does not exist
    ORA-06512: at "SCHEMA1.PROCEDURE1" line 36
    ORA-06512: at "SCHEMA1.PROCEDURE1" line 943
    ORA-06512: at "SCHEMA1.PROCEDURE1" line 1385
    ORA-06512: at line 2
    
    View program sources of error stack?
  • 12. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    CP wrote:
    View program sources of error stack?
    OK. So the problem has nothing to do with your inability to call procedure1 from procedure2.

    What's going on at line 36 of schema1.procedure1?

    Is schema1.procedure1 an invoker's rights stored procedure? Or a definer's rights stored procedure?

    Justin
  • 13. Re: Calling a Procedure from one schema to other
    CP Newbie
    Currently Being Moderated
    Have a cursor defined after begin in the Sub procedure which is being called in procedure1.

    Cursor has 3 tables. These 3 tables are present only in schema1. It is erroring out here.

    P.S.: When I added schema name before the table names in the cursor, it is not failing at this stage. It is entering in to the loop and failing at next level.
  • 14. Re: Calling a Procedure from one schema to other
    Justin Cave Oracle ACE
    Currently Being Moderated
    Is schema1.procedure1 an invoker's rights stored procedure? Or a definer's rights stored procedure?

    Justin
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points