Oracle stored procedure with complex parameters - Array of structs and blobs

The following sniplet of code really helped me in implementing a procedure call containing an array of structs and array of blobs as part of its parameter signature: (I hope it will help you too)

import java.sql.Array;
import java.sql.Blob;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import oracle.jdbc.OracleConnection;
import org.springframework.dao.InvalidDataAccessApiUsageException;
import org.springframework.jdbc.core.support.AbstractSqlTypeValue;

public class SqlBlobArrayValue extends AbstractSqlTypeValue {

    private List<byte[]> values;

    private String defaultTypeName;

    public SqlBlobArrayValue(List<byte[]> values) {
        this.values = values;
    }

    public SqlBlobArrayValue(List<byte[]> values, String defaultTypeName) {
        this.values = values;
        this.defaultTypeName = defaultTypeName;
    }

    protected Object createTypeValue(Connection conn, int sqlType, String typeName)
            throws SQLException {
        if (typeName == null && defaultTypeName == null) {
            throw new InvalidDataAccessApiUsageException(
                    "The typeName is null in this context. Consider setting the defaultTypeName.");
        }

        Blob[] blobs = new Blob[values.size()];
        for (int i = 0; i < blobs.length; ++i) {
            Blob blob = conn.createBlob();
            blob.setBytes(1, values.get(i));
            blobs[i] = blob;
        }

        Array array = conn.unwrap(OracleConnection.class).createOracleArray(typeName != null ? typeName : defaultTypeName, blobs);
        return array;
    }
}

With recognition to Luke Woodward on Stackoverflow. Thank you Luke!!

ADF Stored Proc Output Cursor View Object Implementation

I had to write an ADF implementation of an Oracle Form which relies a lot on Stored Procedures for the read and display of data from Output Cursors. Hence, I've eventually implemented what I call, a generic stored procedure output cursor view object implementation in order to support the create of a simple VO in ADF for use in the View Layer. Please find the implementation below:

public class StoredProcOutputCursorVOImpl extends ViewObjectImpl {
 
  private String cursorParameterName = null; 
  private String storedProcFullyQualifiedSql = null; 
  // stored proc parameters : the ProcParam class is simply an encapsulation of a stored proc parameter
  private ProcParam[] procParameters; 
  // cursor cached data : a Map like object representing the output from the cursor (also used for caching)
  private RefCursorData refCursorData = null;
 
    /**
   * This is the default constructor (do not remove).
   */
  public StoredProcOutputCursorVOImpl() {   
  }

  public StoredProcOutputCursorVOImpl(String storedProcFullyQualifiedSql, String cursorParameterName) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;
    this.cursorParameterName = cursorParameterName;
  }
 
  private Collection<Map<String, Object>> executeStoredProcWithRefCursorResult(String storedProcFullyQualifiedSql,
    String refCursorName, ProcParam... parameters) {   
    return DbUtils.callProcReturningRefCursorResult(this.getDBTransaction(), storedProcFullyQualifiedSql,
        refCursorName, parameters);
  } 

  /**
   * executeQueryForCollection - overridden for custom java data source support.
   */
  protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams) {   
    RefCursorData refCursorData = new RefCursorData(this.getOrderByClause());   
    if (this.getProcParameters() == null) {
      throw new JboException("Stored Procedure Parameters need to be set. At least initialize and empty ProcParam Array!");
    }
    Collection<Map<String, Object>> refCursorList =
      this.executeStoredProcWithRefCursorResult(this.getStoredProcFullyQualifiedSql(), this.getCursorParameterName(),
        this.getProcParameters());
    refCursorData.setResultCollection(refCursorList);
    this.setRefCursorDataForCollection(qc, refCursorData);   
    // Prime the pump with the first row.
    this.hasNextForCollection(qc);

    super.executeQueryForCollection(qc, params, noUserParams);
  }

  /**
   * hasNextForCollection - overridden for custom java data source support.
   */
  protected boolean hasNextForCollection(Object qc) {
    boolean hasNext = getRefCursorDataForCollection(qc).getNextRow();
    if(!hasNext) {
      setFetchCompleteForCollection(qc, true);
      return false;
    }
    return true;
  }

  /**
   * createRowFromResultSet - overridden for custom java data source support.
   */
  protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
    ViewRowImpl row = createNewRowForCollection(qc);   
    // populate row from Map entry in RefCursorDataCollection
    Map<String, Object> cursorRow = ((RefCursorData) getRefCursorDataForCollection(qc)).getNextRow();
    int rowIndex = 0;
    for(Object obj : cursorRow.values()) {
      this.populateAttributeForRow(row, rowIndex, obj);
      rowIndex++;
    }
    return row;
  }

  /**
   * getQueryHitCount - overridden for custom java data source support.
   */
  public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    QueryCollection qColl = viewRowSet.getQueryCollection();
    RefCursorData refCurrData = (RefCursorData) getRefCursorDataForCollection(qColl);
    if (refCurrData != null) {
      return refCurrData.size(); 
    } else {
      return 0;
    }   
  }

  protected void create() {   
    getViewDef().setQuery(null);
    getViewDef().setSelectClause(null);
    setQuery(null);
  }

  public void setRefCursorDataForCollection(Object queryCollection, RefCursorData refCursorData) {
    this.refCursorData = refCursorData;
  }

  public RefCursorData getRefCursorDataForCollection(Object queryCollection) {
    return refCursorData;
  }

  public void setCursorParameterName(String cursorParameterName) {
    this.cursorParameterName = cursorParameterName;
  }

  public String getCursorParameterName() {
    return cursorParameterName;
  }

  public void setStoredProcFullyQualifiedSql(String storedProcFullyQualifiedSql) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;
  }

  public String getStoredProcFullyQualifiedSql() {
    return storedProcFullyQualifiedSql;
  }

  public void setProcParameters(ProcParam[] procParameters) {
    this.procParameters = procParameters;
  }

  public ProcParam[] getProcParameters() {
    return procParameters;
  }
}

With the above code in place, one simply creates a programmatic View Object with its Java implementation extending the above class...providing the SQL to call the appropriate stored procedure as well as defining the output cursor parameter name.

And there you have a usable View Object calling a stored procedure and populating the attributes dynamically from the stored proc output cursor!

I had to update the code listing above (fixed a bug in the RefCursorData class) and hence I realised I also need to provide the code for the RefCursorData class as part of this post:

public class RefCursorData {
 
  private String orderByClause;
  private Collection<Map<String,Object>> resultCollection;
  private Iterator<Map<String,Object>> statefulPointer = null;
 
  public RefCursorData() {
  }
 
  public RefCursorData(String orderByClause) {
    this.orderByClause = orderByClause;
  } 
 
  public Map<String,Object> getNextRow() {   
    if (this.statefulPointer != null && this.statefulPointer.hasNext()) {
      return this.statefulPointer.next();
    }
    return null;
  }
 
  public boolean hasNextRow() {
    if (this.statefulPointer != null) {
      return this.statefulPointer.hasNext();
    }
    return false;
  }
 
  public int size() {
    if (this.resultCollection != null) {
      return this.resultCollection.size();    
    }
    return 0; 
  }
 
  public String getOrderByClause() {
    return this.orderByClause;
  }
 
  public void setOrderByClause(String orderByClause) {
    this.orderByClause = orderByClause;
  }
 
  public void setResultCollection(Collection<Map<String,Object>> resultList) {
    this.resultCollection = resultList;
    this.statefulPointer = this.resultCollection.iterator();
  }
}

Enjoy!

ADF Stored Proc Output Parameters View Object Implementation

I had to write an ADF implementation of an Oracle Form which relies a lot on Stored Procedures for the read and display of data. Hence, I've eventually implemented what I call, a generic stored procedure output parameter view object implementation in order to support the create of a simple VO in ADF for use in the View Layer. Please find the implementation below:

public class StoredProcOutputParametersVOImpl extends ViewObjectImpl {
 
  private String storedProcFullyQualifiedSql = null; 
  // stored proc parameters : the ProcParam class is simply an encapsulation of a stored proc parameter
  private ProcParam[] procParameters;
 
  // view attribute mapping to stored proc parameters
  // Key   : view attribute name
  // Value : stored procedure parameter name
  private Map<String,String> viewAttributeMapping;
 
  // Collection Map containing output parameter name value pairs as result collection for caching purposes
  private Collection<Map<String,Object>> resultCollection;
 
  public StoredProcOutputParametersVOImpl() {    
  }
 
  public StoredProcOutputParametersVOImpl(String storedProcFullyQualifiedSql) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;   
  }
 
  private Collection<Map<String, Object>> executeStoredProcWithOutputParametersResult(String storedProcFullyQualifiedSql,
    ProcParam... parameters) {    
    return DbUtils.callProcReturningParametersResult(this.getDBTransaction(), storedProcFullyQualifiedSql,
             parameters);
  }
 
  /**
   * executeQueryForCollection - overridden for custom java data source support.
   */
  protected void executeQueryForCollection(Object qc, Object[] params, int noUserParams) {
    
    if (this.getProcParameters() == null) {
      throw new JboException("Stored Procedure Parameters need to be set. At least initialize and empty ProcParam Array!");
    }
    
    Collection<Map<String, Object>> outputParamResult =
      executeStoredProcWithOutputParametersResult(this.getStoredProcFullyQualifiedSql(),
        this.getProcParameters());   
    
    // cache result collection
    this.setResultCollection(outputParamResult);
    
    // Prime the pump with the first row.
    this.hasNextForCollection(qc);
    
    super.executeQueryForCollection(qc, params, noUserParams);
  }
 
  /**
   * hasNextForCollection - overridden for custom java data source support.
   */
  protected boolean hasNextForCollection(Object qc) {
    boolean hasNext = getResultCollection().iterator().hasNext();
    if(!hasNext) {
      setFetchCompleteForCollection(qc, true);
      return false;
    }
    return true;
  }
 
  /**
   * createRowFromResultSet - overridden for custom java data source support.
   */
  protected ViewRowImpl createRowFromResultSet(Object qc, ResultSet resultSet) {
    if (this.viewAttributeMapping == null || this.viewAttributeMapping.size() <= 0) {
      throw new JboException("All Output Parameters have to be mapped to corresponding view attributes on this View Object!");
    }    
    ViewRowImpl row = createNewRowForCollection(qc);
    
    // get output parameters data from result collection to populate row from
    Map<String, Object> outputParametersRow = getResultCollection().iterator().next();   
    
    // get reference to view attribute mapping (maps view attributes to proc params)    
    Set<String> viewAttrNames = this.viewAttributeMapping.keySet();
    int rowIndex = 0;
    for (String viewAttrName: viewAttrNames) {
      // set row/view attributes according to view attribute mapping.
      String procParamName = this.viewAttributeMapping.get(viewAttrName);     
      Object viewAttrValue = outputParametersRow.get(procParamName);
      this.populateAttributeForRow(row, rowIndex, viewAttrValue);
      rowIndex++;
    }
    
    // return populated row
    return row;
  }
 
  /**
   * getQueryHitCount - overridden for custom java data source support.
   */
  public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
    return getResultCollection().size();
  }
 
  protected void create() {    
    getViewDef().setQuery(null);
    getViewDef().setSelectClause(null);
    setQuery(null);
  }
 
  public void setStoredProcFullyQualifiedSql(String storedProcFullyQualifiedSql) {
    this.storedProcFullyQualifiedSql = storedProcFullyQualifiedSql;
  }
 
  public String getStoredProcFullyQualifiedSql() {
    return storedProcFullyQualifiedSql;
  }
 
  public void setProcParameters(ProcParam[] procParameters) {
    this.procParameters = procParameters;
  }
 
  public ProcParam[] getProcParameters() {
    return procParameters;
  }
 
  public void setViewAttributeMapping(Map<String, String> viewAttributeMapping) {
    this.viewAttributeMapping = viewAttributeMapping;
  }
 
  public Map<String, String> getViewAttributeMapping() {
    return viewAttributeMapping;
  }
 
  public void setResultCollection(Collection<Map<String, Object>> resultCollection) {
    this.resultCollection = resultCollection;
  }
 
  public Collection<Map<String, Object>> getResultCollection() {
    return resultCollection;
  }
}

With the above code in place, one simply creates a programmatic View Object with its Java implementation extending the above class...providing the SQL to call the appropriate stored procedure as well as defining the view attribute - stored procedure parameter mapping.

And there you have a usable View Object calling a stored procedure and populating the attributes dynamically from the stored proc output parameters!