Skip Headers

Oracle9i SQLJ Developer's Guide and Reference
Release 2 (9.2)

Part Number A96655-01
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index


Go to previous page Go to next page

Getting Started

This chapter guides you through the basics of testing your Oracle SQLJ installation and configuration and running a simple application.

Note that if you are using an Oracle database and Oracle JDBC driver, you should also verify your JDBC installation according to the Oracle9i JDBC Developer's Guide and Reference.

This chapter discusses the following topics:

Assumptions and Requirements

This section discusses basic assumptions about your environment and requirements of your system so that you can run Oracle SQLJ, covering the following topics:

Assumptions About Your Environment

The following assumptions are made about the system on which you will be running Oracle SQLJ.

Requirements for Using Oracle SQLJ

The following are required to use Oracle SQLJ:


Also be aware of the following:

  • In SQLJ release 8.1.6 and earlier, there was only one runtime library, and the runtime JAR/ZIP file was a subset of the translator JAR/ZIP file. In Oracle9i this is no longer the case. You must now specify both a runtime file and the translator file in your classpath.
  • The runtime-nonoracle library provides portability across different Java and JDBC environments, but does not support Oracle-specific functionality.
  • The runtime library provides flexibility across different Java and Oracle JDBC environments, but does not support all ISO SQLJ functionality.
  • For ISO SQLJ-compliant support for JDBC 2.0 types such as java.sql.Ref, Clob, Blob, Struct, and SQLData, use the runtime12 or runtime12ee library with JDK 1.2 or J2EE and an Oracle9i JDBC driver.
  • If you will be running only SQLJ applications that have already been translated, compiled, and customized, you will not need the translator JAR/ZIP file.
  • The translator and runtime JAR files are compressed. The ZIP files are uncompressed, however, for maximum portability.

Oracle SQLJ Environment: Key Scenarios and Guidelines

To ensure you have a fully working environment, you must consider several aspects of your environment--SQLJ and its code generation mode, JDBC, and the JDK. This section first discusses the two main Oracle scenarios of supported combinations, and then discusses some important general guidelines.

Also see "Environment Issues and Limitations" for related information.


Code generation is determined by the Oracle SQLJ -codegen option. See "Code Generation (-codegen)" for more information.

Scenario 1: Oracle-Specific Code

This section documents a typical environment setup for Oracle-specific code generation. Note that in this case, the SQLJ generic runtime library is not an option.

Scenario 2: ISO Standard Code

This section documents a typical environment setup for ISO standard code generation.

  1. SQLJ code generation: -codegen=iso
  2. SQLJ library during translation:
    • SQLJ runtime11 library with JDK 1.1


    • SQLJ runtime12 or runtime12ee library with JDK 1.2 or higher

    or, for Oracle JDBC versions 8.1.7 and prior:

    • SQLJ generic runtime library with any JDK 1.1 or higher
  3. JDBC and JDK: any Oracle JDBC version with any JDK 1.1 or higher
  4. SQLJ library during runtime: same library as for translation

Environment Scenarios: Key Guidelines

Regarding your environment for running Oracle SQLJ, be aware of the following important guidelines and considerations:

Environment Issues and Limitations

This section discusses a key environmental issue--JDK migration regarding type maps--and lists resulting limitations and some additional environment issues and limitations. These are among the considerations in planning the typical scenarios outlined in "Oracle SQLJ Environment: Key Scenarios and Guidelines".

  • Where the generic runtime library is discussed, an Oracle JDBC environment is still assumed (version 8.1.7 or prior). For non-Oracle JDBC environments, substitute the runtime-nonoracle library.
  • The runtime and runtime-nonoracle libraries are intended mainly for backward compatibility. They do not support Oracle-specific features.

JDK Migration Issues Regarding Type Maps

The type for JDBC type maps changed between JDK 1.1.x and JDK 1.2.x, from java.util.Dictionary to java.util.Map.

The getTypeMap() method of all SQLJ connection context classes returns a type map instance. The Java type system requires that an implemented method must return exactly the type specified in an underlying interface (in this case, the JDBC Connection interface). Consider the following method signature:

java.util.Hashtable getTypeMap() { ... }

This would seem to implement both the JDK 1.1 and JDK 1.2 specifications, because Hashtable extends Dictionary and implements Map; however, it is not acceptable to the Java type system.

This incompatibility between the JDK 1.1 and JDK 1.2 interfaces has the following consequences if your SQLJ code declares connection context types:

Other Environment Issues and Limitations

The following list notes additional environmental issues and limitations, mostly related to the type map issues discussed above.

Oracle SQLJ Backward Compatibility

Be aware of the following regarding Oracle SQLJ backward compatibility:

Checking the Installation and Configuration

Once you have verified that the above assumptions and requirements are satisfied, you must check your Oracle SQLJ installation.

Check for Installed Directories and Files

Verify that the following directories have been installed and are populated.

Directories for Oracle JDBC

If you are using one of the Oracle JDBC drivers, refer to the Oracle9i JDBC Developer's Guide and Reference for information about JDBC files that should be installed on your system.

Directories for Oracle SQLJ

Installing the Oracle9i Java environment will include, among other things, installing a sqlj directory under your [Oracle_Home] directory. The sqlj directory contains the following subdirectories:

In addition, directly under [Oracle_Home] is the following directory, containing utilities for all Java product areas:

Check that all these directories have been created and populated, especially lib and bin.

The structure is similar if you download SQLJ from a Web site, such as the Oracle Technology Network address. The bin directory, with both SQLJ and JPublisher executable files, is directly under the sqlj directory.

Set the Path and Classpath

Make sure your PATH and CLASSPATH environment variables have the necessary settings for Oracle SQLJ (and Oracle JDBC if applicable).

Path and Classpath for Oracle JDBC

If you are using one of the Oracle JDBC drivers, you will need the Oracle JDBC classes JAR/ZIP file that is appropriate for your environment.

JDK 1.1-compatible classes are in classes111.jar or .zip; JDK 1.2 (or higher) compatible classes are in classes12.jar or .zip; JDK 1.4-compatible classes are in ojdbc14.jar or .zip. Presuming you use a Sun Microsystems JDK, make sure the appropriate JAR/ZIP file name is in your classpath setting. There may also be alternative JDBC driver libraries available, such as classes12_g.jar, which permits driver debugging information to be printed.

For more information about libraries and required path and classpath settings for Oracle JDBC, refer to the Oracle9i JDBC Developer's Guide and Reference.

Path and Classpath for Oracle SQLJ

Set your PATH and CLASSPATH variables as follows for Oracle SQLJ.

Path Setting

To be able to run the sqlj script (which invokes the SQLJ translator) without having to fully specify its path, verify that your PATH environment variable has been updated to include the following:


Use backward slashes for Windows. Replace [Oracle_Home] with your actual Oracle home directory.

Classpath Setting

Update your CLASSPATH environment variable to include the current directory as well as the following (either .jar or .zip):


Use backward slashes for Windows. Replace [Oracle_Home] with your actual 
Oracle home directory.

In addition, you must include one of the following runtime libraries in your classpath (either .jar or .zip):


See "Requirements for Using Oracle SQLJ" regarding which runtime library to use for your JDBC driver and Java environment.


You will not be able to run the SQLJ translator if you do not add a runtime library. You must specify a runtime library as well as the translator library in your classpath.

To see if SQLJ is installed correctly, and to see version information for SQLJ, JDBC, and Java, execute the following command:

sqlj -version-long

Verify Installation of sqljutl Package


This step is relevant only for online checking during translation, and is applicable only if you are using SQLJ stored procedures or functions with a pre-8.1.5 Oracle database (or an 8.1.5 or later database that was installed without a server-side JVM).

The package sqljutl is required for online checking of stored procedures and functions in an Oracle database. For Oracle release 8.1.5 and later, it should have been installed automatically under the SYS schema during installation of your database server-side JVM. To verify the installation of sqljutl, issue the following SQL command (from SQL*Plus, for example):

describe sys.sqljutl

This should result in a brief description of the package. If you get a message indicating that the package cannot be found, then you must install it manually. To do so, use SQL*Plus to run the sqljutl.sql script, which is located as follows:


(The sqljutl package is installed in the SYS schema.)

Consult your installation instructions if necessary.

Testing the Setup

You can test your database, JDBC, and SQLJ setup using demo applications defined in the following source files:

There is also a Java properties file,, that helps you set up your database connection. You must edit this file to set appropriate user, password, and URL values.

These demo applications are provided with your SQLJ installation in the demo directory:


You must edit some of the source files as necessary and translate/compile them as appropriate (as explained in the following subsections).

The demo applications provided with the Oracle SQLJ installation refer to tables on an Oracle account with user name scott and password tiger. Most Oracle installations have this account. You can substitute other values for scott and tiger if desired.


Running the demo applications requires that the demo directory be the current directory and that the current directory (".") be in your classpath, as described earlier.

Set Up the Runtime Connection

This section describes how to update the file to configure your Oracle connection for runtime. The file is in the demo directory and looks something like the following:

# Users should uncomment one of the following URLs or add their own.
# (If using Thin, edit as appropriate.)
# User name and password here

(User scott and password tiger are used for the demo applications.)

Connecting with an Oracle JDBC Driver

With Oracle9i, use "oci" in the connect string for the Oracle JDBC OCI driver in any new code. For backward compatibility, however, "oci8" is still accepted, so you do not have to change existing code. (Also, "oci7" is accepted for Oracle JDBC release 7.3.4.)

If you are using the JDBC Thin driver, then uncomment the thin URL line in and edit it as appropriate for your Oracle connection. Use the same URL that was specified when your JDBC driver was set up.

Connecting with a non-Oracle JDBC Driver

If you are using a non-Oracle JDBC driver, then add a line to to set the appropriate URL, as follows:


Use the same URL that was specified when your JDBC driver was set up.

You must also register the driver explicitly in your code. This is performed automatically in the demo and test programs if you use an Oracle JDBC driver. See "Driver Selection and Registration for Runtime".

In addition, in the SQLJ demo programs, you must replace the following code:

Oracle.connect(url, user, password);

with the following:

DriverManager.registerDriver(new yourdriver());
Connection conn = DriverManager.getConnection(url, user, password);
DefaultContext.setDefaultContext(new DefaultContext(conn));

Create a Table to Verify the Database

The following tests assume a table called SALES. If you compile and run TestInstallCreateTable as follows, it will create the table for you if the database and your JDBC driver are working and your connection is set up properly in the file:

java TestInstallCreateTable


If you already have a table called SALES in your schema and do not want it altered, edit to change the table name. Otherwise, your original table will be dropped and replaced.

If you do not want to use TestInstallCreateTable, you can instead create the SALES table using the following command in a command-line processor (such as SQL*Plus):

      ITEM_NAME CHAR(30),

Verify the JDBC Driver

If you want to further test the Oracle JDBC driver, use the TestInstallJDBC demo.

Verify that your connection is set up properly in as described above, then compile and run TestInstallJDBC:

java  TestInstallJDBC 

The program should print:

Hello, JDBC! 

Verify the SQLJ Translator and Runtime

Now translate and run the TestInstallSQLJ demo, a SQLJ application that has similar functionality to TestInstallJDBC. Use the following command to translate the source:

sqlj  TestInstallSQLJ.sqlj

After a brief wait you should get your system prompt back with no error output. Note that this command also compiles the application and customizes it to use an Oracle database.

In a UNIX environment, the sqlj script is in [Oracle_Home]/bin, which should already be in your path as described above. (On Windows, use the sqlj.exe executable in the bin directory.) The SQLJ translator JAR/ZIP file has the class files for the SQLJ translator and runtime. It is located in [Oracle_Home]/sqlj/lib and should already be in your classpath as described above.

Now run the application:

java  TestInstallSQLJ

The program should print:

Hello, SQLJ!

Verify the SQLJ Translator Connection to the Database

If the SQLJ translator is able to connect to a database, then it can provide online semantics-checking of your SQL operations during translation. The SQLJ translator is written in Java and uses JDBC to get information it needs from a database connection that you specify. You provide the connection parameters for online semantics-checking using the sqlj script command line or using a SQLJ properties file (called by default).

While still in the demo directory, edit the file and update, comment, or uncomment the sqlj.password, sqlj.url, and sqlj.driver lines, as appropriate, to reflect your database connection information, as you did in the file. For some assistance, see the comments in the file.

Following is an example of what the appropriate driver, URL, and password settings might be if you are using the Oracle JDBC OCI driver. The user name will be discussed next.


Online semantics-checking is enabled as soon as you specify a user name for the translation-time connection. You can specify the user name either by uncommenting the sqlj.user line in the file or by using the -user command-line option. The user, password, URL, and driver options all can be set either on the command line or in the properties file. This is explained in "Connection Options".

You can test online semantics-checking by translating the file TestInstallSQLJChecker.sqlj, located in the demo directory, as follows (or using another user name if appropriate):

sqlj -user=scott TestInstallSQLJChecker.sqlj

This should produce the following error message if you are using one of the Oracle JDBC drivers:

TestInstallSQLJChecker.sqlj:41: Warning: Unable to check SQL query. Error 
returned by database is: ORA-00904: invalid column name

Edit TestInstallSQLJChecker.sqlj to fix the error on line 41. The column name should be ITEM_NAME instead of ITEM_NAMAE. Once you make this change, you can translate and run the application without error using the following commands:

sqlj -user=scott TestInstallSQLJChecker.sqlj
java  TestInstallSQLJChecker

If everything works, this prints:

Hello, SQLJ Checker!

Go to previous page Go to next page
Copyright © 1999, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Table Of Contents
Go To Index

Master Index