Sending empty list to stored procedure

Question

I have created a custom Type in oracle as below.

create or replace TYPE  "ABC_OBJECT"  AS OBJECT (
   ticket_no  VARCHAR2(50),
);

create or replace TYPE "ABC_OBJECT_LIST"  IS TABLE OF ABC_OBJECT;

ABC_OBJECT_LIST Type is used as datatype for an input field in stored procedure. I am calling the stored procedure from my java code. I have written a custom TypeHandler to handle the list type input and pass to stored procedure. Everything is working and i am able to send my values to stored procedure and retrieve them successfully using below code in handler.

    @Override
    public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType arg3) throws SQLException {
      List<Object> object = (List<Object>)parameter;
      structDescriptor = getStructureDescriptor(con, PASSENGER_DETAILS_JDBC_OBJECT);
      arrayDescriptor = getJDBCArrayDescriptor(con, PASSENGER_DETAILS_JDBC_OBJECT_LIST);

      STRUCT[] structs = new STRUCT[objects.size()];
      for (int index = 0; index < objects.size(); index++) {
        structs[index] = getJDBCDataObject(structDescriptor, con, objects.get(index));
      }

      ARRAY oracleArray = getJDBCArrayObject(arrayDescriptor, con, structs);
      ps.setArray(i, oracleArray);
    }

    private STRUCT getJDBCDataObject(final StructDescriptor structDescriptor, final Connection con, final Object object) {
    STRUCT struct = null;
    Object[] params = new Object[1];
    params[0] = "This is test text";


    struct = getJDBCStructureObject(structDescriptor, con, params);
    return struct;
  }


  private STRUCT getJDBCStructureObject(final StructDescriptor structDescriptor, final Connection con,
      final Object[] params) {
    STRUCT struct = null;
    try {
      struct = new STRUCT(structDescriptor, con, params);
    } catch (SQLException e) {
      LOG.error("Error in creating JDBC structure Object.", e);
    }
    return struct;
  }

  private ARRAY getJDBCArrayObject(final ArrayDescriptor arrayDescriptor, final Connection con,
      final STRUCT[] structs) {
    ARRAY oracleArray = null;
    try {
      oracleArray = new ARRAY(arrayDescriptor, con, structs);
    } catch (SQLException e) {
      LOG.error("Error in creating JDBC Array Object.", e);
    }
    return oracleArray;
  }

But this works only if I send atleast one object in the list. If i try to send empty list i get the below error.

Caused by: org.springframework.jdbc.UncategorizedSQLException: Could not set parameters for mapping:

I have googled and found a solution to use the below.

preparedStatement.setNull(i, Types.ARRAY);

But that did not work either. I also tried defining DEFAULT NULL in my stored procedure but that also didn't work. Tried the below as well.

preparedStatement.setArray(i, new ARRAY(arrayDescriptor, con, new STRUCT[]{null}));

This gave Wrong or invalid arguments exception. Also tried the below.

preparedStatement.setNull(i,Types.Array, "ABC_OBJECT_LIST");

And get below error.

Caused by: org.springframework.jdbc.BadSqlGrammarException: 
### Error querying database.  Cause: java.sql.SQLException:ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PROCESSAUTOTICKETING'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I googled but mostly found solutions for primitive types like Integer, String etc but not List and the solution i found(setNull) didn't work.

I am using MyBatis(annotation) to call the stored procedure. Is there any way to handle this to send null as input or may be a blank list to stored procedure.

Answer 1

I have finally resolved this after a long list of hit and trial options. The solution was to define NULL in Oracle Type as below.

create or replace TYPE  "ABC_OBJECT"  AS OBJECT (
   ticket_no  VARCHAR2(50) NULL,
);

create or replace TYPE "ABC_OBJECT_LIST"  IS TABLE OF ABC_OBJECT NULL;

This allows to pass null in array in typehandler as below

preparedStatement.setArray(i, new ARRAY(arrayDescriptor, con, null));

This enables passing null to stored procedure if the list is empty in java code.

Hope this saves someone's time.