null oracle.sql.ARRAY parameters in CallableStatements?

Is there a way to pass null to a PL/SQL stored procedure for an array (indexed table) paramerer?

I have a PL/SQL stored procedure in a package with the following signature:

  CREATE OR REPLACE TYPE number_tt AS TABLE OF NUMBER(19);

  FUNCTION open_um_event_ids(
    member IN NUMBER,
    status_tt IN number_tt := NULL, -- Defaults to NULL     owner IN NUMBER := NULL
  )
  RETURN pkg_query_helpers.cursor_t;

The Java code for calling this function is:

  long memberPrimaryKey = 66366;
  long[] states = {1902};
  String preparedStatementText1 = "{ ? = call pkg_mdv_searches.open_um_event_ids(?, ?, ?) }";

  CallableStatement call1 = con.prepareCall(preparedStatementText1);

  call1.registerOutParameter(1, OracleTypes.CURSOR);

  call1.setLong(2, memberPrimaryKey);
  ArrayDescriptor anArrayDescriptor1 = new ArrayDescriptor("NUMBER_TT", con);
  ARRAY idARRAY1 = new ARRAY(anArrayDescriptor1, con, ids);   // ((OracleCallableStatement) call1).setARRAY(3, states); // This works fine
  ((OracleCallableStatement) call1).setARRAY(3, null);

  call1.setNull(3, Types.INTEGER);

  call1.execute();

However this call results in a java.sql.SQLException: Invalid argument(s) in call exception for the ((OracleCallableStatement) call1).setARRAY(3, null); statement. call1.setNull(2, Types.ARRAY) doesn't execute either.

Can I pass null somehow or can I force in the call to use the default values for a specific parameter? I imagine something like:

  String preparedStatementText1 = "{ ? = call pkg_mdv_searches.open_um_event_ids(?, , ?) }"; // There's no ? for the second param
  String preparedStatementText1 = "{ ? = call pkg_mdv_searches.open_um_event_ids(?, default, ?) }"; // Default keyword replaces the second param

Oracle refuses these types of calls. However the next call works fine.

  String preparedStatementText1 = "{ ? = call pkg_mdv_searches.open_um_event_ids(?) }"; // The last two parameters are ignored

But I might have to handle situations when there're two array parameters and any one of them might be null, so this isn't a really good solution for me.

Hope this is clear enough to get some helpful answers.

Take Care,

Kurta Received on Wed Feb 11 2004 - 10:53:20 CST