Hi All, Could you please help me on this sceanrio. I have array list i need to pass the list as input parameter
Step 1: create or replace TYPE list_tbl_type_test AS TABLE OF list_rec_type_test;
Step2 : create or replace TYPE list_rec_type_test AS OBJECT (Seq_no NUMBER);
Step3:
create or replace PACKAGE BODY MANL_APPR_TEST AS
PROCEDURE SEI_EDI_CC_MANL_APPROVAL_TEST(list_test IN list_tbl_type_test,
--P_Seq_no in number,
p_status IN VARCHAR2,
o_error_message OUT VARCHAR2)
IS
BEGIN
FOR i IN 1..list_test.count
LOOP
Update Table_Name
set status = p_status,
APP_REJ_DATE = trunc(sysdate),
APP_REJ_BY = user,
LAST_UPDATE_DATE = sysdate,
UPDATED_BY = user
where SEQ_NO = list_test(i).seq_no; --P_Seq_no
END LOOP;
EXCEPTION
WHEN OTHERS THEN
o_error_message := sql_lib.create_msg('PRocedure_ERROR', sqlerrm, 'MANL_APPROVAL_TEST', to_char
(sqlcode));
END MANL_APPROVAL_TEST;
END MANL_APPR_TEST;
Step4: Managed Bean method
public void onYesClick(ActionEvent actionEvent) {
String returnMsg;
String status = "A";
String SuccessFail = "1";
ViewObject searchflatfeeVO = getViewObjectFromAMImpl("EOVO1", "AppModuleDataControl");
Row[] selectedRows = searchflatfeeVO.getFilteredRows("tSelect", true);
System.out.println("row count" + searchflatfeeVO.getEstimatedRowCount());
System.out.println("printing row count" + selectedRows);
int counter = 0;
Integer Seqid;
String newStatus="A";
String str = "";
ADFContext adfCtx = ADFContext.getCurrent();
SecurityContext secCntx = adfCtx.getSecurityContext();
String _user = secCntx.getUserName();
java.sql.Timestamp startDate = null;
List<ApprovalRecords> arl= new ArrayList<ApprovalRecords>();
List<Integer> dummyList = new ArrayList();
Array nwar;
ApprovalRecords ar;
String[] itemsArray ;
Map<Integer,String> listtoMap= new HashMap<Integer,String>();
for (Row r : selectedRows) {
String newSlin = (String) r.getAttribute("Slin");
String newItem = (String) r.getAttribute("Item");
Seqid = (Integer) r.getAttribute("SeqNo");
System.out.println("printing data ");
System.out.println("id IS " + Seqid);
setPageFlowScopeValue("sequence\_id",Seqid);
dummyList.add(Seqid);
System.out.println("List type values are getting printed in bean method"+dummyList);
// setPageFlowScopeValue("arrayListValues",newValues);
listtoMap.put(Seqid, newStatus.toString());
System.out.println("printing Map "+listtoMap);
if ("E".equals(r.getAttribute("SrcType"))) {
counter++;
}
}
DCBindingContainer bindings = (DCBindingContainer) BindingContext.getCurrent().getCurrentBindingsEntry();
OperationBinding operBind;
operBind = bindings.getOperationBinding("procedureCall");
operBind.getParamsMap().put("itemListVal", dummyList);
operBind.getParamsMap().put("status", newStatus);
returnMsg = (String) operBind.execute();
System.out.println("method exit from managed bean");
}
Step6: AM Impl Method public String adhocUploadValidate(ArrayList<Integer> itemListVal,String status ){
CallableStatement st = null;
String returnMsg = "Success";
System.out.println("printing itemListVal" + itemListVal);
System.out.println("printing itemListVal"+itemListVal);
List<ApprovalRecords> recList = new ArrayList<ApprovalRecords>();
OracleConnection conn ;
try {
String sql ="DECLARE \\n " + " p\_edi\_cc\_list\_test edi\_cc\_list\_tbl\_type\_test:=?;\\n p\_status VARCHAR2(5) :=?;\\n" + "o\_error\_message VARCHAR2(255) :=?;\\n " +
"BEGIN\\n " + "SEI\_EDI\_CC\_MANL\_APPR\_TEST.SEI\_EDI\_CC\_MANL\_APPROVAL\_TEST(p\_edi\_cc\_list\_test,p\_status,o\_error\_message);\\n" + "END;\\n";
System.out.println("printing procedure executable statement"+sql);
st = getDBTransaction().createCallableStatement(sql, 0);
ArrayDescriptor arrydesc =
ArrayDescriptor.createDescriptor("edi\_cc\_list\_tbl\_type\_test",
st.getConnection());
Array orderDetailArray = new ARRAY(arrydesc, st.getConnection(), itemListVal.toArray());
try {
st.setArray(1, orderDetailArray);
st.setString(2, status);
st.registerOutParameter(3, 12);
st.executeUpdate();
} catch (SQLException e) {
throw new JboException(e);
}
return returnMsg;
} catch (Exception e) {
throw new JboException(e);
} finally {
if (st != null) {
try {
st.close();
} catch (SQLException e) {
e.getStackTrace();
}
}
}
}
While invoking the Am IMPL method getting error like <ADF: Adding the following JSF error message: invalid name pattern: edi_cc_list_tbl_type_test>
java.sql.SQLException: invalid name pattern: list_tbl_type_test
at oracle.jdbc.oracore.OracleTypeADT.initMetadata12(OracleTypeADT.java:560)
at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:481)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:447)
at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1607)
at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:275)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:133)
at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:85)