Oracle8i JDBC Developer's Guide and Reference Release 3 (8.1.7) Part Number A83724-01 |
Library | Service | Contents | Index

## 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]);

| Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
Library | Service | Contents | Index