從PL/SQL儲存函式返回陣列

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;

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] +"'" );

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