How to return an array from a PL/SQL Stored function

Date: 19-Feb-2003

After completing this how-to you should be able to:

• Create a VARRAY in Oracle database.

• Use oracle.sql.ARRAY class

• Access VARRAY from Java 

Introduction

This document demonstrates how to return an array from a PL/SQL function and access it from java application. An array is an ordered set of data elements.  A VARRAY is an array of varying size. It has an ordered set of data elements, and all the elements are of the same data type. Each element has an index, which is a number corresponding to the element's position in the VARRAY. The number of elements in a VARRAY is the "size" of the VARRAY. You must specify a maximum size when you declare the VARRAY type.

In this How to, a PL/SQL stored function fetches the names of all the employees from the EMP table in the SCOTT schema, creates an array from these names and returns it. This PL/SQL stored function is invoked from a java application and the employee names are displayed to the user.

Software Requirements

• Oracle9i Database version 9.0.1 or later. You can download the Oracle9i database from Oracle Technology Network. 

• JDK1.2.x or above This can be downloaded from here .

• Oracle9i JDBC Driver. The JDBC driver is available at ORACLE_HOME/jdbc/lib. Or it can be downloaded from here.

Create a SQL VARRAY type in the database and in this scenario it is of type VARCHAR2.   Connect to your database as scott/tiger user and execute the following command at the SQL prompt.

SQL>CREATE OR REPLACE TYPE EMPARRAY is VARRAY(20) OF VARCHAR2(30)
SQL>/

Then create the following function that returns a VARRAY.

CREATE OR REPLACE FUNCTION getEmpArray RETURN EMPARRAY
AS
  l_data EmpArray := EmpArray();
  CURSOR c_emp IS SELECT ename FROM EMP;
  BEGIN
    FOR emp_rec IN c_emp LOOP
      l_data.extend;
      l_data(l_data.count) := emp_rec.ename;
    END LOOP;
    RETURN l_data;
  END;

Once the function is created in the database, it can be invoked from the java application and get the array data in the application. Below given is the code snippet  to execute the PL/SQL stored function from a Java Application. Click here to see the source code of the complete application.

public static void main( ) {
.........
......... 
  OracleCallableStatement stmt =(OracleCallableStatement)conn.prepareCall
                ( "begin ? := getEMpArray; end;" );

    // The name we use below, EMPARRAY, has to match the name of the 
    // type defined in the PL/SQL Stored Function
    stmt.registerOutParameter( 1, OracleTypes.ARRAY,"EMPARRAY" );
    stmt.executeUpdate();

    // Get the ARRAY object and print the meta data assosiated with it
    ARRAY simpleArray = stmt.getARRAY(1);

    System.out.println("Array is of type " +  simpleArray.getSQLTypeName());

    System.out.println("Array element is of type code "+simpleArray.getBaseType());

    System.out.println("Array is of length " + simpleArray.length());

    // Print the contents of the array
    String[] values = (String[])simpleArray.getArray();

    for( int i = 0; i < values.length; i++ )
      System.out.println( "row " + i + " = '" + values[i] +"'" );

...........
...........

In the above code snippet, you can see that OracleCallableSatatement is used to invoke the PL/SQL Stored function. Before executing the PL/SQL stored function, register the return data type as OracleTypes.ARRAY and also specify the name of the type (EMPARRAY) defined in the database. Then execute the PL/SQL stored function and get the return value as oracle.sql.ARRAY. oracle.sql.ARRAY class has methods to get details about the array like array type, array length etc. Use the getArray() method of oracle.sql.ARRAY to get the contents of the array and print the contents.

Summary

This document explained about how to create a VARRAY and return an array from PL/SQL stored function and how to access it from a java application.