The following are required to use the Oracle SQLJ implementation:

• 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

  Note:

  For more information about translator.jar, refer to "Set the Path and Classpath".

• Class files for the SQLJ run time.

  ORACLE_HOME/sqlj/lib/runtime12.jar

  Note:

  runtime12ee.jar has been deprecated since Oracle Database 11g Release 1. Use runtime12.jar instead.

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.

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

• JDBC drivers: Oracle Database 12c Release 2 (12.2)

• JDK version: 6 or 7

You can run the application against a JDK version that is at least as high as the version you translated the code under.

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 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 Oracle SQLJ run time. In these circumstances, you will have to retranslate the code.

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

Directories for JDBC

Refer to the Oracle Database JDBC Developer’s Guidefor information about JDBC files that should be installed on your system.

Directories for SQLJ

Installing the Oracle Database 12c Release 2 (12.2) Java environment includes, 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)

• lib (.jar files containing class files for SQLJ)

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

The ORACLE_HOME/bin directory contains utilities for all Java product areas, including the SQLJ executable files.

Ensure that the PATH and CLASSPATH environment variables have the necessary settings for the Oracle SQLJ implementation. 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.

  Include the following run-time library in the CLASSPATH:

  ORACLE_HOME/sqlj/lib/runtime12.jar
  

  In addition, you must include the following JDBC JAR files in the CLASSPATH:

  ORACLE_HOME/jdbc/lib/ojdbc6.jar
  ORACLE_HOME/jdbc/lib/ojdbc7.jar
  

  Note:

  • To translate or run SQLJ programs in JDK 6 environment, you should have ojdbc6.jar in the classpath and to translate or run SQLJ programs in JDK 7 environment, you should have ojdbc7.jar in the classpath. Ensure that the correct JDBC JAR is picked up at runtime for connecting to Oracle Database.

  • 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 the CLASSPATH.

    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
    

  See Also:

  "Requirements for Using the Oracle SQLJ Implementation"

The sqljutl package is required for online checking of stored procedures and functions in Oracle Database instance. The package 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, which is located at:

ORACLE_HOME/sqlj/lib/sqljutl.sql

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:

jdbc:oracle:thin:@localhost:5221:orcl

jdbc:oracle:thin:@localhost:5221/myservice

# Users should uncomment one of the following URLs or add their own.
# (If using Thin, edit as appropriate.)
#sqlj.url=jdbc:oracle:thin:@localhost:5221/myservice
#sqlj.url=jdbc:oracle:oci:@
#
# User name and password here
sqlj.user=HR
sqlj.password=hr

Connecting with an Oracle JDBC Driver

Use oci in the connection string for 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.

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 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.

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 Oracle JDBC OCI driver.

sqlj.url=jdbc:oracle:oci:@
sqlj.driver=oracle.jdbc.OracleDriver
sqlj.password=hr

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.

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=HR TestInstallSQLJChecker.sqlj

This should produce the following error message if you are using one of 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=HR TestInstallSQLJChecker.sqlj
% java  TestInstallSQLJChecker

If everything works, then the following line is displayed:

Hello, SQLJ Checker!