Use Spring SimpleJdbcCall to invoke Oracle stored procedure with

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:  

1. 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.
2. 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.
3. 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;
    }
}