Type Support

Oracle Type Extensions

Oracle SQLJ supports the following Oracle-specific datatypes:

LOBs (BLOB, CLOB, BFILE)
ROWID
REF CURSOR types
other Oracle8i datatypes (such as NUMBER and RAW)

These datatypes are supported by classes in the oracle.sql package, discussed below, which requires you to use one of the Oracle JDBC drivers and to customize your profiles appropriately (the default Oracle customizer, oracle.sqlj.runtime.util.OraCustomizer, is recommended).

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

BigDecimal

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:
You must use one of the Oracle JDBC drivers to use Oracle type extensions.

Package oracle.sql

SQLJ users as well as JDBC users should be aware of the oracle.sql package, which includes classes to support all of 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.

To use these classes, you must use an Oracle JDBC driver and customize your SQLJ profiles appropriately. The default Oracle customizer, oracle.sqlj.runtime.util.OraCustomizer, is recommended. This is used automatically when you run the sqlj script unless you specify otherwise.

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 three LOB (large object) datatypes: BLOB (binary LOB), CLOB (single-character LOB), and BFILE (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 DBMS_LOB.

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.

The following examples contrast use of the oracle.sql methods with use of the DBMS_LOB package. 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.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.

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 Stored Function Results

Host variables of type BLOB, CLOB, and BFILE can be assigned to the result of a stored function call.

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 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.

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.

LOB 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);

LOB 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.

Declaration:

#sql iterator NamedLOBIter(CLOB c);

Executable code:

...
NamedLOBIter iter;  
#sql iter = { SELECT c FROM basic_lob_table };
iter.next();
CLOB c1 = iter.c();
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 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 identical type.

The following example employs table basic_lob_table and method writeToBlob() defined in previous examples.

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.

ROWID 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 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.

About 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 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 the results from stored functions
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:

The Oracle typecode for REF CURSOR types is OracleTypes.CURSOR.

There is no oracle.sql class for REF CURSOR. Use either an iterator class or java.sql.ResultSet.

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 oracle.sql.NUMBER, oracle.sql.CHAR, or oracle.sql.RAW.

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 perhaps 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 in "Supported Types for Host Expressions" 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 are using Oracle8i, an Oracle JDBC driver, and the Oracle customizer. 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.