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
]