This chapter guides you through the basics of testing your Oracle SQLJ installation and configuration and running a simple application.
This chapter discusses the following topics:
This section discusses basic assumptions about your environment and requirements of your system so that you can run SQLJ, covering the following topics:
The following assumptions are made about the system on which you will be running the Oracle SQLJ implementation:
You have a standard Java environment that is operational on your system. This would typically be using a Sun Microsystems Java Development Kit (JDK), but other implementations of Java will work. Ensure that you can run Java (typically java
) and the Java compiler (typically javac
).
To translate and run SQLJ applications on a Sun JDK, you must use JDK 1.2.x, 1.3.x, or 1.4.x. You must also choose an appropriate JDBC driver. There are Oracle JDBC Thin and OCI driver releases that work with each supported JDK version.
Note:
A Java run-time environment (JRE), such as the one installed with Oracle Database 11g, is not by itself sufficient for translating SQLJ programs. However, a JRE is sufficient for running SQLJ programs that have already been translated and compiled.You can already run JDBC applications in your environment.
If you are using Oracle Database and Oracle JDBC drivers, then you should complete the steps in Chapter 2, "Getting Started", of the Oracle Database JDBC Developer's Guide and Reference.You can also refer to Chapter 1, "Introducing JDBC", of that document for information about the Oracle JDBC drivers and how to decide which is appropriate for your situation.
The following are required to use the Oracle SQLJ implementation:
A JDBC driver implementing the standard java.sql
JDBC interfaces
The Oracle SQLJ product works with any standard JDBC driver.
A database system that is accessible using your JDBC driver
Class files for the SQLJ translator
Translator-related classes are available in the following file:
ORACLE_HOME
/sqlj/lib/translator.jar
Class files for the SQLJ run time.
Several SQLJ run time versions are available. You must select a run time version that is compatible with your Java environment and JDBC driver (these are all in ORACLE_HOME
/sqlj/lib
):
runtime12.jar
: For use with Oracle9i or Oracle10g JDBC drivers under JDK 1.2.x, 1.3.x, or 1.4.x, providing full ISO SQLJ functionality
runtime12ee.jar
: For use with Oracle9i or Oracle10g JDBC drivers in a Java2 Platform, Enterprise Edition (J2EE) environment that includes JDK 1.2.x, 1.3.x, or 1.4.x, providing full ISO SQLJ functionality
runtime.jar
: For use with older Oracle JDBC drivers (intended for Oracle8i release 8.1.7)
Notes:
Also be aware of the following:SQLJ installations with Oracle9i Application Server provide only the runtime12ee.jar
file for JDK 1.2.x, 1.3.x, or 1.4.x environments. The runtime12.jar
file is not included.
The runtime.jar
library provides flexibility across different Java environments and Oracle JDBC implementations, 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.jar
or runtime12ee.jar
library with JDK 1.2.x, 1.3.x, or 1.4.x and an Oracle9i or Oracle10g JDBC driver.
If you will be running only SQLJ applications that have already been translated, compiled, and customized, then you will not need the translator.jar
file.
To ensure that 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.
Note:
Code generation is determined by the SQLJ-codegen
option. Refer to "Code Generation (-codegen)" for more information.Scenario 1: Oracle-Specific Code
The following is a typical environment setup for Oracle-specific code generation:
SQLJ code generation: -codegen=oracle
(default)
SQLJ translation library: translator.jar
SQLJ run time library: runtime12.jar
or runtime12ee.jar
JDBC drivers: Oracle9i release 1 (9.0.1) or later
JDK version: 1.2.x, 1.3.x, or 1.4.x
Notes:
In this case, the SQLJ generic runtime.jar
library is not an option.
If you are running against different JDBC versions, then translate against the earlier version.
The following is a typical environment setup for ISO standard code generation:
SQLJ code generation: -codegen=iso
SQLJ translation library: translator.jar
SQLJ run time library: runtime12.jar
or runtime12ee.jar
with JDK 1.2.x or later and Oracle9i Database release 1 (9.0.1) or later JDBC drivers
JDBC drivers: Any Oracle JDBC release (appropriate for SQLJ run time library as noted in the preceding point)
JDK version: 1.2.x, 1.3.x, or 1.4.x
Environment Scenarios: Key Guidelines
The following are the important guidelines and considerations regarding your environment for running the Oracle SQLJ implementation:
In general, use the same versions of the SQLJ library, JDBC library, and JDK in translating and compiling all components of your application.
Be aware of the following cross-compatibility considerations:
If you want to run against different Oracle JDBC driver releases, then translate against the earlier release. Generated code is optimized toward the JDBC driver in the CLASSPATH
during translation.
For cross-compatibility, avoid using declared connection context classes. If you use JPublisher, then use the default settings for the -compatible
and -context
options.
See Also:
Oracle Database JPublisher User's Guide for information about these options.For maximum cross-compatibility, you can use the generic runtime.jar
library. However, this library has disadvantages, such as not supporting the oracle.jdbc.Oracle
XXX
interfaces. This causes problems in the middle tier. Note that SQLJ in the Oracle9i Application Server middle tier requires release 2 (9.0.2) or later version of Oracle Application Server.
See Also:
"Environment Considerations"This section discusses the key environmental issue and limitations. The following list notes environmental issues and limitations:
With the default -codegen=oracle
setting, you cannot use the generic runtime.jar
library.
You can run the application against a JDK version that is at least as high as the version you translated the code under. If you translate your code under JDK 1.2.x, then you can run the application either under JDK 1.2.x or 1.3.x or 1.4.x.
Note:
Theruntime.jar
library is intended mainly for backward compatibility. It does not support Oracle-specific features.You must keep in mind the following points regarding backward compatibility of the Oracle SQLJ implementation:
Code generated with an earlier release of the SQLJ translator can continue to run and compile against current run time libraries. However, this is subject to the cross-compatibility limitations discussed in "Environment Considerations".
Oracle-specific translator output, that is, code generated with the default -codegen=oracle
setting, must be created and executed using the runtime12.jar
or runtime12ee.jar
library. In addition:
Such code will be executable under future Oracle JDBC and SQLJ implementations.
Such code, however, will not be executable under earlier releases of Oracle JDBC drivers and the Oracle SQLJ run time. In these circumstances, you will have to retranslate the code.
Also remember that Oracle-specific code is not portable.
ISO standard translator output, that is, code generated with the -codegen=iso
setting, can be created and executed against an earlier Oracle JDBC implementation using the current runtime.jar
library.
After you have verified that the preceding assumptions and requirements are satisfied, you must check your SQLJ installation. You must:
Following are the release-specific notes regarding availability of SQLJ and its demo applications:
SQLJ and its demo applications are available from the Oracle Technology Network (OTN) at the following location:
http://www.oracle.com/technology/sample_code/tech/java/sqlj_jdbc/sqlj.html
For Oracle Database 11g, SQLJ and its demo applications are included with the installation.
For Oracle9i Database release 2 (9.2), SQLJ and its demo applications are included if you do a full installation or if you otherwise install HTTPClient
.
Note:
SQLJ installations with Oracle9i Application Server provide only theruntime12.jar
file for JDK 1.2.x or later environments. The runtime.jar
file is not included.Verify that the following directories have been installed and are populated:
If you are using one of the Oracle JDBC drivers, refer to the Oracle Database JDBC Developer's Guide and Reference for information about JDBC files that should be installed on your system.
Installing the Oracle Database 11g 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)
doc
lib
(.jar
files containing class files for SQLJ)
Check whether all these directories have been created and populated, especially lib
.
The structure is similar if you download SQLJ from the OTN:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/index.html
The ORACLE_HOME
/bin
directory contains utilities for all Java product areas, including the SQLJ and JPublisher executable files.
Ensure that the PATH
and CLASSPATH
environment variables have the necessary settings for the Oracle SQLJ implementation and the Oracle JDBC implementation, if applicable.
If you are using one of the Oracle JDBC drivers, then you will need the JDBC library that is appropriate for your environment.
Classes for JDK 1.2.x or later are in classes12.jar
. Classes for JDK 1.4.x specifically are in ojdbc14.jar
. Presuming you use a Sun Microsystems JDK, ensure that the appropriate library file name is in your classpath setting. There might also be alternative JDBC driver libraries available, such as classes12_g.jar
, which enables driver debugging information to be printed.
For more information about libraries and required path and classpath settings for the Oracle JDBC implementation, refer to the Oracle Database JDBC Developer's Guide and Reference.
Set the PATH
and CLASSPATH
environment variables as follows for the Oracle SQLJ implementation:
Setting PATH
To run the sqlj
script, which invokes the SQLJ translator, without having to fully specify its path, verify that the PATH
environment variable has been updated to include the following:
ORACLE_HOME/bin
Use backslash (\) for Microsoft Windows. Replace ORACLE_HOME
with your actual Oracle home directory.
Setting CLASSPATH
Update the CLASSPATH
environment variable to include the current directory as well as the following:
ORACLE_HOME/sqlj/lib/translator.jar
Use backslash (\) for Microsoft Windows. Replace ORACLE_HOME
with your actual Oracle home directory.
In addition, you must include one of the following run time libraries in the CLASSPATH
:
ORACLE_HOME/sqlj/lib/runtime12.jar ORACLE_HOME/sqlj/lib/runtime12ee.jar ORACLE_HOME/sqlj/lib/runtime.jar
Note:
You will not be able to run the SQLJ translator if you do not add a run time library. You must specify a run time library as well as the translator library in theCLASSPATH
.
To see if SQLJ is installed correctly, and to see the version information for SQLJ, JDBC, and Java, run the following command:
% sqlj -version-long
The sqljutl
package is required for online checking of stored procedures and functions in an Oracle Database instance. Beginning with Oracle8i Database release 8.1.5, it is installed automatically under the SYS
schema during installation of the server-side Java virtual machine (JVM) for a Java-enabled database. If your database is not Java-enabled, then you will have to manually install this package.
If you want to verify the installation of sqljutl
, then issue the following SQL command from SQL*Plus:
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, or if you want to install an updated version of the package, then you can install it by using SQL*Plus to run the sqljutl.sql
script (or sqljutl8.sql
for Oracle8i Database), which is located at:
ORACLE_HOME/sqlj/lib/sqljutl.sql
You can test your database, JDBC, and SQLJ setup using demo applications defined in the following source files:
TestInstallCreateTable.java
TestInstallJDBC.java
TestInstallSQLJ.sqlj
TestInstallSQLJChecker.sqlj
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.
The demo applications discussed here are provided with your SQLJ installation in the demo
directory:
ORACLE_HOME/sqlj/demo
You may have to edit some of the source files and translate and compile them, as appropriate. The demo applications provided with the Oracle SQLJ implementation refer to tables on an Oracle Database account with user name scott
and password tiger
. Most Oracle Database installations have this account. You can substitute other values for scott
and tiger
if desired.
Note:
Running the demo applications requires that thedemo
directory be the current directory, and that the current directory (".
") should be specified in the CLASSPATH
.This section covers the following topics:
This section describes how to update the connect.properties
file to configure your Oracle connection for run time. The file is in the demo
directory and looks something like the following:
Note:
In the Oracle Database 11g JDBC implementation, database URL connect strings using SIDs are deprecated. Following is an example, whereorcl
is the SID:
jdbc:oracle:thin:@localhost:1521:orcl
This would now generate a warning, but not a fatal error. Instead, you are encouraged to use database service names, such as myservice
in the following example:
jdbc:oracle:thin:@localhost:1521/myservice
Refer to the Oracle Database JDBC Developer's Guide and Reference for information about database service names.
# 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:@ # # User name and password here sqlj.user=scott sqlj.password=tiger
Connecting with an Oracle JDBC Driver
With Oracle9i Database or later versions, use oci
in the connect string for the Oracle JDBC OCI driver in any new code. For backward compatibility, however, oci8
is still accepted. Therefore, you do not have to change existing code.
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.
The following tests assume a table called SALES
. Compile and run TestInstallCreateTable
as follows:
% javac TestInstallCreateTable.java % java TestInstallCreateTable
This will create the table for you if the database and the JDBC driver are working and the connection is set up properly in the connect.properties
file.
Note:
If you already have a table calledSALES
in your schema and do not want it altered, edit TestInstallCreateTable.java
to change the table name. Otherwise, your original table will be dropped and replaced.If you do not want to use TestInstallCreateTable
, then you can create the SALES
table using the following SQL statement:
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, then use the TestInstallJDBC
demo. Verify that your connection is set up properly in connect.properties
. Then, compile and run TestInstallJDBC
, as follows:
% javac TestInstallJDBC.java % java TestInstallJDBC
The program should print:
Hello, JDBC!
Now translate and run the TestInstallSQLJ
demo, a SQLJ application that has functionality similar to that of TestInstallJDBC
. Use the following command to translate the source:
% sqlj TestInstallSQLJ.sqlj
Note that this command also compiles the application.
On a UNIX environment, the sqlj
script is in ORACLE_HOME
/bin
, which should already be in the PATH
. On Windows, use the sqlj.exe
executable in the bin
directory. The SQLJ translator.jar
file has the class files for the SQLJ translator and run time. It is located in ORACLE_HOME
/sqlj/lib
and should already be in the CLASSPATH
.
See Also:
"Set the Path and Classpath"Now run the application as follows:
% java TestInstallSQLJ
The program should print:
Hello, SQLJ!
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, which is sqlj.properties
by default.
While still in the demo
directory, edit the sqlj.properties
file and update, comment, or uncomment the sqlj.password
, sqlj.url
, and sqlj.driver
lines, as appropriate, to reflect your database connection information. For assistance, refer to the comments in the sqlj.properties
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.
sqlj.url=jdbc:oracle:oci:@ sqlj.driver=oracle.jdbc.OracleDriver sqlj.password=tiger
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.
See Also:
"Connection Options"You can test online semantics-checking by translating the TestInstallSQLJChecker.sqlj
file 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, then the following line is displayed:
Hello, SQLJ Checker!