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

## Oracle Type Extensions

Oracle SQLJ offers extended functionality for the following JDBC 2.0 and Oracle-specific datatypes:

- JDBC 2.0 LOB datatypes (`BLOB` and `CLOB`)

- Oracle `BFILE` datatype

- Oracle `ROWID` datatype

- Oracle `REF CURSOR` datatypes

- other Oracle8i datatypes (such as `NUMBER` and `RAW`)

These datatypes are supported by classes in the `oracle.sql` package, discussed below. LOBs and BFILEs are handled similarly in many ways, so are discussed together.

Additionally, Oracle SQLJ offers extended support for the following standard JDBC type:

- `BigDecimal`

JDBC 2.0 functionality for user-defined database objects (both weakly and strongly typed), object references, and collections (variable arrays and nested tables) are also supported. These are discussed in Chapter 6, "Objects and Collections".

Note that using Oracle extensions in your code requires the following:

- Use one of the Oracle JDBC drivers.

- Customize the profiles appropriately (the default customizer, `oracle.sqlj.runtime.util.OraCustomizer`, is recommended).

- Use the Oracle SQLJ runtime when your application runs.

(The Oracle SQLJ runtime and an Oracle JDBC driver are required whenever you use the Oracle customizer, even if you do not actually use Oracle extensions in your code.)

You also must import the `oracle.sql` package, as follows (unless you use the fully qualified class names in your code):

    import oracle.sql.*;

For Oracle-specific semantics-checking, you must use an appropriate checker. The default checker, `oracle.sqlj.checker.OracleChecker`, acts as a front end and will run the appropriate checker based on your environment. This will be one of the Oracle-specific checkers if you are using an Oracle JDBC driver.

### Package oracle.sql

SQLJ users, as well as JDBC users, should be aware of the `oracle.sql` package, which includes classes to support all the Oracle8i datatypes (for example, `oracle.sql.ROWID`, `oracle.sql.CLOB`, and `oracle.sql.NUMBER`). The `oracle.sql` classes are wrappers for the raw SQL data and provide appropriate mappings and conversion methods to Java formats. An `oracle.sql.*` object contains a binary representation of the corresponding SQL data in the form of a byte array.

Each `oracle.sql.*` datatype class is a subclass of the `oracle.sql.Datum` class.

You also must import the package, as follows (unless you use the fully qualified class names in your code):

    import oracle.sql.*;

For Oracle-specific semantics-checking, you must use an appropriate checker. The default checker, `oracle.sqlj.checker.OracleChecker`, acts as a front end and will run the appropriate checker based on your environment. This will be one of the Oracle-specific checkers if you are using an Oracle JDBC driver.

For information about translator options relating to semantics-checking, see "Connection Options" and "Semantics-Checking Options".

For more information about the `oracle.sql` classes, see the Oracle8i JDBC Developer's Guide and Reference.

### Support for BLOB, CLOB, and BFILE

Oracle JDBC and SQLJ support JDBC 2.0 large object (LOB) datatypes--`BLOB` (binary LOB) and `CLOB` (character LOB)--and provide similar support for the Oracle-specific `BFILE` type (read-only binary files stored outside the database). These datatypes are supported by the following classes:

- `oracle.sql.BLOB`

- `oracle.sql.CLOB`

- `oracle.sql.BFILE`

See the Oracle8i JDBC Developer's Guide and Reference for more information about LOBs and files, and use of supported stream APIs.

The `oracle.sql.BLOB`, `oracle.sql.CLOB`, and `oracle.sql.BFILE` classes can be used in Oracle-specific SQLJ applications in the following ways:

- as `IN`, `OUT`, or `INOUT` host variables in executable SQLJ statements (including use in INTO-lists)

- as return values from stored function calls

- as column types in iterator declarations (both named and positional)

You can manipulate LOBs by using methods defined in the `BLOB` and `CLOB` classes (recommended) or by using the procedures and functions defined in the PL/SQL package `DBMS_LOB`. All procedures and functions defined in this package can be called by SQLJ programs.

You can manipulate BFILEs by using methods defined in the `BFILE` class (recommended) or by using the file-handling routines of the `DBMS_LOB` package.

Using methods of the `BLOB`, `CLOB`, and `BFILE` classes in a Java application is more convenient than using the `DBMS_LOB` package and may also lead to faster execution in some cases.

Note that the type of the chunk being read or written depends on the kind of LOB being manipulated. For example, CLOBs contain character data; therefore, Java strings are used to hold chunks of data. BLOBs contain binary data; therefore, Java byte arrays are used to hold chunks of data.

Note: `DBMS_LOB` is a database package, requiring a round trip to the server. Methods in the `BLOB`, `CLOB`, and `BFILE` classes may also result in a round trip to the server.

#### BFILE Class versus DBMS_LOB Functionality for BFILEs

The following examples contrast use of the `oracle.sql` methods with use of the `DBMS_LOB` package for BFILEs.

##### Example: Use of oracle.sql.BFILE File-Handling Methods with BFILE

This example manipulates a BFILE using file-handling methods of the `oracle.sql.BFILE` class.

    BFILE openFile (BFILE file) throws SQLException
    {
      String dirAlias, name;
      dirAlias = file.getDirAlias();
      name = file.getName();
      System.out.println("name: " + dirAlias + "/" + name);

      if (!file.isFileOpen())
      {
        file.openFile();
      }
      return file;
    }

The `BFILE` `getDirAlias()` and `getName()` methods construct the full path and file name. The `openFile()` method opens the file. You cannot manipulate BFILEs until they have been opened.

##### Example: Use of DBMS_LOB File-Handling Routines with BFILE

This example manipulates a BFILE using file-handling routines of the `DBMS_LOB` package.

    BFILE openFile(BFILE file) throws SQLException
    {
       String dirAlias, name;
       #sql { CALL dbms_lob.filegetname(:file, :out dirAlias, :out name) };
       System.out.println("name: " + dirAlias + "/" + name);

       boolean isOpen;
       #sql isOpen = { VALUES(dbms_lob.fileisopen(:file)) };
       if (!isOpen)
       {
          #sql { CALL dbms_lob.fileopen(:inout file) };
       }
       return file;
    }

The `openFile()` method prints the name of a file object then returns an opened version of the file. Note that BFILEs can be manipulated only after being opened with a call to `DBMS_LOB.FILEOPEN` or equivalent method in the `BFILE` class.

#### BLOB and CLOB Classes versus DBMS_LOB Functionality for LOBs

The following examples contrast use of the `oracle.sql` methods with use of the `DBMS_LOB` package for BLOBs and CLOBs. For each example using `oracle.sql` methods, the example that follows it is functionally identical but uses `DBMS_LOB` instead.

##### Example: Use of oracle.sql.CLOB Read Methods with CLOB

This example reads data from a CLOB using methods of the `oracle.sql.CLOB` class.

    void readFromClob(CLOB clob) throws SQLException
    {
      long clobLen, readLen;
      String chunk;

      clobLen = clob.length();

      for (long i = 0; i < clobLen; i+= readLen) {
        chunk = clob.getSubString(i, 10);
        readLen = chunk.length();
        System.out.println("read " + readLen + " chars: " + chunk);
      }
    }

This method contains a loop that reads from the CLOB and returns a 10-character Java string each time. The loop continues until the entire CLOB has been read.

##### Example: Use of DBMS_LOB Read Routines with CLOB

This example uses routines of the `DBMS_LOB` package to read from a CLOB.

    void readFromClob(CLOB clob) throws SQLException
    {
       long clobLen, readLen;
       String chunk;

       #sql clobLen = { VALUES(dbms_lob.getlength(:clob)) };

       for (long i = 1; i <= clobLen; i += readLen) {
           readLen = 10;
           #sql { CALL dbms_lob.read(:clob, :inout readLen, :i, :out chunk) };
           System.out.println("read " + readLen + " chars: " + chunk);
       }
    }

This method reads the contents of a CLOB in chunks of 10 characters at a time. Note that the chunk host variable is of the type `String`.

##### Example: Use of oracle.sql.BLOB Write Routines with BLOB

This example writes data to a BLOB using methods of the `oracle.sql.BLOB` class. Input a BLOB and specified length.

    void writeToBlob(BLOB blob, long blobLen) throws SQLException
    {
      byte[] chunk = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
      long chunkLen = (long)chunk.length;

      for (long i = 0; i < blobLen; i+= chunkLen) {
        if (blobLen < chunkLen) chunkLen = blobLen;
        chunk[0] = (byte)(i+1);
        chunkLen = blob.putBytes(i, chunk);
      }
    }

This method goes through a loop that writes to the BLOB in 10-byte chunks until the specified BLOB length has been reached.

##### Example: Use of DBMS_LOB Write Routines with BLOB

This example uses routines of the `DBMS_LOB` package to write to a BLOB.

    void writeToBlob(BLOB blob, long blobLen) throws SQLException
    {
       byte[] chunk = { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
       long chunkLen = (long)chunk.length;

       for (long i = 1; i <= blobLen; i += chunkLen) {
          if ((blobLen - i + 1) < chunkLen) chunkLen = blobLen - i + 1;
          chunk[0] = (byte)i;
          #sql { CALL dbms_lob.write(:INOUT blob, :chunkLen, :i, :chunk) };
       }
    }

This method fills the contents of a BLOB in 10-byte chunks. Note that the chunk host variable is of the type `byte[]`.

#### LOB and BFILE Stored Function Results

Host variables of type `BLOB`, `CLOB`, and `BFILE` can be assigned to the result of a stored function call. The following example is for a CLOB, but code for BLOBs and BFILEs would be functionally the same.

First, presume the following function definition:

    CREATE OR REPLACE function longer_clob (c1 clob, c2 clob) return clob is
       result clob;
    BEGIN
       if dbms_lob.getLength(c2) > dbms_lob.getLength(c1) then
          result := c2;
       else
          result := c1;
       end if;
       RETURN result;
    END longer_clob;

The following example uses a CLOB as the assignment type for a return value from the function defined above.

    void readFromLongest(CLOB c1, CLOB c2) throws SQLException
    {
       CLOB longest;
       #sql longest = { VALUES(longer_clob(:c1, :c2)) };
       readFromClob(longest);
    }

The `readFromLongest()` method prints the contents of the longer passed CLOB, using the `readFromClob()` method defined previously.

#### LOB and BFILE Host Variables and SELECT INTO Targets

Host variables of type `BLOB`, `CLOB`, and `BFILE` can appear in the INTO-list of a `SELECT INTO` executable statement. The following example is for a BLOB and CLOB, but code for BFILEs would be functionally the same.

Assume the following table definition:

    CREATE TABLE basic_lob_table(x varchar2(30), b blob, c clob);
    INSERT INTO basic_lob_table
       VALUES('one', '010101010101010101010101010101', 'onetwothreefour');
    INSERT INTO basic_lob_table
       VALUES('two', '020202020202020202020202020202', 'twothreefourfivesix');

The following example uses a BLOB and a CLOB as host variables that receive data from the table defined above, using a `SELECT INTO` statement.

    ...
    BLOB blob;
    CLOB clob;
    #sql { SELECT one.b, two.c INTO :blob, :clob
         FROM basic_lob_table one, basic_lob_table two
         WHERE one.x='one' AND two.x='two' };
    #sql { INSERT INTO basic_lob_table VALUES('three', :blob, :clob) };
    ...

This example selects the BLOB from the first row and the CLOB from the second row of the `BASIC_LOB_TABLE`. It then inserts a third row into the table using the BLOB and CLOB selected in the previous operation.

#### LOBs and BFILEs in Iterator Declarations

The types `BLOB`, `CLOB`, and `BFILE` can be used as column types for SQLJ positional and named iterators. Such iterators can be populated as a result of compatible executable SQLJ operations.

Here are sample declarations that will be repeated and used below.

    #sql iterator NamedLOBIter(CLOB c);
    #sql iterator PositionedLOBIter(BLOB);
    #sql iterator NamedFILEIter(BFILE bf);

#### LOB and BFILE Host Variables and Named Iterator Results

The following example employs the table `BASIC_LOB_TABLE` and the method `readFromLongest()` defined in previous examples, and uses a CLOB in a named iterator. Similar code could be written for BLOBs and BFILEs.

Declaration:

    #sql iterator NamedLOBIter(CLOB c);

Executable code:

    ...
    NamedLOBIter iter;
    #sql iter = { SELECT c FROM basic_lob_table };
    if (iter.next())
       CLOB c1 = iter.c();
    if (iter.next())
       CLOB c2 = iter.c();
    iter.close();
    readFromLongest(c1, c2);
    ...

This example uses an iterator to select two CLOBs from the first two rows of the `BASIC_LOB_TABLE`, then prints the larger of the two using the `readFromLongest()` method.

#### LOB and BFILE Host Variables and Positional Iterator FETCH INTO Targets

Host variables of type `BLOB`, `CLOB`, and `BFILE` can be used with positional iterators and appear in the INTO-list of the associated `FETCH INTO` statement if the corresponding column attribute in the iterator is of the identical type.

The following example employs table `BASIC_LOB_TABLE` and method `writeToBlob()` defined in previous examples. Similar code could be written for CLOBs and BFILEs.

Declaration:

    #sql iterator PositionedLOBIter(BLOB);

Executable code:

    ...
    PositionedLOBIter iter;
    BLOB blob = null;
    #sql iter = { SELECT b FROM basic_lob_table };
    for (long rowNum = 1; ; rowNum++)
    {
        #sql { FETCH :iter INTO :blob };
        if (iter.endFetch()) break;
        writeToBlob(blob, 512*rowNum);
    }
    iter.close();
    ...

This example calls `writeToBlob()` for each BLOB in `BASIC_LOB_TABLE`. Each row writes an additional 512 bytes of data.

### Support for Oracle ROWID

The Oracle-specific type `ROWID` stores the unique address for each row in a database table. The class `oracle.sql.ROWID` wraps ROWID information and is used to bind and define variables of type `ROWID`.

Variables of type `oracle.sql.ROWID` can be employed in SQLJ applications connecting to an Oracle database in the following ways:

- as `IN`, `OUT` or `INOUT` host variables in SQLJ executable statements (including use in INTO-lists)

- as a return value from a stored function call

- as column types in iterator declarations (both named and positional)

  Note: Oracle does not currently support positioned `UPDATE` or positioned `DELETE` by way of a `WHERE CURRENT OF` clause, as specified by the SQLJ specification. Instead, Oracle recommends the use of ROWIDs to simulate this functionality.

#### ROWIDs in Iterator Declarations

You can use the type `oracle.sql.ROWID` as a column type for SQLJ positional and named iterators, as shown in the following declarations:

    #sql iterator NamedRowidIter (String ename, ROWID rowid);

    #sql iterator PositionedRowidIter (String, ROWID);

#### ROWID Host Variables and Named-Iterator SELECT Results

You can employ `ROWID` objects as `IN`, `OUT` and `INOUT` parameters in SQLJ executable statements. In addition, you can populate iterators whose columns include `ROWID` types. This code example uses the preceding example declarations.

Declaration:

    #sql iterator NamedRowidIter (String ename, ROWID rowid);

Executable code:

    ...
    NamedRowidIter iter;
    ROWID rowid;
    #sql iter = { SELECT ename, rowid FROM emp };
    while (iter.next())
    {
       if (iter.ename().equals("TURNER"))
       {
           rowid = iter.rowid();
           #sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };
       }
    }
    iter.close();
    ...

The preceding example increases the salary of the employee named Turner by $500 according to the ROWID. Note that this is the recommended way to encode `WHERE CURRENT OF` semantics.

#### ROWID Stored Function Results

Presume the following function exists in the database.

    CREATE OR REPLACE function get_rowid (name varchar2) return rowid is
       rid rowid;
    BEGIN
       SELECT rowid INTO rid FROM emp WHERE ename = name;
       RETURN rid;
    END get_rowid;

Given the preceding stored function, the following example indicates how a `ROWID` object is used as the assignment type for the function return result.

    ROWID rowid;
    #sql rowid = { values(get_rowid('TURNER')) };
    #sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };

This example increases the salary of the employee named Turner by $500 according to the ROWID.

#### ROWID SELECT INTO Targets

Host variables of type `ROWID` can appear in the INTO-list of a `SELECT INTO` statement.

    ROWID rowid;
    #sql { SELECT rowid INTO :rowid FROM emp WHERE ename='TURNER' };
    #sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };

This example increases the salary of the employee named Turner by $500 according to the ROWID.

#### ROWID Host Variables and Positional Iterator FETCH INTO Targets

Host variables of type `ROWID` can appear in the INTO-list of a `FETCH INTO` statement if the corresponding column attribute in the iterator is of the identical type.

Declaration:

    #sql iterator PositionedRowidIter (String, ROWID);

Executable code:

    ...
    PositionedRowidIter iter;
    ROWID rowid = null;
    String ename = null;
    #sql iter = { SELECT ename, rowid FROM emp };
    while (true)
    {
       #sql { FETCH :iter INTO :ename, :rowid };
       if (iter.endFetch()) break;
       if (ename.equals("TURNER"))
       {
           #sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };
       }
    }
    iter.close();
    ...

This example is similar to the previous named iterator example, but uses a positional iterator with its customary `FETCH INTO` syntax.

### Support for Oracle REF CURSOR Types

Oracle PL/SQL and Oracle SQLJ support the use of cursor variables that represent database cursors.

#### Overview of REF CURSOR Types

Cursor variables are functionally equivalent to JDBC result sets, essentially encapsulating the results of a query. A cursor variable is often referred to as a `REF CURSOR`, but `REF CURSOR` itself is a type specifier, not a type name. Instead, named `REF CURSOR` types must be specified. The following example shows a `REF CURSOR` type specification:

    TYPE EmpCurType IS REF CURSOR;

Stored procedures and stored functions can return parameters of Oracle `REF CURSOR` types. You must use PL/SQL to return a `REF CURSOR` parameter; you cannot accomplish this using SQL alone. A PL/SQL stored procedure or function can declare a variable of some named `REF CURSOR` type, execute a `SELECT` statement, and return the results in the `REF CURSOR` variable.

For more information about cursor variables, see the PL/SQL User's Guide and Reference.

#### REF CURSOR Types in SQLJ

In Oracle SQLJ, a `REF CURSOR` type can be mapped to iterator columns or host variables of any iterator class type or of type `java.sql.ResultSet`, but host variables can be `OUT` only. Support for `REF CURSOR` types can be summarized as follows:

- as result expressions for stored function returns

- as output host expressions for stored procedure or function output parameters

- as output host expressions in INTO-lists

- as iterator columns

You can use the Oracle SQL `CURSOR` operator for a nested `SELECT` within an outer `SELECT` statement. This is how you can write a `REF CURSOR` to an iterator column or `ResultSet` column in an iterator, or write a `REF CURSOR` to an iterator host variable or `ResultSet` host variable in an INTO-list.

"Using Iterators and Result Sets as Host Variables" has examples showing the use of implicit `REF CURSOR` variables, including an example of the `CURSOR` operator.

Notes: Use the typecode `OracleTypes.CURSOR` for `REF CURSOR` types. There is no `oracle.sql` class for `REF CURSOR` types. Use either `java.sql.ResultSet` or an iterator class. (Close the result set or iterator to release database resources when you are done processing it.)

#### REF CURSOR Example

The following sample method shows a `REF CURSOR` type being retrieved from an anonymous block. This is part of a full sample application that is in "REF CURSOR--RefCursDemo.sqlj".

    private static EmpIter refCursInAnonBlock(String name, int no)
      throws java.sql.SQLException {
      EmpIter emps = null;

      System.out.println("Using anonymous block for ref cursor..");
      #sql { begin
               INSERT INTO emp (ename, empno) VALUES (:name, :no);
               OPEN :out emps FOR SELECT ename, empno FROM emp ORDER BY empno;
             end
           };
      return emps;
    }

### Support for Other Oracle8i Datatypes

All `oracle.sql` classes can be used for iterator columns or for input, output, or input-output host variables in the same way that any standard Java type can be used. This includes the classes mentioned in the preceding sections and others, such as the `oracle.sql.NUMBER`, `oracle.sql.CHAR`, and `oracle.sql.RAW` classes.

Because the `oracle.sql.*` classes do not require conversion to Java type format, they offer greater efficiency and precision than equivalent Java types. You would need to convert the data to standard Java types, however, to use it with standard Java programs or to display it to end users.

### Extended Support for BigDecimal

SQLJ supports `java.math.BigDecimal` in the following situations:

- as host variables in SQLJ executable statements

- as return values from stored function calls

- as iterator column types

Standard SQLJ has the limitation that a value can be retrieved as `BigDecimal` only if that is the JDBC default mapping, which is the case only for numeric and decimal data. (See Table 5-1 for more information about JDBC default mappings.)

In Oracle SQLJ, however, you can map to non-default types as long as the datatype is convertible from numeric and you use Oracle8i, an Oracle JDBC driver, the Oracle customizer, and the Oracle SQLJ runtime. The datatypes `CHAR`, `VARCHAR2`, `LONG`, and `NUMBER` are convertible. For example, you can retrieve data from a `CHAR` column into a `BigDecimal` variable. To avoid errors, however, you must be careful that the character data consists only of numbers.

Note: To use `BigDecimal`, import `java.math` or specify `BigDecimal` by its fully qualified name.

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