6

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

Beta033
  • 1,975
  • 9
  • 35
  • 48
  • 1
    When you use `TYPE`s in Parameters, do you use [STRUCT](http://docs.oracle.com/cd/B28359_01/java.111/b31224/oraoot.htm#i1039476) in your `JDBC`? – Maheswaran Ravisankar Sep 08 '14 at 16:58
  • You cannot just send null in place of the type, instead pack an empty struct! – Maheswaran Ravisankar Sep 08 '14 at 17:04
  • @Maheswaran Ravisankar So you can pass null as varchar2, int, blob, clob, or any other built in type but not your own types? There has to be some way around this. I don't know why oracle would make this the only option, further i can call the proc just fine with null from another procedure like `proc_test(null);` – Beta033 Sep 08 '14 at 17:37

1 Answers1

12

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • 1
    This works! I played with SetNull a bit, but i used the wrong overloaded method, setNull(int parameterIndex, int sqlType). Which for me caused just a different set of errors. Thanks for the guidance. – Beta033 Sep 09 '14 at 21:42