0 Replies Latest reply on Jun 11, 2018 2:26 PM by 3f96b779-d9b0-485c-8cc2-e826c4afbebc

    SQL Developer Unit Test. Null error

    3f96b779-d9b0-485c-8cc2-e826c4afbebc

      So my question is about SQL Developer Unit Test Suite. I am using a windows machine , SQL Developer version 4.1.2.20 and Java version 1.8. I am creating a unit test for a procedure with ref cursor output, when I run my test which is testing for an exception of NULL input value I get an error: java.lang.NullPointerException:null.. please could you point me in the right direction to work around this error. I was told for earlier version for Java and SQL Developer Unit Test this error does not persist. Thanks.

       

      Here is the use case:

      Sample Procedure:
      PROCEDURE get_person(userid IN person.userid%TYPE,
      person_data OUT SYS_REFCURSOR) AS
      exc_invalid EXCEPTION;
      lower_userid person.userid%TYPE;

      BEGIN
      IF userid IS NULL THEN
      RAISE exc_invalid;
      END IF;

      lower_userid :=lower(user_id);

      OPEN person_data FOR
      SELECT *
      FROM person_table
      WHERE lower(user_id) = lower_userid;

      EXCEPTION
      WHEN exc_invalidTHEN
      –code here
      END get_person;

      Unit Test for if Userid is NULL:
      Specify parameter:
      Parameter Datatype in/out input Result TestResult
      USERID VARCHAR2 IN Userid
      PERSON_DATA REF CURSOR OUT PERSON_DATA$

      Dynamic Value Query:
      select NULL as USERID , NULL as PERSON_DATA$ from dual

      Expected Result Exception 2001 Enter expected error number or “ANY”

      Process Validation:
      Query returning no row(s)
      Apply
      select * from person_table
      where lower(t_userid) = ‘{USERID}’;

      When I run this unit test I get:
      Implementation – NegativeTestCase_InputIsNull ERROR 89.0 java.lang.NullPointerException: null
      oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:366)
      oracle.jdbc.driver.OracleCallableStatement.getCursor(OracleCallableStatement.java:2645)
      oracle.jdbc.driver.OracleCallableStatementWrapper.getCursor(OracleCallableStatementWrapper.java:956)
      oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCStatement$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.getCursor(Unknown Source)
      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingCURSOR.customOutputByPosition(CallableBindingCURSOR.java:53)
      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingDatumAtName.customOutput(CallableBindingDatumAtName.java:123)
      oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.getOutput(CallableBindingImpl.java:74)
      oracle.dbtools.unit_test.testObjects.UtTestImplArgs.getOutput(UtTestImplArgs.java:320)
      oracle.dbtools.unit_test.runner.Runner.executeRunnerObject(Runner.java: …
      Operation Call ERROR 89.0 java.lang.NullPointerException: null
      oracle.jdbc.driver.T4CResultSetAccessor.getCursor(T4CResultSetAccessor.java:366)
      oracle.jdbc.driver.OracleCallableStatement.getCursor(OracleCallableStatement.java:2645)
      oracle.jdbc.driver.OracleCallableStatementWrapper.getCursor(OracleCallableStatementWrapper.java:956)
      oracle.jdbc.proxy.oracle$1dbtools$1raptor$1proxy$1driver$1oracle$1RaptorProxyOJDBCStatement$2oracle$1jdbc$1internal$1OracleCallableStatement$$$Proxy.getCursor(Unknown Source)
      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingCURSOR.customOutputByPosition(CallableBindingCURSOR.java:53)
      oracle.dbtools.raptor.datatypes.strategies.callablestatement.CallableBindingDatumAtName.customOutput(CallableBindingDatumAtName.java:123)
      oracle.dbtools.raptor.datatypes.impl.CallableBindingImpl.getOutput(CallableBindingImpl.java:74)
      oracle.dbtools.unit_test.testObjects.UtTestImplArgs.getOutput(UtTestImplArgs.java:320)
      oracle.dbtools.unit_test.runner.Runner.executeRunnerObject(Runner.java: …
      IN Parameter #1 – USERID Value: [null]
      OUT Parameter #2 – PERSON_DATA

      I tried to replicated my user case the best I can. Please let me know if you can help with this.