import java.sql.*;
import java.io.*;
import java.util.*;
import java.math.BigDecimal;
import oracle.sql.*;
import oracle.jdbc.driver.*;

public class callInOutStructArray extends Object {

/*
   This program demonstrates how to emulate calling a stored procedure
   with PL/SQL table of record parameters via JDBC.  You cannot call a 
   PL/SQL table of records parameter directly, however, you can use an 
   Object with the same structure as your table of records.

   The Script used to create the procedure in this example is as follows:

   drop type rectab;

   create or replace type rectype as object(col1 varchar2(10),col2 varchar2(10));
   /

   create or replace type rectab as table of rectype;
   /

   create or replace package ioStructArray as

   procedure testproc(iorec in out rectab,orec out rectab);

   end ioStructArray;
   /

   create or replace package body ioStructArray as
 
   procedure testproc(iorec in out rectab,orec out rectab) is
   begin
      orec := iorec;
      for i in 1..iorec.count loop
         iorec(i).col1 := orec(i).col2;
         iorec(i).col2 := orec(i).col1;
      end loop;
   end testproc;
   end ioStructArray;
   /

*/
   public static void main(String[] args) throws SQLException {

      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

    // Connect to the database

      Connection conn =
         DriverManager.getConnection ("jdbc:oracle:oci8:@S692815.WORLD",
                                   "scott", "tiger");

   //  First, declare the Object arrays that will store the data.

      Object [] p1recobj = {"FIRST","LAST"};
      Object [] p2recobj = {"SECOND","LAST"};
      Object [] p3recobj;
      Object [] p4recobj;

   //  Declare the Object Arrays to hold the STRUCTS.

      Object [] p1arrobj;
      Object [] p2arrobj;

   //  Declare two descriptors, one for the ARRAY TYPE
   //  and one for the OBJECT TYPE.

      StructDescriptor desc1=StructDescriptor.createDescriptor("RECTYPE",conn);
      ArrayDescriptor desc2=ArrayDescriptor.createDescriptor("RECTAB",conn);

   //  Create the STRUCT objects to associate the host objects
   //  with the database records.

      STRUCT p1struct = new STRUCT(desc1,conn,p1recobj);
      STRUCT p2struct = new STRUCT(desc1,conn,p2recobj);

   //  Initialize the Input array object - to an array of STRUCT Objects.

      p1arrobj = new Object []{p1struct,p2struct};

   //  Set up the ARRAY object.

      ARRAY  p1arr = new ARRAY(desc2,conn,p1arrobj);
      ARRAY  p2arr;

   //  Declare the callable statement.
   //  This must be of type OracleCallableStatement.

      OracleCallableStatement ocs =
         (OracleCallableStatement)conn.prepareCall("{call iostructarray.testproc(?,?)}");

   //  The first parameter is in out so we have to use setARRAY to
   //  pass it to the statement

      ocs.setARRAY(1,p1arr);

   //  The first parameter is in out, so we have to Register the 
   //  parameter as well.
   //  Note the re use of the TYPE.

      ocs.registerOutParameter(1,OracleTypes.ARRAY,"RECTAB");

  //   The second paramter is out, so that has to be registered too.
  //   Note the re use of the TYPE.

      ocs.registerOutParameter(2,OracleTypes.ARRAY,"RECTAB");

   //  Execute the procedure

      ocs.execute();

   //  Associate the returned arrays with the ARRAY objects.

      p1arr = ocs.getARRAY(1);
      p2arr = ocs.getARRAY(2);

   //  Get the data back into the data arrays.

      p1arrobj = (Object [])p1arr.getArray();

      p2arrobj = (Object [])p2arr.getArray();

   //  Get the data records from each array element (which is of type STRUCT).

      p1recobj = ((STRUCT)p1arrobj[0]).getAttributes();
      p2recobj = ((STRUCT)p1arrobj[1]).getAttributes();

      p3recobj = ((STRUCT)p2arrobj[0]).getAttributes();
      p4recobj = ((STRUCT)p2arrobj[1]).getAttributes();

   //  Show the results:

      System.out.println("First Object is now "+p1recobj[0]+" and "+p1recobj[1]);
      System.out.println("                    "+p2recobj[0]+" and "+p2recobj[1]);
      System.out.println("Second Object is now "+p3recobj[0]+" and "+p3recobj[1]);
      System.out.println("                    "+p4recobj[0]+" and "+p4recobj[1]);

   }
}