4 Replies Latest reply on Dec 23, 2013 3:31 AM by Ening-Oracle

    Unit Test Parameters


      Is it possible to use the unit test framework for procedures / functions that take a table as a parameter?


      For example:

      TYPE simple_record is RECORD (column1 VARCHAR2(50));
      TYPE simple_table_type IS TABLE OF simple_record INDEX BY PLS_INTEGER;

      PROCEDURE simple_proc(i_table IN simple_table_type);


      When I try to create a unit test for this procedure in SQL developer, I get the following error:

      SIMPLE_PACKAGE.SIMPLE_PROC failed: Invalid column type: -333










      oracle.dbtools.unit_test.runner.Runner.executeRunnerObject(R ...

        • 1. Re: Unit Test Parameters

          Post the full versions you are using of sql developer, ojdbc jar file and Oracle db.

          • 2. Re: Unit Test Parameters

            Oracle Database 11g Enterprise Edition Release


            SQL Developer  Version and Version (same error for both)

            • 3. Re: Unit Test Parameters

              Thanks for the info.


              The 'Oracle SQL Developer 3.0: Overview and New Features' says that associative arrays are supported for unit tests:



              Support added for abstract data types



              PL/SQL VARRAY



              PL/SQL Nested Table



              PL/SQL Associative Array



              Ref Cursors

              I can't find any example that demonstrates that support nor do I understand how they might have implemented that support.


              PL/SQL types are not accessible by SQL so you can only create or reference them from PL/SQL. So the only way I know of to test a procedure that has one of those as a parameter is to write a wrapper procedure that creates the proper collection type and then calls the procedure you want to test.


              Is it possible to use the unit test framework for procedures / functions that take a table as a parameter?

              Can we assume you know that your 'INDEXED BY' type is NOT a 'table'? It is an associate array.


              You can certainly test using SQL types such as a nested table. But sql developer is still lacking some basic functionality in the unit test area, such as the ability to capture/display any DBMS_OUTPUT.


              Afraid you will need to wait for one of the SQL DEV team to respond to your thread. I suggest you edit your thread subject to specifically include ASSOCIATE ARRAY; maybe that will get their attention.

              • 4. Re: Unit Test Parameters

                reproducible, and log one bug for tracking

                Let's wait for developer's reply


                SQL Developer QA Team

                Emily Ning