WTF with SQLJ

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 (iter.next()) { 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 10.1.0.1, 10.1.0.2, and 10.1.0.3) but is fully re-supported in the Oracle Database 10.1.0.4 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 };

Expressions

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(iter.next()) {

System.out.println(iter.xmltype_col().getStringVal());

}

//

while (iter.next())

{

System.out.println(iter.xmltype_col.getClobVal());

}

Learn more about SQLJ and Oracle implementation and code samples

• Oracle SQLJ Doc http://www.oracle.com/pls/db102/to_pdf?pathname=java.102%2Fb16018.pdf&remark=portal+%28Application+development%29
• OTN Forum: http://forums.oracle.com/forums/forum.jspa?forumID=99.
• Book http://db360.blogspot.com/2006/08/oracle-database-programming-using-java_01.html