Skip to main content


Insert data into Stored Proc with Oracle object as its input type

I had come across this requirement recently to insert data into a Stored Procedure which has Oracle object as input type. It is not as straight forward as a simple call to Stored Procedure with primitive data types as input. Since the input parameter is a Oracle object we will have to do additional steps to make it work.

1. Create a SimpleJdbcCall object in your DAO. It is a multi-threaded, reusable object representing a call to a stored procedure or a stored function.

jdbcCall=new SimpleJdbcCall(jdbcTemplate)
.declareParameters(new SqlParameter(“subData”, OracleTypes.STRUCT, “WORKFLOW.SUBMISSION_DATA”)
, new SqlOutParameter(“sbmsn_id”, OracleTypes.INTEGER))

2. Procedure Name and parameter declarations are case sensitive. It should be the same as declared in the Stored Procedure.

3. If you take a close look at declareParameters, the following statement maps as below:
.declareParameters(new SqlParameter(“subData”, OracleTypes.STRUCT, “WORKFLOW.SUBMISSION_DATA”)
subData – Name of the input parameter declared in the stored procedure
OracleTypes.STRUCT – This denotes the type is Oracle object.
WORKFLOW.SUBMISSION_DATA – Name of the Oracle object as defined in the DB [SchemaName.ObjectName]

4. Next step is to create a Java type that maps to the Oracle object type. All you need to do to convert a java entity to an Oracle object type is to have it implement SQLData interface.

public class SubmissionDataType extends SubmissionData implements SQLData {

public String getSQLTypeName() throws SQLException {

public void readSQL(SQLInput sqlInput, String string) throws SQLException {

public void writeSQL(SQLOutput sqlOutput) throws SQLException {

In the above example :
SubmissionData is the entity that corresponds to SUBMISSION_DATA Oracle object.
Here we specify the SUBMISSION_DATA advanced data type as the type supported by this implementation.
Here we specify the implementation used to map data between the SUBMISSION_DATA advanced data type and the SubmissionData class during a read operation.
Here we specify the implementation used to map data between the SubmissionData class and the SUBMISSION_DATA advanced data type during a write operation.

With the above steps we can invoke a stored procedure which takes oracle object type as input and get the results back.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Bagieswari Umapathy

More from this Author

Follow Us