The Oracle SQLJ and JDBC implementations do not support calling arguments or return values of the PL/SQL BOOLEAN type or RECORD types.

Support for TABLE Types

Oracle JDBC drivers support scalar PL/SQL indexed-by tables.

The Oracle SQLJ implementation simplifies the process of writing and retrieving data in scalar indexed-by tables. The following array types are supported:

• Numeric types: int[], long[], float[], double[], short[], java.math.BigDecimal[], oracle.sql.NUMBER[]

• Character types: java.lang.String[], oracle.sql.CHAR[]

The following is an example of writing indexed-by table data to the database:

int[] vals = {1,2,3};
#sql { call procin(:vals) };

The following is an example of retrieving indexed-by table data from the database:

oracle.sql.CHAR[] outvals;
#sql { call procout(:OUT outvals/*[111](22)*/) };

You must specify the maximum length of the output array being retrieved, using the [xxx] syntax inside the /*...*/ syntax, as shown. Also, for character-like binds, you can optionally include the (xx) syntax, as shown, to specify the maximum length of an array element in bytes.

Workarounds for Unsupported Types

As a workaround for an unsupported type, you can create wrapper procedures that process the data using supported types. For example, to wrap a stored procedure that uses PL/SQL boolean values, 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 TABLE types, you can break the data into components or perhaps use Oracle collection types.

The following 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;

Backward Compatibility for Oracle8i Database

The following Oracle Database 11g features, which are also available in Oracle9i Database, are not supported or supported differently in the Oracle8i JDBC drivers:

• The oracle.sql.ORAData and ORADataFactory interfaces for Java mapping of user-defined SQL types

  Use the oracle.sql.CustomDatum and CustomDatumFactory interfaces instead.

• Oracle extensions for character types for globalization support: NCHAR, NCLOB, NString, and NcharCharacterStream (or NcharAsciiStream and NcharUnicodeStream in earlier releases)

Standard SQLJ enables 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.

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

1. Determine the length of the data.

2. Create an appropriate standard Java data object for input. For BinaryStream, this would be an input stream, an instance of java.io.InputStream or some subclass. For CharacterStream, this would be a reader object, an instance of java.io.Reader or some subclass.

3. Create an instance of the appropriate SQLJ stream class depending on the type of data, passing the data object and length 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.

Updating LONG or LONG RAW from a File

This section illustrates how to create a CharacterStream object or a BinaryStream object from a File object and use it to update the database. The code example at the end uses a CharacterStream for a LONG column.

In updating a database 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.

Following are the steps for 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 ensure that 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. For character data, create a java.io.FileReader object from the File object. You can pass the File object to the FileReader constructor.

   For binary data, create a java.io.FileInputStream object from the File object. You can pass the File object to the FileInputStream constructor.

4. Create an appropriate SQLJ stream object. This would be a CharacterStream object for a character file or a BinaryStream object for a binary file. Pass the FileReader or FileInputStream object, as applicable, and the data length as an int to the SQLJ stream class constructor.

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 want to insert the file contents into a LONG column, chardata, in the filetable database table.

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

...
File myfile = new File ("/private/mydir/myfile.html");
int length = (int)myfile.length();     // Must cast long output to int.
FileReader filereader = new FileReader(myfile);
CharacterStream charstream = new CharacterStream(filereader, length);
#sql { INSERT INTO filetable (chardata) VALUES (:charstream) };
charstream.close();
...

Updating LONG RAW from a Byte Array

This section illustrates how to create a BinaryStream object from a byte array and uses it to update the database.

You must determine the length of the data before updating the database from a byte array. This is more trivial for arrays than for files, though, because all Java arrays have functionality to return the length.

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

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, BINDATA, in the BINTABLE database table.

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

...
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();
...

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 Oracle Database 12c Release 2 (12.2) and an Oracle JDBC driver, you must be careful in how you access and process the stream data.

As 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 while 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 manner in which streams must be processed, which is 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.

The precautions you must take can be summarized as follows:

• When using a positional iterator, you can have only one stream column and it must be the last column. As soon as you have fetched each row of the iterator, writing the stream item to a local input stream variable in the process, you must read and process the local stream variable before advancing to the next row of the iterator.

• When using a named iterator, you can have multiple stream columns. However, as you process each iterator row, each time you access a stream field, writing the data to a local stream variable in the process, you must read and process the local stream immediately, before reading anything else from the iterator.

  Furthermore, in processing each row of a named iterator, you must call the column accessor methods in the same order in which the database columns were selected in the query that populated the iterator. As mentioned in the preceding discussion, this is because stream data remains in the communications pipe after the query. If you try to access columns out of order, then the stream data may be skipped over and lost in the course of accessing other columns.

To retrieve data as a stream, standard SQLJ enables 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 the preceding section.

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

   See Also:

   "Using Positional Iterators"

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

7. Close the local input stream each time through the iterator processing loop.

8. Close the iterator.

<<<[for 11g?] Use code examples. >>>

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:

<<<[for 11g?] Use code examples. >>>

Binary Stream Methods

The BinaryStream class is a subclass of the sqlj.runtime.StreamWrapper class. The StreamWrapper class provides the following key methods:

• InputStream getInputStream(): You can optionally use this method to get the underlying java.io.InputStream object. However, this is not required, because you can also process SQLJ stream objects directly.

• void setLength(int length): You can use this to set the length attribute of a SQLJ stream object. This is not necessary if you have already set length in constructing the stream object, unless you want to change it for some reason.

  The length attribute must be set to an appropriate value before you send a SQLJ stream to the database.

• int getLength(): This method returns the value of the length attribute of a SQLJ stream. This value is meaningful only if you explicitly set it using the stream object constructor or the setLength() method. When you retrieve data into a stream, the length attribute is not set automatically.

The sqlj.runtime.StreamWrapper class is a subclass of the java.io.FilterInputStream class, which is a subclass of the java.io.InputStream class. The following important methods of the InputStream class are supported by the SQLJ BinaryStream class as well:

• int read (): Reads the next byte of data from the input stream. The byte of data is returned as an int value in the range 0 to 255. If the end of the stream has already been reached, then the value -1 is returned. This method blocks program execution until one of the following:

  • Input data is available

  • The end of the stream is detected

  • An exception is thrown

• int read (byte b[]): Reads up to b.length bytes of data from the input stream, writing the data into the specified b[] byte array. It returns an int value indicating how many bytes were read, or -1 if the end of the stream has already been reached. This method blocks program execution until input is available.

• int read (byte b[], int off, int len): Reads up to len bytes of data from the input stream, starting at the byte specified by the offset, off, and writing the data into the specified b[] byte array. It returns an int value indicating how many bytes were read, or -1 if the end of the stream has already been reached. This method blocks until input is available.

• long skip (long n): Skips over and discards n bytes of data from the input stream. However, in some circumstances, this method will actually skip a smaller number of bytes. It returns a long value indicating the actual number of bytes skipped.

• void close(): Closes the stream and releases any associated resources.

Character Stream Methods

The CharacterStream class provides the following key methods:

• Reader getReader(): You can optionally use this method to get the underlying java.io.Reader object. However, this is not required, because you can also process SQLJ stream objects directly.

• void setLength(int length): You can use this method to set the length of the stream object.

• int getLength(): You can use this method to get the length of the stream object.

The sqlj.runtime.CharacterStream class is a subclass of the java.io.FilterReader class, which is a subclass of the java.io.Reader class. The following important methods of the Reader class are supported by the SQLJ CharacterStream class as well:

• int read (): Reads the next character of data from the reader. The data is returned as an int value in the range 0 to 65535. If the end of the data has already been reached, then the value -1 is returned. This method blocks program execution until one of the following:

  • Input data is available

  • The end of the data is detected

  • An exception is thrown

• int read (char cbuf[]): Reads characters into an array, writing the data into the specified cbuf[] char array. It returns an int value indicating how many characters were read, or -1 if the end of the data has already been reached. This method blocks program execution until input is available.

• int read (char cbuf[], int off, int len): Reads up to len characters of data from the input, starting at the character specified by the offset, off, and writing the data into the specified char[] char array. It returns an int value indicating how many characters were read, or -1 if the end of the data has already been reached. This method blocks until input is available.

• long skip (long n): Skips over and discards n characters of data from the input. However, in some circumstances, this method will actually skip a smaller number of characters. It returns a long value indicating the actual number of characters skipped.

• void close(): Closes the stream and releases any associated resources.

Streams as Stored Procedure Output Parameters

You can use the BinaryStream and CharacterStream types 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 sample code uses a call to the out_longdata stored procedure to read the long data:

import sqlj.runtime.*;

...
CharacterStream 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();
...

Streams as Stored Function Results

You can use the BinaryStream and CharacterStream types 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:

import sqlj.runtime.*;

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

The Oracle SQLJ and JDBC implementations support JDBC 2.0 LOB types and provide similar support for the Oracle specific BFILE type (read-only binary files stored outside the database). These data types are supported by the following classes:

• oracle.sql.BLOB

• oracle.sql.CLOB

• oracle.sql.BFILE

These classes can be used in Oracle-specific SQLJ applications in the following ways:

• As IN, OUT, or INOUT host variables in executable SQLJ statements and in INTO-lists

• As return values from stored function calls

• As column types in iterator declarations

You can manipulate LOBs by using methods defined in the BLOB and CLOB classes, which is recommended, or by using the procedures and functions defined in the DBMS_LOB PL/SQL package. 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, which is 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, character large objects (CLOBs) contain character data and, therefore, Java strings are used to hold chunks of data. Binary large objects (BLOBs) contain binary data and, therefore, Java byte arrays are used to hold chunks of data.

BFILE Class versus DBMS_LOB Functionality for BFILEs

Example 6-3 and Example 6-4 contrast use of the oracle.sql methods with use of the DBMS_LOB package for BFILEs:

BLOB and CLOB Classes versus DBMS_LOB Functionality for LOBs

Example 6-5 and Example 6-6 contrast use of the oracle.sql methods with use of the DBMS_LOB package for BLOBs, and Example 6-7 and Example 6-8 contrast use of the oracle.sql methods with use of the DBMS_LOB package for CLOBs.

LOB and BFILE Stored Function Results

Host variables of the BLOB, CLOB, and BFILE type 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 longer_clob function:

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 the BLOB, CLOB, and BFILE type 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, 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 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 BLOB, CLOB, and BFILE types 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.

Following are sample declarations:

#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 uses the BASIC_LOB_TABLE table and the readFromLongest() method defined in previous examples and a CLOB in a named iterator. Similar code could be written for BLOBs and BFILEs.

#sql iterator NamedLOBIter(CLOB c);

...
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 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 the BLOB, CLOB, and BFILE type 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 uses the BASIC_LOB_TABLE table and the writeToBlob() method defined in previous examples. Similar code could be written for CLOBs and BFILEs.

#sql iterator PositionedLOBIter(BLOB);

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

ROWIDs in Iterator Declarations

You can use 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 use 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.

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

...
NamedRowidIter iter; 
ROWID rowid;
#sql iter = { SELECT first_name, rowid FROM employees };
while (iter.next())
{
   if (iter.first_name().equals("Peter Hall"))
   {
       rowid = iter.rowid();
       #sql { UPDATE employees SET salary = salary + 500 WHERE rowid = :rowid };
   }
}
iter.close();
...

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

ROWID Stored Function Results

Consider the following function:

CREATE OR REPLACE FUNCTION get_rowid (name VARCHAR2) RETURN ROWID IS
   rid ROWID;
BEGIN
   SELECT rowid INTO rid FROM employees WHERE first_name = 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 employees SET salary = salary + 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 the ROWID type can appear in the INTO-list of a SELECT INTO statement.

ROWID rowid;
#sql { SELECT rowid INTO :rowid FROM employees WHERE first_name='PETER HALL' };
#sql { UPDATE employees SET salary = salary + 500 WHERE rowid = :rowid };

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

ROWID Host Variables and Positional Iterator FETCH INTO Targets

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

#sql iterator PositionedRowidIter (String, ROWID);

...
PositionedRowidIter iter;
ROWID rowid = null;
String ename = null;
#sql iter = { SELECT first_name, rowid FROM employees };
while (true)
{
   #sql { FETCH :iter INTO :ename, :rowid };
   if (iter.endFetch()) break;
   if (ename.equals("PETER HALL"))
   {
       #sql { UPDATE employees SET salary = salary + 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.

Positioned Update and Delete

Since Oracle Database 11g Release 1, SQLJ supports positioned update and delete operations. A positioned update or delete operation can be done using an iterator. The iterator used for positioned update or delete should implement the sqlj.runtime.ForUpdate interface. You can use a named iterator, positional iterator, or scrollable iterator.

The following code illustrates a positioned update:

...
#sql iterator iter implements sqlj.runtime.ForUpdate(String str)
...
#sql iter = {SELECT first_name FROM employees WHERE department_id=10};
...
while(iter.next())
{
 #sql {UPDATE employees SET salary=salary+5000 WHERE CURRENT OF :iter};
}
...

In the preceding code, an iterator iter is created and used to update the employees table.

You can similarly perform a positioned delete. For example:

...
#sql {DELETE FROM employees WHERE CURRENT OF :iter}
...

In the preceding example, iter is an iterator used to perform positioned delete.

The iterators that can be used with the WHERE CURRENT OF clause have the following limitations:

• The query used to populate the iterator should not operate on multiple tables.

• You cannot use a PL/SQL procedure returning a REF CURSOR with the iterator.

• You cannot use an iterator that has been populated from a result set. That is, an iterator populated using the following statement, where rs is a result set:

  #sql iter = {cast :rs}
  

for_update Option

If for_update option is set at translation time, then "FOR UPDATE" is appended to the SELECT statements, which in turn return results into a ForUpdate iterator as follows:

% sqlj –for_update abc.sqlj
 
/* abc.sqlj */
#sql iterator  SalByName (double sal, String ename)  implements sqlj.runtime.ForUpdate;
 
public class abc {
…..
  void func1()
  {
    SalByName salbn;
    #sql salbn = {select salary, first_name from employees };
  }
…..
}

Now, "FOR UPDATE" is appended to the SELECT statement returning the ForUpdate iterator salbn in the following way:

………
String theSqlTS = “SELECT rowid sjT_rowid,first_name, salary FROM employees WHERE first_name =  :1 FOR UPDATE";
………

Table 6-3 shows the plausible values for the for_update option and the corresponding SQL statement for the preceding example:

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

REF CURSOR Types in SQLJ

In the Oracle SQLJ implementation, a REF CURSOR type can be mapped to iterator columns or host variables of any iterator class type or of the java.sql.ResultSet type, 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 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.

REF CURSOR Example

The following sample method shows a REF CURSOR type being retrieved from an anonymous block:

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 employees (first_name, employee_id) VALUES (:name, :no);
           OPEN :out emps FOR SELECT first_name, employee_id FROM employees ORDER BY employee_id;
         end
       };
  return emps;
}