Returning data from anonymous PL/SQL block

The example here demonstrates the use of an anonymous PL/SQL block to return data to a calling Java program. It also shows how to use nested tables in PL/SQL, and the use of auto-generated column values. This requires Java 5 and Oracle 10g Release 2. In order to use the example, on the database

1. Create a trigger on the database table that inserts a value into the new row, or use the following piece of code before the insert statement and insert the seqval variable in the id column.

   select req_seq.nextval into seqval from dual;

   Here req_seq is a sequence holding the req_id sequence.
2. Create two new types as shown below

   create or replace TYPE RQ_ROW AS TABLE OF VARCHAR(500);
   create or replace TYPE RQ_LIST AS TABLE OF RQ_ROW;
   create or replace TYPE RESULTS_LIST AS TABLE OF RESULT_LIST;
   create or replace TYPE RESULT_LIST AS VARRAY OF NUMBER;

   
   RQ_ROW represents a row in the table. RQ_LIST represents a set of rows. RESULT_LIST is a two element list where the first element represents the index and the second element is the autogenerated column value. This was used since nested tables do not guarantee the order of elements. RESULTS_LIST is used to return the autogenerated keys back to Java.

Once the database is setup use the following PL/SQL block to insert rows into the table

DECLARE  
 rqResults RESULTS_LIST;  
 rqRow RQ_ROW;  
 rqList RQ_LIST;  
 p Number;  
 q Number; 

BEGIN    

 rqList := ?;   
 rqresults := results_list();  
 q := rqList.COUNT;  
 FOR k IN 1..q LOOP     
  INSERT INTO rquisitions(RQ_CD, RQ_STATUS) 
       VALUES (rqList(k)(1), rqList(k)(2)) RETURNING rq_id INTO p;   
  rqResults.EXTEND;  
  rqResults(k) := RESULT_LIST(k,p); 
 END LOOP;   

  ? := rqResults; 

 END;

rqList is the input parameter here. Note the ? := rqResults; This represents the output parameter. The following code snippet shows the implementation of this in a Java program.

private String sql = "DECLARE " +
        "rqResults RESULTS_LIST; " +
        "rqRow RQ_ROW; " +
        "rqList RQ_LIST; " +
        "p Number; " +
        "q Number;" +
        "BEGIN   " +
        "rqList := ?;  " +
        "rqresults := results_list(); " +
        "q := rqList.COUNT; " +
        "FOR k IN 1..q LOOP    " +
        "INSERT INTO rquisitions(RQ_CD, RQ_STATUS) VALUES " +
        "(rqList(k)(1), rqList(k)(2)) RETURNING rq_id INTO p;  " +
        "rqResults.EXTEND; " +
        "rqResults(k) := RESULT_LIST(k,p);" +
        "END LOOP;  " +
        "? := rqResults;" +
        "END;";


public void load() {
    Connection connection = getConnection();
    String RQ_COLS[] = { "req_id" };
    try {
        String data[][] = {{"ab", "cd"}, {"ef", "gh"}, {"ij", "kl"}};

        ArrayDescriptor descriptor = 
               ArrayDescriptor.createDescriptor("SCOTT.RQ_LIST", connection);
        Array array = new ARRAY(descriptor, connection, data);
        CallableStatement cstmt = null;


        cstmt = connection.prepareCall(sql);
        cstmt.setArray(1, array);

        cstmt.registerOutParameter(2, Types.ARRAY, "SCOTT.RESULTS_LIST");

        cstmt.executeUpdate();
 
 // Print the result set.

        Array arr = cstmt.getArray(2);

        ResultSet rSet = arr.getResultSet();
        long[] values = new long [6];
        while (rSet.next()) {

            Array arr1 = (Array)rSet.getArray(2);
            ResultSet rrSet = arr1.getResultSet();

            while(rrSet.next()) {

                int index = rrSet.getInt(2);
                if(rrSet.next()) {
                    long value = rrSet.getLong(2);
                    values[index] = value;
                }
            }
        }

        for(int i  = 1; i < values.length; i ++) {
            System.out.println(i + ", " + values[i]);
        }


        cstmt.close();
        connection.close();

    } catch (SQLException e) {
        e.printStackTrace();
    }

Note the use of a callable statement and that the out parameter is an array. It is actually an array of arrays. The java.sql.Array class provides a getResultSet() method that can be used to browse the array elements.

This code was tested on Java 5.0 with Oracle 10g Release 2.

References:

• Using Multilevel Collections in PL/SQL
• Oracle 10g Samples