4 Replies Latest reply: Dec 22, 2013 9:31 PM by Ening-Oracle RSS

    Unit Test Parameters

    user13341346

      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.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3950)

      oracle.jdbc.driver.OraclePreparedStatement.setPlsqlIndexTableInternal(OraclePreparedStatement.java:9147)

      oracle.jdbc.driver.OracleCallableStatement.setPlsqlIndexTable(OracleCallableStatement.java:6573)

      oracle.jdbc.driver.OraclePreparedStatementWrapper.setPlsqlIndexTable(OraclePreparedStatementWrapper.java:1439)

      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingIndexTABLE.customBindIN(CallableBindingIndexTABLE.java:38)

      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingDatumAtName.customBindIN(CallableBindingDatumAtName.java:156)

      oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.customBind(CallableBindingImpl.java:99)

      oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.bind(CallableBindingImpl.java:93)

      oracle.dbtools.unit_test.testObjects.UtTestImplArgs.bind(UtTestImplArgs.java:294)

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

        • 1. Re: Unit Test Parameters
          rp0428

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


          • 2. Re: Unit Test Parameters
            user13341346

            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

            ojdbc6.jar

            SQL Developer  Version 4.0.0.13 and Version 3.2.20.09 (same error for both)

            • 3. Re: Unit Test Parameters
              rp0428

              Thanks for the info.

               

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

              http://www.oracle.com/technetwork/developer-tools/sql-developer/sqldevnewfeatures30-presentation-395556.pdf

               

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

                reproducible, and log one bug for tracking

                Let's wait for developer's reply

                 

                SQL Developer QA Team

                Emily Ning