|Oracle9i SQLJ Developer's Guide and Reference
Release 2 (9.2)
Part Number A96655-01
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:
This section discusses basic assumptions about your environment and requirements of your system so that you can run Oracle SQLJ, covering the following topics:
The following assumptions are made about the system on which you will be running Oracle SQLJ.
java) and your Java compiler (typically
To translate and run Oracle SQLJ applications on a Sun JDK, you must use a JDK 1.2.x (or higher) or JDK 1.1.x version, with an appropriate JDBC driver. There are Oracle JDBC Thin and OCI driver versions that work with any of these JDK scenarios.
For more information, see "Oracle SQLJ Environment: Key Scenarios and Guidelines".
If you are using an Oracle database and Oracle JDBC driver, then you should complete the steps in Chapter 2, "Getting Started", of the Oracle9i JDBC Developer's Guide and Reference.You can also refer to Chapter 1, "Overview", of that document for information about the Oracle JDBC drivers and how to decide which is appropriate for your situation.
If you are using a non-Oracle JDBC driver, you must do the following:
The following are required to use Oracle SQLJ:
java.sqlJDBC interfaces from Sun Microsystems
Oracle SQLJ works with any standard JDBC driver.
Translator-related classes are available in the file:
Several SQLJ runtime versions are available. You must select a runtime version that is compatible with your Java environment and JDBC driver (these are all in
.zip)--for use with Oracle9i JDBC drivers under JDK 1.2.x or higher, providing full ISO SQLJ functionality
.zip)--for use with Oracle9i JDBC drivers in a J2EE environment (using JDK 1.2.x or higher), providing full ISO SQLJ functionality
.zip)--for use with Oracle9i JDBC drivers under JDK 1.1.x
.zip)--for use with older Oracle JDBC drivers and any JDK environment (intended for Oracle JDBC release 8.1.7 and prior)
.zip)--for use with non-Oracle JDBC drivers and any JDK environment
Also be aware of the following:
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
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.
If you might be running against either the 9.0.1 or 9.2 JDBC driver, translate against 9.0.1. In general, compile against the oldest driver you might use.
This section documents a typical environment setup for ISO standard code generation.
or, for Oracle JDBC versions 8.1.7 and prior:
Regarding your environment for running Oracle SQLJ, be aware of the following important guidelines and considerations:
runtime11library under JDK 1.1. This allows the option of using the
runtime12eelibrary under JDK 1.2 at runtime. If you translate against JDK 1.2, several JDBC 2.0 APIs that are not supported under JDK 1.1 are compiled into your class files.
Generated code is optimized toward the JDBC driver in the classpath during translation.
-contextoptions. See the Oracle9i JPublisher User's Guide for information about these options.
You can also consider using the
runtime library for cross-compatibility, but this library has disadvantages (such as not supporting the Oracle9i
oracle.jdbc.OracleXXX interfaces, which causes problems in the middle tier).
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".
The type for JDBC type maps changed between JDK 1.1.x and JDK 1.2.x, from
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:
This would seem to implement both the JDK 1.1 and JDK 1.2 specifications, because
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:
runtime11library, or when you compile under any JDK when translating against the SQLJ generic
runtimelibrary, the generated code will return
Dictionaryinstances. The code should run under both JDK 1.1.x and JDK 1.2.x or higher. (Under JDK 1.2, the SQLJ runtime uses Java Reflection as necessary.)
runtime12eelibrary, you will not be able to run under JDK 1.1.x (because
java.util.Map, among other things, was not defined in JDK 1.1.x).
.sqljfiles under JDK 1.1.x, the generated
.javafiles will not compile under JDK 1.2.x or higher. (This is relevant if you run SQLJ with the
-compile=falsesetting, in order to translate and compile separately.) Likewise, if you translate under JDK 1.2.x or higher, the generated
.javafiles will not compile under JDK 1.1.x.
If you use JPublisher, the default setting is to use
The following list notes additional environmental issues and limitations, mostly related to the type map issues discussed above.
-codegen=oraclesetting, you cannot use the generic
runtimelibrary. Use the
runtime11library (for JDK 1.1) or the
runtime12eelibrary (for JDK 1.2 or higher).
-codegen=isoand the generic
runtime11library) or all modules must be translated against JDK 1.2 or higher (using the
runtime12eelibrary). There cannot be a mixture. This includes situations where you use JPublisher with a nondefault
oracle.jdbc2package, which Oracle SQLJ does not support. Oracle JDBC used this package to support JDBC 2.0 types under JDK 1.1.x. If you translate under JDK 1.2, you can run the application under JDK 1.2 or higher.
Be aware of the following regarding Oracle SQLJ backward compatibility:
-codegen=oraclesetting) must be created and executed using the
Also remember that Oracle-specific code is not portable.
-codegen=iso) can be created and executed against an earlier Oracle JDBC release using the current
Regarding Oracle JDBC backward compatibility to prior database releases, any given Oracle JDBC driver release is compatible with any Oracle database release from 7.3.4 up to the release number of the JDBC driver. For more information see the Oracle9i JDBC Developer's Guide and Reference.
Once you have verified that the above assumptions and requirements are satisfied, you must check your Oracle SQLJ installation.
Verify that the following directories have been installed and are populated.
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.
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:
demo(demo applications, including some referenced in this chapter)
.zipfiles containing class files for SQLJ)
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
The structure is similar if you download SQLJ from a Web site, such as the Oracle Technology Network
http://otn.oracle.com address. The
bin directory, with both SQLJ and JPublisher executable files, is directly under the
Make sure your
CLASSPATH environment variables have the necessary settings for Oracle SQLJ (and Oracle JDBC if applicable).
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
.zip; JDK 1.2 (or higher) compatible classes are in
.zip; JDK 1.4-compatible classes are in
.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.
CLASSPATH variables as follows for Oracle SQLJ.
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 environment variable to include the current directory as well as the following (either
[Oracle_Home]/sqlj/lib/translator.jar 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
[Oracle_Home]/sqlj/lib/runtime12.jar [Oracle_Home]/sqlj/lib/runtime12ee.jar [Oracle_Home]/sqlj/lib/runtime11.jar [Oracle_Home]/sqlj/lib/runtime.jar [Oracle_Home]/sqlj/lib/runtime-nonoracle.jar
See "Requirements for Using Oracle SQLJ" regarding which runtime library to use for your JDBC driver and Java environment.
To see if SQLJ is installed correctly, and to see version information for SQLJ, JDBC, and Java, execute the following command:
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).
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):
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:
sqljutl package is installed in the
Consult your installation instructions if necessary.
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,
connect.properties, 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
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
tiger if desired.
Running the demo applications requires that the
This section describes how to update the
connect.properties 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.) #sqlj.url=jdbc:oracle:thin:@localhost:1521:ORCL #sqlj.url=jdbc:oracle:oci:@ # # User name and password here sqlj.user=scott sqlj.password=tiger
scott and password
tiger are used for the demo applications.)
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
connect.properties and edit it as appropriate for your Oracle connection. Use the same URL that was specified when your JDBC driver was set up.
If you are using a non-Oracle JDBC driver, then add a line to
connect.properties 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:
with the following:
DriverManager.registerDriver(new yourdriver()); Connection conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(false); DefaultContext.setDefaultContext(new DefaultContext(conn));
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
If you already have a table called
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
CREATE TABLE SALES ( ITEM_NUMBER NUMBER, ITEM_NAME CHAR(30), SALES_DATE DATE, COST NUMBER, SALES_REP_NUMBER NUMBER, SALES_REP_NAME CHAR(20));
If you want to further test the Oracle JDBC driver, use the
Verify that your connection is set up properly in
connect.properties as described above, then compile and run
The program should print:
Now translate and run the
TestInstallSQLJ demo, a SQLJ application that has similar functionality to
TestInstallJDBC. Use the following command to translate the source:
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:
The program should print:
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
sqlj.properties by default).
While still in the
demo directory, edit the file
sqlj.properties and update, comment, or uncomment the
sqlj.driver lines, as appropriate, to reflect your database connection information, as you did in the
connect.properties file. For some assistance, see the comments in the
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
sqlj.properties 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):
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
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:
If everything works, this prints: