4 Replies Latest reply on Feb 26, 2014 8:48 AM by don123

    execute procedure




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


      SQL> show user



      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

          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;





          --- 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


          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;






          • 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.


            1 person found this helpful
            • 3. Re: execute procedure

              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

              1 person found this helpful
              • 4. Re: execute procedure

                HI, Thanks to all


                I was missing username when calling.