First we will create an array (nested table array or varray), use it in a stored procedure as OUT parameter and then populate and retrieve data through a JDBC program.

Creating array

We can create a nested table array as:

CREATE TYPE array_table AS TABLE OF VARCHAR2 (50);

Or

We can create a VARRAY as:

CREATE TYPE array_table AS VARRAY(5) OF VARCHAR2(50);

Note: In nested table array (AS TABLE OF), you don’t specify an upper bound whereas in VARRAY, you specify an upper bound. If you pass more elements to a VARRAY than specified, you will get an exception as: Exceeded maximum VARRAY limit.

Oracle Stored Procedure with only Array OUT parameters

CREATE OR REPLACE PROCEDURE procarrayout ( len OUT NUMBER,

                                              p_arr_out  OUT array_table)

AS

   v_count   NUMBER;

BEGIN

   len := 5;

   p_arr_out := NEW array_table ();

   p_arr_out.EXTEND (len);

     v_count := 0;

 

   FOR i IN 1 .. len

   LOOP

      p_arr_out (i) := 'dummy data';

      v_count := v_count + 1;

   END LOOP;

END;

JDBC Program to retrieve Arrays from an Oracle Stored Procedure

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.Types;

 

import oracle.jdbc.OracleCallableStatement;

import oracle.jdbc.internal.OracleTypes;

import oracle.sql.ARRAY;

 

public class JDBCArraySPOUT {

 

  public static void passArray() {

    try {

 

      Class.forName("oracle.jdbc.OracleDriver");

      Connection con = DriverManager.getConnection(

          "jdbc:oracle:thin:@<host>:<port>:<sid>", "hej01", "hej01");

 

      CallableStatement st = con.prepareCall("call procarrayout(?,?)");

 

      st.registerOutParameter(1, Types.INTEGER);

      st.registerOutParameter(2, OracleTypes.ARRAY, "ARRAY_TABLE");

      st.execute();

 

      System.out.println("size : " + st.getInt(1));

 

      // Retrieving array from the resultset after executing the stored

      // procedure

      ARRAY arr = ((OracleCallableStatement) st).getARRAY(2);

      String[] recievedArray = (String[]) arr.getArray();

 

      for (int i = 0; i < recievedArray.length; i++)

        System.out.println(recievedArray[i]);

 

    } catch (Exception e) {

      System.out.println(e);

    }

  }

 

  public static void main(String args[]) {

    passArray();

  }

}