Calling stored procedure with a table as argument

[mafi]

Hi,

Working with Oracle we have the following SQL:

TYPE person AS OBJECT (
id NUMBER,
name VARCHAR(240)
);

TYPE person_tab AS TABLE OF person;

PROCEDURE save_persons(persons IN person_tab);


It is possible to access such a declaration through Spring?

Reading the manuals and searching the web we found only one relevant post. This dealt with accessing a stored procedure that returned an Oracle object.

We are pretty sure that a similar approach is possible for an in parameter,
but exacly how should it be done? It is not just the opposite direction (in instead of out), but also a table of such objects.


Regards,

Ricardo M.

[trisberg]

Look at the org.springframework.jdbc.core.SqlTypeValue class. It has a callback method

setTypeValue(PreparedStatement ps, int paramIndex, int sqlType, String typeName) throws SQLException;

that should let you create the table and set it using regular Oracle JDBC constructs.