1 2 Previous Next 18 Replies Latest reply: Mar 5, 2013 5:57 AM by CP RSS

    Calling a Procedure from one schema to other

    CP
      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
          JustinCave
          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
            >
            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
              >
              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
                >
                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
                  JustinCave
                  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
                    >
                    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
                      >
                      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
                        JustinCave
                        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
                          >
                          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
                            JustinCave
                            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
                              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
                                JustinCave
                                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
                                  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
                                    JustinCave
                                    Is schema1.procedure1 an invoker's rights stored procedure? Or a definer's rights stored procedure?

                                    Justin
                                    1 2 Previous Next