8.6.2 Using a Collection

Create collection types NUMBER_ARRAY and CHAR_ARRAY in the Advanced Server database:

CREATE OR REPLACE TYPE NUMBER_ARRAY AS TABLE OF NUMBER;

CREATE OR REPLACE TYPE CHAR_ARRAY AS TABLE OF VARCHAR(50);

Listing 1.17-a is an SPL function that uses collection types NUMBER_ARRAY and CHAR_ARRAY as IN parameters and CHAR_ARRAY as the OUT parameter.

The function concatenates the employee ID from the NUMBER_ARRAY IN parameter with the employee name in the corresponding row from the CHAR_ARRAY IN parameter. The resulting concatenated entries are returned in the CHAR_ARRAY OUT parameter.

Listing 1.17-a

CREATE OR REPLACE FUNCTION concatEmpIdName

(

arrEmpIds NUMBER_ARRAY,

arrEmpNames CHAR_ARRAY

) RETURN CHAR_ARRAY

AS

DECLARE

i INTEGER := 0;

arrEmpIdNames CHAR_ARRAY;

BEGIN

arrEmpIdNames := CHAR_ARRAY(NULL,NULL);

FOR i IN arrEmpIds.FIRST..arrEmpIds.LAST LOOP

arrEmpIdNames(i) := arrEmpIds(i) || ' ' || arrEmpNames(i);

END LOOP;

RETURN arrEmpIdNames;

END;

Listing 1.17-b is a Java method that calls the Listing 1.17-a function, passing and retrieving the collection types:

Listing 1.17-b

public static void testTableOfAsInOutParams() throws SQLException {

Connection conn = null;

try {

conn = getEDBConnection();

String commandText = "{? = call concatEmpIdName(?,?)}";

CallableStatement stmt = conn.prepareCall(commandText);

 

// create collections to specify employee id and name values

Array empIdArray = conn.createArrayOf("integer",

new Integer[]{7900, 7902});

Array empNameArray = conn.createArrayOf("varchar",

new String[]{"JAMES", "FORD"});

 

// set TABLE OF VARCHAR as OUT param

stmt.registerOutParameter(1, Types.ARRAY);

 

// set TABLE OF INTEGER as IN param

stmt.setObject(2, empIdArray, Types.OTHER);

 

// set TABLE OF VARCHAR as IN param

stmt.setObject(3, empNameArray, Types.OTHER);

stmt.execute();

java.sql.Array empIdNameArray = stmt.getArray(1);

String[] emps = (String[]) empIdNameArray.getArray();

 

System.out.println("items length: " + emps.length);

System.out.println("items[0]: " + emps[0].toString());

System.out.println("items[1]: " + emps[1].toString());

 

} catch (ClassNotFoundException cnfe) {

System.err.println("Error: " + cnfe.getMessage());

} finally {

if (conn != null) {

conn.close();

}

}

}

A CallableStatement object is prepared to invoke the concatEmpIdName() function:

String commandText = "{? = call concatEmpIdName(?,?)}";

CallableStatement stmt = conn.prepareCall(commandText);

createArrayOf() initializes and creates collections named empIdArray and empNameArray:

Array empIdArray = conn.createArrayOf("integer",

new Integer[]{7900, 7902});

Array empNameArray = conn.createArrayOf("varchar",

new String[]{"JAMES", "FORD"});

The call to registerOutParameter() registers the parameter type (Types.ARRAY) of the OUT parameter:

stmt.registerOutParameter(1, Types.ARRAY);

The setObject() method binds the collections empIdArray and empNameArray to the IN placeholders:

stmt.setObject(2, empIdArray, Types.OTHER);

stmt.setObject(3, empNameArray, Types.OTHER);

A call to stmt.execute() invokes the concatEmpIdName() function:

stmt.execute();

getArray() retrieves the collection returned by the function. The first two rows consisting of the concatenated employee IDs and names are displayed:

java.sql.Array empIdNameArray = stmt.getArray(1);

String[] emps = (String[]) empIdNameArray.getArray();

System.out.println("items length: " + emps.length);

System.out.println("items[0]: " + emps[0].toString());

System.out.println("items[1]: " + emps[1].toString());