Announcement Announcement Module
Collapse
No announcement yet.
OracleTypes.STRUCT Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • OracleTypes.STRUCT

    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:

    Code:
    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:

    Code:
     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.

  • #2
    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:
    Code:
    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:
    Code:
    CREATE OR REPLACE TYPE item_type AS OBJECT(id INTEGER, descr VARCHAR(50), exp_date DATE);

    Comment

    Working...
    X