3 Replies Latest reply on Aug 4, 2016 11:31 PM by rp0428

    Unit Test failing execution call to procedure

    Vin Steele1

      We have a problem executing Unit Tests for procedures in a particular schema. We get failure of the unit test to process, with an ORA-06550 and a PLS-00302 on the call to execute the procedure, saying "component 'PROCEDURE_NAME' must be declared". I hypothesize that one or more permissions/roles/grants are missing, but we do not know what they are. Any help would be greatly appreciated.

       

      We are using:

      Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

      PL/SQL Release 11.2.0.2.0 - Production

      "CORE 11.2.0.2.0 Production"

      TNS for Linux: Version 11.2.0.2.0 - Production

      NLSRTL Version 11.2.0.2.0 - Production

       

      About

      -----

       

       

      Oracle SQL Developer 4.1.3.20

      Version 4.1.3.20

      Build MAIN-20.78

       

       

      IDE Version: 12.2.1.0.42.151001.0541

      Product ID: oracle.sqldeveloper

      Product Version: 12.2.0.20.78

       

       

      Version

      -------

       

       

      Component Version

      ========= =======

      Oracle IDE 4.1.3.20.78

      Java(TM) Platform 1.8.0_60

      Versioning Support 4.1.3.20.78

       

       

      Properties

      ----------

       

       

      Name Value

      ==== =====

       

       

      Extensions

      ----------

       

      We have set up our environment with one shared repository, from which tests can be run in multiple Oracle instances and multiple schemas within those instances. Recently, we had a new procedure in a schema where we had not run the unit tester before. We attempted to create unit tests for that procedure. It failed with an ORA-06550 and a PLS-00302 on the call to execute the procedure, saying "component 'PROCEDURE2' must be declared".

       

      Here are two identical procedures (with different names), created and run in the same Oracle instance, but in different schemas. One tests successfully, the other fails with the PLS-00302.

       

      In schema UTL (success):

      create or replace PROCEDURE PROCEDURE1 AS

      BEGIN

        NULL;

      END PROCEDURE1;

       

      Test:

      <?xml version = '1.0' encoding = 'Cp1252'?>

      <UT>

         <object class="oracle.dbtools.unit_test.testObjects.UtTest">

            <test id="f1efa44c-3608-411e-beaa-3bceae1ed234" name="PROCEDURE1" obj_name="PROCEDURE1" obj_owner="UTL" obj_type="PROCEDURE" obj_call="" src_conn_name="IdeConnections%23DWDEV2+UTL" coverage="false">

               <test_impl id="ab195504-fee2-422a-884f-2172cb56d73f" test_id="f1efa44c-3608-411e-beaa-3bceae1ed234" name="Test Implementation 1" expected_ret="" expected_err=""/>

            </test>

         </object>

      </UT>

      Debug of Test:

      The following procedure was run.

       

       

      Execution Call

      BEGIN

      "UTL"."PROCEDURE1";

      END;

       

       

      Bind variables used

       

       

      Execution Results

      SUCCESS

       

      In schema STG_CMN (failure):

      create or replace PROCEDURE PROCEDURE2 AS

      BEGIN

        NULL;

      END PROCEDURE2;

       

      Test:

      <?xml version = '1.0' encoding = 'Cp1252'?>

      <UT>

         <object class="oracle.dbtools.unit_test.testObjects.UtTest">

            <test id="e52caf87-aec5-40dc-b9a0-aa8ba1b794f7" name="PROCEDURE2" obj_name="PROCEDURE2" obj_owner="STG_CMN" obj_type="PROCEDURE" obj_call="" src_conn_name="IdeConnections%23DWDEV2+STG_CMN" coverage="false">

               <test_impl id="ce5d5a5b-ceb2-459f-a621-796bbcaae521" test_id="e52caf87-aec5-40dc-b9a0-aa8ba1b794f7" name="Test Implementation 1" expected_ret="" expected_err=""/>

            </test>

         </object>

      </UT>

      Debug of Test:

      The following procedure was run.

       

       

      Execution Call

      BEGIN

      "STG_CMN"."PROCEDURE2";

      END;

       

       

      Bind variables used

       

       

      Execution Results

      ERROR

      Expected exception: [NONE], Received: [6550: ORA-06550: line 2, column 13:

      PLS-00302: component 'PROCEDURE2' must be declared

      ORA-06550: line 2, column 3:

      PL/SQL: Statement ignored

      ]

        • 1. Re: Unit Test failing execution call to procedure

          I hypothesize that one or more permissions/roles/grants are missing, but we do not know what they are. Any help would be greatly appreciated.

           

          We don't know what they are either because you haven't posted ANY info about what privileges you have granted or what user is executing the tests.

           

          In Oracle EVERYTHING is forbidden except that which is granted.

           

          If you haven't granted execute permission on the code then no one but the owner can execute it.

          • 2. Re: Unit Test failing execution call to procedure
            Vin Steele1

            We have set up our environment with one shared repository, from which tests can be run in multiple Oracle instances and multiple schemas within those instances.

             

            How do I grant execution permissions to a different schema in a different Oracle instance? Obviously we did it once (for other schemas, as the unit test repository has worked for them), but neither the DBA nor I understand what we need to do now. 

            • 3. Re: Unit Test failing execution call to procedure

              We have set up our environment with one shared repository, from which tests can be run in multiple Oracle instances and multiple schemas within those instances.

               

              How do I grant execution permissions to a different schema in a different Oracle instance? Obviously we did it once (for other schemas, as the unit test repository has worked for them), but neither the DBA nor I understand what we need to do now.

              Hmmm - are you SURE you provided that DBA will what I said in my reply.

               

              Any DBA I have ever known is familiar with system and object privileges and how to use them.

               

              Even if they weren't they certainly know how to find that info in the Oracle docs.

               

              This is the first result of a simple search for 'oracle 12c execute permission'

              https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG99868

              Procedure privileges. Procedures, including standalone procedures and functions, can be granted the EXECUTE privilege. See "Procedure Privileges" for more information.

              Procedure Privileges

              To enable users to be able to run procedures, functions (either standalone or in packages), you must grant them the EXECUTE privilege. You can grant users privileges to create or replace their procedures, or to compile a procedure. You should be aware of how procedure privileges affect packages and package objects.

              Topics: