4 Replies Latest reply: Feb 26, 2014 2:48 AM by don123 RSS

    execute procedure

    don123

      Hi

       

      I have created a procedure and works fine and granted privileges to other user to execute.

       

      SQL> show user

      SYS

       

      SQL> grant execute on user1.fixdim to user2;

       

      Then I logged in as user2 and tried to execute the procedure fixdim, but there is error 'FIXDIM' must be declared.

       

      Please help me with your suggestions.

        • 1. Re: execute procedure
          AnnPricks E

          How you are executing the procedure.. It should be like

          EXEC user1.fixdim;

           

          See the below testcases

          -- create one procedure in scott

          SCOTT@orcl>CREATE OR REPLACE PROCEDURE test_proc(v_empno OUT NUMBER)

            2  AS

            3  BEGIN

            4  v_empno := 1;

            5  END;

            6  /

           

          Procedure created.


           

          SCOTT@orcl>VAR l_empno NUMBER;

          SCOTT@orcl>EXEC test_proc(:l_empno);

           

          PL/SQL procedure successfully completed.

           

          SCOTT@orcl>PRINT l_empno;

           

             L_EMPNO

          ----------

                   1

          --- grant execute privilege to test_user

           

           

          SYS@orcl>GRANT EXECUTE ON scott.test_proc TO test_user;

           

          Grant succeeded.


          --- Run the procedure in test_user

           

           

          @>conn test_user/test_user

          Connected.

          TEST_USER@orcl>VAR l_empno NUMBER;

          --- See the below error

          TEST_USER@orcl>EXEC test_proc(:l_empno);

          BEGIN test_proc(:l_empno); END;

           

                *

          ERROR at line 1:

          ORA-06550: line 1, column 7:

          PLS-00201: identifier 'TEST_PROC' must be declared

          ORA-06550: line 1, column 7:

          PL/SQL: Statement ignored


          --- After modified with schemaname.procedurename

           

           

          TEST_USER@orcl>EXEC SCOTT.test_proc(:l_empno);

           

          PL/SQL procedure successfully completed.


           

          TEST_USER@orcl>PRINT l_empno;

           

             L_EMPNO

          ----------

                   1

           

          • 2. Re: execute procedure
            Partha Sarathy S

            Did you execute using USER1.FIXDIM? because if you do not specify schema name then it will search for the object in your schema.

             

            If you are calling this procedure straight away then you could use like EXEC USER1.FIXDIM.

            Else if you are calling inside a procedure just use USER1.FIXDIM.

             

            • 3. Re: execute procedure
              KarK

              Can you post the statement , which you used to execute the procedure ?

               

              You should execute the procedure by mentioning the username who created the procedure).

               

              In your case , it should be like this:

               

              EXEC user1.fixdim;    --- Mention the username

              • 4. Re: execute procedure
                don123

                HI, Thanks to all

                 

                I was missing username when calling.