How do I pass a user-defined type as an input to a stored procedure?

Question

I have two related stored procedures that involve user-defined types. The first accepts an object ID and returns the corresponding instance of a user-defined type. The second accepts an instance of the same user-defined type and does something with it.

I am using Java, JDBC and a little bit of Spring JDBC. I have successfully completed the first stored procedure, ie. I can retrieve instances of my user-defined type from the DB, However, I cannot get the second stored procedure to work.

Here's the basic outline of what I have so far:

Schema (PL/SQL)

create or replace type example_obj as object
  (ID     NUMBER,
   NAME   VARCHAR2(100))

create or replace type example_tab as table of example_obj

create or replace package
example as

procedure getExample
(p_id      in number,
 p_example out example_tab);

procedure useExample
(p_example  in example_tab);

end example;

Entity (Java) - represents the user-defined type in Java

public class Example {
    public BigDecimal ID;
    public String Name;
}

Mapper (Java) - maps from the SQL type to the Java type and back

public class ExampleMapper extends Example implements SQLData {
    public static final String SQL_OBJECT_TYPE_NAME = "example_obj";
    public static final String SQL_TABLE_TYPE_NAME  = "example_tab";    

    @Override
    public String getSQLTypeName() throws SQLException {
        return SQL_TABLE_TYPE_NAME;
    }

    @Override
    public void readSQL(SQLInput stream, String typeName) throws SQLException  {
        ID   = stream.readBigDecimal();
        Name = stream.readString();
    }

    @Override
    public void writeSQL(SQLOutput stream) throws SQLException {
        stream.writeBigDecimal(ID);
        stream.writeString(Name);
    }
}

First Stored Procedure (Java) - retrieves an Example object given its ID

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import org.springframework.jdbc.core.JdbcTemplate;

public Example getExample(BigDecimal ID) throws SQLException {
    String query = "begin example.getExample(?, ?); end;";
    Connection connection = jdbcTemplate.getDataSource().getConnection();
    CallableStatement callableStatement = connection.prepareCall(query);

    callableStatement.setBigDecimal("p_id", ID);

    Map<String, Class<?>> typeMap = connection.getTypeMap();
    typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
    callableStatement.registerOutParameter("p_example", Types.ARRAY, Example.SQL_TABLE_TYPE_NAME);
    connection.setTypeMap(typeMap);

    callableStatement.execute();

    Array array = (Array)callableStatement.getObject("p_example");
    Object[] data = (Object[])array.getArray();
    Example example = (Example)data[0]; // It's an ExampleMapper, but I only want Example
    return example;
}

As I noted earlier, the first stored procedure is working correctly. The object retrieved from the database is automagically mapped into the corresponding Java object. The next step is to be able to call the stored procedure that accepts an instance of this user-defined type.

Second Stored Procedure (Java) - uses an Example object - incomplete

public void useExample(Example example) throws SQLException {
    String query = "begin example.useExample(?); end;";
    Connection connection = jdbcTemplate.getDataSource().getConnection();
    CallableStatement callableStatement = connection.prepareCall(query);

    // Is this required (as per getExample())?
    Map<String, Class<?>> typeMap = connection.getTypeMap();
    typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
    connection.setTypeMap(typeMap);

    /***
     *** What goes here to pass the object in as a parameter?
     ***/
    callableStatement.setObject("p_example", ???);

    callableStatement.execute();
}

Answer 1

After a fair bit of mucking around, I was able to develop a solution. A few observations:

• There is not much documentation about how to do this on the web.
• It seems to me that using user-defined types as inputs is not well supported.
• I found I had to use a Struct which was counter-intuitive (as only arrays were used for outputs).
• The SQLData interface was not used, ie. writeSQL() was never called as I found I had to build the struct manually. readSQL() is called when mapping outputs.
• I had to use DB-specific code for array creation, in my case this meant Oracle classes.

It's possible I may be going about things the wrong way, so I'd welcome comments on my solution.

public void useExample(Example example) throws SQLException {
    String query = "begin example.useExample(?); end;";
    Connection connection = jdbcTemplate.getDataSource().getConnection();
    CallableStatement callableStatement = connection.prepareCall(query);

    Map<String, Class<?>> typeMap = connection.getTypeMap();
    typeMap.put(Example.SQL_OBJECT_TYPE_NAME, ExampleMapper.class);
    connection.setTypeMap(typeMap);

    // Manually convert the example object into an SQL type.
    Object[] exampleAttributes = new Object[]{example.ID, example.Name};
    Struct struct = connection.createStruct(type.getObjectType(), exampleAttributes);

    // Build the array using Oracle specific code.
    DelegatingConnection<OracleConnection> delegatingConnection = (DelegatingConnection<OracleConnection>) new DelegatingConnection(connection);
    OracleConnection oracleConnection = (OracleConnection) delegatingConnection.getInnermostDelegate();
    Object[] data = new Object[]{struct};
    Array array oracleConnection.createOracleArray(Example.SQL_TABLE_TYPE_NAME, data);

    // Set the input value (finally).
    callableStatement.setObject("p_example", array);

    callableStatement.execute();
}