Re: null oracle.sql.ARRAY parameters in CallableStatements?

I got it:

void setNull(int parameterIndex, int sqlType, String sql_type_name) takes a SQL type name in addition to a parameter index and a SQL type code. You use this method only when the SQL type code is REF, ARRAY, or STRUCT.

So setNull(3, ARRAY, "NUMBER_TT") will work (and it works).

Anyway the second part of my question is still unanswered: can I force in the call to use the default values for a specific parameter?

Thx,

Kurta

Kurta wrote in message news:<efcb1994.0402110853.26de77c6_at_posting.google.com>...
> 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 Thu Feb 12 2004 - 04:02:40 CST