Oracle9i SQLJ Developer's Guide and Reference
Release 1 (9.0.1)

Part Number A90212-01
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Go to previous page Go to next page

5
Type Support

This chapter documents datatypes supported by Oracle SQLJ, listing supported SQL types and the Java types that correspond to them, including information about backwards compatibility to Oracle8 and Oracle7. This is followed by details about support for streams and Oracle type extensions. SQLJ "support" of Java types refers to types that can be used in host expressions.

For information about Oracle SQLJ support for user-defined types--SQL objects, object references, and collections--see Chapter 6, "Objects and Collections".

This chapter covers the following topics:

Supported Types for Host Expressions

This section summarizes the types supported by Oracle SQLJ, including information about new support for JDBC 2.0 types, and backwards compatibility for the 8.0.x and 7.3.x Oracle JDBC drivers.

For a complete list of legal Java mappings for each Oracle SQL type, see the reference information in the Oracle9i JDBC Developer's Guide and Reference.


Note:

SQLJ (and SQL) perform implicit conversions between SQL and Java types. Although this is generally useful and helpful, it can produce unexpected results. Do not rely on translation-time type-checking alone to ensure the correctness of your code. 


Summary of Supported Types

Table 5-1 lists the Java types that you can use in host expressions when employing the Oracle JDBC drivers. This table also documents the correlation between Java types, SQL types whose typecodes are defined in the class oracle.jdbc.OracleTypes, and datatypes in Oracle9i.


Note:

The OracleTypes class simply defines a typecode, which is an integer constant, for each Oracle datatype. For standard JDBC types, the OracleTypes value is identical to the standard java.sql.Types value. 


SQL data output to a Java variable is converted to the corresponding Java type. A Java variable input to SQL is converted to the corresponding Oracle datatype.

Where objects, object references, and arrays are referred to as "JPub-generated", this refers to the Oracle JPublisher utility that can be used in defining Java classes to correspond to Oracle9i objects, object references, and arrays. The JPublisher utility is discussed in "JPublisher and the Creation of Custom Java Classes" and documented in further detail in the Oracle9i JPublisher User's Guide.

Table 5-1 Type Mappings for Supported Host Expression Types  
Java Type  OracleTypes Definition  Oracle Datatype 

STANDARD JDBC 1.x TYPES 

 

 

boolean 

BIT 

NUMBER 

byte 

TINYINT 

NUMBER 

short 

SMALLINT 

NUMBER 

int 

INTEGER 

NUMBER 

long 

BIGINT 

NUMBER 

float 

REAL 

NUMBER 

double 

FLOAT, DOUBLE 

NUMBER 

java.lang.String 

CHAR
VARCHAR
LONGVARCHAR 

CHAR
VARCHAR2
LONG 

byte[] 

BINARY
VARBINARY
LONGVARBINARY 

RAW
RAW
LONGRAW 

java.sql.Date 

DATE 

DATE 

java.sql.Time 

TIME 

DATE 

java.sql.Timestamp 

TIMESTAMP 

DATE 

java.math.BigDecimal 

NUMERIC
DECIMAL 

NUMBER
NUMBER 

STANDARD JDBC 2.0 TYPES 

 

 

java.sql.Blob 

BLOB 

BLOB 

java.sql.Clob 

CLOB 

CLOB 

java.sql.Struct 

STRUCT 

STRUCT 

java.sql.Ref 

REF 

REF 

java.sql.Array 

ARRAY 

ARRAY 

custom object classes implementing java.sql.SQLData 

STRUCT 

STRUCT 

JAVA WRAPPER CLASSES 

 

 

java.lang.Boolean 

BIT 

NUMBER 

java.lang.Byte 

TINYINT 

NUMBER 

java.lang.Short 

SMALLINT 

NUMBER 

java.lang.Integer 

INTEGER 

NUMBER 

java.lang.Long 

BIGINT 

NUMBER 

java.lang.Float 

REAL 

NUMBER 

java.lang.Double 

FLOAT, DOUBLE  

NUMBER 

SQLJ STREAM CLASSES 

 

 

sqlj.runtime.BinaryStream 

LONGVARBINARY 

LONG RAW 

sqlj.runtime.AsciiStream 

LONGVARCHAR 

LONG 

sqlj.runtime.UnicodeStream 

LONGVARCHAR 

LONG 

ORACLE EXTENSIONS 

 

 

oracle.sql.NUMBER 

NUMBER 

NUMBER 

oracle.sql.CHAR 

CHAR 

CHAR 

oracle.sql.RAW 

RAW 

RAW 

oracle.sql.DATE 

DATE 

DATE 

oracle.sql.ROWID 

ROWID 

ROWID 

oracle.sql.BLOB 

BLOB 

BLOB 

oracle.sql.CLOB 

CLOB 

CLOB 

oracle.sql.BFILE 

BFILE 

BFILE 

oracle.sql.STRUCT 

STRUCT 

STRUCT 

oracle.sql.REF 

REF 

REF 

oracle.sql.ARRAY 

ARRAY 

ARRAY 

custom object classes implementing oracle.sql.ORAData 

STRUCT 

STRUCT 

custom reference classes implementing oracle.sql.ORAData 

REF 

REF 

custom collection classes implementing oracle.sql.ORAData 

ARRAY 

ARRAY 

any other custom Java classes implementing oracle.sql.ORAData (to wrap any oracle.sql type) 

any 

any 

GLOBALIZATION SUPPORT 

 

 

oracle.sql.NCHAR 

CHAR 

CHAR 

oracle.sql.NString 

CHAR
VARCHAR
LONGVARCHAR 

CHAR
VARCHAR2
LONG 

oracle.sql.NCLOB 

CLOB 

CLOB 

oracle.sqlj.runtime.NcharAsciiStream 

LONGVARCHAR 

LONG 

oracle.sqlj.runtime.NcharUnicodeStream 

LONGVARCHAR 

LONG 

QUERY RESULT OBJECTS 

 

 

java.sql.ResultSet 

CURSOR 

CURSOR 

SQLJ iterator objects 

CURSOR 

CURSOR 

The following points relate to type support for standard SQLJ features:

The following points relate to Oracle extensions, which are covered in "Support for JDBC 2.0 LOB Types and Oracle Type Extensions" and in Chapter 6, "Objects and Collections":

Supported Types and Requirements for JDBC 2.0

As indicated in Table 5-1 above, Oracle JDBC and SQLJ support JDBC 2.0 types in the standard java.sql package.

This section lists JDBC 2.0 supported types and related Oracle extensions.


Important:

In a Sun Microsystems JDK environment, JDBC 2.0 types require a JDK 1.2.x or higher version. While Oracle JDBC under JDK 1.1.x supports oracle.jdbc2 extensions to mimic JDBC 2.0 type functionality, Oracle SQLJ has never supported the oracle.jdbc2 package.

To use JDBC 2.0 types or corresponding Oracle extended types in Oracle SQLJ, use the SQLJ runtime12 or runtime12ee library, which support JDK 1.2.x. 


Table 5-2 lists the JDBC 2.0 types supported by Oracle SQLJ. You can use them wherever you can use the corresponding Oracle extensions, summarized in the table.

The Oracle extensions have been available in prior releases and are still available as well. These oracle.sql.* classes provide functionality to wrap raw SQL data, and are described in the Oracle9i JDBC Developer's Guide and Reference.

Table 5-2 Correlation between Oracle Extensions and JDBC 2.0 Types  
JDBC 2.0 Type  Oracle Extension 

java.sql.Blob 

oracle.sql.BLOB 

java.sql.Clob 

oracle.sql.CLOB 

java.sql.Struct 

oracle.sql.STRUCT 

java.sql.Ref 

oracle.sql.REF 

java.sql.Array 

oracle.sql.ARRAY 

java.sql.SQLData 

n/a 

n/a 

oracle.sql.ORAData
(_SQL_TYPECODE = OracleTypes.STRUCT

ORAData functionality is an Oracle-specific alternative to standard SQLData functionality for Java support of user-defined types. For information, see "Custom Java Classes".

For information about support for other types in Table 5-2, see "Support for BLOB, CLOB, and BFILE" and "Support for Weakly Typed Objects, References, and Collections".

Unsupported Types

The types summarized in Table 5-3, while supported by Oracle JDBC, are not currently supported by Oracle SQLJ or JPublisher.

Table 5-3 Unsupported Host Expression Types  
Java Type  OracleTypes Definition  Oracle Datatype 

Java classes for SQLJ object types 

JAVA_STRUCT 

SQLJ object types 

OPAQUE types 

OPAQUE 

OPAQUE 

scalar index-by-table 

n/a 

n/a 

DATE/TIME types:
TIMESTAMP
TIMESTAMPTZ
TIMESTAMPLTZ 


TIMESTAMPNS
TIMESTAMPTZ
TIMESTAMPLTZ 


TIMESTAMP
TIMESTAMP-WITH-TIMEZONE
TIMESTAMPE-WITH-LOCAL-TIMEZONE 

In addition, the following JDBC 2.0 types are currently not supported in Oracle JDBC or SQLJ:

Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

Oracle SQLJ does not support calling arguments or return values of the PL/SQL types TABLE (now known as indexed-by tables), RECORD, or BOOLEAN. (RECORD and BOOLEAN types are not supported by Oracle JDBC.)

As a workaround, you can create wrapper procedures that process the data using supported types. For example, to wrap a stored procedure that uses PL/SQL booleans, you can create a stored procedure that takes a character or number from JDBC and passes it to the original procedure as BOOLEAN, or, for an output parameter, accepts a BOOLEAN argument from the original procedure and passes it as a CHAR or NUMBER to JDBC. Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

Here is an example of a PL/SQL wrapper procedure MY_PROC for a stored procedure PROC that takes a BOOLEAN as input:

PROCEDURE MY_PROC (n NUMBER) IS
BEGIN
   IF n=0
      THEN proc(false);
      ELSE proc(true);
   END IF;
END;

PROCEDURE PROC (b BOOLEAN) IS
BEGIN
...
END;

Backwards Compatibility for Previous Oracle JDBC Releases

This section summarizes backwards compatibility issues when using Oracle SQLJ with previous Oracle JDBC releases.

Backwards Compatibility for Oracle8i

The following Oracle9i features are not supported, or are supported differently, in the Oracle8i JDBC drivers:

Backwards Compatibility for Oracle 8.0.x and 7.3.x

Some of the Oracle type extensions supported by the Oracle9i JDBC drivers are either not supported or supported differently by the Oracle 8.0.x and 7.3.x JDBC drivers. Following are the key points:

Table 5-4 summarizes these differences.

Table 5-4 Type Support Differences for Oracle 8.0.x and 7.3.x JDBC Drivers  
Java Type  Oracle Types Definition  Oracle Datatype 

ORACLE EXTENSIONS 

 

 

oracle.sql.NUMBER 

not supported 

n/a 

oracle.sql.CHAR 

not supported 

n/a 

oracle.sql.RAW 

not supported 

n/a 

oracle.sql.DATE 

not supported 

n/a 

oracle.jdbc.OracleRowid 

ROWID 

ROWID 

oracle.jdbc.OracleBlob 

BLOB in 8.0.x

not supported in 7.3.x 

BLOB in 8.0.x

n/a in 7.3.x 

oracle.jdbc.OracleClob 

CLOB in 8.0.x

not supported in 7.3.x 

CLOB in 8.0.x

n/a in 7.3.x 

oracle.jdbc.OracleBfile 

BFILE in 8.0.x

not supported in 7.3.x 

BFILE in 8.0.x

n/a in 7.3.x 

oracle.sql.STRUCT 

not supported 

n/a 

oracle.sql.REF 

not supported 

n/a 

oracle.sql.ARRAY 

not supported 

n/a 

JPub-generated objects 

not supported 

n/a 

JPub-generated object references 

not supported 

n/a 

JPub-generated arrays 

not supported 

n/a 

client-customized types (customization of any oracle.sql types, including objects, references, and collections) 

not supported 

n/a 

Support for Streams

Standard SQLJ provides three specialized classes, listed below, for convenient processing of long data in streams. These stream types can be used for iterator columns to retrieve data from the database, or for input host variables to send data to the database. As with Java streams in general, these classes allow the convenience of processing and transferring large data items in manageable chunks.

These classes are in the sqlj.runtime package.

This section discusses general use of these classes, Oracle SQLJ extended functionality, and stream class methods.

General Use of SQLJ Streams

With respect to Oracle9i, Table 5-1 lists the datatypes you would typically process using these stream classes. To summarize:

Of course, any use of streams is at your discretion. As Table 5-1 documents, LONG and VARCHAR2 data can also be manifested in Java strings, while RAW and LONGRAW data can also be manifested in Java byte arrays. Furthermore, if your database supports large object types such as BLOB (binary large object) and CLOB (character large object), you may find these to be preferable to using types such as LONG and LONG RAW (although streams may still be used in extracting data from large objects). Oracle SQLJ and JDBC support large object types--see "Support for BLOB, CLOB, and BFILE".

You can use the SQLJ stream types for host variables to either send or retrieve data. All three SQLJ stream classes are subclasses of the standard Java input stream class, java.io.InputStream, and act as wrappers to provide the functionality required by SQLJ. This functionality is to communicate to SQLJ the type and length of data in the underlying stream so that it can be processed and formatted properly.


Note:

In using any method that takes an InputStream object as input, you can use an object of any of the SQLJ stream classes instead. 


Using SQLJ Streams to Send Data

Standard SQLJ allows you to use streams as host variables to update the database.

A key point in sending a SQLJ stream to the database is that you must somehow determine the length of the data and specify that length to the constructor of the SQLJ stream. This will be further discussed below.

You can use a SQLJ stream to send data to the database as follows:

  1. Determine the length of your data.

  2. Create a standard Java input stream--an instance of java.io.InputStream or some subclass--as you normally would.

  3. Create an instance of the appropriate SQLJ stream class (depending on the type of data), passing the input stream and length (as an int) to the constructor.

  4. Use the SQLJ stream instance as a host variable in a suitable SQL operation in a SQLJ executable statement.

  5. Close the stream (this is not required, but is recommended).

This section now goes into more detail regarding two typical examples of sending a SQLJ stream to the database:

Updating LONG or LONG RAW from a File

In updating a database column (presumably a LONG or LONG RAW column) from a file, a step is needed to determine the length. You can do this by creating a java.io.File object before you create your input stream.

Here are the steps in updating the database from a file:

  1. Create a java.io.File object from your file. You can specify the file path name to the File class constructor.

  2. Use the length() method of the File object to determine the length of the data. This method returns a long value, which you must cast to an int for input to the SQLJ stream class constructor.


    Note:

    Before performing this cast, test the long value to make sure it is not too big to fit into an int variable. The static constant MAX_VALUE in the class java.lang.Integer indicates the largest possible Java int value. 


  3. Create a java.io.FileInputStream object from your File object. You can pass the File object to the FileInputStream constructor.

  4. Create an appropriate SQLJ stream object. This would be a BinaryStream object for a binary file, an AsciiStream object for an ASCII file, or a UnicodeStream object for a Unicode file. Pass the FileInputStream object and data length (as an int) to the SQLJ stream class constructor.

    The SQLJ stream constructors all have the same signature, as follows:

    BinaryStream (InputStream in, int length)
    AsciiStream (InputStream in, int length)
    UnicodeStream (InputStream in, int length)
    
    

    You can input an instance of java.io.InputStream or of any subclass, such as FileInputStream, to these constructors.

  5. Use the SQLJ stream object as a host variable in an appropriate SQL operation in a SQLJ executable statement.

The following is an example of writing LONG data to the database from a file. Presume you have an HTML file in /private/mydir/myfile.html and you want to insert the file contents into a LONG column called asciidata in a database table named filetable.

Imports:

import java.io.*;
import sqlj.runtime.*;

Executable code:

File myfile = new File ("/private/mydir/myfile.html");
int length = (int)myfile.length();     // Must cast long output to int.
FileInputStream fileinstream = new FileInputStream(myfile);
AsciiStream asciistream = new AsciiStream(fileinstream, length);
#sql { INSERT INTO filetable (asciidata) VALUES (:asciistream) };
asciistream.close();
...

Updating LONG RAW from a Byte Array

You must determine the length of the data before updating the database from a byte array. (Presumably you would be updating a LONG RAW column.) This is more trivial for arrays than for files, though, because all Java arrays have functionality to return the length.

Here are the steps in updating the database from a byte array:

  1. Use the length functionality of the array to determine the length of the data. This returns an int, which is what you will need for the constructor of any of the SQLJ stream classes.

  2. Create a java.io.ByteArrayInputStream object from your array. You can pass the byte array to the ByteArrayInputStream constructor.

  3. Create a BinaryStream object. Pass the ByteArrayInputStream object and data length (as an int) to the BinaryStream class constructor.

    The constructor signature is as follows:

    BinaryStream (InputStream in, int length)
    
    

    You can use an instance of java.io.InputStream or of any subclass, such as the ByteArrayInputStream class.

  4. Use the SQLJ stream object as a host variable in an appropriate SQL operation in a SQLJ executable statement.

The following is an example of writing LONG RAW data to the database from a byte array. Presume you have a byte array bytearray[] and you want to insert its contents into a LONG RAW column called BINDATA in a database table named BINTABLE.

Imports:

import java.io.*;
import sqlj.runtime.*;

Executable code:

byte[] bytearray = new byte[100];

(Populate bytearray somehow.)
...
int length = bytearray.length;
ByteArrayInputStream arraystream = new ByteArrayInputStream(bytearray);
BinaryStream binstream = new BinaryStream(arraystream, length);
#sql { INSERT INTO bintable (bindata) VALUES (:binstream) };
binstream.close();
...


Note:

It is not necessary to use a stream as in this example--you can also update the database directly from a byte array. 


Retrieving Data into Streams--Precautions

You can also use the SQLJ stream classes to retrieve data, but the logistics of using streams make certain precautions necessary with some database products.

When reading long data and writing it to a stream using Oracle9i and an Oracle JDBC driver, you must be careful in how you access and process the stream data.

As the Oracle JDBC drivers access data from an iterator row, they must flush any stream item from the communications pipe before accessing the next data item. Even though the stream data is written to a local stream as the iterator row is processed, this stream data will be lost if you do not read it from the local stream before the JDBC driver accesses the next data item. This is because of the way streams must be processed, due to their potentially large size and unknown length.

Therefore, as soon as your Oracle JDBC driver has accessed a stream item and written it to a local stream variable, you must read and process the local stream before anything else is accessed from the iterator.

This is especially problematic in using positional iterators, with their requisite FETCH INTO syntax. With each fetch, all columns are read before any are processed. Therefore, there can be only one stream item, and it must be the last item accessed.

To summarize the precautions you must take:

Using SQLJ Streams to Retrieve Data

To retrieve data as a stream, standard SQLJ allows you to select data into a named or positional iterator that has a column of the appropriate SQLJ stream type.

This section covers the basic steps in retrieving data into a SQLJ stream using a positional iterator or a named iterator, taking into account the precautions documented in "Retrieving Data into Streams--Precautions".

These are general steps. For more information, see "Processing SQLJ Streams" and "Examples of Retrieving and Processing Stream Data".

Using a SQLJ Stream Column in a Positional Iterator

Use the following steps to retrieve data into a SQLJ stream using a positional iterator:

  1. Declare a positional iterator class with the last column being of the appropriate SQLJ stream type.

  2. Declare a local variable of your iterator type.

  3. Declare a local variable of the appropriate SQLJ stream type. This will be used as a host variable to receive data from each row of the SQLJ stream column of the iterator.

  4. Execute a query to populate the iterator you declared in step 2.

  5. Process the iterator as usual. (See "Using Positional Iterators".) Because the host variables in the INTO-list of the FETCH INTO statement must be in the same order as the columns of the positional iterator, the local input stream variable is the last host variable in the list.

  6. In the iterator processing loop, after each iterator row is accessed, immediately read and process the local input stream, storing or outputting the stream data as desired.

  7. Close the local input stream each time through the iterator processing loop (this is not required, but is recommended).

  8. Close the iterator.

Using SQLJ Stream Columns in a Named Iterator

Use the following steps to retrieve data into one or more SQLJ streams using a named iterator:

  1. Declare a named iterator class with one or more columns of appropriate SQLJ stream type.

  2. Declare a local variable of your iterator type.

  3. Declare a local variable of some input stream type for each SQLJ stream column in the iterator. These will be used to receive data from the stream-column accessor methods. These local stream variables do not have to be SQLJ stream types; they can be standard java.io.InputStream if desired. (They do not have to be SQLJ stream types, because the data was already correctly formatted as a result of the iterator columns being of appropriate SQLJ stream types.)

  4. Execute a query to populate the iterator you declared in step 2.

  5. Process the iterator as usual. (See "Using Named Iterators".) In processing each row of the iterator, as each stream-column accessor method returns the stream data, write it to the corresponding local input stream variable you declared in step 3.

    To ensure that stream data will not be lost, call the column accessor methods in the same order in which columns were selected in the query in step 4.

  6. In the iterator processing loop, immediately after calling the accessor method for any stream column and writing the data to a local input stream variable, read and process the local input stream, storing or outputting the stream data as desired.

  7. Close the local input stream each time through the iterator processing loop (this is not required, but is recommended).

  8. Close the iterator.


    Note:

    When you populate a SQLJ stream object with data, the length attribute of the stream will not be meaningful. This attribute is meaningful only when you set it explicitly, either using the setLength() method that each SQLJ stream class provides, or specifying the length to the constructor (as discussed in "Using SQLJ Streams to Send Data").  


Processing SQLJ Streams

In processing a SQLJ stream column in a named or positional iterator, the local stream variable used to receive the stream data can be either a SQLJ stream type or the standard java.io.InputStream type. In either case, standard input stream methods are supported.

If the local stream variable is a SQLJ stream type--BinaryStream, AsciiStream, or UnicodeStream--you have the option of either reading data directly from the SQLJ stream object, or retrieving the underlying java.io.InputStream object and reading data from that. This is just a matter of preference--the former approach is simpler; the latter approach involves more direct and efficient data access.

The following important methods of the InputStream class--the skip() method, close() method, and three forms of the read() method--are supported by the SQLJ stream classes as well.

In addition, SQLJ stream classes support the following important method:

Examples of Retrieving and Processing Stream Data

This section provides examples of various scenarios of retrieving stream data, as follows:

Example: Selecting LONG Data into AsciiStream Column of Named Iterator

This example selects data from a LONG database column, populating a SQLJ AsciiStream column in a named iterator.

Assume there is a table named FILETABLE with a VARCHAR2 column called FILENAME that contains file names, and a LONG column called FILECONTENTS that contains file contents in ASCII.

Imports and declarations:

import sqlj.runtime.*;
import java.io.*;
...
#sql iterator MyNamedIter (String filename, AsciiStream filecontents);

Executable code:

MyNamedIter namediter = null;
String fname;
AsciiStream ascstream;
#sql namediter = { SELECT filename, filecontents FROM filetable };
while (namediter.next()) {
   fname = namediter.filename();
   ascstream = namediter.filecontents();
   System.out.println("Contents for file " + fname + ":");
   printStream(ascstream);
   ascstream.close();
}
namediter.close();
...
public void printStream(InputStream in) throws IOException
{
   int asciichar;
   while ((asciichar = in.read()) != -1) {
      System.out.print((char)asciichar);
   }
}

Remember that you can pass a SQLJ stream to any method that takes a standard java.io.InputStream as an input parameter.

Example: Selecting LONG RAW Data into BinaryStream Column of Positional Iterator

This example selects data from a LONG RAW column, populating a SQLJ BinaryStream column in a positional iterator.

As explained in "Retrieving Data into Streams--Precautions", there can be only one stream column in a positional iterator, and it must be the last column.

Assume there is a table named BINTABLE with a NUMBER column called IDENTIFIER and a LONG RAW column called BINDATA that contains binary data associated with the identifier.

Imports and declarations:

import sqlj.runtime.*;
...
#sql iterator MyPosIter (int, BinaryStream);

Executable code:

MyPosIter positer = null;
int id=0;
BinaryStream binstream=null;
#sql positer = { SELECT identifier, bindata FROM bintable };
while (true) {
   #sql { FETCH :positer INTO :id, :binstream };
   if (positer.endFetch()) break;
   
   (...process data as desired...)
   
   binstream.close();
}
positer.close();
...

SQLJ Stream Objects as Output Parameters and Function Return Values

As described in the preceding sections, standard SQLJ supports use of the BinaryStream, AsciiStream, and UnicodeStream classes in the package sqlj.runtime for retrieval of stream data into iterator columns.

In addition, the Oracle SQLJ implementation allows the following uses of SQLJ stream types if you use Oracle9i, an Oracle JDBC driver, the Oracle customizer, and the Oracle SQLJ runtime:

Streams as Stored Procedure Output Parameters

You can use the types AsciiStream, BinaryStream, and UnicodeStream as the assignment type for a stored procedure or stored function OUT or INOUT parameter.

Assume the following table definition:

CREATE TABLE streamexample (name VARCHAR2 (256), data LONG);
INSERT INTO streamexample (data, name)
   VALUES
   ('0000000000111111111112222222222333333333344444444445555555555',
   'StreamExample');

Also presume the following stored procedure definition, which uses the STREAMEXAMPLE table:

CREATE OR REPLACE PROCEDURE out_longdata 
                            (dataname VARCHAR2, longdata OUT LONG) IS
BEGIN
   SELECT data INTO longdata FROM streamexample WHERE name = dataname;
END out_longdata;

The following code calls the out_longdata stored procedure to read long data.

Imports:

import sqlj.runtime.*;

Executable code:

AsciiStream data;
#sql { CALL out_longdata('StreamExample', :OUT data) };
int c;
while ((c = data.read ()) != -1)
   System.out.print((char)c);
System.out.flush();
data.close();
...


Note:

Closing the stream is recommended, but not required. 


Streams as Stored Function Results

You can use the types AsciiStream, BinaryStream and UnicodeStream as the assignment type for a stored function return result.

Assume the same STREAMEXAMPLE table definition as in the preceding stored procedure example.

Also assume the following stored function definition, which uses the STREAMEXAMPLE table:

CREATE OR REPLACE FUNCTION get_longdata (dataname VARCHAR2) RETURN long
   IS longdata LONG;
BEGIN
   SELECT data INTO longdata FROM streamexample WHERE name = dataname;
   RETURN longdata;
END get_longdata;

The following sample code uses a call to the get_longdata stored function to read the long data.

Imports:

import sqlj.runtime.*;

Executable code:

AsciiStream data;
#sql data = { VALUES(get_longdata('StreamExample')) };
int c;
while ((c = data.read ()) != -1)
   System.out.print((char)c);
System.out.flush();
data.close();
...


Note:

Closing the stream is recommended, but not required. 


Stream Class Methods

The SQLJ stream classes in the sqlj.runtime package--BinaryStream, AsciiStream, and UnicodeStream--are all subclasses of the sqlj.runtime.StreamWrapper class.

The StreamWrapper class provides the following methods inherited by the SQLJ stream classes:

Support for JDBC 2.0 LOB Types and Oracle Type Extensions

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

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:

JDBC 2.0 functionality for user-defined SQL 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:

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.

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.

Oracle-specific types are defined in the oracle.sql package, discussed in "Package oracle.sql" below.

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

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 Oracle9i 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:

See the Oracle9i 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:

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 an Oracle9i 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 Oracle9i in the following ways:

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("CHUCK 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 Chuck 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 Oracle9i.

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('AMY FEINER')) };
#sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };

This example increases the salary of the employee named Amy Feiner 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='CHUCK TURNER' };
#sql { UPDATE emp SET sal = sal + 500 WHERE rowid = :rowid };

This example increases the salary of the employee named Chuck 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("CHUCK 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 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:

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 object to an iterator column or ResultSet column in an iterator, or write a REF CURSOR object 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 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 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 Oracle9i 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:

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 Oracle9i, an Oracle JDBC driver, the Oracle customizer (or Oracle-specific code generation), 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:

The BigDecimal class is in the standard java.math package. 



Go to previous page Go to next page
Oracle
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index