Oracle from Java. CallableStatement and Null for object parameter?

Question

I've got, what started as a fairly complex stored procedure boils down to a very simple use case that doesn't seem to be working as i'm expecting.

The stored procedure now looks like this for my test:

CREATE OR REPLACE PROCEDURE proc_test(
v_someString varchar2
                      ) as
BEGIN
    dbms_output.put_line('test');
END;

I can call this stored procedure from java using the callable statement API

java.sql.CallableStatement stmt = conn.prepareCall("call proc_test(?)");
stmt.setObject(1,null);
stmt.execute();

The above works as expeted. The method executes and completes successfully. Nows where it gets tricky. We've created a set of oracle object types to allow us to pass more complicated structures around similar to the following

CREATE OR REPLACE
type SOMETYPE_TYPE force  UNDER BASE_TYPE (value varchar2(255),
CONSTRUCTOR FUNCTION SOMETYPE_TYPE RETURN SELF AS RESULT) NOT FINAL;

if i simply change the procedure to accept these types as parameters, like this:

CREATE OR REPLACE PROCEDURE proc_test(
v_someString SOMETYPE_TYPE 
                      ) as
BEGIN
    dbms_output.put_line('test');
END;

Oracle will break with PLS-00306: wrong number or types of arguments in call to 'CNV_CREATE_PERSON'

What gives? If i call this procedure from another procedure with null as the value, it works fine. Only calling it from the java API does it break.

I've been scouring the documentation for any solution and can't find one. Perhaps one of you wise and learned fellows could point me in the right direction.

Thanks

Answer 1

You need to use the setNull() method to specify your user-defined type:

void setNull(String parameterName,
           int sqlType,
           String typeName)
             throws SQLException

Sets the designated parameter to SQL NULL. This version of the method setNull should be used for user-defined types and REF type parameters. Examples of user-defined types include: STRUCT, DISTINCT, JAVA_OBJECT, and named array types.

Note: To be portable, applications must give the SQL type code and the fully-qualified SQL type name when specifying a NULL user-defined or REF parameter. In the case of a user-defined type the name is the type name of the parameter itself. For a REF parameter, the name is the type name of the referenced type.

So in your case:

stmt.setNull(1, java.sql.Types.STRUCT, "SOMETYPE_TYPE");

If you just pass null then it doesn't know what type it represents and can't do any implicit conversion. That does mean that your code needs to branch depending on whether your Java object is null or not, to either call setNull() or setObject() as appropriate.

You should really use setNull() for your varchar2 example as well, but can use the simpler version, as:

stmt.setNull(1, java.sql.Types.VARCHAR);

... but you get away with just passing null.