OracleTypes.STRUCT

[cornflakeGirl]

Hey Guys,

I apologize if this question has been answered already -- I have tried to search the forums, but was not sure if I was looking at dated information.

So previously without using Spring, if in a stored procedure I wanted to pass in an object, I could do something similar to this:

Map typeMap = (Map) conn.getTypeMap();
typeMap.put(MyObject.ORACLE_OBJECT_NAME, MyObject.class);
conn.setTypeMap(typeMap);

OracleCallableStatement stmt = ...
stmt.setObject(2, myObject);

How do I do this using Spring's JDBC support? I have figured out how to get out an object, but for the life of me, I cannot understand how to pass one in.

I have tried to do something like this, but it does not work:

 private class TestProcedure extends StoredProcedure {
        public static final String FUNC_NAME = "blahblah";

        public SetDriverInfo(DataSource ds) {
            super(ds, FUNC_NAME);
            setFunction(true);
            declareParameter(new SqlOutParameter("returnVal", OracleTypes.NUMBER));
            declareParameter(new SqlParameter("test", OracleTypes.STRUCT,
                    MyObject.ORACLE_OBJECT_NAME));

            compile();
        }

        public Map execute(final MyObject myObject) {
            Map inParameters = new HashMap();
            inParameters.put("test",
                new PreparedStatementSetter() {
                    public void setValues(PreparedStatement ps)
                        throws SQLException {
                        ps.setObject(2, myObject
                    }
                });

            return super.execute(inParameters);
        }
    }

Can anyone please point me in the right direction? As I mentioned before, I am successful in getting out an object, but I am having trouble passing one in.

[trisberg]

You need to create a StructDescriptor and then a STRUCT object that you can then set.

An alternative to the PreparedStatementSetter is to use an AbstractSqlTypeValue for this feature, and put that in the Map of in-parameters.

Here is an example of a StoredProcedure implementation passing in a STRUCT:

private class AddItem extends StoredProcedure {

    public AddItem(JdbcTemplate jdbcTemplate) {
        super(jdbcTemplate, "item_package.add_item");
        declareParameter(new SqlParameter("item", OracleTypes.STRUCT, "ITEM_TYPE"));
        declareParameter(new SqlOutParameter("return", Types.NUMERIC));
        compile();
    }

    public Number execute(final TestItem testItem) {
        SqlTypeValue value = new AbstractSqlTypeValue() {
            protected Object createTypeValue(Connection conn, int sqlType, String typeName) throws SQLException {
                StructDescriptor itemDescriptor = new StructDescriptor(typeName, conn);
                Struct item = new STRUCT(itemDescriptor, conn,
                        new Object[] {
                                testItem.getId(),
                                testItem.getDescription(),
                                new java.sql.Date(testItem.getExpirationDate().getTime())
                        });
                return item;
            }
        };
        Map inParam = new HashMap(1);
        inParam.put("item", value);
        Map outValues = execute(inParam);
        return (Number)outValues.get("return");
    }
}

The type declaration used for this example is:

CREATE OR REPLACE TYPE item_type AS OBJECT(id INTEGER, descr VARCHAR(50), exp_date DATE);