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.

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

DAO:
jdbcCall=new SimpleJdbcCall(jdbcTemplate)
.withSchemaName(“WORKFLOW”)
.withCatalogName(“PERSIST_SUBMISSION”)
.withProcedureName(“persistSubmissionData”)
.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.

Choosing a Global Software Development Partner to Accelerate Your Digital Strategy

To be successful and outpace the competition, you need a software development partner that excels in exactly the type of digital projects you are now faced with accelerating, and in the most cost effective and optimized way possible.

Get the Guide

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.

SubmissionDataType.Java
public class SubmissionDataType extends SubmissionData implements SQLData {

@Override
public String getSQLTypeName() throws SQLException {
return “WORKFLOW.SUBMISSION_DATA”;
}

@Override
public void readSQL(SQLInput sqlInput, String string) throws SQLException {
setFileID(sqlInput.readString());
}

@Override
public void writeSQL(SQLOutput sqlOutput) throws SQLException {
sqlOutput.writeString(getFileID());
}

In the above example :
SubmissionData is the entity that corresponds to SUBMISSION_DATA Oracle object.
getSQLTypeName
Here we specify the SUBMISSION_DATA advanced data type as the type supported by this implementation.
readSQL
Here we specify the implementation used to map data between the SUBMISSION_DATA advanced data type and the SubmissionData class during a read operation.
writeSQL
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.