When column definition is enabled, the Oracle SQLJ automatically registers OUT column types and sizes as follows:

Refer to Chapter 8 for a description of the SQL CHAR and VARCHAR2 types. As Table 12.1 indicates, SQL CHAR and VARCHAR2 columns can be mapped, among other possibilities, to and from Java String and oracle.sql.CHAR.

// Example #1
int num = 1331;
String name = "EMPLOYE";
#sql [ctx] { insert into  emp (empno, ename, hiredate, sal)
             values (:i, :(name+"_"+num), sysdate, 3500 )
           };

// Example #2
oracle.sql.CHAR   char_in = new CHAR("This is a CHAR", null);
oracle.sql.CHAR   char_out = null;
#sql { select :char_in INTO :char_out FROM dual };

Similar to JDBC, SQLJ does not support Java char types. The following code fragment, which declares the host variable ename as Java char, will fail and a “Java type char for column ename is illegal” exception will be thown.

char ename;
 #sql { SELECT ename INTO :ename FROM emp
               WHERE ename ='KING' };

Instead, the following code fragment, which maps a VARCHAR2 column (i.e., ename) to a Java String (the host variable is declared as Java String) will succeed.

 String ename;
 #sql { SELECT ename INTO :ename FROM emp
               WHERE ename ='KING' };

SQLJ furnishes the -fixedchar option for CHAR comparisons in a WHERE clause. When set to true, SQLJ behaves like the JDBC setFixedCHAR() method; during WHERE clause comparisons. This option works with both Oracle-specific and ISO standard code generations.

% sqlj -fixedchar MyProgram.sqlj ...

Notice that similar to JDBC, SQLJ treats zero-length strings (i.e., “”) as null.

The oracle.sql.NString class is a wrapper for java.lang.String, distributed with the SQLJ runtime, which ensures that the national language character form of use is registered with the JDBC driver. It furnishes the following methods:

However, in order to use the String host variable to bind to NCHAR columns, you must translate the SQLJ file with the -ncharconv SQLJ translator option. Starting with Oracle Database 10g Release 2, Oracle SQLJ furnishes the -ncharconv option, which instructs the translator^[1] to use the SetFormOfUse method to bind to NCHAR columns to String host variables.

% sqlj –ncharconv=true myNCHAR.sqlj

Then you can use the String variables to bind NCHAR columns, as follows:

...
String v_a = "uFF5E";
String v_nb = "uFF5E";
#sql {INSERT INTO Tbl1 (ColA, NColB) VALUES (:v_a, :v_nb)};
...

Refer to Chapter 8 for a description of SQL NUMBER. All numeric types in Oracle Database 10g are stored as NUMBER. Per Table 12.1, SQLJ can map SQL NUMBER to oracle.sql.NUMBER, Short, Integer, Long, short, int, long, and so on. However, mapping to types other than oracle.sql.NUMBER, you may lose the precision information during the mapping.

// Mapping SQL NUMBER to oracle.sql.NUMBER
oracle.sql.NUMBER com;
oracle.sql.NUMBER enum = new oracle.sql.NUMBER(7902);
#sql { SELECT COMM INTO :com FROM EMP WHERE EMPNO = :enum };

int rowNum = 2232
oracle.sql.NUMBER numb = new oracle.sql.NUMBER(rowNum/
1000.0));
#sql [ctx] {  INSERT INTO TAB VALUES (:numb)};

// Mapping SQL NUMBER to java.lang.Integer
java.lang.Integer sal;
#sql { select SAL into :sal from emp WHERE ENAME = 'SMITH' };

Refer to Chapter 8 for a description of SQL BINARY_FLOAT and BINARY_DOUBLE. These types can be mapped, respectively, to oracle.sql.BINARY_FLOAT and oracle.sql.BINARY_DOUBLE.

Assume a binary_tab table with SQL BINARY_FLOAT and BINARY_DOUBLE columns. The following code fragment maps an oracle.sql.BINARY_FLOAT value and an oracle.sql.BINARY_FLOAT value to the corresponding SQL columns.

BINARY_FLOAT bf = new BINARY_FLOAT((float)789.669);
BINARY_DOUBLE bd = new BINARY_DOUBLE((double)897.9999);
#sql ( insert into binary_tab values(:bf, :bd)};

Refer to Chapter 8 for a description of SQL RAW data types. Similar to JDBC, the Oracle SQLJ can map SQL RAW to/from oracle.sql.RAW and Java bytes[].

 import oracle.sql.*;
 ...
 byte[] bytearr = {
2,3,5,7,11,13,17,19,23,29,31,37,41,43,47,53,59,61 };
 RAW oraw = new RAW(bytearr);

 // Update a SQL RAW column withto oracle.sql.RAW data
 #sql { update TypesTab set xraw = :oraw);

 // Retrieve a SQL RAW column as oracle.sq.RAW
 #sql oraw = {SELECT xraw FROM TypesTab};

Refer to the corresponding JDBC section in Chapter 8 for a description of SQL LONG and SQL LONG RAW data. Similar to JDBC, SQLJ may map SQL LONG to oracle.sql.RAW or java.lang.String, and maps SQL LONG RAW to oracle.sql.RAW, or byte[].

Basic Example:

byte[] buf = { 00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05,
00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05,
00, 01, 02, 03, 04, 05, 00, 01, 02, 03, 04, 05 };

#sql {INSET INTO raw_tab VALUES (:buf)};

However, as with JDBC, LONG and LONG RAW data types are by default (or most of the time) manipulated in SQLJ, in streaming mode, which is the topic of the next section.

SQLJ supports binary and character streams through the BinaryStream and CharacterStream classes.

The sqlj.runtime.BinaryStream class allows mapping and streaming of binary streams, including LONG RAW and RAW data in SQLJ. It is a wrapper for an InputStream, which cannot be passed directly as a parameter to an executable SQL operation because of the need to specify its length and its interpretation; therefore, an instance of BinaryStream is passed instead.

public class sqlj.runtime.BinaryStream extends
sqlj.runtime.StreamWrapper
{   public sqlj.runtime.BinaryStream(java.io.InputStream);
    public sqlj.runtime.BinaryStream(java.io.InputStream,int);
    public java.io.InputStream getInputStream();
    public int getLength();
    public void setLength(int);
}

The StreamWrapper class extends java.io.FilterInputStream and provides the following methods:

The java.io.FilterInputStream class is a subclass of the java.io.InputStream, which furnishes the following methods to the SQLJ BinaryStream class:

The sqlj.runtime.CharacterStream class allows the manipulation of character streams in SQLJ (i.e., mapping of LONG and VARCHAR2 data). It is a wrapper for java.io.FilterReader and consequently for java.io.Reader object, which cannot be passed directly as an input parameter because of the need to specify its length; therefore, an instance of CharacterStream object is passed instead.

public class sqlj.runtime.CharacterStream extends
java.io.FilterReader
{   public sqlj.runtime.CharacterStream(java.io.Reader);
    public sqlj.runtime.CharacterStream(java.io.Reader,int);
    public int getLength();
    public java.io.Reader getReader();
    public void setLength(int);
}

The java.io.Reader class furnishes the following methods to the SQLJ CharacterStream class:

The following code sample illustrates binary SQLJ stream manipulation and performs the following operations:

The Oracle-specific code generation allows SQLJ stream objects to be used as OUT or INOUT host variables in a stored procedure or function call, and also as the return value from a stored function call.

Assume the following stored procedure, which takes a LONG data type column as input parameter and returns it as a VARCHAR2 data type:

create or replace procedure CharStrProc(
  long_col in long,
  vc2_col out varchar2)
  as begin
    vc2_col := long_col;
  end;

The following code fragment invokes the procedure:

// Note: a ByteArrayInputStream contains an
// internal buffer that
// contains bytes that may be read from the stream
CharacterStream chstr = new CharacterStream
  ( new ByteArrayInputStream("Character String".getBytes()));
String vc2 = (new String(""));
chstr.setLength(20);
#sql { call CharStrProc(:in chstr, :out vc2);
System.out.printl("VC2 is " + vc2);

Assume the following stored function, which takes a LONG as input parameter and returns a VARCHAR2 data:

create or replace function CharStrFunc(
  long_col in long) return varchar2
  as
  vc2_col varchar2(32767);
  begin
    vc2_col := long_col;
    return (vc2_col);
  end;

The following code fragment invokes the function:

CharacterStream chstr = new CharacterStream
  ( new ByteArrayInputStream("Character String".getBytes()));
String vc2 = (new String(""));
chstr.setLength(20);
#sql vc2 =  {  values(CharStrFunc(:in chstr)) };
System.out.printl("VC2 is " + vc2);

I recommend reading the discussion on SQL DATE support in Oracle JDBC in Chapter 8 before proceeding. Per Table 12.1, SQL DATE can be mapped to java.sql.Date, java.sql.Time, java.sql.Timestamp, java.lang.String, and oracle.sql.DATE.

The following code fragment retrieves SQL DATE into a java.sql.Date host variable:

//
java.sql.Date hireDat= null;
String ename= null;
#sql pc = { select hiredate, ename
                   from emp order by empno};
while (true) {
    #sql { fetch :pc into :hireDat, :ename };
    if (pc.endFetch()) break;
    System.out.println("Employee " + ename + " was hired on "
+ hireDat);
 }

The following code fragment retrieves a SQL DATE column into an oracle.sql.DATE host variable:

import oracle.sql.DATE;
...
DATE date=null;
#sql date={VALUES(Sysdate)  };

The following code fragment inserts an oracle.sql.DATE value into a SQL DATE column:

//
oracle.sql.DATE   d_in = new DATE(new Timestamp(0L));
#sql { insert INTO  Tab VALUES(:d_in)};

Refer to the timestamp discussion in Chapter 8. Assuming a TIMESTAMP_TAB table with the various timestamp type columns (i.e., C_TIMESTAMP, C_TIMESTAMP, C_TIMESTAMP), the following code fragment (a reuse of the JDBC timestamp-related code fragment) shows the mapping of SQL TMESTAMP data types to java.sql.Timestamp, java.sql.Timezone, and oracle.sql.TIMESTAMPLTZ in SQLJ:

import java.sql.Timestamp;
import java.util.Date;
import oracle.sql.*;
//
String my_date = "2006-01-06 12:23:47.66";
oracle.sql.TIMESTAMPLTZ      my_tsltz = null;
GregorianCalendar            my_gcal  = null;
Timestamp my_tss = Timestamp.valueOf(my_date);
TimeZone my_tz = TimeZone.getDefault();
my_tz.setID("US/Pacific");
my_gcal = new GregorianCalendar(my_tz);
my_tsltz = new oracle.sql.TIMESTAMPLTZ(conn, my_tss, my_cal);
my_tstz = new oracle.sql.TIMESTAMPTZ(conn, my_tss, my_cal);
my_ts = new oracle.sql.TIMESTAMP(my_tss);

#sql { update timestamp_tab set c_timestamp = :my_ts,
       c_timestamptz = :my_tstz, c_timestampltz = my_tsltz))};

Refer to the JDBC description of this SQL data type in Chapter 8. Similar to JDBC, SQLJ can map the SQL INTERVALYM/INTERVALDS data types to string or oracle.sql.INTERVALDS/oracle.sql.INTERVALYM, as shown in the following code fragment:

import oracle.sql.*;
//
INTERVALDS ids = new INTERVALDS ("15 08:12:42.0");
#sql {INSERT INTO idstab VALUES (:ids) };

Refer to Chapter 8 for a description of SQL LOB data types, including BLOBs, CLOBs, and BFILEs. The Oracle SQLJ can map SQL BLOB, CLOB, and BFILE as:

There are primarily three ways to manipulate LOB data: using the DBMS_LOB package; using the standard JDBC LOB API (java.sql.Blob, java.sql.Clob) and Oracle JDBC LOB wrapper classes (oracle.sql.BLOB, oracle.sql.CLOB, oracle.sql.BFILE); and using SQLJ streaming.

Assume the following table with a BLOB and a CLOB column type:

create table XOB_tab (vc2 varchar2 (30), bcol blob, ccol
clob);

The DBMS_LOB package contains functions and procedures to manipulate LOBs; however, these incur systematic roundtrip(s) to the RDBMS. The following code fragments illustrate the steps, procedures, and functions to use. Steps 1 to 3 are common to all LOB types; step 4 retrieves a LOB; step 5 inserts a CLOB; and step 6 opens a BFILE.

See the PL/SQL documentation for more details on the package and its methods.

JDBC 2.0 specifies standard java.sql.Blob and java.sql.Clob API for manipulating LOBs. The oracle.sql.CLOB, oracle.sql.BLOB, and oracle.sql.BFILE wrapper classes furnish methods such as putBytes(), getBytes(), putChars(), getChars(), and so on for manipulating LOB locators and LOB data.

The SQLJ streaming classes (i.e., sqlj.runtime.BinaryStream and sqlj.runtime.CharacterStream) can be used to stream the LOB content directly to the database (or retrieve the content directly). In the previous example, we used putBytes() or putChar() to write LOB content to the database. In this case, the buffer must be written to the database, using the BinaryStream and CharacterStream approach instead (refer to the previous SQLJ stream code sample). There is no need or opportunity to commit or roll back changes when data is streamed directly into the database.

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

CLOB clobLoc;
#sql clobLoc = { VALUES(return_clob(...)) };

Host variables of the BLOB, CLOB, and BFILE type can appear in the INTO-list of a SELECT INTO executable statement. Assume the previously created table XOB_Tab, with the following rows:

INSERT INTO XOB_Tab VALUES
    ('one', '010101010101010101010101010101',
'onetwothreefour'),
INSERT INTO XOB_tab VALUES
  ('two', '020202020202020202020202020202',
'twothreefourfivesix'),

The following code fragment uses a BLOB and a CLOB as host variables that receive data from the table defined, using a SELECT INTO statement:

...
BLOB blobLoc;
CLOB clobLoc;
#sql { SELECT t1.b, t2.c INTO :blobLoc, :clobLoc
     FROM XOB_TAb t1, XBO_Tab t2
     WHERE t1.vc2='one' AND t2.vc2='two' };
#sql { INSERT INTO XOB_Tab VALUES('three', :blobLoc,
:clobLoc) };
...

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.

#sql iterator NamedLOBIter(CLOB ccol);
#sql iterator PositionLOBIter(BLOB);
#sql iterator NamedFILEIter(BFILE bfile);

BLOB, CLOB, and BFILE host variables can be used with positional iterators and appear in the INTO-list of the FETCH INTO statements.

#sql iterator PositionLOBIter(BLOB);

PosLOBIter pliter;
BLOB blobLoc = null;
#sql pliter = { SELECT bcol FROM XOB_tab };
for (long rowNum = 1; ; rowNum++)
{
    #sql { FETCH :pliter INTO :blobLoc };
    if (pliter.endFetch()) break;
    // write to Blob
}

The most well-known example of an OPAQUE type implementation is the SQL XMLType (i.e., SYS.XMLTYPE) to represent and natively store XML documents (XMLType can be used as a column type in a table or view). It has a SQL API with built-in member functions to create, query, extract, and index XML data stored in an Oracle Database 10g.

Oracle SQLJ maps SYS.XMLType to oracle.xdb.XMLType; however, the JDBC-OCI drivers are required.

Assume a table with an XMLType column, created as follows:

create xmltype_tbl (xmltype_col SYS.XMLType);
insert into xmltype_tbl values(SYS.XMLType('<name>tom</name>'));
insert into xmltype_tbl values(SYS.XMLType('<name>jon</name>'));

It can be manipulated using the following code fragments:

  import oracle.xdb.XMLType;
  ...
  //
  #sql iter={select xmltype_col from xmltype_tbl;}
  while(iter.next()) {
     System.out.println(iter.xmltype_col().getStringVal());
  }
  //
  while (iter.next()) {
     System.out.println(iter.xmltype_col.getClobVal());
  } 

XMLType can also be used for parameters in stored procedures, and as return values. Alternatively, you can leverage the PL/SQL API for XML in the XDB documentation^[3] and embed XMLType-related PL/SQL blocks within SQLJ.

Refer to Chapter 8 for an explanation of type-maps. SQLJ supports type-maps through connection contexts and iterators. Reusing the JDBC type map example in Chapter 8, assume you want to map the SQL type Address_t to the class AddressObj. A type map can be associated with the connection context class, as follows:

#sql public static context TypeMapCtx with
(typeMap="AddressObj");

The generated TypeMapCtx class will define a public static final String typeMap attribute that will be initialized to the value AddressObj.

A type map can also be associated with the iterator class in an iterator declaration:

#sql public static iterator TypeMapIterator with
(typeMap="AddressObj")
   (Person pers, Address addr);

For Oracle-specific code generation, the iterator and connection context declarations must be of the same type-maps.

public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

SQLJ applications can perform custom mapping and processing of user-defined types (i.e., Oracle objects, references, or collections), using the following weakly typed classes: java.sql.Struct or oracle.sql.STRUCT for objects, java.sql.Ref or oracle.sql.REF for object references, and java.sql.Array or oracle.sql.ARRAY for collections. See a description of java.sql.Struct and oracle.sql.STRUCT classes and their methods in Chapter 8.

These classes can be used for:

However, when objects or collections are written or read to and from instances of these classes, SQLJ cannot perform type checking; also, because the underlying user-defined SQL type name (i.e., Address_t) is not known by the Oracle JDBC driver, these classes cannot be used in the following host expressions:

Alternatively to weak type mapping and similar to JDBC, SQLJ can perform strong type mapping of a user-defined object, using classes that implement either the standard SQLData interface or Oracle-specific ORAData interface. Both interfaces are described in Chapter 8.

Custom mapping of user-defined object types using SQLData requires the following:

import java.sql.*;
import oracle.jdbc.OracleDriver;
import sqlj.runtime.*;
import oracle.sqlj.runtime.*;
import sqlj.runtime.ref.DefaultContext;
import java.math.BigDecimal;

public class mySqlData
{

  #sql public static context StudCtx with (typeMap = "StudentObj");
  #sql public static iterator StudIter with (typeMap = "StudentObj")
                                            (StudentObj studobj);

  public static void main(String args []) throws Exception
  {
    Oracle.connect(mySqlData.class, "connect.properties");
    StudCtx Ctx = new StudCtx(DefaultContext.getDefaultContext());
    try
    {
     // Clean-up previous attempts
      #sql [Ctx] { drop table Student_TABLE };
      #sql [Ctx] { drop type Student FORCE };
    } catch (SQLException e){
      // ignore clean-up exception
    }
    // Create the type and populate the table
    System.out.println("Create Student type and table");
    #sql [Ctx] {CREATE TYPE  StudentObj AS OBJECT
        (studName VARCHAR2(50),studNo INTEGER)};
    #sql [Ctx] {CREATE TABLE Student_TABLE (studobj StudentObj)};
    #sql [Ctx] {INSERT INTO  Student_TABLE
                   VALUES (StudentObj('Zoe Nucci', 231))};
    // Create a SQLData object
    StudentObj stdobj =
          new StudentObj("SCOTT.Student", "Bobo Lafleur", 564);

    // Insert the SQLData object
    System.out.println("Insert new student");
    #sql [Ctx] { insert into Student_table values (:stdobj) };
   // Retrieve the inserted object
    System.out.println("Rerieve the inserted student");
    StudIter objiter;
    #sql [Ctx] objiter = { select studobj from Student_table };
    while(objiter.next())
    {
        stdobj = objiter.studobj();
        System.out.println("Student Name: " + stdobj.studName +
                                     " student #: " + stdobj.studNo );
    }
    objiter.close();
    Oracle.close();

  }
}

Strong type mapping using SQLData is restricted to user-defined objects only; however, Oracle’s ORAData allows strong type mapping of object references, collections, and other SQL types.

Custom mapping of user-defined object types using ORAData requires:

public static final int _SQL_TYPECODE =
OracleTypes.STRUCT;

The wrapper class must have a _SQL_NAME initialized to the SQL name:

public static final String _SQL_NAME = "Student";

Here is a handcrafted wrapper class implementing ORAData; as already mentioned, the lazy but smart developer can use JPublisher to generate it.

StudentORAData.java
====================
import java.sql.*;
import oracle.sql.*;
import oracle.jdbc.OracleTypes;

public class StudentORAData implements ORAData, ORADataFactory
{
  static final StudentORAData _factory = new StudentORAData ();
  public static final String _SQL_NAME = "STUDENTORADATA";
  public static final int _SQL_TYPECODE = OracleTypes.STRUCT;

  private String sql_type = "SCOTT.STUDENTORADATA";
  public String studName;
  public int studNo;

  public static ORADataFactory getORADataFactory()
  {
    return _factory;
  }

  //Constructors
  public StudentORAData() { }

  public StudentORAData (String studName, int studNo) {
    //this.sql_type = sql_type;
    this.studName = studName;
    this.studNo = studNo;
  }
  public Datum toDatum(Connection conn)
    throws SQLException
  {
    StructDescriptor strdesc =
           StructDescriptor.createDescriptor(sql_type, conn);
    Object [] attribs = {  studName, new Integer(studNo) };
    return new STRUCT(strdesc, conn, attribs);
  }
public ORAData create(Datum datm, int sqlType) throws SQLException
  {
    if (datm == null) return null;
    Datum[] attribs = ((STRUCT) datm).getOracleAttributes();
    return new StudentORAData ( attribs[0].stringValue (),
                        attribs[1].intValue ());
  }

  public String toString ()
  {
    return sql_type + " = " + studName + ", " + studNo;
  }
 }

$ javac StudentORAData.java

Listing 12.4 hereafter uses StudentORAData.

import java.sql.*;
import oracle.jdbc.OracleDriver;
import sqlj.runtime.*;
import oracle.sqlj.runtime.*;
import sqlj.runtime.ref.DefaultContext;
import java.math.BigDecimal;

public class myORAData
{

  #sql public static iterator StudIter (StudentORAData studobj);

  public static void main(String args []) throws Exception
  {
    Oracle.connect(mySqlData.class, "connect.properties");
    try
    {
     // Clean-up previous attempts
      System.out.println("Drop student table and type");
      #sql { drop table Student_TABLE };
      #sql { drop type Student FORCE };
    } catch (SQLException e){
       // ignore clean-up exception
    }

    // Create the type and populate the table
    System.out.println("Create Student type and table");
    #sql {CREATE TYPE  StudentORAData AS OBJECT (studName VARCHAR2(50),studNo
INTEGER)};
    #sql {CREATE TABLE Student_TABLE (studobj StudentORAData)};
    #sql {INSERT INTO  Student_TABLE VALUES (StudentORAData('Zoe Nucci',
231))};

    // Create a ORAData object
    StudentORAData stdobj = new StudentORAData("Bobo Lafleur", 564);

    // Insert the ORAData object
    System.out.println("Insert new student");
    #sql { insert into Student_table values (:stdobj) };
    // Retrieve the inserted object
    System.out.println("Rerieve the inserted student");
    StudIter objiter;
    #sql objiter = { select studobj from Student_table };
    while(objiter.next())
    {
       stdobj = objiter.studobj();
       System.out.println("Student Name: " + stdobj.studName +
                                  " student #: " + stdobj.studNo );
    }
    objiter.close();
    Oracle.close();

  }
}

$ java -Doracle.net.tns_admin=$TNS_ADMIN myORAData
Drop student table and type
Create Student type and table
Insert new student
Rerieve the inserted student
Student Name: Zoe Nucci student #: 231
Student Name: Bobo Lafleur student #: 564
$

As described in Chapter 8, SQLJ object types are specified by ANSI SQLJ Part II and allow creating SQL types using Java classes that implement SQLData or ORAData interface. Similar to JDBC in Chapter 8, SQLJ applications may use SQLJ object types.

SQL REF types are explained in the corresponding section in Chapter 8. Similar to JDBC, SQLJ can automatically map SQL REF column types to and from the standard java.sql.Ref or to and from the oracle.sql.REF wrapper. Alternatively, you may use the custom mapping approach using a class that implements ORAData.

The Oracle SQLJ supports using REF:

REF myRef = null;
int objnum = 1;
#sql { begin select * into :OUT myRef from ref_obj_tab p
        where p.objid  = :IN objnum; end; };

For custom mapping of object references using ORAData, the following requirements must be met:

public static final String _SQL_BASETYPE ="PERSON";

See Chapter 8 for more elaborate examples that you can easily implement using SQLJ.

REF cursors contain references to database cursors. Oracle SQLJ supports using REF CURSOR types for mapping the following:

The following code fragment retrieves a REF CURSOR from an anonymous PL/SQL block:

 #sql { begin
            INSERT INTO emp (ename, empno) VALUES (:name, :num);
            OPEN :out emplist FOR SELECT ename, empno FROM emp ORDER BY empno;
          end
       };

Listing 12.5 illustrates how to use the SQL CURSOR operator for a nested SELECT within an outer SELECT statement, to retrieve the department name of each employee.

import java.sql.*;
import sqlj.runtime.*;
import java.sql.ResultSet;
import sqlj.runtime.ref.DefaultContext;
import oracle.jdbc.driver.*;
import oracle.sqlj.runtime.*;

public class myRefCursor
{
  #sql static public iterator NameRefCursIter
                    (String ename, ResultSet deptlis);
  public static void main(String[] args)
  {
    System.out.println("**** ResultSet as Ref Cursor");
    try
    {
      Oracle.connect(basicFunc.class, "connect.properties");
      NameRefCursIter nrciter;
      #sql nrciter = { select ename,
                       CURSOR (select dname from dept
                                       where deptno = emp.deptno)
                                       as deptlis
                                       from emp
                     };

      System.out.println("Loop through the Iterators, print Ename and
Dname");
      while (nrciter.next ())
      {
        ResultSet deptRSet = nrciter.deptlis();
        if (deptRSet.next ())
        {
          String deptName = deptRSet.getString (1);
          System.out.println("Name: " + nrciter.ename() +
                                           " Dept: " + deptName);
        }
        deptRSet.close();
      }
      nrciter.close();
    } catch (SQLException e) {
      e.printStackTrace();
      System.out.println(e);
    }
  }
}

$ sqlj myRefCursor.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN myRefCursor

**** ResultSet as Ref Cursor
Loop through the Iterators and print Ename and Dname
Name: SMITH Dept: RESEARCH
Name: ALLEN Dept: SALES
Name: WARD Dept: SALES
Name: JONES Dept: RESEARCH
Name: MARTIN Dept: SALES
Name: BLAKE Dept: SALES
Name: CLARK Dept: ACCOUNTING
Name: SCOTT Dept: RESEARCH
Name: KING Dept: ACCOUNTING
Name: TURNER Dept: SALES
Name: ADAMS Dept: RESEARCH
Name: JAMES Dept: SALES
Name: FORD Dept: RESEARCH
Name: MILLER Dept: ACCOUNTING
$

See Chapter 3 for a Java stored procedure returning a REF cursor.

The Oracle-specific code generation allows mapping a serializable Java class to RAW or BLOB columns. It uses a nonstandard extension to typeMap facility or adds a typecode field to the serializable class, so that the instances of the serializable class can be stored as RAW or BLOB.

public final static int _SQL_TYPECODE =
oracle.jdbc.OracleTypes.RAW;

public final static int _SQL_TYPECODE =
oracle.jdbc.OracleTypes.BLOB;

You can also use ORAData to define a serializable wrapper class whose instances can be stored in RAW or BLOB columns. Here are the steps (from the Oracle SQLJ documentation) for achieving such mapping:

The following uses a SerializableDatum instance as a host variable:

...
SerializableDatum pinfo = new SerializableDatum();
pinfo.setData (
   new Object[] {"Some objects", new Integer(51), new
Double(1234.27) } );
String pname = "MILLER";
#sql { INSERT INTO persondata VALUES(:pname, :pinfo) };
...

The following code fragment uses SerializableDatum as a named iterator column:

#sql iterator PersonIter (SerializableDatum info,
String name);

...
PersonIter pcur;
#sql pcur = { SELECT * FROM persondata WHERE info IS NOT
NULL };
while (pcur.next())
{
   System.out.println("Name:" + pcur.name() + " Info:" +
pcur.info());
}
pcur.close();
...

The following code sample describes how to exchange serialized Java objects by value, between a client-side Java/SQLJ and server-side Java/SQLJ. The various RowSet models covered in Chapter 8 are probably more elegant approaches. The serialized Java object is passed as LONG RAW arguments to stored procedure and function using the PL/SQL interface, which limits the size of LONG RAW arguments to 32 K bytes. This example comprises the following files: EmpObjClient.sqlj, EmployeeObj.java, EmployeeObject.sqlj, Util.java, and empobj.sql.

EmployeeObject.sqlj
===================
/*
 * Server-side SQLJ providing entrypoint to get
 * EmployeeObj or EmployeeObj references
 */

import java.sql.*;
import oracle.sql.RAW;

#sql iterator EmpIter (String ename, Integer mgr, int sal);
public class EmployeeObject
{
  public static byte[] getEmployeeObj (Integer empno) throws Exception
  {
    EmployeeObj empobj = getEmployee (empno);
    return Util.serializeObject (empobj);
  }

  public static void updateEmployeeObj (byte[] obj) throws Exception {
    EmployeeObj empobj = (EmployeeObj)Util.deserializeObject (obj);
    updateEmployee (empobj);
  }

  private static EmployeeObj getEmployee (Integer empno)
       throws SQLException
  {
    if (empno == null)
      return null;
    else {
      EmpIter it;
      #sql it = {select ename, sal, mgr from emp where empno =
:empno};
      if (!it.next ()) return null;
      return new EmployeeObj (empno.intValue (), it.ename (), it.sal
(), getEmployee (it.mgr ()));
    }
  }

  private static void updateEmployee (EmployeeObj empobj)
        throws SQLException
  {
    if (empobj != null) {
      updateEmployee (empobj.manager);
      if (empobj.manager != null)
          #sql {update emp set ename = :(empobj.ename),
                               mgr = :(empobj.manager.empno),
                               sal = :(empobj.salary)
                           where empno = :(empobj.empno) };
       else
          #sql {update emp set ename = :(empobj.ename),
                               sal = :(empobj.salary)
                           where empno = :(empobj.empno) };
    }
  }
}

EmployeeObj.java
================
/* This Class represent a Java object passed by value between the
   Client and the Server.
   */

import java.io.Serializable;

public class EmployeeObj implements Serializable
{
  public int empno;
  public String ename;
  public int salary;
  public EmployeeObj manager;

  public EmployeeObj (int empno, String ename, int salary,
                       EmployeeObj manager){
     this.empno = empno;
     this.ename = ename;
     this.salary = salary;
     this.manager = manager;
  }
}

Util.java
======
/* Utility class for serializing / deserializing objects */

import java.io.*;

public class Util {

  public static byte[] serializeObject (Object obj)
        throws IOException
  {
    if (obj == null) return null;
    ByteArrayOutputStream ostream = new ByteArrayOutputStream ();
    ObjectOutputStream p = new ObjectOutputStream (ostream);
    p.writeObject (obj);
    p.flush ();
    return ostream.toByteArray ();
  }

  public static Object deserializeObject (byte[] bytes)
       throws IOException, ClassNotFoundException
  {
    if (bytes == null) return null;
    InputStream istream = new ByteArrayInputStream (bytes);
    return new ObjectInputStream (istream).readObject ();
  }
}

$ loadjava -resolve -user scott/tiger
                   EmployeeObj.java EmployeeObject.sqlj Util.java

You may ignore the warnings.

empobj.sql
==========

create or replace package empobj as
  function getempobj (empno in number) return long raw;
  procedure updateempobj (obj long raw);
end;
/

create or replace package body empobj as
  function getempobj (empno in number) return long raw
     as language java name
       'EmployeeObject.getEmployeeObj(java.lang.Integer) return
byte[]';
  procedure updateempobj (obj long raw)
     as language java name
       'EmployeeObject.updateEmployeeObj (byte[])';
end;
/

show errors;
exit

$ sqlplus scott/tiger @empobj

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Jan 15 15:32:57 2006

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 -
Production
With the Partitioning and Data Mining options


Package created.


Package body created.

No errors.
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 - Production
With the Partitioning and Data Mining options
$

EmpObjCLient.sqlj
=================
import java.sql.*;
import java.io.IOException;
import sqlj.runtime.ref.DefaultContext;
import sqlj.runtime.BinaryStream;
import oracle.sqlj.runtime.*;

public class EmpObjClient
{
  public static void main (String args[])
       throws Exception
  {
    Oracle.connect(EmpObjClient.class, "connect.properties");

    System.out.println ("Getting employee number 7788");
    EmployeeObj emp = getEmployee (7788);
    printEmp (emp);

    System.out.println ("Raising employee number 7788 and his manager
by 10%");
    emp.salary += emp.salary * 0.10;
    emp.manager.salary += emp.manager.salary * 0.10;
    updateEmployee (emp);

    System.out.println ("Getting employee number 7788 again");
    emp = getEmployee (7788);
    printEmp (emp);
  }

  public static void printEmp (EmployeeObj emp) {
    if (emp != null) {
      System.out.println (emp.empno + ": " + emp.ename +
                          " salary " + emp.salary);
      if (emp.manager != null) {
        System.out.print ("  managed by ");
        printEmp (emp.manager);
      }
    }
  }

  public static EmployeeObj getEmployee (int number)
       throws SQLException, IOException, ClassNotFoundException
  {
     byte[] serial_empobj = null;
     #sql serial_empobj = { values (empobj.getempobj (:number)) };
     return (EmployeeObj)Util.deserializeObject (serial_empobj);
  }

  public static void updateEmployee (EmployeeObj emp)
       throws SQLException, IOException
  {
    byte[] serial_empobj = Util.serializeObject (emp);
    #sql { call empobj.updateempobj (:serial_empobj) };
  }
}
$ sqlj EmpObjCLient.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN EmpObjClient
Getting employee number 7788
7788: SCOTT salary 4350
  managed by 7566: JONES salary 2975
  managed by 7839: KING salary 5000
Raising employee number 7788 and his manager by 10%
Getting employee number 7788 again
7788: SCOTT salary 4785
  managed by 7566: JONES salary 3272
  managed by 7839: KING salary 5000
$

SQL VARRAYs are described in Chapter 8. Similar to JDBC, the Oracle SQLJ maps the SQL VARRAY to the oracle.sql.ARRAY wrapper class (which implements the standard java.sql.Array interface) or to the custom wrapper class.

VARRAY can be used for host variable, iterators, parameters for stored procedures and functions, and within anonymous PL/SQL blocks.

Beginning with Oracle Database 10g, the Oracle SQLJ supports array types in iterator columns. In other words, you can declare an iterator that uses java.sql.Array or oracle.sql.ARRAY columns.

#sql public static iterator NamedArrayIter (BigDecimal order,
ARRAY parts);
#sql public static iterator PositArrayIter (BigDecimal,
ARRAY);

static ARRAY myArray = null;
#sql { select t.parts into :(myArray) from VARRAY_tab t where
t.order=203};

#sql { insert into VARRAY_tab (order, parts) values (310,
:(myArray) )} ;

VARRAYs can be used as IN parameters for stored procedures.

#sql { call insertVARRAY1(204, :IN myArray ) };

SQL 2003 specifies getArray() on RTResultSet object, and setArray() on RTStatement for inserting and retrieving ARRAY/MULTISET. However, as of Oracle Database 10g Release 2 (10.2), the Oracle SQLJ does not support getURL on RTResultSet and setURL on RTStatement.

For custom mapping of user-defined collections such as VARRAYs using ORAData, the following requirements must be met:

public static final String _SQL_NAME ="ADT_VARRAY";

Nested Tables are unordered and unbound user-defined collections. See Chapter 8 for a further description and examples of nested tables. Similar to VARRAY, Nested Tables can be mapped to java.sql.Aray, oracle.sql.VARRAY, and custom wrapper classes.

Nested Tables can be used in iterator declaration, IN variables in select statement, IN and OUT parameter for stored procedures, and return values of stored functions.

#sql public static iterator NamedArrayIter(BigDecimal order,
NTab_parts parts);
#sql public static iterator PositionedArrayIter(BigDecimal,
NTab_parts );

NTab_parts myNTab = new NTab_parts();

#sql {select t.parts into :(myNTab) from NTAb_parts_tab t
where t.order=101};

NTab_parts yourNTab = new new NTab_parts();
#sql { select :myNTab into :yourNTab from dual };

RTResultSet and RTStatement do not support getURL and setURL methods. SQL 2003 introduced the DATALINK data type. Variables of DATALINK type can be retrieved from the database using the getURL method on the RTResultSet, and can be set using the setURL on RTStatement.

JDBC 3.0 named parameters are not currently supported by Oracle SQLJ.

Like the Oracle JDBC, the Oracle SQLJ does not support calling arguments or return values of the PL/SQL BOOLEAN type or RECORD types.

As a workaround for an unsupported type, you can create wrapper procedures that process the data using supported types. See conversion technique in JPublisher (covered later).