Passing multiple rows into oracle PL/SQL procedure

Introduction
Oracle off course uses standard Java database connectivity interfaces for communication with it's database. This is all nice and good, but if you know that you will use only Oracle database for your project, maybe it is useful to use some Oracle specific Java libraries in you project.

In this tutorial I will represent some specific Oracle Java libraries that can help you pass structured data into PL/SQL stored procedures. This structured data can contain multiple rows (each row contain multiple columns). So how can you do that?

Implementation

We will use two oracle specific classes:
oracle.sql.ARRAY
and
oracle.sql.STRUCT

First class (ARRAY) help us creating array of STRUCT object. STRUCT object is created by using static oracle.sql.StructDescriptor.createDescriptor method. This method uses existing Object type from database.
ARRAY object is created by using oracle.sql.ArrayDescriptor.createDescriptor method. This method uses existing collection type from database. This collection elements are of type that is same as our object type (I hope you understand this :) - This is basically "array of our object type" defined on database as collection type).

I create simple helper class to handle creation of structure and call to PL/SQL procedure. Here it is...

import java.sql.Connection;

import oracle.jbo.client.Configuration;
import oracle.jbo.server.DBTransaction;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class TransferStructure {

    private StructDescriptor structureDescriptor;
    private ArrayDescriptor arrayDescriptor;
    private Object[] structValues;
    private int fieldsCount;
    private Connection connection;
    private STRUCT[] allRows;
    private String[] structDescColumnNames;
    private DBTransaction dbTransaction;
    private int currentRowIndx = 0;

    public TransferStructure(String objectTypeName, String arrayTypeName,
                             int numberOfRows,
                             DBTransaction dbTransaction) throws Exception {
        this.dbTransaction = dbTransaction;
        this.connection =
                Call_Pl_SqlCodeUtil.getCurrentConnection(dbTransaction);
        allRows = new STRUCT[numberOfRows];

        structureDescriptor =
                StructDescriptor.createDescriptor(objectTypeName, this.connection);
        arrayDescriptor =
                ArrayDescriptor.createDescriptor(arrayTypeName, this.connection);
        structValues = new Object[fieldsCount];
        fieldsCount = structureDescriptor.getMetaData().getColumnCount();
        structDescColumnNames = new String[fieldsCount];

    }

    public void initializeRow() throws Exception {
        structValues = new Object[fieldsCount];        
    }
    
    public void setFiledValue(String fieldName,
                              Object value) throws Exception {
        structValues[fieldPos(fieldName)] = value;
    }
    
    /**
     * Sadly but you need to call this at the end of row populatio.
     * @throws Exception
     */
    public void finalizeRow() throws Exception {
      STRUCT struct =
          new STRUCT(structureDescriptor, this.connection, structValues);
      allRows[currentRowIndx++] = struct;
    }
    
    /**
     * Finds field position in structure.
     * @param fieldName
     * @return
     * @throws Exception
     */
    private int fieldPos(String fieldName) throws Exception {
        int fieldPosition = -1;

        for (int i = 1; i < fieldsCount + 1; i++) {
            String currentField =
                structureDescriptor.getMetaData().getColumnName(i);
            if (currentField.equals(fieldName)) {
                fieldPosition = i - 1;
                break;
            }
        }
        return fieldPosition;
    }

    private ARRAY getArrayStructure() throws Exception {
        return new ARRAY(arrayDescriptor, this.connection, allRows);
    }

    public void makeProcedureCall(String procedureName) throws Exception {
        Call_Pl_SqlCodeUtil.callStoredProcedure(dbTransaction, procedureName,
                                                new Object[] { getArrayStructure() });
    }
}

You can use this helper class like so:

public static void main(String[] args) throws Exception {

        TransferStructure ts =
            new TransferStructure("YOUR_OBJECT_TYPE", "YOUR_ARRAY_TYPE_OF_OBJECTS",
                                  2, "getDatabaseConnection");
        ts.initializeRow();
        ts.setFiledValue("YOUR_FIRST_ATTRIBUTE", "value1");
        ts.finalizeRow();
        ts.initializeRow();
        ts.setFiledValue("YOUR_SECOND_ATTRIBUTE", new oracle.jbo.domain.Number(1234));
        ts.finalizeRow();
        ts.makeProcedureCall("YOUR_PACKAGE.your_procedure_with_in_parameter(?)");

}

You will also need method to call stored procedure from Java. This is not so hard to find on net. Also I want to note that this example is mainly implemented to run on Oracle ADF framework, but I think it will also run on some non-oracle development environments if you acquire necessary libraries.