Here I would like to show a simple example of using Spring SimpleJdbcCall to invoke one Oracle stored procedure with out parameters as the table of the PL/SQL object.
Maven
The Oracle database is Oracle 11g and JDBC driver class used ojdbc6-11.2.0.4.jar. The below is Maven dependency in the project file.
- <dependency>
- <groupId>org.springframework</groupId>
- <artifactId>spring-jdbc</artifactId>
- <version>${spring.version}</version>
- <dependency>
- <groupId>com.oracle</groupId>
- <artifactId>ojdbc6</artifactId>
- <version>11.2.0.4</version>
- </dependency>
Table Schema
The below is DDL for the table BATCH.
- CREATE TABLE "BATCH"
- (
- "ID" NUMBER NOT NULL,
- "BATCH_NAME" VARCHAR2(256),
- "BATCH_STATUS" VARCHAR2(20),
- "LAST_UPDATE_DATE" DATE,
- "CREATION_DATE" DATE,
- "LAST_UPDATED_BY" VARCHAR2(100),
- "CREATED_BY" VARCHAR2(100),
- CONSTRAINT BATCH_PK PRIMARY KEY (ID)
- ) ;
Object Type
In the database one object type: BATCH_REC is defined.
- CREATE or REPLACE
- TYPE BATCH_REC as OBJECT (
- BATCH_ID NUMBER(10,0),
- BATCH_NAME VARCHAR2(256),
- BATCH_STATUS VARCHAR2(20),
- CREATION_DATE DATE,
-
- INVOICES INVOICE_TAB
- );
-
- CREATE OR REPLACE
- TYPE BATCH_TAB IS TABLE OF BATCH_REC;
- /
Stored Procedure
The stored procedure RETRIEVE_BATCH_BY_BATCHSTATUS is shown as below. This is used to retrieve a list of batches by using batchStatus. batchStatus is defined as in parameter and batches of type BATCH_TAB as out parameter.
- CREATE OR REPLACE PROCEDURE "RETRIEVE_BATCH_BY_BATCHSTATUS"
- (
- batchStatus in BATCH.BATCH_STATUS%TYPE,
- batches out BATCH_TAB,
- p_error_flag out varchar2,
- p_error_code out varchar2,
- p_error_message out varchar2
- )
- IS
-
- BEGIN
-
- batches := BATCH_TAB();
-
- select BATCH_REC(bat.ID,
- bat.BATCH_NAME,
- bat.BATCH_STATUS,
- bat.VENDOR_SITE_ID,
- bat.CREATION_DATE,
- bat.LAST_UPDATE_DATE,
- bat.LAST_UPDATED_BY,
- bat.CREATED_BY)
- bulk collect into batches
- from BATCH bat where bat.BATCH_STATUS=batchStatus;
-
- p_error_flag := 'N';
-
- EXCEPTION
- WHEN OTHERS THEN
- p_error_code := SQLCODE;
- p_error_message := concat( concat( SQLERRM, ' '), dbms_utility.format_error_backtrace() );
- p_error_flag := 'Y';
-
- END;
Spring Class
Here is the DAO class which will use Spring SimpleJdbcCall to invoke the stored procedure: RETRIEVE_BATCH_BY_BATCHSTATUS. In order to return a list of batches from SimpleJdbcCall there are some points worth to be noticed:
- When declaring out parameter batches, specify its type as Types.ARRAY and also specify its type name as "BATCH_TAB" which is collection type defined in the database.
- After the invocation the return value of batches will be casted into the object of oracle.sql.ARRAY, from which the array will be casted into array of Object.
- When looping each object in the arry cast each object into oracle.sql.STRUCT. From the object of STRUCT a list of attributes are obtained. The values of the attribute match the values of BATCH_REC.
-
- public class BatchDAO {
- private static String PROC_NAME="RETRIEVE_BATCH_BY_BATCHSTATUS";
-
- private static String PARA_BATCHSTUS="batchStatus";
-
- private static String PARA_BATCHES="batches";
-
- private static String PARA_ERROR_FLAG="p_error_flag";
-
- private static String PARA_ERROR_CODE="p_error_code";
-
- private static String PARA_ERROR_MESSAGE="p_error_message";
-
- private SimpleJdbcCall simpleJdbcCall;
-
- @Autowired
- public void setDataSource(DataSource dataSource) {
- // Set up datasource
- this.simpleJdbcCall = new SimpleJdbcCall(dataSource)
- .withProcedureName(PROC_NAME)
- .declareParameters(
- new SqlParameter(PARA_BATCHSTUS, Types.VARCHAR),
- new SqlOutParameter(PARA_BATCHES, Types.ARRAY, "BATCH_TAB"),
- new SqlOutParameter(PARA_ERROR_FLAG, Types.VARCHAR),
- new SqlOutParameter(PARA_ERROR_CODE, Types.VARCHAR),
- new SqlOutParameter(PARA_ERROR_MESSAGE, Types.VARCHAR));
-
- }
-
-
- public List<Batch> getBatch(String status) throws ScanningException {
-
-
- SqlParameterSource in = new MapSqlParameterSource().addValue(PARA_BATCHSTUS, status);
-
- Map<String, Object> out = simpleJdbcCall.execute(status);
-
- List<Batch> batches = new ArrayList<Batch>();
- String errorFlag = (String)out.get(PARA_ERROR_FLAG);
-
- if ( errorFlag.equals("N")) {
- try {
- ARRAY oracleObjectArray = (ARRAY)out.get(PARA_BATCHES);
- Object[] objArr = (Object[])oracleObjectArray.getArray();
- logger.info("Length of retrieved batches from database = "+objArr.length);
- for (int i=0; i<objArr.length; i++) {
- STRUCT st = (STRUCT)objArr[i];
- Object[] obj = st.getAttributes();
-
- Batch batch = new Batch();
- batch.setBatchName((String)obj[1]);
- batch.setStatus((String)obj[2]);
- batch.setCreationDate((Date)obj[3]);
-
- batches.add(batch);
- }
- } catch (SQLException ex) {
- throw new ScanningException("SQLException occurred.", ex);
- }
- } else {
- String errorCode = (String)out.get(PARA_ERROR_CODE);
- String errorMessage = (String)out.get(PARA_ERROR_MESSAGE);
-
- throw new ScanningException("Exception occurred in "+PROC_NAME);
- }
-
- return batches;
- }
- }
-