Sometime we need to call Stored Procedure or Stored function in ADF application. Programmetic View objects are used for this. In this blog I will explain how to use Programmetic VO to call the Strored procedure and function.
We need to override following methods of the ViewImpl class
1.executeQueryForCollection()
2.hasNextForCollection()
3.createRowFromResultSet()
4.releaseUserDataForCollection()
Below are the detailed steps
1.create VO as below
2. add the needed atributes
3. Override the above mentioned methods in VOImpl Class
4. Make sure all the attributes in VO are updatable
5. Atleast one attribute should be key attribute
6. I have created Employee VO as below
Define the Stored Function in DB,
CREATE OR REPLACE FUNCTION HR.FUNC_returnEmployee
RETURN SYS_REFCURSOR
AS
REF_TEST SYS_REFCURSOR;
BEGIN
OPEN REF_TEST FOR
SELECT employee_id,first_name,phone_number,salary
FROM employees;
RETURN REF_TEST;
END;
Code of ViewObject Impl class which override above define method.
package model.views;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import oracle.jbo.JboException;
import oracle.jbo.server.ViewObjectImpl;
import oracle.jbo.server.ViewRowImpl;
import oracle.jbo.server.ViewRowSetImpl;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
// ---------------------------------------------------------------------
// --- File generated by Oracle ADF Business Components Design Time.
// --- Mon May 05 14:59:26 IST 2014
// --- Custom code may be added to this class.
// --- Warning: Do not modify method signatures of generated methods.
// ---------------------------------------------------------------------
public class EmployeeProgramaticVoImpl extends ViewObjectImpl {
/**
* This is the default constructor (do not remove).
*/
public EmployeeProgramaticVoImpl() {
}
/**
* executeQueryForCollection - overridden for custom java data source support.
*/
protected void executeQueryForCollection(Object qc, Object[] params,
int noUserParams) {
addEmployeeRecords(qc, callToStroredFunc(qc, params));
super.executeQueryForCollection(qc, params, noUserParams);
}
private ResultSet callToStroredFunc(Object qc, Object[] params) {
ResultSet rs =getStoredProcParams();
return rs;
}
public ResultSet getStoredProcParams() {
ResultSet rs = null;
try {
String sp = "{? = call func_returnEmployee()}";
CallableStatement proc = null;
proc = this.getDBTransaction().createCallableStatement(sp,0);
proc.registerOutParameter(1, OracleTypes.CURSOR);
proc.executeQuery();
rs= ((OracleCallableStatement)proc).getCursor(1);
} catch (SQLException sqlerr) {
throw new JboException(sqlerr);
}
return rs;
}
private void addEmployeeRecords(Object qc, ResultSet rs) {
ResultSet existingRs = (ResultSet)getUserDataForCollection(qc);
// If this query collection is getting reused, close out any previous rowset
if (existingRs != null) {
try {
existingRs.close();
} catch (SQLException e) {
throw new JboException(e);
}
}
setUserDataForCollection(qc, rs);
hasNextForCollection(qc); // Prime the pump with the first row.
}
/**
* hasNextForCollection - overridden for custom java data source support.
*/
protected boolean hasNextForCollection(Object qc) {
ResultSet rs = (ResultSet)getUserDataForCollection(qc);
boolean nextOne = false;
if (rs != null) {
try {
nextOne = rs.next();
/*
* When were at the end of the result set, mark the query collection
* as "FetchComplete".
*/
if (!nextOne) {
setFetchCompleteForCollection(qc, true);
/*
* Close the result set, we're done with it
*/
rs.close();
}
} catch (SQLException s) {
throw new JboException(s);
}
}
return nextOne;
}
/**
* createRowFromResultSet - overridden for custom java data source support.
*/
protected ViewRowImpl createRowFromResultSet(Object qc,
ResultSet resultSet) {
resultSet = (ResultSet)getUserDataForCollection(qc);
/*
* Create a new row to populate
*/
ViewRowImpl r = createNewRowForCollection(qc);
if (resultSet != null) {
try {
/*
* Populate new row by attribute slot number for current row in Result Set
*/
populateAttributeForRow(r, 0,
resultSet.getString(1));
populateAttributeForRow(r, 1,
resultSet.getString(2));
populateAttributeForRow(r, 2,
resultSet.getString(3));
populateAttributeForRow(r, 3,
resultSet.getString(4));
} catch (SQLException s) {
throw new JboException(s);
}
}
return r;
}
protected void releaseUserDataForCollection(Object qc, Object rs) {
ResultSet userDataRS = (ResultSet)getUserDataForCollection(qc);
if (userDataRS != null) {
try {
userDataRS.close();
} catch (SQLException s) {
/* Ignore */
}
}
super.releaseUserDataForCollection(qc, rs);
}
/**
* getQueryHitCount - overridden for custom java data source support.
*/
public long getQueryHitCount(ViewRowSetImpl viewRowSet) {
return 100;
}
}
Run the AM and see the result.
