Wednesday, November 29, 2006

Get Bolder with LOBs Manipulation in Java/JDBC

The Oracle database supports very large CLOB/BLOB columns/data however, the average Java/JDBC applications manipulates mostly medium size LOBs of tens/hundreds Kbytes in size.

WTF with LOB Programming in Java/JDBC

If you have ever dealt with LOBs in Java/JDBC applications, you know that it is not always straightforward, and sometime difficult to program cleanly. Let's take a closer look at the currently available approaches with Orace JDBC.

3 Ways to Slice and Dice LOBs

As of 10g Release 2, Oracle JDBC furnishes 3 ways of accessing and manipulating LOBs including:Oracle's LOB methods, optimized standard LOB methods, and LONG Streaming. Which one to use and when?In this article, i want to shed some light so as to make the reader more comfortable, bolder, when using CLOBs, BLOBs, and BFILE in Java/JDBC applications.

Oracle LOB Approach
LOB manipulation is done in two steps

Step#1: Manipulate the LOB Locator

Create or Retrieve, Insert, and Update the LOB locator using createTemporary, getEmptyCLOB, getEmptyBLOB methods. setBlob, setBLOB, setClob, setCLOB, getBlob, getBLOB, getClob, updateBlob, updateClob, setBLOB, setBLOBATName, setBFILE, updateBLOB, …, and updateCLOB methods.

ResultSet rs = stmt.executeQuery(“SELECT blobcol from XobTab”;
BLOB BlobLoc = ((OracleResultSet)rs).getBLOB(1);

Step#2: Manipulate the LOB Content

Write LOB content using getString, getSubString, and getBytes, setStrings, and putBytes methods.

tempLob.putBytes (1, bytarr);
PreparedStatement pstmt = conn.prepareStatement ( "insert into XobTab values (?)”;
((OraclePreparedStatement)ps).setBLOB (1, tempLob);

Read the LOB contents and Stream the content out, using getBinaryStream, get-String, getCharacterStream, and getAsciiStream.

byte[] bytarr = new byte[20]; // 20 bytes chunk
BLOB LOBLoc = ps.getBLOB (1);
InputStream is = LOBLoc.getBinaryStream();
while ((length = != -1)
// process the chunk
bytarr = new byte[10];

In pre-10g releases, JDBC-Thin used the PL/SQL DBMS_LOB package for LOB operations; in 10g, it uses a more nativead faster protocol however, certain operations such as hasPattern and isSubLob are still done via the dbms_lob package.

Standard and Optimized LOB Approach

For LOBs of size less than 2 gigabytes, LOB manipulation has been simplified especially in 10g Release 2) by removing the need to explicitly manage the locator. The standard methods getBytes, getBinaryStream, getString, getCharacterStream, getAsciiStream setBytes, setBinaryStream, setString, setCharacterStream, and setAsciiStream have been extended to take BLOB, CLOB and BFILE columns as parameters. This approach simplifies LOB programming; under the covers, the driver does the right thing (i.e., taking care of the locator, etc).

Example: Strings or byte arrays can be stored/read into/from the database, by simply using setString or setBytes, getString or getBytes.

LONG Streaming Approach

LONG streaming is furnished to help faster retrieval of data from a LOB without all the LOB overhead (i.e., retrieving the lob locators and going through the lob protocol), which can be very costly for small data. All you need to do is define the column as a LONG, then the driver takes care of LOB to LONG conversion and vice versa. This technique is faster than the locator approach (up to an order ofmagnitude) however, it is not as flexible as the traditional or optimized LOB manipulation and has the following restrictions: LONG streaming always starts at the beginning of the LOB (i.e., you cannot position or start anywhere); you may read as much data as you wish but only a single pass is allowed (i.e., you cannot read the stream twice).
Using this approach, CLOBs can be declared as LONGVARCHAR using defineColumnType. and streamed using getAsciiStream by redefining it a as

Similarly BLOBs columns can be declared as LONGVARBINARY using defineColumnType then streamed using getBinaryStream
(OracleStatement)stmt.defineColumnType(, Types.LONGVARBINARY);

// Pretend that these columns are of LONG and LONG RAW types
(OracleStatement) stmt.defineColumnType(1,Types.LONGVARBINARY);
ResultSet rs = stmt.executeQuery(“select LOBCOL from XOBTab”);
// Retrieving a Clob with getString");
ocrs.setCommand ("SELECT lobcol FROM XOBTab");
ocrs.execute ();
while ( ())
bytarr = stmt.getString (1));
ocrs.close ();

// Retrieving a Blob with getBytes");
ocrs.execute ();
while ( ())
ocrs.getBytes (2).length);

Get More

This article, excerpted from my book, summarizes the various approaches to LOB manipulation using Oracle JDBC drivers. You can find more details and code snippets in either the Oracle JDBC 10g R2 doc or in chapter 8 of my book.

Sunday, November 05, 2006


What is SQLJ

SQLJ allows embedding SQL statements in Java, similar to Pro*C (and all precompilable languages). A SQLJ program is pre-compiled into JDBC (by a translator) and therefore represents a simpler and more productive alternatve to straight JDBC programming in client-tier, middle-tier and database-tier. A runtime mechanism is usually needed at execution time.

#sql iter = {SELECT ENAME FROM EMP where EMPNO ='7934'};
if ( { System.out.println("Employee# 7934 is " + iter.ENAME()); }

Oracle SQLJ Status

SQLJ has been briefly de-supported in early releases of the Oracle Database 10g (namely,, and but is fully re-supported in the Oracle Database and up.

SQLJ Benefits
Many benefits including simplicity features, translation-time type checking, interoperability with JDBC, Oracle extensions to SQLJ, SQLJ Stored Procedures, and Runtime-free code generation.
Simplicity Features (Compared with JDBC)
  • Embedded host variables (JDBC uses parameter markers).
  • No need to cast data types.
  • No need to programmatically register output parameters.
  • No need to explicitly handle the NULL value (See JDBC support for Null in Part II of my book).
  • Support for SELECT INTO statements.
  • Support for PL/SQL anonymous blocks makes it easy to embed PL/SQL in Java.
  • No need for String concatenation for long SQL statements.

Translation-Time Type Checking

  • Syntax checking of SQLJ constructs: a SQLJ parser checks the grammar, according to the SQLJ language specification.
  • Syntax checking of Java instructions: a Java parser (invoked under the covers) checks the syntax of Java host variables and expressions within SQLJ executable statements.
  • Semantics checking: Depending on the option settings, this includes: Syntax of SQL statements by a SQL semantics checker; SQL offline parser; Validation of schema objects (i.e., tables name, columns name); Validation of the mapping between Java types and SQL types.

Interoperability with JDBC

  • JDBC connections can be used by SQLJ as a connection context and vice versa.
  • JDBC ResultSet can be passed to a SQLJ application as a SQLJ Iterator, and vice versa.

Oracle Extensions

Support for Dynamic SQL: Oracle extends it's implementation beyond the specification which only allows static SQL statements, to allow constructing SQL statements at runtime, similarly to JDBC.

String table = "new_Dept"; #sql { INSERT INTO :{table :: dept} VALUES (:x, :y, :z) };

Compile-Time Performane Optimizations

  • Row prefetching
  • SQLJ statement caching
  • Update batching
  • Column type(s) definition
  • Parameter size definition

SQLJ Stored Procedures

The database-resident Java VM (covered in depth in Part I of my book) embeds a SQLJ translator and runtime which allow you to load, pre-compile, compile and execute SQLJ source programs direcly in the database.

dbprint("Hello from SQLJ in the Database!");

Date today;

#sql {select sysdate into :today from dual};

dbprint("Today is " + today);


static void dbprint(String s) {

try {

#sql { call dbms_output.put_line(:s)


} catch (SQLException e) {} }

Runtime-free code generation

SQLJ traditionally requires a runtime (implemenrtor specific). Oracle'simplementation allows you to generate runtime-free code (i.e., oure JDBC).

Advanced/Powerful SQLJ Concepts

SQLJ is more than a mere high-level JDBC code; the language/specification defines concepts such as connection contexts, execution context, iteratorsand expressions that are more advanced than their JDBC counterpart (when they exis).
A SQLJ program is a Java/JDBC code with SQLJ declaration statements,and SQLJ executable statements.

SQLJ declaration statements
This category contains import statements, connection contexts declaration,execution contexts declarations, and iterators declarations.

Excutable Statements

Executable statements can be grouped into Statementclauses and Assignment clauses.
Statement Clause:

#sql [optional SQLJ space] { Statement clause };

Assignment clauses

Query clause

#sql [conctx, exectx] iter = { SQLJ clause };

Function clause

#sql result = { VALUES ( ())};

Iterator conversion clause

#sql iter = { CAST :rset };


Expresions are another powerful SQLJ mechanism that allows you to exploit Java expressions in SQLJ statements including: host expressions,context expressions and result expressions.

#sql [connctx_exp, execctx_exp] result_exp = { SQL with host expression };

Accessing Oracle SQL and PL/SQL Data Types
SQL and PL/SQL data are manipulated in SQLJ through SQLJ host variables/expressions, result expressions, and iterator accessors.

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

create xmltype_tbl (xmltype_col SYS.XMLType);

insert into xmltype_tbl values(SYS.XMLType('tom'));

insert into xmltype_tbl values(SYS.XMLType('jon'));

It can be manipulated using the following code fragments:

import oracle.xdb.XMLType;



#sql iter={select xmltype_col from xmltype_tbl;}

while( {




while (




Learn more about SQLJ and Oracle implementation and code samples