This section looks at what exactly SQLJ is and why you should consider using it. Readers who are familiar with the SQLJ technology and Oracle SQLJ may skip this overview.
In 1997, a group of engineers from IBM Tandem and Oracle started a project to allow embedding of SQL statements in Java. Sun, Sybase, and Informix joined the group, which gave birth to the “JSQL” specifications that were submitted to ANSI; as they say, the rest is history!
SQLJ is an umbrella name for an ANSI SQL-1999 (SQL3 or SQL-99) multipart specification, which comprises:
Part 0—Embedded SQL in Java: Specifies the ability to embed SQL statements in Java, similar to Pro*C and a more productive alternative to JDBC for both client-side and server-side Java. Oracle’s implementation, which is the purpose of this part of the book, is called “SQLJ” and furnishes many extensions, such as support for dynamic SQL, beyond the standard specification.
Part 1—SQL Routines Using Java: Specifies the ability to invoke static Java methods from SQL as procedures and functions. Oracle’s implementation of Java stored procedures and functions was covered in Part I.
Part 2—SQL Types Using Java: Specifies the ability to use Java classes as wrappers for user-defined data types in SQL. Oracle’s implementation, called SQLJ object types, covered in both Part I and Part II.
In the rest of this part of the book, “SQLJ” will refer to “SQLJ Part 0.” The Oracle SQLJ environment comprises the JDK (i.e., stand-alone Java compiler and runtime), the OracleJVM (i.e., Java compiler and runtime in the database), the Oracle JDBC drivers, the SQLJ translator (stand-alone and in the database), the SQLJ runtime (stand-alone and in the database), and the SQLJ profiler.
The SQLJ translator takes SQLJ sources files (.sqlj), Java sources files (.java), Java classes (.class), and Java resources (.ser) as input and performs the following operations (detailed later): SQLJ-to-Java parsing, online/offline type checking, customization of code generation (optional), and compilation of Java source(s) (can be deferred).
The Oracle SQLJ translator is a pure Java code for use as stand-alone on the client side, in the middle tier, as part of a Java application server, and also in the database.
The sqlj command, which is in fact a shell script, invokes the translator, more precisely the sqlj.tools.Sqlj class in $ORACLE_HOME/sqlj/lib/translator.jar.
It carries its own options as well as the options for the Java VM, the compiler, and the profiler.
sqlj <-J-javavm-options>
<-P-profiler-options>
<-C-compiler-options>
<-sqlj-options> <*.java, *.sqlj, *.jar and *.ser files>The SQLJ runtime is launched by the Java VM during the execution of the resulting Java class (i.e., java <sqlj class>). It handles SQLJ runtime operations and delegates the execution of SQL operations to a JDBC driver. The Oracle SQLJ runtime requires the Oracle JDBC drivers; it is a pure Java code for use as stand-alone on the client side, in the middle tier, as part of a Java application server, and also in the database.
SQLJ profiles are serialized Java resources (.ser file) or Java classes (.class file), which contain metadata that allows vendor customization in various areas, such as SQL operations, specific data types support, and performance optimization.
Now that you have a glimpse of SQLJ, your next legitimate question is (should be): “Why?” or, “What for?”
SQLJ allows embedding SQL statements directly into Java sources, similar to embedding SQL in C programs (i.e., Pro*C). SQLJ is a multivendor ISO/ANSI standard, defined and implemented mainly by RDBMS vendors (e.g., IBM, Sybase, Oracle)—you now understand why it is not part of the J2EE specification. Here are a few quotes from the publicly available JDBC/SQLJ forum[1] of the Oracle Technology Network:
“Most of our developers were comfortable with Pro*C, and SQLJ allowed a smooth transition to Java.”
“SQLJ provides several benefits, but perhaps the most significant to us in terms of software development is the compile time checking of SQL statements.”
“I began using SQLJ. It is very good. I am in the process of converting all of my JDBC programs to SQLJ/JDBC combination. The result is much simpler code and more powerful.”
In summary, the key benefit is increased Java/database developers’ productivity through simplicity, strong translation-time type checking (both syntax and semantic), and interoperability with JDBC. Additional benefits brought by the Oracle SQLJ implementation are support for dynamic SQL, enhanced runtime performance through optimized JDBC code generation, and the use of the JPublisher utility (covered later) to automatically map complex user-defined SQL types and PL/SQL types to Java. Sounds like an interesting value proposition, so let’s look at the benefits in greater details.
SQLJ is a developer-friendly alternative to JDBC.
Example 1: Assume the following JDBC code fragment updating DEPT table:
PreparedStatement st =
conn.prepareStatement("UPDATE dept SET location = "San
Francisco" WHERE dname = ? ");
st.setString(1, dName);
st.executeUpdate();
st.close();The corresponding SQLJ code fragment is:
#sql { UPDATE dept SET location = "San Francisco" WHERE dname
= :dName };Example 2: Consider the following JDBC code fragment, which queries the EMP table and retrieves the result set:
Statement st = conn.createStatement("SELECT ename, empid FROM
emp");
ResultSet rs = st.executeQuery();
while (rs.next()) {
x = rs.getString(1);
y = rs.getInt(2);
}
rs.close();Here is the SQLJ equivalent:
sqlj.runtime.ResultSetIterator rsi;
#sql rsi = { SELECT ename, empid FROM emp };
while (rsi.next()) {
#sql { FETCH CURRENT FROM :rsi INTO :x, :y };
}
rsi.close();Here is a highlight of simplicity features that SQLJ brings compared with JDBC:
Host variables are embedded, while JDBC uses parameter markers.
No need to cast data types.
No need to programmatically register output parameters.
No need to explicitly handle the
NULLvalue.Support for
SELECT INTOstatements.Support for PL/SQL anonymous blocks makes it easy to embed PL/ SQL in Java.
No need for
Stringconcatenation for long SQL statements
Concise code is easier to write and debug, resulting in greater productivity. Simply put, SQLJ is to JDBC what JavaServer Pages (JSP) are in some respect for Java servlets; in other words, SQLJ is “JDBC made simpler.”
Here is a highlight of controls that the SQLJ translator performs:
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 the following:
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.
These translation checks reduce run-time errors/exceptions and result in more robust and faster to deploy applications.
You can generate pure ISO SQLJ code with the Oracle SQLJ translator and execute it with the Oracle SQLJ runtime; however, like JDBC, if you are deploying primarily against the Oracle database, you can uptake and benefit from the following extensions.
Even though the ANSI/ISO specification supports only static SQL, Oracle’s implementation allows dynamic SQL statements (constructed at runtime), so as to be on par with JDBC, as illustrated by the following code fragments:
// dynamic SQL support directly in SQLJ
//
#sql { insert into :{table_name :: emp} (ename,empno,sal)
values(:ename, :empno, :sal) };
// dynamic SQL support through Anonymous PL/SQL block
//
#sql { begin
execute immediate
'insert into ' || :table_name ||
'(ename, empno, sal) values( :1, :2, :3)'
using :ename, :empno, :sal;
end;
};And here is the JDBC equivalent:
String dml = "insert into "+ table_name +"(ename,empno,sal) values(?,?,?)"; PreparedStatement ps = DefaultContext.getDefaultContext().getConnection().prepareSta tement(dml); ps.setString(1,ename); ps.setInt(2,empno); ps.setDouble(3,sal); ps.executeUpdate(); ps.close();
Dynamic SQL support allows you to write entire SQLJ applications without the need to switch back and forth to JDBC, just to work around a limitation in the SQLJ specification.
This section describes the SQLJ translator options, the Oracle customizer, the SQLJ runtime and environment setup, and, finally, your first SQLJ program. Figure 10.1 summarizes the various steps and pieces of the Oracle SQLJ architecture.
A SQLJ parser and a Java parser are used to process all the source code and check the syntax. As the SQLJ translator parses the .sqlj file, it invokes the following:
The SQLJ parser, which checks the syntax of SQLJ constructs (
#sql ...) and the grammar against the SQLJ language specificationThe Java parser, which checks the syntax of Java statements, Java host variables, and expressions within SQLJ statements
When you invoke the SQL command without any argument, it displays a short summary of its options, as follows:
$ sqlj
Usage: sqlj [options] file1.sqlj [file2.java] ...
or sqlj [options] file1.ser [file2.jar] ...
where options include:
-d=<directory> root directory for generated binary files
-encoding=<encoding> Java encoding for source files
-user=<user>/<password> enable online checking
-url=<url> specify URL for online checking
-status print status during translation
-compile=false do not compile generated Java files
-linemap instrument compiled class files from sqlj source
-profile=false do not customize generated *.ser profile files
-ser2class convert generated *.ser files to *.class files
-P-<option> -C-<option> pass -<option> to profile customizer or compiler
-P-help -C-help get help on profile customizer or compiler
-J-<option> pass -<option> to the JavaVM running SQLJ
-version get SQLJ version
-help-alias get help on command-line aliases
-help-long get full help on all front-end options
Note: place -<key>=<value> in sqlj.properties as sqlj.<key>=<value>
$Table 10.1 furnishes a comprehensive and explanatory list of the translation options; these can be grouped into four categories, including the basic options, the Javac options, environment options, and advanced options.
Table 10.1. Offline Parsing versus Online Semantics Checking
Feature | By Offline Parser? | By Online Checker? |
|---|---|---|
Verify data manipulation language (DML), SELECT, and PL/SQL syntax. | Yes | Yes |
Verify data definition language (DDL) syntax. | Yes | No |
Verify DML, SELECT, and PL/SQL semantics (comparison against database schema). | No | Yes |
Verify DDL semantics (comparison against database schema). | No | No |
Command-line only options: The following options can only be specified on the command line (not in properties files) or in the
SQLJ_OPTIONSenvironment variable:-props, -classpath, -help,-help-long,-help-alias,-P-help,-C-help,-version,-version-long,-nConnection options: The following database connections are used to enable/disable online semantics checking:
-user, -password, -url, -default-url-prefix, -driverReporting options: The following options specify the conditions to monitor, including real-time errors, status messages, and whether to explain the cause and action:
-warn, -status, -explain.Line-mapping options: The following options enable tracing runtime errors from the generated Java
.classfile back to the original.sqljsource file:-linemap, -jdblinemap(-jdblinemapshould be used in conjunction with the Sun Microsystemsjdbdebugger; otherwise, use–linemap).Output files and directories options: The following options specify the location for SQLJ output files for encoding
.ser, .class,and.javafiles:-d, -dir.Oracle’s Dynamic Monitoring System (DMS) options:
-instrument, -components.Code generation, optimizations, CHAR comparison, and bind options:
-codegen, -optcols, -optparams, -optparamdefaults, -fixedchar, -ncharconv.
The Oracle Database 10g SQLJ is by default configured to work with Sun Microsystems JDK (compiler and runtime). You can alter this environment using the the following options: -vm, -compiler-executable, -compiler-encoding-flag, -compiler-output-file, and -compiler-pipe-output-flag.
In addition, the -checkfilename option prevents SQLJ from verifying that the souce file name matches the public class.
The –passes option instructs SQLJ to perfom SQLJ parsing and Java parsing in two distinct steps.
Compiler or Java VM options can be passed through the SQLJ translator, using the -J and -C prefixes.
Note
The –vm and -passes options and the -J prefix can only be used on the command line or in the SQLJ_OPTIONS environment variable, not in a properties file.
For ISO-specific code generation (no profile is generated for Oracle-specific code generation), the Oracle SQLJ is also configured to work with the default Oracle profile customizer (oracle.sqlj.runtime.util.OraCustomizer), but an alternative customizer can be specified using the -default-customizer option.
-default-customizer=sqlj.mypav=ckage.Customizer
The profiler options can be passed directly using the –P prefix.
The Oracle SQLJ supports Java compiler options using -C-bootclasspath, -classpath, -d, -depend, -deprecation, -encoding, -Cextdirs, -g, -nowarn, -O, -C-target, -verbose.
$ sqlj -vm=/usr/local/packages/jdk1.4.1/bin/java -compiler-executable=/usr/local/packages/jdk1.4.1/bin/javac -C-bootclasspath=/usr/local/packages/jdk1.4.1/jre/lib/rt.jar -C-extdirs="" -C-target=1.3.1 Demo.sqlj
For ISO code generation, the -profile option enables/disables the processing of the generated profile files (.ser) by the SQLJ profile customizer.
-profile=false
The -ser2class optionconverts .ser files into .class files.
-ser2class=true
The -checksource flag instructs SQLJ to examine source files in the classpath for Java types that appear in SQLJ statements.
-checksource=false
The -offline option enables the offline checker or parser, while the -online option specifies the online checker. The Oracle SQLJ furnishes OracleChecker, a default front-end, which chooses the appropriate checker to use, depending on whether or not you have enabled online checking and which Java Database Connectivity (JDBC) driver you are using.
As sumarized in Table 10.1, the offline parser performs syntax checking of all SQL and PL/SQL statements without connecting to the database (prior to Oracle 9i, syntax checking required a database connection). However, it does not perform verification of operations against the database schema. Setting the -parse option to true enables the offline parser. Also, as summarized in Table 10.1, the online parser performs the following operations:
Validates
SQLJ FETCH,CAST,CALL,SET TRANSACTION,VALUES, andSETstatements.Checks Java expressions in SQLJ statements against SQL types of table columns, of
IN and OUTparameters of stored procedures/functions.Verifies that tables, views, and stored procedures actually exist in the database.
Checks the
NULLability of table columns used in iterators.
SQLJ code generation is governed by the codegen option; the generated code will differ depending on the specified option (iso or oracle).
-codegen=iso:Generates a standard ISO SQLJ code, along with profile file(s).Profile files as serialized resource files (i.e.,
Class_SJProfile0.ser, Class_SJProfile1.ser), which can be further customizedIf the
–ser2classis set totrue,the profile files are generated as Java classes files (i.e., Class_SJProfile0.class, Class_SJProfile1.class); however, theseclassfiles cannot be customized.
There is a mapping between SQLJ executable statements in the application and SQL operations in the profile. The SQLJ runtime reads the profiles and creates connected profiles, with database connections. Then for each database access by the application:
The runtime uses methods in a SQLJ-generated profile-keys class to access the connected profile and read the relevant SQL operations.
The runtime reads the SQL operation from the profile and passes it to the JDBC driver along with input parameters
The JDBC driver executes the SQL operations and transmits returned data, if any, to the SQLJ runtime, which, in turn, passes the returned data to the application.
-codegen=oracle: Generates Oracle-specific code, which supports Oracle extensions; no profile file is generated/needed. The SQLJ runtime performs the following operations:
If this is your first contact with SQLJ, you may want to skip this topic and come back to it later when you really need it. (All of the code samples were run with the default settings.)
The role of the SQLJ customizer is to allow applications to use any vendor-specific database types or features. The SQLJ translator invokes a Java front end called the customizer harness, which acts as a command-line utility. The harness invokes either the default Oracle customizer or a customizer specified through SQLJ option settings (see Table 10.2).
Table 10.2. SQLJ Translator Options
| Flag to treat multiple appearances of the same host variable in a given SQLJ statement as a single bind occurrence. |
|
|
| Prefix that marks options to pass to the Java compiler. |
|
|
| Enables caching of online semantics-checking results (to reduce trips to database). |
|
|
| Specifies whether a warning is issued during translation if a source file name does not correspond to the name of the public class (if any) defined there. |
|
|
| Instructs SQLJ type resolution to examine source files in addition to class files in certain circumstances. |
|
|
| Specifies the CLASSPATH to the JVM and Java compiler; also passed to javac. Use this on the command line only. |
|
|
| Specifies mode of code generation: oracle for Oracle-specific code generation with direct Oracle Java Database Connectivity (JDBC) calls; iso for ISO standard SQLJ code generation. |
|
|
| Enables or disables the Java compilation step, either for .java files generated during the current SQLJ run or for previously generated or other .java files specified on the command line. |
|
|
| Specifies the Java compiler to use. |
|
|
| Instructs SQLJ whether to pass the -encoding setting, if set, to the Java compiler. |
|
|
| Specifies a file to which the Java compiler output should be written. If this option is not set, then SQLJ assumes that compiler output goes to standard output. |
|
|
| Instructs SQLJ whether to set the javac.pipe.output system property, which determines whether the Java compiler prints errors and messages to |
|
|
| Specifies the components (packages and classes) to instrument for use with Oracle Dynamic Monitoring Service (DMS). This assumes instrumentation is enabled through the -instrument option. Use all to instrument all components being translated. |
|
|
| Specifies the output directory for .ser profile files, if applicable, generated by SQLJ, and .class files generated by the compiler; also passed to javac. |
|
|
| Determines the profile customizer to use. Specify a class name. |
|
|
| Sets the default prefix for URL settings. |
|
|
| Passed to javac; enables -checksource. This option requires the -C compiler prefix if set in a properties file. |
|
|
| Passed to javac only. This option requires the -C compiler prefix if set in a properties file. |
|
|
| Sets the output directory for SQLJ-generated .java files. |
|
|
| Determines the JDBC driver class to register. Specify a class name or comma-delimited list of class names. |
|
|
| Specifies the encoding that SQLJ and the compiler will use in globalization support; also passed to javac. You can use -e on the command line. |
|
|
| Flag to request cause and action information to be displayed with translator error messages. |
|
|
| Flag to account for blank padding when binding a string into a WHERE clause for comparison with CHAR data. |
|
|
| Passed to javac; enables -linemap. This option requires the -C compiler prefix if set in a properties file. |
|
|
| Flags to display different levels of information about SQLJ option names, descriptions, and current values. Use these on the command line only. You can use -h instead of -help. |
|
|
| Specifies whether to instrument translated files for use with Oracle DMS. |
|
|
| Variant of -linemap option for use with the Sun Microsystems jdb debugger. |
|
|
| Prefix that marks options to pass to the JVM. Use this on the command line only. |
|
|
| Enables mapping of line numbers between the generated Java class file and the original SQLJ code. |
|
|
| Instructs the sqlj script to echo the full command line as it would be passed to the SQLJ translator, including settings in SQLJ_OPTIONS, without having the translator execute it. This is equivalent to -vm=echo. Use this on the command line only. |
|
|
| Performs bind to NCHAR columns for String host variables. |
|
|
| Passed to javac; sets -warn=none. This option requires the -C compiler prefix if set in a properties file. |
|
|
| Passed to javac; disables -linemap. This option requires the -C compiler prefix if set in a properties file. |
|
|
| Determines the offline checker to use for semantics-checking. Specify a list of fully qualified class names. |
|
|
| Determines the online checker to use for semantics-checking. Specify a fully qualified class name. (You must also set -user to enable online checking.) |
|
|
| Enables iterator column type and size definitions to optimize performance. It is used directly by the translator for Oracle-specific code generation, or forwarded to the Oracle customizer along with user, password, and URL settings for ISO code generation. |
|
|
| Enables parameter size definitions to optimize JDBC resource allocation (used with -optparamdefaults). This is used directly by the translator for Oracle-specific code generation, or forwarded to the Oracle customizer for ISO code generation. |
|
|
| Sets parameter size defaults for particular data types (used with -optparams). This is used directly by the translator for Oracle-specific code generation, or forwarded to the Oracle customizer for ISO code generation. |
|
|
| Prefix that marks options to pass to the SQLJ profile customizer. |
|
|
| Option to enable the offline SQL parser. Possible settings: both, online-only, offline-only, none, or the name of a Java class that implements an alternative parser. Note: Some settings for this option will also disable online semantics-checking, overriding the effect of the -user option. |
|
|
| Instructs the sqlj script to run SQLJ in two separate passes, with compilation in between. Use this on the command line only. |
|
|
| Sets the user password for the database connection for online semantics-checking. You can use -p on the command line. |
|
|
| For ISO code generation, enables or disables the profile customization step for profile files generated during the current SQLJ run. |
|
|
| Specifies a properties file, an alternative to the command line for setting options. (The sqlj.properties is also still read.) Use this on the command line only. |
|
|
| For ISO code generation, instructs SQLJ to translate generated .ser profiles to .class files. |
|
|
| Requests SQLJ to display status messages as it runs. Instead of -status, you can use -v on the command line. |
|
|
| Sets the URL for the database connection for online semantics-checking. |
|
|
| Enables online semantics-checking and sets the user name (and optionally password and URL) for the database connection. You can use -u on the command line. |
|
|
| Passed to javac; enables -status. This requires the -C compiler prefix if set in a properties file. |
|
|
| Flag to display different levels of SQLJ and JDBC driver version information. Use these settings on the command line only. |
|
|
| Specifies the JVM to use for running the SQLJ translator. Use this on the command line only. |
|
|
| Comma-delimited list of flags to enable or disable different SQLJ warnings. Individual flags are cast/ nocast precision/noprecision, nulls/nonulls, portable/noportable, strict/nostrict, and verbose/ noverbose. The global flag is all/ none. |
|
|
[*] Use directory of | |||
The translator creates a profile as follows:
As an instance of the
sqlj.runtime.profile.Profileclass.It inserts information about embedded SQL operations into the profile.
It serializes the profile object (in its original nonserialized state) into a Java resource file (
.ser).
A customizer is a JavaBeans, which implement the sqlj.runtime.profile.util.ProfileCustomizer interface (which specifies a customize() method). The Oracle customizer is defined in the oracle.sqlj.runtime.OraCustomizer class.
The Oracle customizer implements the following options:
compat: Displays the version-compatibility information.force: Customizes even if a valid customization already exists.optcols: Enables iterator column type and size definitions to optimize performance.optparams: Enables parameter size definitions to optimize JDBC resource allocation (used in conjunction withoptparamdefaults).optparamdefaults: Sets parameter size defaults for particular data types (used in conjunction withoptparams).fixedchar: EnablesCHARcomparisons with blank padding forWHEREclauses.showSQL: Displays SQL statement transformations.stmtcache: Sets the statement cache size (the number of statements that can be cached for each connection during runtime) for performance optimization, or set it to zero to disable statement caching.summary: Displays a summary of Oracle features used in your application.
The profile information can be printed using the -P-print option. Any output is written to the standard output. See the Oracle SQLJ documentation for more details.
The following exit codes are returned by the SQLJ translator:
0: No error in execution
1: Error in SQLJ execution
2: Error in Java compilation
3: Error in profile customization
4: Error in class instrumentation, the optional mapping of line numbers from
.sqljsource file to.classfile5: Error in
ser2classconversion, the optional conversion of profile files from.serfiles to.classfiles
The Oracle SQLJ runtime is certified against JDK 1.2.x, 1.3.x, or 1.4.x, and delivered through the following JARS:
runtime12.jar:[2] For use in all environments including Oracle 9i or Oracle 10g JDBC drivers against Oracle database 9i Release 2 and Oracle database 10g Releases 1 and 2, and middle-tier environment.runtime12ee.jar: Was furnished in previous releases for middle-tier environments (i.e., Java Enterprise Edition containers/servers); has been deprecated and subsummed byruntime12.jar.The database-resident runtime for running SQLJ in the database
It supports any standard JDBC driver; however, the Oracle JDBC driver is required by your application whenever you use the Oracle customizer during translation, even if you do not use Oracle extensions in your code. It furnishes full ISO SQLJ functionality if your application has no customizations.
The following packages are used internally by the runtime:
sqlj.runtime.profile:Contains interfaces that define SQLJ ISO profilessqlj.runtime.profile.ref:Contains classes that implement the ISO profiles interfacessqlj.runtime.error:For generic/standard ISO SQLJ error messagesoracle.sqlj.runtime.error:For Oracle SQLJ specific error messages
SQLJ and JPublishr are part of the Oracle Database 10g Client (from the client CD or downloaded from OTN). See Chapter 13 “Installing JPublisher” for SQLJ install. It creates the $ORACLE_HOME/sqlj directory with lib, doc, and demo subdirectories.
The following command checks the status of SQLJ in your environment:
$ sqlj -version Oracle SQLJ Release 10.2.0.1.0 Production Copyright (c) 1997, 2005, Oracle Corporation. All Rights Reserved. $
In addition, Oracle furnishes the following code samples under $ORACLE_HOME/sqlj/demo/ to check your environment:
connect.properties // to be customized to your environmentTestInstallCreateTable.javaTestInstallJDBC.javaTestInstallSQLJ.sqljTestInstallSQLJChecker.sqlj
And here is how to use them to check your environment:
// create a table for test purposes $ javac TestInstallCreateTable.java $ java -Doracle.net.tns_admin=$TNS_ADMIN TestInstallCreateTable SALES table created $ // Check JDBC install $ javac TestInstallJDBC.java $ java -Doracle.net.tns_admin=$TNS_ADMIN TestInstallJDBC Hello JDBC! $ // Check the SQLJ translator, runtime, and the application $ sqlj TestInstallSQLJ.sqlj $ java -Doracle.net.tns_admin=$TNS_ADMIN TestInstallSQLJ Hello, SQLJ! $
Otherwise, ensure that your environment meets the following requirements:
The executables (script and binaries) are available under
$ORACLE_HOME/bin.The
PATHenvironment variable must include$ORACLE_HOME/bin.The
CLASSPATHenvironment variable must include the following:The JDBC jars (ojdbc14.jar, or classes12.jar)
ORACLE_HOME /sqlj/lib/translator.jar
ORACLE_HOME /sqlj/lib/runtime12.jar
The
connect.propertiesfile (a sample is located at$ORACLE_HOME/sqlj/demo/)allows configuring and customizing the connection to the Oracle database for the runtime.
$ cat connect.properties # Users should uncomment one of the following URLs or add their own. # (If using Thin, edit as appropriate.) sqlj.url=jdbc:oracle:thin:@localhost:1521/myservice #sqlj.url=jdbc:oracle:oci:@ # Uncommenting User name triggers the onlike checking sqlj.user=scott sqlj.password=tiger $ // And here is how it is referenced in a SQLJ source code Oracle.connect(mySQLJ.class, "connect.properties");
Listing 10.1 is the SQLJ version of the JDBCPrimer.java in Chapter 6:
Example 10.1. SqljPrimer.sqlj
import java.sql.SQLException;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;
// specify the iterator for the query
#sql iterator EnameIter (String ENAME);
class SqljPrimer
{
public static void main (String args[])
{
try
{
Oracle.connect(SqljPrimer.class, "connect.properties");
EnameIter iter;
#sql iter = {SELECT ENAME FROM EMP where EMPNO ='7934'};
if (iter.next()) {
System.out.println("Employee# 7934 is " + iter.ENAME());
}
} catch (SQLException e){
System.err.println("Exception: " + e);
}
}
}
connect.properties
==================
# Users should uncomment one of the following URLs or add their own.
#
#sqlj.url=jdbc:oracle:thin:@localhost:1521:sid
sqlj.url=jdbc:oracle:thin:@inst1
#sqlj.url=jdbc:oracle:oci8:@
# User name and password here (edit to use different user/password)
sqlj.user=scott
sqlj.password=tiger
~
$ sqlj SqljPrimer.sqlj
$ java -Doracle.net.tns_admin=$TNS_ADMIN SqljPrimer
Employee# 7934 is MILLER
$Contrast this with the JDBC equivalent and you’ll get a feel for the conciseness and simplicity of SQLJ.
Chapter 5 showed you how to run non-Java languages in the database using the embedded Java VM;[3] SQLJ is one of these languages. This section looks at translating and running SQLJ directly in the database.
The database-resident Java VM (also known as OracleJVM) embeds a SQLJ translator and runtime.You can translate your SQLJ source on the client side and then load the resulting class and resource file(s) in the database using loadjava; however, the Java VM in the database is J2SE 1.4 compatible as of Oracle Database 10g Releases 1 and 2. Alternatively, the embedded SQLJ translator automatically translates SQLJ source files directly in the database upon loading. Note that the -codegen option is not supported in the database; therefore, only Oracle-specific code can be generated in the server. However, ISO standard code can be generated on the client side and then loaded in the database. See the next section on the differences in behavior of SQLJ in the database versus SQLJ on the client side.
The following command checks that the SYS.SQLJUTL package is loaded in the database:
SQL> describe sys.sqljutl FUNCTION BOOL2INT RETURNS NUMBER(38) Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- B BOOLEAN IN FUNCTION CHAR2IDS RETURNS INTERVAL DAY TO SECOND Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- CH CHAR IN FUNCTION CHAR2IYM RETURNS INTERVAL YEAR TO MONTH Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- CH CHAR IN PROCEDURE GET_TYPECODE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- TID RAW IN CODE NUMBER OUT CLASS VARCHAR2 OUT TYP NUMBER OUT FUNCTION HAS_DEFAULT RETURNS NUMBER Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- OID NUMBER IN PROC CHAR IN SEQ NUMBER IN OVR NUMBER IN FUNCTION IDS2CHAR RETURNS CHAR Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- IV INTERVAL DAY TO SECOND IN FUNCTION INT2BOOL RETURNS BOOLEAN Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- I NUMBER(38) IN FUNCTION IYM2CHAR RETURNS CHAR Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- IV INTERVAL YEAR TO MONTH IN FUNCTION URI2VCHAR RETURNS VARCHAR2 Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- URI URITYPE IN SQL>
Otherwise, run the sqljutl.sql script to install it.
The following query checks the availability of the SQLJ translator in the database (using system or a DBA account)
SQL> select object_type, status from all_objects where 2 dbms_java.longname(object_name) ='oracle/sqlj/checker/ JdbcVersion'; OBJECT_TYPE STATUS ------------------- ------- JAVA CLASS VALID SYNONYM VALID SQL>
Similar to Java in the database (covered in Part I), the loadjava utility can be used to load .sqlj file(s), as well as SQLJ-related .class, .jar, and .ser files. (See Listing 10.2.)
The -resolve option of loadjava performs the translation and compilation of the SQLJ application as it is loaded.
===========================
import java.sql.Date;
import java.sql.SQLException;
class SqljDb
{
public static void main (String argv[])
{
// use the default context and the default connection
try {
dbprint("Hello from SQLJ in the Database!");
Date today;
#sql {select sysdate into :today from dual};
dbprint("Today is " + today);
} catch (java.sql.SQLException e) {
dbprint("Got this error: " + e);
}
}
static void dbprint(String s) {
try {
#sql { call dbms_output.put_line(:s)};
} catch (SQLException e) {}
}
}
~
$ loadjava -u scott/tiger -r -f -v SqljDb.sqlj
arguments: '-u' 'scott/tiger' '-r' '-f' '-v' 'SqljDb.sqlj'
creating : source SqljDb
loading : source SqljDb
resolving: source SqljDb
Classes Loaded: 0
Resources Loaded: 0
Sources Loaded: 1
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0
$
sqljdb.sql
==========
create or replace procedure sqljdb as language java
name 'SqljDb.main (java.lang.String[])';
/
set serveroutput on
call sqljdb()
/
exit
$ sqlplus scott/tiger @sqljdb
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Dec 30 17:08:59 2005
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
Procedure created.
Hello from SQLJ in the Database!
Today is 2005-12-30
Call completed.
Disconnected from Oracle Database 10g Enterprise Edition Release
10.2.0.1.0 – Production
With the Partitioning and Data Mining options
$Conversely, the dropjava utility allows removing SQLJ sources, Java sources, Java classes, and resource files that have been previously loaded by loadjava.
Before running loadjava, however, you may set SQLJ translator options. The translator and compiler options are maintained in a schema-specific JAVA$OPTIONS table (see Chapter 3 for more details). The get_compiler_option(), set_compiler_option(), and reset_compiler_option() in dbms_java let you manipulate the options:
sql> execute dbms_java.set_compiler_option('x.y', 'online', 'true'),
sql> execute dbms_java.set_compiler_option('x.y', 'encoding',
'SJIS'),However, the encoding option can be set on the loadjava command line, as follows:
$ loadjava -user scott/tiger -resolve -encoding SJIS mySQLJ.sqlj
Similar to Java in the database, in order for SQL, PL/SQL, and JDBC to invoke your SQLJ application, the top-level methods and their signature(s) must be “published” to the RDBMS catalog. Chapter 3 provided extensive coverage of call specs for Java in the database, and the same mechanisms work for SQLJ. Alternatively, for smart-but-lazy developers, JPublisher (covered later) does this for you!
You need to be aware of the following differences when developing and deploying SQLJ applications in the database.
As mentioned in Part I,
auto-commitis not supported (i.e.,false) in the server-side JDBC driver(s). You must explicitly commit or roll back your changes, as follows:#sql { COMMIT }; #sql { ROLLBACK };A default connection is automatically initialized for SQLJ programs; therefore, setting the default connection context to
null, as follows, only restates the default connection context:(i.e., DefaultContext.setDefaultContext(null)) :For standard ISO code, unlike client-side SQLJ, closing the JDBC connection object does not reclaim statement handles. In order to clean up the statements cache, you must close the connection context instance, as follows:
DefaultContext ctx = new DefaultContext(conn); // Use JDBC connection #sql [ctx] { SQL operation }; ... // release the connection context instance ctx.close(sqlj.runtime.ConnectionContext.KEEP_CONNECTIO N); ...For Oracle-specific code, the statements are cached within the JDBC statement cache and are automatically reclaimed when you “close” the default JDBC connection (even though the connection handles remain active). In order for Oracle-specific code to interoperate with ISO SQLJ code, you must use an explicit
ExecutionContextas follows:public static ExecutionContext exec = new ExecutionContext(); ... #sql [exec] { SQL operation };In the server, the
onlineoption is only a flag that enables online checking using a default checker. On a client, the-onlineoption specifies which checker to use, but the-useroption enables online checking.Both online semantics checking and offline parsing are enabled in the server by default, equivalent to the default
-parse=bothsetting on a client. You can override this to disable online semantics checking through theonlineoption, but you cannot disable offline parsing.Similar to Java, the same SQLJ code can be used both on the server side and the client side; to determine where the code is running, use the following code fragment:
if (System.getProperty("oracle.server.version") != null { // (running in server) } ...In general, when a statement tries to use an execution context that is already in use by another statement, that statement will be blocked until the other statement completes. However, in the server, SQLJ allows recursive calls to use the same execution context.
Similar to Java error reporting, SQLJ errors are reported in the
USER_ERRORStable in the user schema. TheTEXTcolumn can be retrieved to get the text of the error message. However, withloadjavayou can log the error messages originating from from the server-side translator using the-fileout<file> option.
Now that we have seen the rationales for SQLJ in general, the additional benefits of Oracle extensions, the architecture of Oracle’s implementation, a review of the translator options, the runtime configuration, and SQLJ in the database, it’s time to look at the key structures of a SQLJ program, in the next chapter.
[2] runtime.jar is furnished for older Oracle JDBC drivers against Oracle 8i Release 8.1.7; it does not provide all ISO SQLJ functionality or Oracle extensions.