We need to pass the entire table records into table PL/SQL Package using table type objects and then insert into tables. Here is the code :
CREATE OR REPLACE PROCEDURE xx_test(p_table IN INVOICE_TBL,x_error OUT error_tbl) IS
j NUMBER := 0;
BEGIN
x_error := error_tbl();
FOR i IN p_table.FIRST .. p_table.LAST LOOP
INSERT INTO invoice_table(invoice_date
,invoice_num
,invoice_type
,invoice_id )
VALUES(TO_DATE(p_table(i).invoice_date,'YYYY/MM/DD')
,p_table(i).invoice_num
,p_table(i).invoice_type
,TO_NUMBER(p_table(i).invoice_id)
);
COMMIT;
j:= j + 1;
x_error.EXTEND;
x_error(j):= error_rec('Error in saving record number '||j);
END LOOP;
END;
CREATE OR REPLACE TYPE invoice_tbl IS TABLE OF invoice_rec;
CREATE OR REPLACE TYPE invoice_rec AS OBJECT invoice_date VARCHAR2(40)
,invoice_num VARCHAR2(240)
,invoice_type VARCHAR2(40)
,invoice_id VARCHAR2(20) );
CREATE OR REPLACE TYPE error_rec AS OBJECT (error VARCHAR2(240));
CREATE OR REPLACE TYPE error_tbl IS TABLE OF error_rec;
create table invoice_table (invoice_date DATE,
invoice_num VARCHAR2(240),
invoice_type VARCHAR2(20),
invoice_id NUMBER);
import java.sql.Types;
import oracle.sql.ARRAY;
public void Pass()
{
/*How to pass VO Rows to PL/SQL*/
String invoice_id = null;
String invoice_num = null;
String invoice_type = null;
String invoice_date = null;
OAViewObject vo = getinvoiceVO1();
invoiceVORowImpl rowi = null;
int fetchedRowCount = vo.getRowCount();
System.out.println("Fetched Row Count is "+fetchedRowCount);
RowSetIterator selectIter = vo.createRowSetIterator("selectIter");
STRUCT finalStruct = null;
Object [] attrField = new Object[4];
Object [] errorarrobj;
Object [] errorrecobj;
ARRAY error;
ArrayList lineTableData = new ArrayList();
Connection conn = getOADBTransaction().getJdbcConnection();
try
{
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("INVOICE_TBL",conn);
StructDescriptor voRowStructDesc = StructDescriptor.createDescriptor("INVOICE_REC",conn);
ArrayDescriptor descriptor1 = ArrayDescriptor.createDescriptor("ERROR_TBL",conn);
StructDescriptor voRowStructDesc1 = StructDescriptor.createDescriptor("ERROR_REC",conn);
if (fetchedRowCount > 0)
{
selectIter.setRangeStart(0);
selectIter.setRangeSize(fetchedRowCount);
for (int i = 0; i < fetchedRowCount; i++)
{
rowi = (invoiceVORowImpl)selectIter.getRowAtRangeIndex(i);
invoice_id = (String)rowi.getInvoiceId().toString();
invoice_num = (String)rowi.getInvoiceNum();
invoice_type = (String)rowi.getInvoiceTypeLookupCode();
invoice_date = (String)rowi.getInvoiceDate().toString();
System.out.println("Invoice Date is "+invoice_date);
//take all
attrField[0] = (String)invoice_date;
attrField[1] = (String)invoice_num;
attrField[2] = (String)invoice_type;
attrField[3] = (String)invoice_id;
//assign all
finalStruct = new STRUCT(voRowStructDesc, conn, attrField);
lineTableData.add(finalStruct);
}
}
selectIter.closeRowSetIterator();
System.out.println("Size of arraylist :"+lineTableData.size());
oracle.sql.ARRAY passArray = new oracle.sql.ARRAY(descriptor, conn, (Object[]) lineTableData.toArray());
try
{
System.out.println("Calling Procedure");
CallableStatement cstmt = conn.prepareCall("{call XX_TEST(:1,:2)}");
cstmt.setArray(1,passArray);
System.out.println("Passed Array");
cstmt.registerOutParameter(2,Types.ARRAY,"ERROR_TBL");
cstmt.execute();
error = (ARRAY)cstmt.getArray(2);
cstmt.close();
errorarrobj = (Object [])error.getArray();
System.out.println("Number of rows="+errorarrobj.length);
System.out.println("Printing results...");
for (int i=0; i
errorrecobj = ((STRUCT)errorarrobj[i]).getAttributes();
System.out.println();
System.out.print("Row "+i);
System.out.print("|"+errorrecobj[0]);
}
}
catch(Exception e)
{
throw OAException.wrapperException(e);
}
}
catch(java.sql.SQLException ex)
{
throw OAException.wrapperException(ex);
}
}
