How to send entire table rows into PL/SQL Package at one go..

Hi,

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);
      }
 }