Receiving OUT Parameters

This section describes how to register a PL/SQL index-by table as an OUT parameter. In addition, it describes how to access the OUT bind values in various mapping styles.

Note: The methods this section describes apply to function return values and the IN OUT parameter mode as well.

Registering the OUT Parameters

To register a PL/SQL index-by table as an OUT parameter, use the registerIndexTableOutParameter() method defined in the OracleCallableStatement class.

synchronized registerIndexTableOutParameter 
   (int paramIndex, int maxLen, int elemSqlType, int elemMaxLen)
   throws SQLException

Table 11-3 describes the arguments of the registerIndexTableOutParameter() method.

Table 11-3 Arguments of the registerIndexTableOutParameter () Method

Argument	Description
int paramIndex	This argument indicates the parameter position within the statement.
int maxLen	This argument specifies the maximum table length of the index-by table bind value to be returned.
int elemSqlType	This argument specifies the index-by table element type based on the values defined in the OracleTypes class.
int elemMaxLen	This argument specifies the index-by table element maximum length in case the element type is CHAR, VARCHAR, or RAW. This value is ignored for other types.

The following code example uses the registerIndexTableOutParameter() method to register an index-by table as an OUT parameter:

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned.
int maxLen = 10;

// index-by table element type
int elemSqlType = OracleTypes.NUMBER;

// index-by table element length in case the element type
// is CHAR, VARCHAR or RAW. This value is ignored for other
// types
int elemMaxLen = 0;

// register the return value
funcnone.registerIndexTableOutParameter
   (1, maxLen, elemSqlType, elemMaxLen);

Accessing the OUT Parameter Values

To access the OUT bind value, the OracleCallableStatement class defines multiple methods that return the index-by table values in different mapping styles. There are three mapping choices available in JDBC drivers:

JDBC default mappings	getPlsqlIndexTable(int)
Oracle mappings	getOraclePlsqlIndexTable(int)
Java primitive type mappings	getPlsqlIndexTable(int, Class)

JDBC Default Mappings

The getPlsqlIndexTable() method with the (int) signature returns index-by table elements using JDBC default mappings.

public Object getPlsqlIndexTable (int paramIndex)
   throws SQLException

Table 11-4 describes the argument of the getPlsqlIndexTable() method.

Table 11-4 Argument of the getPlsqlIndexTable () Method

Argument	Description
int paramIndex	This argument indicates the parameter position within the statement.

The return value is a Java array. The elements of this array are of the default Java type corresponding to the SQL type of the elements. For example, for an index-by table with elements of NUMERIC typecode, the element values are mapped to BigDecimal by the Oracle JDBC driver, and the getPlsqlIndexTable() method returns a BigDecimal[] array. For a JDBC application, you must cast the return value to a BigDecimal[] array to access the table element values. (See "Datatype Mappings" for a list of default mappings.)

The following code example uses the getPlsqlIndexTable() method to return index-by table elements with JDBC default mapping:

// access the value using JDBC default mapping 
BigDecimal[] values = 
   (BigDecimal[]) procout.getPlsqlIndexTable (1); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i].intValue()); 

Oracle Mappings

The getOraclePlsqlIndexTable() method returns index-by table elements using Oracle mapping.

public Datum[] getOraclePlsqlIndexTable (int paramIndex)
      throws SQLException 

Table 11-5 describes the argument of the getOraclePlsqlIndexTable() method.

Table 11-5 Argument of the getOraclePlsqlIndexTable () Method

Argument	Description
int paramIndex	This argument indicates the parameter position within the statement.

The return value is an oracle.sql.Datum array and the elements in the Datum array will be the default Datum type corresponding to the element's SQL type. For example, the element values of an index-by table of numeric elements are mapped to the oracle.sql.NUMBER type in Oracle mapping, and the getOraclePlsqlIndexTable() method returns an oracle.sql.Datum array (Datum[]) that contains oracle.sql.NUMBER elements.

The following code example uses the getOraclePlsqlIndexTable() method to access the elements of a PL/SQL index-by table OUT parameter, using Oracle mapping. (The code for registration is omitted.)

// Prepare the statement 
OracleCallableStatement procout = (OracleCallableStatement)
                                  conn.prepareCall ("begin procout (?); end;");

...

// execute the call
procout.execute ();
 
// access the value using Oracle JDBC mapping
Datum[] outvalues = procout.getOraclePlsqlIndexTable (1);

// print the elements
for (int i=0; i<outvalues.length; i++)
   System.out.println (outvalues[i].intValue());

Java Primitive Type Mappings

The getPlsqlIndexTable() method with the (int, Class) signature returns index-by table elements in Java primitive types. The return value is a Java array.

synchronized public Object getPlsqlIndexTable

   (int paramIndex, Class primitiveType) throws SQLException

Table 11-6 describes the arguments of the getPlsqlIndexTable() method.

Table 11-6 Arguments of the getPlsqlIndexTable () Method

Argument	Description
int paramIndex	This argument indicates the parameter position within the statement.
Class primitiveType	This argument specifies a Java primitive type to which the index-by table elements are to be converted. For example, if you specify java.lang.Integer.TYPE, the return value is an int array. The following are the possible values of this parameter: java.lang.Integer.TYPE java.lang.Long.TYPE java.lang.Float.TYPE java.lang.Double.TYPE java.lang.Short.TYPE

The following code example uses the getPlsqlIndexTable() method to access the elements of a PL/SQL index-by table of numbers. In the example, the second parameter specifies java.lang.Integer.TYPE, so the return value of the getPlsqlIndexTable() method is an int array.

OracleCallableStatement funcnone = (OracleCallableStatement) 
   conn.prepareCall ("begin ? := funcnone; end;"); 

// maximum length of the index-by table value. This 
// value defines the maximum table size to be returned. 
int maxLen = 10; 

// index-by table element type 
int elemSqlType = OracleTypes.NUMBER; 

// index-by table element length in case the element type 
// is CHAR, VARCHAR or RAW. This value is ignored for other 
// types 
int elemMaxLen = 0; 

// register the return value 
funcnone.registerIndexTableOutParameter (1, maxLen, 
                                        elemSqlType, elemMaxLen); 
// execute the call 
funcnone.execute (); 

// access the value as a Java primitive array. 
int[] values = (int[]) 
   funcnone.getPlsqlIndexTable (1, java.lang.Integer.TYPE); 

// print the elements 
for (int i=0; i<values.length; i++) 
   System.out.println (values[i]); 

---