People tend to write lot of business logic in PL/SQL when there is lot of data to manipulate with or to improve the performance. When PL/SQL collections are the best way to accept multiple records as input and return the response.
This post shows the sample code to call a PL/SQL API accepting PL/SQL table from an ADF application module.
PL/SQL Objects:
CREATE OR REPLACE TYPE emp_rec_type AS OBJECT (
employee_id NUMBER(18),
first_name VARCHAR2(30),
last_name VARCHAR2(30),
middle_name VARCHAR2(30),
employee_type VARCHAR2(30),
location VARCHAR2(30),
addr_line1 VARCHAR2(30),
addr_line2 VARCHAR2(30),
addr_line3 VARCHAR2(30)
city VARCHAR2(30)
);
CREATE OR REPLACE TYPE emp_tbl_type AS TABLE OF emp_record;
CREATE OR REPLACE PACKAGE emp_ddl_package AS
PROCEDURE validate_emp_records (
emp_rec_list IN OUT NOCOPY emp_tbl_type,
validation_status OUT NOCOPY VARCHAR2
);
END emp_ddl_package;
CREATE OR REPLACE PACKAGE BODY emp_ddl_package AS
PROCEDURE validate_emp_records(
emp_rec_list IN OUT NOCOPY emp_tbl_type,
validation_status OUT NOCOPY VARCHAR2
)IS
BEGIN
— do the coding here
END validate_emp_records;
END emp_ddl_package;
Sample Code of Java Method in Application Module:
Following is the typical piece of code that can be used in ADF application module to call the PL/SQL API:
public List<EmpVORowImpl> validateEmployees(List<EmpVORowImpl> empList) throws Exception {
OracleCallableStatement stmt = null;
ArrayList<EmpVORowImpl> empRespList = null;
if (empList != null && empList.size() > 0) {
empRespList = new ArrayList<EmpVORowImpl>();
try {
// Call the pl/sql API to determine invoice status
String sql =
"begin emp_ddl_package.validate_emp_records(emp_rec_list=>:1,validation_status=>:2); end;";
stmt =
(OracleCallableStatement)getDBTransaction().createCallableStatement(sql,0);
ArrayList<STRUCT> inputEmpList = new ArrayList<STRUCT>();
StructDescriptor structDescriptor = StructDescriptor.createDescriptor(“EMP_REC_TYPE”,stmt.getConnection());
for (int i = 0; i < empList.size(); i++) {
EmpVORowImpl empRow = empList.get(i);
Object[] o =
new Object[] { empRow.getEmployeeId(), empRow.getFirstName(),
empRow.getLastName(), empRow.getMiddleName(),
empRow.getEmployeeType(), empRow.getLocation(),
empRow.getAddrLine1(), empRow.getAddrLine2(),
empRow.getAddrLine3(), empRow.getCity() };
STRUCT empRowStruct = new STRUCT(structDescriptor,stmt.getConnection(), o);
inputEmpList.add(empRowStruct);
}
ArrayDescriptor empArray =
ArrayDescriptor.createDescriptor("EMP_TBL_TYPE",
stmt.getConnection());
ARRAY empArrayTbl =
new ARRAY(empArray, stmt.getConnection(), inputEmpList.toArray());
stmt.registerOutParameter(1, OracleTypes.ARRAY, "EMP_TBL_TYPE");
stmt.setARRAY(1, empArrayTbl);
stmt.registerOutParameter(2, OracleTypes.VARCHAR, 0, 1);
stmt.execute();
//get the output table from pl/sql API and return
Array outputArray = stmt.getArray(1);
String returnStatus = stmt.getString(2);
if (returnStatus != null && "Success".equals(returnStatus) &&
outputArray != null) {
ResultSet rs = outputArray.getResultSet();
EmpVORowImpl tempRow;
while (rs != null && rs.next()) {
Struct empRespStruct = (Struct)rs.getObject(2);
Object[] empAttributes = empRespStruct.getAttributes();
if (empAttributes != null) {
tempRow = (EmpVORowImpl)getEmpVO().createRow();
tempRow.setAttributeValues(Arrays.asList(tempRow.getAttributeNames()), Arrays.asList(empAttributes));
empRespList.add(tempRow);
}
}
}
} catch (SQLException sqlex) {
throw new Exception("sql exception");
} catch (Exception ex) {
throw new Exception("exception");
} finally {
try {
stmt.close();
} catch (SQLException sqlex) {
throw new Exception("sql exception");
}
}
}
return empRespList;
}
Siva's Blog 