CREATE OR REPLACE
PROCEDURE GetApprovers(
INDUSTRY IN VARCHAR2,
APPROVERS OUT VARCHAR2 ) AUTHID CURRENT_USER
AS
BEGIN
IF INDUSTRY LIKE 'SALES' THEN
APPROVERS := 'Salesbusinessanalyst1,Salesbusinessanalyst2';
END IF;
IF INDUSTRY LIKE 'IT' THEN
APPROVERS := 'ITbusinessanalyst1,ITbusinessanalyst2';
ELSE
APPROVERS := 'businessanalyst,businessanalystmanager';
END IF;
END GetApprovers;
COMMIT;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import oracle.bpel.services.workflow.IWorkflowConstants;
import oracle.bpel.services.workflow.runtimeconfig.impl.RuntimeConfigUtil;
import oracle.bpel.services.workflow.runtimeconfig.model.ApprovalGroupMember;
import oracle.bpel.services.workflow.task.IDynamicApprovalGroup;
import oracle.bpel.services.workflow.task.model.Task;
import org.w3c.dom.Element;
import org.w3c.dom.NodeList;
public class XXDynamicAG implements IDynamicApprovalGroup {
public XXDynamicAG() {
super();
}
public List getMembers(Task task) {
Element payloadElem = task.getPayloadAsElement();
String IndustryName = null;
IndustryName = getElementValue(payloadElem, "Industry");
String getStatus = "";
String[] results = { };
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("========== class loaded");
}
catch (ClassNotFoundException ex) {
System.out.println("========== class load error");
ex.printStackTrace();
}
Connection connection = null;
CallableStatement cstmt = null;
try {
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521/mydb", "DEV20_SOAINFRA", "Welcome1");
System.out.println("========== connection=" + connection);
// prepare call
cstmt = connection.prepareCall("{call GetApprovers (?,?)}");
cstmt.setString(1, IndustryName);
//Register Output
cstmt.registerOutParameter(2, java.sql.Types.VARCHAR);
// Call the stored procedure
cstmt.execute();
System.out.println("========== procedure executed");
//Get the output parameter array
String Approverstr = cstmt.getString(2);
results = Approverstr.split(",");
System.out.println("done");
System.out.println(getStatus);
}
catch (SQLException ex) {
ex.printStackTrace();
} finally {
try {
if (cstmt != null)
// close the callable statement
{
cstmt.close();
cstmt = null;
}
System.out.println("========== stmt closed");
} catch (SQLException ex) {
System.out.println("========== stmt close err");
ex.printStackTrace();
}
try {
if (connection != null)
// close the connection
{
connection.close();
connection = null;
}
System.out.println("========== conn closed");
} catch (SQLException ex) {
System.out.println("========== conn close err");
ex.printStackTrace();
}
}
List approversList;
approversList = new ArrayList();
for (int i = 0; i < results.length; i++) {
ApprovalGroupMember taskAssignee =
RuntimeConfigUtil.getFactory(). createApprovalGroupMember();
taskAssignee.setMember(results[i]);
taskAssignee.setType (IWorkflowConstants.IDENTITY_TYPE_USER);
taskAssignee.setSequence(i);
approversList.add(taskAssignee);
}
return approversList;
}
public static String getElementValue(Element payloadElem,
String pElementName) {
String value = null;
NodeList myNodeList = payloadElem.getElementsByTagName (pElementName);
Element myElement = (Element)myNodeList.item(0);
NodeList myChildNodeList = myElement.getChildNodes();
for (int i = 0; i < myChildNodeList.getLength(); i++) {
value = (myChildNodeList.item(i)).getNodeValue(). trim();
}
return value;
}
}