This document contains answers to frequently asked questions about Oracle's SQLJ drivers. Note that these address specific technical questions only and are used to document solutions to frequent customer questions as well as any known problems. Please consult the SQLJ Developer�s Guide and Reference for full background information.

If you have any questions or feedback on this document, please e-mail mailto:helpsqlj_us@oracle.com or mailto:sqljsup_us@oracle.com.

Last updated: 27 April 2001

Quick Jump

Need Troubleshooting?

Go to the Troubleshooting Checklist to solve a problem or issue you have encountered.

Or, jump directly to one of the following

• Errors When Starting the Translator
• Error Messages Encountered During Translation
• Additional Translation Issues
• Error Messages Encountered During Deployment or Runtime
• Deploying and Running in the Oracle Server JavaVM
• Using REF Cursors
• Additional Deployment and Runtime Issues

Start the General Questions part, or go to one of the following

• SQLJ (and JDBC Basics)
• SQLJ Language and Programming
• Oracle-Specific Features
• Translation Process
• Development Environments
• Deploying SQLJ Programs
• Running SQLJ Programs

Contents

Part A. Troubleshooting

1. Troubleshooting Checklist

2. Problems Translating and Compiling SQLJ Programs

2.1 Errors When Starting the Translator

3.1 Error Messages Encountered During Deployment or Runtime

4. SQLJ (and JDBC) Basics

4.1 SQLJ Resources

7.1 Basic Functionality

Part A. Troubleshooting

The items in this section provide information regarding common problems and errors encountered by SQLJ users.

1. Troubleshooting Checklist

This list should help you to systematically identify a problem you are encountering and point you to where specific issues are being addressed in this FAQ.

1. Do you have a JDK installed in your system, and can you say javac? You can determine your JDK version by issuing

     java -version
See also "Unable to initialize threads: ...", "Exception:access violation", "Error in Java compilation: CreateProcess: javac", "NoClassDefFoundError: sun/io/CharToByteConverter". Also note that JDK 1.3 is not supported with SQLJ 8.1.7 or earlier - see JDK 1.3: SQLJ translator hangs and/or does not show any error messages from the Java compiler.
2. Do you have the Oracle JDBC drivers installed and in your PATH/CLASSPATH? Can you compile and run JDBC programs?

See also "Cannot load JDBC driver class oracle.jdbc.driver.OracleDriver",, "SQLException: No suitable driver", "SQLException: The network adapter could not establish the connection", "SQLException: Connection refused", "SQLException: ORA-01017: invalid username/password; logon denied".
3. Do you have the SQLJ translator.zip and runtime.zip (or runtime11.zip, runtime12.zip) files in your CLASSPATH? Can you issue

     javap sqlj.tools.Sqlj
See also "NoClassDefFoundError: sqlj/tools/Sqlj", "Error: SQLJ runtime library is missing".
4. Can you start the SQLJ translator and have it print out its environment by issuing

     sqlj -version-long
See also "Error in sqlj stub: invalid argument", "ExceptionInInitializerError: NullPointerException", "Unable to initialize threads: ...".
5. Can you translate and compile .sqlj programs offline? You need to have "." (dot) in your CLASSPATH to compile and run the demo programs supplied with SQLJ. Additionally, if you use the translator option -d directory, you need to have directory in your CLASSPATH so that the SQLJ translator can properly resolve Java classes during all translation phases.

See also SQLJ hangs during translation, SQLJ translates but does not produce any .class files, I encounter a core-dump/stack-trace/out-of-memory condition during translation, I encounter an out-of-memory condition during translation, "NullPointerException at java.util.zip.ZipFile.read".
6. Can you translate and compile .sqlj programs online?

See also "Cannot load JDBC driver class oracle.jdbc.driver.OracleDriver", "Warning: Cannot determine default arguments for stored procedures and functions. May need to install SYS.SQLJUTL.".
7. Can you run your SQLJ program?

See also Error Messages Encountered During Deployment or Runtime, unabletoloadconnectproperties, "InvalidClassException: Serializable is incompatible with Externalizable", profilenotfound46130, "ClassNotFoundException: xxx.yyy_SJProfile0 for class xxx.yyy_SJProfileKeys", "ORA-29541: class class_name could not be resolved", JDK 1.2.1: Other "NullPointerException".
8. Are you trying to deploy and run your application in the middle tier?

See also "SQLException: Invalid column type error" and "How can I use SQLJ in a middle-tier environment".
9. Are you tring to return REF CURSORs from the server to the client and process them there?

See also Using REF Cursors
10. Are you trying to deploy and run your application in the Oracle server-side JavaVM or in the Oracle iAS JavaVM?

See also Deploying and Running in the Oracle Server JavaVM.

SQLJ Error Report

Environment Description

Platform/OS Version:

Database version:

PATH value:

CLASSPATH value:

SQLJ_OPTIONS value:

JDK version (java -version):

JDBC version:

SQLJ version (sqlj -version-long):

Problem Description

Problem Area:

_ unable to start translator

_ translation problem - _ offline or _ online

_ runtime problem

_ deployment problem

Error Message:

Error Description:
 
 

2. Problems Translating and Compiling SQLJ Programs

The following problems might be encountered when trying to invoke the translator, or during translation itself. The first and second group of questions address specific error messages; the third group addresses somewhat more general questions.

In either group, some problems may be indicated as being general configuration issues, not specific to your SQLJ code. In these cases, or for general information about SQLJ installation and configuration, see the "Getting Started" chapter of the SQLJ Developer's Guide and Reference.

In brief, before concerning yourself with particular code issues in your application, you should be sure that you can already run the Java compiler, connect to the database using JDBC, and invoke the translator.

2.1 Errors When Starting the Translator

"Error in sqlj stub: invalid argument"

(This is likely a general configuration issue, not something specific to your code.)

The SQLJ translator issues: "Error in sqlj stub: invalid argument" when it is launched. This may happen with SQLJ 8.1.6 on a Windows platform. The problem occurs when the SQLJ wrapper executable sqlj.exe calls

     java <java_arguments> sqlj.tools.Sqlj <sqlj_arguments>

The problem may have to do with the size of the CLASSPATH. Also note that SQLJ does not currently support a CLASSPATH containing one or more spaces.

You can have SQLJ display (but not run) the full Java command line above by issuing

     sqlj -n <options>

You can try to resolve this issue with one or more of the following:

• Obtain a different sqlj.exe stub (such as from the SQLJ 8.1.7 or later distribution, or from SQLJ 8.1.5 or another earlier SQLJ download)

Also note that the SQLJ downloads on OTN contain the C-source code for the wrapper executable.
• You can try to reduce the size of the CLASSPATH to a minimum, for example to "." (dot), the JDBC classesXXX.zip, the SQLJ translator.zip, and the SQLJ runtime.zip (see also "Unable to initialize threads: ...").
• As a workaround, you can also issue

     java sqlj.tools.Sqlj <options>

(This is likely a general configuration issue, not something specific to your code.)

If you are using the Java Development Kit from Sun, you may be experiencing problems from conflicts with previously installed Java environments. The easiest way to avoid such problems is to customize your CLASSPATH setting to include only what you need for the JDK, the JDBC driver, and SQLJ. Essentially, you should start with a clean slate.

You can accomplish this as follows:

1. Clear the CLASSPATH.
2. Add "." (current directory) to the CLASSPATH and then make sure you can run java and javac.
3. Add the appropriate JDBC ZIP file--classes111.zip for JDK 1.1.x, or classes12.zip for JDK 1.2. These files are in the JDBC lib directory, such as the following on Solaris:

     [Oracle Home]/jdbc/lib/classes12.zip
or the following on Windows NT:
     [Oracle Home]\jdbc\lib\classes12.zip
4. Add the SQLJ ZIP file translator.zip. This file is in the sqlj/lib directory, as with the JDBC classesXX.zip files above.
5. Add the SQLJ ZIP file runtime.zip. This file is in the sqlj/lib directory.

• You do not need to add the runtime zip if you are using SQLJ 8.1.6 or earlier, but it will not hurt, either.
• For Sun JDK 1.1.3 or higher, you should not need the JDK directory to appear in the CLASSPATH (in fact, it may be advisable to not have it in the CLASSPATH). (This discussion is regarding the CLASSPATH environment variable, as opposed to the Java/SQLJ -classpath option. When using the -classpath option, you do have to include the JDK directory.)
• A convenient approach to going back and forth between your normal CLASSPATH and your CLASSPATH for SQLJ would be to create a script (for Solaris) or a batch file (for Windows) to set up each configuration.

(This is likely a general configuration issue, not something specific to your code.)

This may also be caused by PATH or CLASSPATH problems. See the answer to the previous question.

"ExceptionInInitializerError: NullPointerException"

(This is likely a general configuration issue, not something specific to your code.)

If you see the following stack trace:

If you are using the command-line version of SQLJ, download and install SQLJ 8.1.6 or later.

If you are using SQLJ from JDeveloper 3.0, download the SQLJ 8.1.6 SDK patch, which contains a translator.zip version to replace the one included in JDeveloper 3.0.

"NoClassDefFoundError: sqlj/tools/Sqlj"

(This is likely a general configuration issue, not something specific to your code.)

The error "Exception in thread main java.lang.NoClassDefFoundError: sqlj/tools/Sqlj" indicates that the SQLJ translator class files cannot be found. Most likely, your CLASSPATH is missing the SQLJ translator.zip file. This file can usually be found in [Oracle Home]/sqlj/lib/. Also remember to add one of the SQLJ runtime zip files to the CLASSPATH � this is required in SQLJ version 8.1.7 and later (see "Error: SQLJ runtime library is missing").

"NoClassDefFoundError: sun/io/CharToByteConverter"

(This is likely a general configuration issue, not something specific to your code.)

Running sqlj results in the following error message.

"Error: SQLJ runtime library is missing"

(This is likely a general configuration issue, not something specific to your code.)

This indicates that your CLASSPATH contains the translator.zip file, but is missing the runtime library. The SQLJ library files can usually be found in [Oracle Home]/sqlj/lib/.

• Add runtime.zip to your CLASSPATH if you want to use SQLJ with any JDBC driver � not just the one that corresponds in version to your SQLJ version. For example, if you have SQLJ 8.1.7 and want to use it with any one of the following JDBC versions: 7.3.4, 8.0.5, 8.1.5, 8.1.6, or 8.1.7.
• Add runtime11.zip to your CLASSPATH if your SQLJ and JDBC versions match and you are running in a JDK 1.1.x environment. This runtime provides a slightly better performance and is also the recommended runtime for applet environments.
• Add runtime12.zip if your SQLJ and JDBC versions match and you are running in a JDK 1.2 or later environment. This is necessary for certain JDBC 2.0 features, such as support for java.sql.SQLData through type maps.

You can also use runtime12ee.zip if your SQLJ and JDBC versions match and you are running in a JDK 1.2 or later environment that provides support for the packages javax.naming and javax.sql.

This is likely a result of the SQLJ command line being too long when invoking the SQLJ translator on Windows. You can do the following to reduce the size of the command line.

• Do not use the CLASSPATH option, but set the CLASSPATH environment variable (you may want to set up a .bat file to do that, see also "Unable to initialize threads: ...").

• Do not provide .java files on the command line but rely on the implicit make capability in both, the SQLJ translator and the Java compiler. Note, however, that you must mention all of the .sqlj files that your program requires, do not rely on implicit make to find all of those.
• You can perform the -ser2class conversion (if required), as well as Java compilation and profile customization in separate steps. Use the setting -compile=false to turn off Java compilation.
• Place as many command line options as possible into the sqlj.properties file. Note that options in the SQLJ_OPTIONS environment variable are passed on the command line, while options in the sqlj.properties file are not.
• Your operating system may permit you to increase the size of the environment (and this may or may not increase the maximum command line length as well).

This message may be encountered with SQLJ 8.1.6 on Windows. The problem is that the SQLJ wrapper executable (sqlj.exe) is unable to determine the location of the Oracle installation from the system registry and exits.

• Try the following as a sanity check:

- add the translator.zip library of your Oracle installation (OracleHome/sqlj/lib/translator.zip) to your CLASSPATH, as well as the classes111.zip (or classes12.zip) from the Oracle JDBC driver
- run: java sqlj.tools.Sqlj -version-long to see whether SQLJ finds everything it needs.
• If SQLJ appears to work fine without the sqlj.exe wrapper, you should use the original wrapper that came with SQLJ 8.1.5/8.0.5/7.3.4 (and that is also distributed with SQLJ 8.1.7 and later). Download SQLJ 8.0.5, or 8.1.7 or later from the OTN download page at http://technet.oracle.com/, unzip it, and use that sqlj.exe file instead of the one that came with your Oracle installation.

SQLJ translator hangs and/or does not show any error messages from the Java compiler (in JDK 1.3)

When using SQLJ 8.1.7 or earlier under JDK 1.3 you may experience hangs, or you will notice that Java compilation errors do not show up anymore. (If you experience hangs and are not using JDK 1.3 or later, please see SQLJ hangs during translation) The problem is that with JDK 1.3 the javac compiler sends error messages to standard error instead of standard output. However, the SQLJ translator tries to capture messages from standard error. Since this issue affects Oracle SQLJ versions 8.1.7 and earlier, you may want to upgrade to SQLJ version 9.0.1 or later. Otherwise you may be able to use the following workaround.

• First, create a new shell script that invokes javac but that also sends stderr to stdout. For example, if you can invoke the Unix shell sh, you might create the following executable script called myjavac and place it in your PATH:

     #!/bin/sh
     # sh script myjavac to redirect stderr to stdout
     javac "$@" 2>&1
• Secondly, you need to tell SQLJ to use the -passes option and also to invoke the myjavac shell script instead of calling the javac compiler:

     sqlj -passes -compiler-executable=myjavac Foo.sqlj
Note that you cannot put the -passes option into the sqlj.properties file, since it must be processed by the sqlj wrapper script/executable. Since you probably do not want to type these two options by hand every time you invoke SQLJ, you may prefer to put them in the SQLJ_OPTIONS environment variable. Depending on your operating system and command line shell, you might say something along the following line (here shown for a csh-like shell):
     setenv SQLJ_OPTIONS "-passes -compiler-executable=myjavac"

"Cannot load JDBC driver class oracle.jdbc.driver.OracleDriver"

When SQLJ translation results in the following warning:

     Cannot load JDBC driver class oracle.jdbc.driver.OracleDriver.

You need to have the JDBC classes111.zip (for JDK 1.1.x) or classes12.zip (for JDK 1.2 or later) in your CLASSPATH environment variable. The following command line should print out the methods on the OracleDriver class.

     javap oracle.jdbc.driver.OracleDriver

Subsequently, the following should print out all sorts of version information on SQLJ, JDBC and the JDK you are using.

     sqlj -version-long

If this shows the JDBC driver version as 0.0, then the Oracle JDBC driver is not yet in the CLASSPATH.

"Return type XXXX ... is not a visible Java type"

You may be trying to return an iterator instance from a stored procedure, or as a column of a result set, and this iterator type is declared as follows:

"Missing equal sign in assignment"

This may be caused by using the pre-ANSI style of connection contexts, using parentheses instead of brackets. Instead of:

(This is a general configuration issue.)

The SQLJUTL package should be installed automatically in the SYS schema. It is used for semantics-checking of Oracle stored procedures and functions that have been defined to have default arguments.

If the translator indicates that it cannot find SQLJUTL, use the following SQL command (from SQL*Plus, for example) to see if it exists:

"Error: unable to load ... java.lang.NoClassDefFoundError"

This may indicate that the translator cannot find your compiled .class files during customization, and is particularly likely if you are using a Java package structure and are not using the SQLJ -d option during translation.

As a first step, rerun the translator with the -status flag enabled. If this error occurs after the [Customizing] line in the status output, this indicates a problem in finding your .class files during customization.

You can avoid this problem by using the SQLJ -d option when you translate. This option allows you to specify a root directory, and all profiles and .class files are placed underneath that root directory according to the package structure. Also, note that this directory must be in your CLASSPATH.

"Unable to convert ...Xxx.ser to a class file"

This occurs if you are using the SQLJ -ser2class option (to convert .ser profiles to .class files) and the translator cannot find the .ser files to convert. This problem is similar to the NoClassDefFoundError discussed in the previous question, and can also be resolved by using the SQLJ -d option during translation.

My code declares a block-level iterator class, and an instance of this class is later created and used in the same block. SQLJ appears to translate my code without difficulty, but javac gives the following error: "Error: Class Xxx not found in type declaration"

This problem, where Xxx is the iterator class name, results from a known bug in the javac compiler, version 1.1.x. Here is an example of a method that would result in this error:

"Error in Java compilation: CreateProcess: javac"

If the following error occurs during translation.

• Ensure that you have installed the Java Development Kit from http://java.sun.com . Specifically, you should be able to run javac from the command line.
• Reduce your PATH and CLASSPATH setting to the bare minimum to exclude possible conflicts of JavaVM's (you can do that most easily with a script or .bat file - see below; also see "Unable to initialize threads: ...").
• Add the -passes option to invoke the Java compiler in a different way.

2.3 Additional Translation Issues

SQLJ hangs during translation.

If you are using JDK 1.3 then please see "JDK 1.3: SQLJ translator hangs and/or does not show any error messages from the Java compiler". If the SQLJ translator appears to hang during translation, interrupt the translation and add the -status flag to diagnose the problem:

Note: If you are using SQLJ 8.1.5, 8.0.5, or 7.3.4 on NT, you should always use the -passes options. Otherwise you will experience this hang.

SQLJ translates but does not produce any .class files

If you run the SQLJ translator, but Java compilation appears to fail silently (you can verify with the -status command line option whether compilation was started) you can try one or more of the following.

• Ensure that you have installed the Java Development Kit from http://java.sun.com .
• Reduce your PATH and CLASSPATH setting to the bare minimum to exclude possible conflicts of JavaVM's (you can do that most easily with a script or .bat file - see below; also see ).
• Add the -passes option to invoke the Java compiler in a different way.

If no previously discussed diagnosis fits your problem, you may want to try the following.

• Turn off your JavaVM's JIT (just-in-time) compiler. You might be able to use one of the following to accomplish this:

Add the -J-Djava.compiler=none command-line flag to the translator invocation.
or:
Set the JAVA_COMPILER=NONE environment variable.
• Re-set your CLASSPATH variable to only contain . (current directory), the JDBC driver (either classes111.zip, or classes12.zip), the SQLJ translator (translator.zip), for SQLJ 8.1.7 or higher a SQLJ runtime (runtime.zip, runtime11.zip, or runtime12.zip), and any other classes that are actually referenced by your source, other than classes that are part of your JDK (the JDK classes do not have to be in your CLASSPATH for JDK 1.1.3 or higher).

If you have a genuine out-of-memory condition during SQLJ translation (as opposed to the issue described in the preceding question), you can increase your Java VM's memory allocation by passing additional flags to the SQLJ translator.

For example, you can set a heap size of 128 MBytes for the JDK 1.1.x Java VM with the -mx128M flag. By prefixing this flag with -J, you can use it with the SQLJ translator as follows:

sqlj -J-mx128M ...

Note that you must either pass this flag on the command line, or place it in the SQLJ_OPTIONS environment variable.

Also note that the -mx flag has changed to -Xmx in JDK 1.2.

Is there a way to speed up translation of a .sqlj file with online checking enabled?

If you enable the translator cache option (-cache=true), then SQLJ remembers the result of online checking and stores it in the file SQLChecker.cache. This removes the need to connect to the database for every #sql statement in your program. Note, however, that only those SQL statements that do not result in error or warning messages are being cached. Whenever SQLChecker.cache has become too large, or you want to start with a clean cache, you can just delete file SQLChecker.cache before running SQLJ.

Why are database errors reported as warnings? When I use the Oracle online checker, why do I get one error and one additional warning from the database?

Errors reported by the database are passed on as warnings by SQLJ. This is because the database reporting may not be fully accurate, resulting in spurious errors. The signature of stored functions and procedures is analyzed directly from the SQLJ client. But these function and procedure invocations are also passed to the database for explicit analysis. This results in duplicate reporting of certain errors or warnings concerning stored procedures and functions.

"Type class_name of host item #nn is not permitted in JDBC"

What does the following warning message mean?

Why was the profile-key class not generated?

I used the SQLJ translator to translate a couple of .sqlj files. Some of them have profile-key classes associated but some don't. Can you tell me why? Do missing profile-key classes hurt?

A profile keys class (and associated .ser files) will only be generated if you have an actual SQLJ statement, that is:

In SQLJ version 9.0.1 and later, you can specify the command line option -codegen=oracle. This results in the direct generation of Oracle JDBC code. In this neither .ser nor profile keys file are being generated for any of your SQLJ statements.

3. Problems Deploying and Running SQLJ Applications and Applets

For general information about deploying and running SQLJ programs, see "Deploying SQLJ Programs" and "Running SQLJ Programs" later in this FAQ.

3.1 Error Messages Encountered During Deployment or Runtime

"SQLException: No suitable driver"

(This is likely a general configuration issue, not something specific to your code.)

If you see the exception trace:

"SQLException: unable to load connect properties file: connect.properties"

When trying to establish a SQLJ connection using the code

• The connect.properties resource must be found either on the CLASSPATH, or -if deployed- in a .jar file in the same package as the class SomeClass.class. In other words, assuming you are deploying your application in a .jar file.

• If SomeClass is a top-level class, then connect.properties should be in the .jar's root directory.
• If SomeClass is actually of the form A.B.C.SomeClass, then connect.properties must be in the /A/B/C subdirectory of the .jar file.

• The entries in the connect.properties file must use the following SQLJ-specific format:

(This is likely a general configuration issue, not something specific to your code.)

The message: "The network adapter could not establish the connection." means one of two things:

1. The host could not be reached or does not exist. Please use the ping program to verify that the host is accessible on the network.
2. The host does not have a TNS listener running on the specified IP address. You can use the Net8 Configuration assistant to configure a database listener for TCP/IP. Alternarively -for example under Unix- you can do this by editing your listener.ora file, adding an additional line to:

     LISTENER = (ADDRESS_LIST=
                (ADDRESS=(PROTOCOL=ipc)(KEY=oracle-sid)) )
as follows:
     LISTENER = (ADDRESS_LIST=
                (ADDRESS=(PROTOCOL=ipc)(KEY=oracle-sid))
                (ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)) )
Now you must stop and then re-start your listener to pick up the new settings:
     lsnrctl stop; lsnrctl start

(This is likely a general configuration issue, not something specific to your code.)

If a listener is up and running as specified, but there is no database with the

specified Oracle SID, you will see the message:

If the username and/or password is not valid, you get:

(This is likely a general configuration issue, not something specific to your code.)

If you see the exception:

• If you are using SQLJ from JDeveloper 3.0, download the SQLJ 8.1.6 SDK patch, which contains a new translator.zip version for replacing the translator.zip file from the JDeveloper 3.0 installation. You must also ensure that the following all match: the JDK version (such as JDK 1.2.2) with which JDeveloper is started, the JDK version used by your project (see your project properties), and the JDK-compatible JDBC ZIP file (such as classes111.zip or classes12.zip).
• If you are using the command-line version of SQLJ, download and install SQLJ 8.1.6 or later or the SQLJ 8.1.6 SDK patch release.

• If you are deploying compiled SQLJ code into an Oracle database (for example, as a Java Stored Procedure, or as an Enterprise Java Bean), then you may want to use the same JDK and SQLJ versions for client compilation that are used in your server JavaVM. Specifically, for Oracle 8.1.7 or higher you may want to use the corresponding runtime12.zip and JDK 1.2, for Oracle 8.1.6 you want to compile under JDK 1.2, and for Oracle 8.1.5 you want to compile using JDK 1.1.x.

You may see this error if you have deployed SQLJ code to the server-side Java VM and are subsequently trying to run it. The error occurs because the SQLJ .ser profile files have not been deployed with the rest of your application, or they may not have been deployed into the same package as the original SQLJ classes that they reference. Please refer to the explanation of the error "ClassNotFoundException: xxx.yyy_SJProfile0 for class xxx.yyy_SJProfileKeys" directly below.

"ClassNotFoundException: xxx.yyy_SJProfile0 for class xxx.yyy_SJProfileKeys"

If you see an exception such as:

Setting a root directory. You may want to use the SQLJ -d <rootdir> flag to ensure that all files required by your project are deposited under a particular directory hierarchy before you run jar or loadjava.

Applets and converting .ser to .class. There is an additional consideration for applets. Older browser versions that are based on JDK 1.0.2, such as Netscape Navigator 3.0 and Microsoft Internet Explorer 3.0, did not have support for loading a serialized object from a resource file associated with the applet. Additionally, the Navigator browser does not permit resources to be loaded from files with a ".ser" extension. These limitations also result in the ClassNotFoundException when you try to run the applet. As a work-around, specify the SQLJ translator -ser2class option when you translate the code. This instructs SQLJ to convert profiles into Java class format in .class files. If you translate MyApplet.sqlj this way, for example, the profile would be in MyApplet_SJProfile0.class instead of MyApplet_SJProfile0.ser. Then you would have to ensure that the profile .class file is available in the SQLJ runtime environment, as with a profile .ser file.

Deploying code to the server, such as Java Stored Procedures and Enterprise Java Beans. Essentially, you have three choices in uploading your code with loadjava: (1) you can upload source code and have the server compile your code, (2) you can upload .class and .ser files, and (3) you can convert .ser files to .class files and upload these together with the other .ser files. If you use method (2) or (3) and omit one or more .ser and -respectively- converted .class files, you will encounter the ClassNotFoundException when your program is run.

Notes:

• In JDeveloper 3.0 and earlier the -ser2class flag is not supported. You will have to use the sqlj command-line translator to obtain this functionality. Additionally, SQLJ provides a stand-alone tool ("profconv") for converting .ser files into .java files (and subsequently .class files) that permits full control over the conversion process. Issue the following to obtain a synopsis of the tool:

     java sqlj.runtime.profile.util.SerProfileToClass
• In SQLJ 8.1.6 and earlier only .ser files of less than 32KB can be transformed in this way.

My SQLJ program precompiles and compiles successfully, resulting in a .class file which I load without error using loadjava. I then create a procedure to run the stored procedure. When I run the procedure, I get the error:

• Add the generated .ser file(s) to be loaded by loadjava.
• Use the -ser2class option when translating your SQLJ source and add the additional .class files to be loaded by loadjava.
• Use loadjava to load SQLJ source rather than .class (and .ser) files.

"SQLNullException: cannot fetch null into primitive data type"

You cannot assign a null value to a Java primitive type (such as int or float), but you can work around this by using a Java object type (one of the java.lang classes). For example, replace a declaration of the form:

An SQLJ statement that calls a stored function or procedure which returns a REF CURSOR issues the exception: "SQLException: Invalid column type".

This can be caused by one of the following:

• You are using SQLJ in a middle-tier environment that does not use the Oracle JDBC driver or that wrappers the Oracle JDBC driver - see "How can I use SQLJ in middle-tier environments".

• You may have changed the way you obtain a connection to using a DataSource connection pool or to wrapping OracleConnections in some other way. The solution is to use Oracle SQLJ version 8.1.7 or later, or �if you need to continue using an earlier version of SQLJ� to obtain a patch from http://technet.oracle.com. Note, however if you are
• The profile (.ser file) was not customized to use the Oracle SQLJ runtime. Make sure that you set the option -profile=true when translating. Also, if you use the option -compile=false, so that you can control compilation of the generated .java files, you need to perform a separate customization step after Java compilation by issuing sqlj *.ser on all of the generated profile files.

This error may occur when selecting an SQL object / a VARRAY / a nested table. The following are possible reasons for this message

• The conversion to an Oracle-specific non-standard class failed because no Oracle customization was installed. If you use the command line setting -profile=false or -compile=false, then this would be the case. You may be able to perform the customization step sperateley (here *.ser refers to all of your application's .ser files):

• The Oracle customization was properly installed, but the Oracle runtime was not used. This can happen if you run the SQLJ program using a non-Oracle JDBC connection. This would also be the case if the original Oracle JDBC driver is wrappered, for example in the middle tier, by some other vendors' driver adaptation (see also "How can I use SQLJ in middle-tier environments"). You can tell from the stack trace whether this situation is the case:
• You will not see any Oracle SQLJ specific runtime (oracle.sqlj.runtime.Xxxx) on the stack, and
• You will see some non-Oracle classes that implement JDBC connections, statements, or result sets. If you are using an Oracle JDBC driver you should only see classes of the form oracle.jdbc.driver.OracleXxxx.

• Finally, you may also see this message if you have mapped a SQL type hierarchy to a hierarchy of Java wrapper classes (generated with JPublisher), but the type map for the wrapper classes has not been initialized properly. Make sure to initialize the type map by creating an instance of each of the wrapper classes.

I am using Oracle 8.1.6 and got the following exception at the code generated by the SQLJ translator for a deployed EJB on Sun's J2EE server:

"java.lang.ClassCastException: weblogic.jdbc20.rmi.SerialCallableStatement", and "weblogic.jdbc20.rmi.SerialConnection ... failed"

When accessing VARRAY parameters in Oracle8i Stored Procedures from a Weblogic server I experience the folowing exceptions:

Note that with the Oracle 9i release, Oracle JDBC support will be fully defined through oracle.jdbc.OracleXxxx interfaces. Any JDBC driver that supports these interfaces will also be supported by SQLJ with full Oracle-specific functionality. In all other cases, the JDBC driver needs to be treated as a generic driver (translation setting -profile=false) and no support for Oracle-specific types is available.

"java.lang.NoClassDefFoundError: oracle/jdbc/OraclePreparedStatement"

When using JDK 1.2 and classes12.zip the statement oracle.sqlj.runtime.Oracle.close() results in the following exception:

(This is likely a general configuration issue, not something specific to your code.)

If you encounter the error message:

• You may have superfluous, conflicting CLASSPATH components in your environment. Try to only have the SQLJ runtime.zip, the JDBC classesXXX.zip, and your application's zips in your CLASSPATH. If your program now works, you can add the other class libraries back step by step to determine who the culprit is.
• Turn off the Java Just-In-Time compiler (JAVA_COMPILER=NONE).
• Try another JavaVM version.

When using JDK 1.2.1 it is possible you may see a NullPointerException due to overzealous garbage collection on part of the JavaVM. Specifically, we have seen positional iterator objects being nulled out before they could be closed, even though they were clearly in scope. You can eliminate this problem by switching to JDK 1.2.2.

ORA-01000: maximum open cursors exceeded

"I suspect that statement.close() leaves DB Cursors open. ... After doing a lot of database fetches in JDBC, I eventually get the following error:"

• The error ORA-01000: maximum open cursors exceeded happens when not all of the statement and result set objects are closed. In almost all cases when this error occurs, the programmer discovers that he or she has inadvertently missed the close() methods. Please make sure that all your statement, result set, and connection objects are explicitly closed after you have finished processing to avoid this error. The following is a snippet for such code:

• Note that if -for other reasons- you need to increase the maximum number of cursors, you can do so in the initxxx.ora by specifying (assuming the default is 50 or so)

• You should consider using SQLJ from the start. SQLJ will automatically manage statement objects (though you still must take care to close your result sets and connection contexts). Then you will not have to look at code such as the following from the Oracle 8.1.5 Application Developer's Guide - Large Objects (LOBs):

• You also need to make sure to close all of the SQLJ connection context objects, rather than waiting for them to be garbage-collected. SQLJ 8.1.6 and later performs automatic caching of the last five SQLJ statements that have been executed. Whenever you close the SQLJ connection context you ensure that the statements cached on it are being closed as well. You can use the method close(sqlj.runtime.ConnectionContext.KEEP_CONNECTION) if you just want to clean up the SQLJ statement cache but not close the underlying JDBC connection.

• A final tip: In order to find the bad cursors, debug/break in the exception catcher, and then look at all opened cursors in TOP SESSIONS. In every case of "max cursors exceeded", the same cursor was present hundreds of times, and easily found.

"We are using Oracle's Java implementation of connection pooling (OracleConnectionCacheImpl) in a Java client program which makes calls to Java stored procedures in the database. The problem we are seeing is that cursors that are implicitly defined and spawned from #sql { ... } statements in the Java code -such as from SELECT INTO statements- are opened in the stored procedures but never closed. Information about what cursors are opened comes from the V$SQL_TEXT_WITH_NEWLINES type views."

• You have to ensure that you are closing all result sets and statements. In SQLJ you do have to close your iterators (this closes the underlying result set). However, you cannot close statements, since the SQLJ runtime manages these automatically.

• Depending on the setting of the SQLJ statement cache size (option -P-Cstmtcache=nnn, available with SQLJ 8.1.6 and higher, default cache setting is 5), a statement is either closed immediately after reading outputs (if any), or the statement is returned to the cache. In order to turn off SQLJ statement caching, you have to use the following command line setting during SQLJ translation:

• Because of SQLJ statement caching you must ensure that rather than just closing the JDBC pooled connection, you are closing the corresponding SQLJ connection context. This will also ensure that all cached statements are closed. Otherwise these statements would remain open, and your program will run out of cursors.

• Also, if you issue SELECT * FROM V$OPEN_CURSOR you may still see cursors remaining open for optimization in a server-side LRU cursor cache after being closed.
• Finally, if you want to employ connection caching, you should use SQLJ version 8.1.7 or later. Previous versions do not properly support connection caches and pooled connections.

(a) #sql { SELECT col1,col2 INTO :x, :y FROM TAB WHERE ... };

#sql
{BEGIN SELECT col1,col2 INTO :OUT x, :OUT y FROM TAB WHERE ...; END;};

When calling a stored procedure that returns a REF CURSOR multiple times from the same session, I receive the following error. I am already using the close() method on the Java result set, but that does not appear to affect the database cursors. How can I avoid this error?

Performance of Java and SQLJ in Stored Procedures

If your code consists mostly of SQL, then PL/SQL will generally be more performant since it implements the SQL datatypes directly. Specifically, conversions between SQL and Java representations tend to be costly. On the other hand, if you are doing lots of computations and logic processing, then Java will show better performance. In general, you may want to use Java or PL/SQL, depending on what you are more comfortable with. However, with Java you do get the advantage of wider portability - you can run the same code in the server, the middle tier, or the client, and even with different vendors.

When using SQLJ in the server, you may want to consider the following tips to ensure good performance:

• Conversions from SQL data into and out of Java types are rather costly. If you do not need Java native representations - for example, if you are just copying data from one place to another- you should use instances of oracle.sql.XXXX types (NUMBER, DATE, CHAR, RAW, etc.)
• If you are using Oracle 8.1.6 or later, several additional performance improvements are available to your SQLJ programs. Statement caching: by default, the last five SQLJ statements are being reused. Batching of inserts/updates: you can enable batching of DML statements that are executed in a loop. Please refer to the documentation for the details. Statement caching as well as batching can provide very significant performance improvements.
• Consider adjusting the number of rows that are automatically prefetched on the OracleConnection. This is available through Oracle's JDBC driver - see sqlj/demo/PrefetchDemo.sqlj for more details. In SQLJ 8.1.7 and later this is also known as the Fetch Limit and can be set directly on the execution context.
• Avoid creating and destroying a multitude of connections. Every connection from the client corresponds to a database session, and establishing these sessions is time consuming. Furthermore, the first time you are calling a Java Stored Procedure in that session, that session's "virtual JavaVM" must be initialized. And the first time you are using SQLJ in that JavaVM, the SQLJ runtime also has to be initialized. In Oracle 8.1.6 and earlier the SQLJ runtime initialization performs an extensive number of class and method lookups to dynamically link with the JDBC driver; whereas in Oracle 8.1.7 and later a statically linked runtime avoids most of this overhead.
• Oracle 8.1.7 and later database versions support native compilation of Java code. You can to use this feature for an extra performance boost.

Is there any way to mimic the user defined and Oracle pre-defined PL/SQL exceptions in SQLJ?

Unfortunately, there is not. When the Java code in the Java Stored Procedure throws an exception, this is rendered as "ORA-29532: Java call terminated by uncaught Java exception: ..." at the SQL level.

"ORA-29531 - no method ... in class ..."

"When trying to call a Java Stored Procedure that was uploaded with loadjava the following message occurs:

• If you want loadjava to immediately resolve the classes, then use the -resolve flag (and also use -verbose to get more information during the uploading and resolution process).

• The published methods must be public static.
• All names in the Java signature are case sensitive and must match the actual class names.
• You must use the full class name. Specifically you must write java.lang.String, java.lang.Integer, and so on. Using String, Integer, ... will result in an error.
• For every OUT or IN OUT argument, the Java parameter must be an array of the underlying type. All OUT or IN OUT arguments are passed as one-element arrays, where the called method can change the array element at index 0. Otherwise you would have no way of modifying primitive or immutable Java values in the body of a method.
• Consider the following example declaration for a SQL wrapper of a JavaClass.javaFun method.

• You must write java.sql.Date (not java.sql.DATE, etc.) - the case-sensitive name of the class.
• You must write java.sql.String (not String) - the fully qualified class name.
• You must use int[] for an OUT (or IN OUT) argument, and not int.
• You may want to try using JDeveloper for uploading and publishing Java Stored Procedures. It has wizards that allow you to select the methods to publish and it automatically issues the appropriate SQL code.

"I deployed a stateless, container-managed Enterprise Java bean into Oracle 8.1.7. The bean contains a method with a SQLJ insert statement. Even though that appears to work, no commit is triggered by the Bean Container when the method call is finished."

On Solaris, this should have worked. On NT you have to explicitly lookup the datasource and do a ds.getConnection() in order to enlist the datasource with the Container. If you use the default kprb (server-JDBC) connection, you have to explicitly set the default-enlist tag to true in the XML deployment descriptor. The following is an example of using <default-enlist>:

<oracle-descriptor>
  <mappings>
    <ejb-mapping>
      <ejb-name>TestEJB</ejb-name>
      <jndi-name>test/TestEJB</jndi-name>
    </ejb-mapping>
    <transaction-manager>
      <default-enlist>true</default-enlist>
    </transaction-manager>
  </mappings>
</oracle-descriptor>

"java.security.AccessControlException: the Permission (java.net.SocketPermission) has not been granted"

"When executing code to create an explicit database connection from inside the server as follows:

CALL dbms_java.grant_permission

     ('username', 'java.net.SocketPermission', '*', 'connect,resolve');

Other SocketPermission�s that you may want to grant are accept and listen.

3.3 Using REF Cursors

Error: class cannot be constructed as an iterator: classname

"This error is encountered when trying to return a REF CURSOR as an OUT parameter of a PL/SQL block. What SQLJ and Java types are permitted for such arguments?"

• In SQLJ the following are permitted as OUT parameters or as stored function returns, for example:

• java.sql.ResultSet
• sqlj.runtime.ResultSetIterator
• a SQLJ iterator type

• The following are permitted in the left hand side of an assignment to a query, such as for example:

• sqlj.runtime.ResultSetIterator
• a SQLJ iterator type

• At this point, however, SQLJ does not support JDBC 2.0 rowsets.

"Could you show some sample code of a PL/SQL Stored Procedure that can return a JDBC ResultSet or a SQLJ iterator to the client?"

• Please look at the following demo in the SQLJ distribution:

• Also note that in Oracle -unlike with some other vendors- you cannot call a stored procedure that performs queries and then subsequently access the returned side-channel result sets from the statement object. In Oracle you always need some sort of cursor. The cursor must be explicitly declared on the stored function/procedure signature, either as the return parameter, or as one of the OUT argument parameters.

In Oracle release 8.1.7 and earlier you cannot return a result set back from a Java stored procedure, though you can do so from a PL/SQL stored procedure. Oracle release 9.0.1 and later permit you to return a result set from a Java Stored Procedure. You have to open the result set by executing a SQL statement. (There is an Oracle-specific API to enable this server-side functionality - please refer to the JDBC Developer's Guide and Reference.) You can optionally retrieve rows from it in the server-side Java code. Finally, you pass the result set with the remaining rows on it from the Java stored procedure as a REF CURSOR OUT parameter or return.

REF CURSORs returned from Stored Procedures lose scrollability

"An Oracle Stored Procedure returns a REF CURSOR to client Java code as a java.sql.ResultSet. The problem is that the scrollability of the ResultSet is lost - it becomes a TYPE_FORWARD_ONLY ResultSet."

Result set scrollability is not supported on result sets returned from a stored procedure or function.

If you look into how scrollability is implemented in the Oracle JDBC driver, you will understand why this cannot be done. Oracle SQL does not support scrollable cursors natively. Thus the behavior is emulated by selecting ROWID's together with the rows specified in your query (in other words: your query is modified at JDBC runtime). While this is possible for top-level queries, there is no way in which the JDBC runtime can modify the original query executed inside of a stored procedure and returned as a REF CURSOR.

3.4 Additional Deployment and Runtime Issues

How can I speed up execution of my SQLJ application?

Tips on improving performance with SQLJ.

• Conversions from SQL data into and out of Java types are rather costly.

If you do not need Java native representations - for example, you
are just copying data from one place to another, you should use
instances of oracle.sql.XXXX types (NUMBER, DATE, CHAR, RAW, etc.)
• Beginning with release 8.1.6, Oracle SQLJ mirrors performance enhancements available with Oracle JDBC:

� statement caching: by default, the last five SQLJ statements are being reused.
� batching of inserts/updates: you can enable batching of DML statements that are executed in a loop.
� registration of parameter and column sizes.
• Also, consider adjusting the number of rows that are automatically prefetched on result sets. This is available through the method setFetchSize() on sqlj.runtime.ExecutionContext - see sqlj/demo/jdbc20/FetchSize.sqlj. If you are using SQLJ 8.1.6 or earlier, you have to set this directly through the Oracle JDBC driver - see sqlj/demo/PrefetchDemo.sqlj for more details.

I'm having problems with retrieval of CHAR fields in SELECT statements.

If your table has a CHAR column, such as TITLE CHAR(120), then beware of SQL blank padding behavior.

Instead of selecting only the exact string, as follows:

#sql iter =
   { SELECT NAME, TITLE FROM Tab WHERE TITLE = :("Dawn") };

you will have to use wildcard search parameters, as in the following:

#sql iter =
   { SELECT NAME, TITLE FROM Tab WHERE TITLE LIKE :("Dawn%") };

Character comparison works with "" but not with NULL.

"The following query works from SQL*Plus:

In general, if you check for a NULL value you should not use a bind variable, but rather employ the SQL syntax "IS NULL". Thus instead of using

Using "WHERE column IN (value_list)" with a value_list of unknown size

My SQL statement has the following form.

SELECT * FROM TAB WHERE col IN (value1,value2,�)

The list value1, value2, � is a list of host variables. However, I do not know ahead of time, how many variables will be in the list. How can I write this in my SQLJ program?

You have several alternatives:

• Use JDBC and build the query string dynamically.

     String nums = ...; //comma-separated list of numbers
     Connection conn = DefaultContext.getDefaultContext()
                                     .getConnection();
     Statement s = conn.createStatement();
     ResultSet rs = s.executeQuery
                ("SELECT * FROM table WHERE nr IN ("+nums+")");
If you want to use the result set as a SQLJ iterator, you can do so as follows.
     MySqljIterator it;
     #sql it = { CAST :rs };
     ...
     it.close(); // also closes rs
• If you have set a maximum number of different values and you do have fewer actual values, then you can use a single SQLJ statement and re-bind the last value multiple times.

For example, if there are at most 5 bind variables, you could write the following.
     int a[] = new int[4];
     � // populate mynumbers
     #sql { SELECT * FROM tab WHERE col in (:(a[0]),:(a[1]),�};
If there are fewer than 5 actual ints, then would just repeat the same int in the rest of the array.
• Another -much less elegant- possibility is to have a different SQLJ statement for every bind variable count.

When I try to update a record using SQLJ, the data remains unchanged. What is going on?

A couple of notes on this.

• JDBC connections have auto-commit on, but SQLJ connections usually have auto-commit off by default. Thus you need to remember to issue a COMMIT to make the update visible.

    #sql { COMMIT };
• You can use an ExecutionContext to see how many rows got updated.

     import sqlj.runtime.ExecutionContext;
     ...
     ExecutionContext ec=new ExecutionContext();
     #sql [ec] { UPDATE table SET ... WHERE ... };
     System.out.println("Updated "+ec.getUpdateCount()+" rows.");
• Remember that if the WHERE clause selects no rows, then 0 rows get updated and you will also not see a SQLException.

If your WHERE clause compares any CHAR columns to Java strings, it very likely will not work: you have to blank-pad the Java strings if you want the comparison to succeed. Or you can declare the column as VARCHAR2. Or you could use a LIKE comparison and append a "%" to the Java string.

Is it possible to use host variables to substitute values into a SQLJ DDL Statement, such as an "ALTER SEQUENCE" statement where the sequence increment is reset?

No. Oracle's SQL engine does not let you use bind variables in DDL statements. In this case you have to revert to JDBC. For example, if you are using the SQLJ default context, you could say the following:

In SQLJ version 8.1.7 or later you can use connections obtained from a JDBC ConnectionCache, or connection classes that wrap or delegate to Oracle JDBC connections, or JDBC connections obtained from a DataSource. Assuming you obtained jdbcConnection this way, you can now do the following:

• Your SQLJ context inherits the autocommit behavior from the underlying connection. Usually you want to make sure it's turned off.
• By default, SQLJ employs a statement cache, keeping up to 5 most recently executes statements open. You can change the caching behavior and size with the -P-Cstmtcache=nnn command line flag. It is important to close the SQLJ connection context, rather than just the underlying JDBC connection, in order to fully clean up this SQLJ statement cache when it is no longer needed. This is particularly important when you run code server-side. Otherwise, since the server-side connection never gets "really closed", you'll start leaking cursor.
• There are some third-party products (such as the Weblogic server) that provide their own connection caching implementation. This is not compatible with using Oracle-specific features, such as returning REF CURSOR parameters, using Oracle object type wrappers -such as those generated by JPublisher-, or using oracle.sql.XXXX types in SQLJ and -in many cases- in JDBC as well. In this case you need to treat the third party driver as a generic JDBC driver, omit profile customization (set the option -profile=false), and avoid all Oracle-specific enhancements.

JDeveloper 3.0 was released prior to SQLJ release 8.1.6, so uses release 8.1.5, which does not support JDK 1.2. To run the application under JDK 1.2, you will have to download the SQLJ 8.1.6 SDK runtime patch from the Oracle Technology Network (OTN) Web site (technet.oracle.com). Oracle SQLJ 8.1.6 and later works with JDK 1.2.

I am running against an Oracle 8.0 database and my SQL object updates are not working.

Even though the JDBC 8.1.x drivers are backward compatible to 8.0.x databases, they require an 8.1.x database to support SQL object features. (JDBC 8.0.x drivers do not support SQL objects.)

How can I use SQLJ in the middle-tier (or EJB, or XXX) environment Y?

This is easiest if the middle tier directly supports SQLJ. Although -in principle- SQLJ can be used in any environment that provides JDBC connectivity, a few issues need to be addressed in practice.

• The SQLJ runtime classes must be available in the environment.
• Obtaining connections. If you can use the Oracle JDBC driver in the environment, this is straightforward. If the environment uses a non-Oracle JDBC driver, then you establish and use SQLJ connection contexts as follows. (You can supply the DefaultContext constructor with an existing connection.)

     import sqlj.runtime.ref.DefaultContext;
     ...
     <load/register JDBC driver, if necessary>
     DefaultContext ctx = new DefaultContext(...);
     #sql [ctx] { ..SQL statement.. };
• If you do not use an Oracle JDBC driver, then add the following flag to your sqlj command line:

-profile=false. This omits Oracle customization and makes the .ser file smaller. In this case you can also remove the whole oracle.* hierarchy from runtime.zip.
Also remember that you cannot use any features specific to Oracle JDBC or another vendor's JDBC, unless you use a vendor-specific SQLJ runtime and customization step.

• Oracle-specific JDBC functionality that is not supported under non-Oracle JDBC drivers or under wrappered Oracle JDBC drivers:

• Retrieving result sets or iterators from queries or as OUT-parameters from stored procedures.
• Using Oracle-specific types from oracle.sql.XXXX..
• Using classes that implement the oracle.sql.CustomDatum or the oracle.sql.ORAData interface.
• Using JDBC 2.0 functionality, such as batching, fetch limit, and scrollable iterators in a JDK 1.1 environment.
• Using JDBC object features: in order to use these, it appears that the Oracle JDBC connection object must not be wrappered.

• BEA/Weblogic middle tier: It appears that the Oracle JDBC driver is being fully wrappered by a BEA/Weblogic middle tier server. As a consequence, when you use features that are not generic JDBC functionality, such as retrieval of JDBC result sets or SQLJ iterators from SELECT columns, or as OUT parameters, you may encounter an "SQLException: Invalid column type error" or other errors.
• You need to ensure that the .ser files get deployed alongside the class files into the environment. Or you can use the -ser2class option during translation and then deploy .class files.
• Another question is whether your deployment tool has any knowledge about .sqlj files - it may only care about .java files. In this case you would have to translate all .sqlj files (and their corresponding .ser files) into .java files before deploying them into the environment. This can be accomplished as follows.

(a) run SQLJ over the .sqlj source with the -compile=false option. This produces .java and .ser files.
(b) turn the .ser files into .java files by running
     java sqlj.runtime.profile.util.SerProfileToClass -nc *.ser
Issue the line without arguments to get an option synopsis.
You would want to do (a) and (b) via a makefile and not by hand.
(c) During deployment time and runtime you must have runtime.zip in the respective CLASSPATHS of the deployment tool and of the middle-tier environment.

How can I use SQLJ with Oracle's BC4J?

"I am using BC4J (Oracle's Business Components For Java) in my application, and would like to write SQLJ code for performing some work in the database. Is it possible to do that?"

The following trick lets you obtain a real JDBC connection and create a SQLJ connection context:

import java.sql.Connection; import sqlj.runtime.ref.DefaultContext;
�
Connection conn = getDBTransaction()
      .createCallableStatement("select 1 from dual",1)
      .getConnection();
DefaultContext ctx = new DefaultContext(conn);
#sql [ctx] � { � };

How can I use SQLJ with JservApache?

"Could anyone can help me to JservApache? How do I install JDBC and SQLJ to Apache?"

Add the following lines to your jserv.properties for all jars, classes, and zips you want to use:

wrapper.classpath=<path>

If you use SQLJ, then do not include the SQLJ classes in the servlet repository. Instead, make them a part of the 'main' servlet and also incude them in wrapper.classpath.

Part B. General Questions

Answers here are only intended to give you a general grasp or idea; they do not go into detail. For further information about any of these topics, please refer to the SQLJ Developer's Guide and Reference.

4. SQLJ (and JDBC) Basics

4.1 SQLJ Resources

Are there SQLJ books and other resources that would help a newcomer?

Extensive documentation on SQLJ is available either from the Oracle Technology Network (OTN) Web site (technet.oracle.com: click on "Technologies", then Java -> SQLJ & JDBC) or from the Oracle SQLJ distribution:

• the exhaustive SQLJ Developer's Guide and Reference, which contains a "Getting Started" chapter that should help you to get up and running.

Notes:

• This is also available on technet.oracle.com: click on "Documentation", then "Data Servers -> Oracle8i", then "General Documentation", and finally "Oracle8i Java Developer's Documentation", which should take you to a menu with all of the Java related manuals, including SQLJ, JDBC, and JPublisher.

• Additional Java information (whitepapers, FAQs, etc.) can be found under the Technologies menu at http://technet.oracle.com/docs/tech/java/sqlj_jdbc/listing.htm.

• the examples in the demo directory hierarchy in the SQLJ distribution
• the primer "SQLJ: Tips, Traps, and Gems"

In addition, you can reference the following:

• N. Morisseau-Leroy, M. Solomon, and G. Momplaisir: Oracle8i SQLJ Programming, published by Osborne/McGraw-Hill
• Chapters on SQLJ programming appear in the following books:
• J. Melton, A. Eisenberg, and R. Cattell: Understanding SQL and Java Together : A Guide to SQLJ, JDBC, and Related Technologies,
• Orfali, Harkey, et. al: Client/Server Programming., and
• Oracle8i for Dummies

Is there an Oracle e-mail address for SQLJ questions?

mailto:helpsqlj_us@oracle.com or. mailto:sqljsup_us@oracle.com.

You can also send suggestions for modifications to this FAQ list.

Where can I find the SQLJ source code (known as the "reference implementation") that Oracle has made available freely to the public and other database vendors?

http://www.sqlj.org

Where can I download the SQLJ translator for a given platform?

SQLJ is available from the Oracle Technology Network (OTN) Web site:

http://technet.oracle.com

Click on the "Software" button, and then "Select a Utility or Driver" -> "SQLJ Translator". We provide downloads for Solaris and Windows NT. However, these versions only differ in the line termination characters used in textual files. If you are using a UNIX platform, you can adapt the sqlj/bin/sqlj shell script for your platform. For Windows platforms, we provide the sqlj\bin\sqlj.exe wrapper executable. However, this executable is only verified for Windows NT. It may not work on other Windows versions.

Why do I find information mentioning JSQL, I thought it is called SQLJ?

I found some Web pages that talk about JSQL, not SQLJ. I thought that the standard for embedding SQL in Java is called SQLJ.

You are right�it is called SQLJ. In the beginning it was called JSQL. However, the name JSQL had been trademarked by Caribou Lake Software for their JDBC driver product, which is not related in any way to SQLJ. The old information that you saw was still using the initial name before it was renamed to SQLJ.

Can SQLJ be faster than JDBC?

There are some papers which notice that SQLJ code could be faster then JDBC. How does this work, if SQLJ is nothing more than a layer on top of JDBC?

To the extent that SQLJ is a layer on top of JDBC it cannot be faster than JDBC. (Incidentally, this is the current situation of Oracle SQLJ.) However, to the extent in which SQLJ can exploit the fact that it represents static SQL statements, rather than dynamic ones, it can become faster than JDBC, for example through precompilation of SQL code, or predetermination of SQL types. In these cases the SQLJ runtime needs to be vendor-specific. A tuned SQLJ runtime can short-circuit several functions that the JDBC runtime always would have to perform dynamically at runtime, such as registration and checking of types, processing of JDBC escape sequences, other special SQL processing (for example to support scrollable result sets).

4.2 SQLJ Overview

Is SQLJ Y2K-compliant?

Yes, both the SQLJ translator and the SQLJ runtime are Y2K-compliant, presuming your Java environment is Y2K-compliant.

What are the pros and cons of choosing Java and SQLJ over C/C++?

Pros:

• a safer and more productive language
• same database API for client, server, and middle tier
• ability to write Java stored procedures in the database
• support for CORBA and EJB in all tiers, including the database
• tighter Java/SQL coupling through typed iterators
• multi-schema abstraction (through connection context types)
• full binary compatibility for all platforms, vendors, and databases (SQLJ only requires JDBC)

• lack of support so far for parameter definitions (offline schema definition)
• poorer raw performance
• an additional step between user code and executable code - this step may be all but transparent, depending on the development environment you use

Stored procedures are pieces of code executed in the database as part of your database session. In Oracle Databases, stored procedures are usually written either in PL/SQL (a proprietary Oracle language) or in Java. If you write your stored procedure in Java you use JDBC or SQLJ to access the database. You can write essentially the same Java code for accessing the database from the client, from the middle tier, or from the server-side JavaVM.

• PL/SQL is an Oracle-proprietary language and has some resemblance to Ada. It supports SQL types and SQL statements directly as part of the language.
• JDBC is an API specified by Sun as part of Java and supports the use of dynamic SQL from the start.
• SQLJ is an ANSI as well as ISO standard for embedding SQL in Java. It primarily supports static SQL, though it also easily interoperates with JDBC.

Is there a reason to write PL/SQL stored procedures instead of Java stored procedures?

Using Java does have some additional cost.

• Space: The overhead of running Java in a database session is a couple of tens of kBytes per session in addition to any memory that is used directly by the Java program.
• Time: Every connection (database session) that you establish, requires a one-time startup time for the JavaVM (and for the SQLJ runtime, see also Performance of Java and SQLJ in Stored Procedures). Moreover, if your code is mostly issuing SQL statements, then the time required for conversion between the SQL and Java formats will dominate your Java performance, whereas PL/SQL understands the SQL data format natively. On the other hand, the more computations and logic you need to perform in your stored procedure the more you should see Java gaining an edge over PL/SQL.

• There is no need to toss out PL/SQL code for Java. All PL/SQL code will continue to work and interoperate seamlessly with current or future server-side code written in Java.
• If you start out with Java expertise, there is no need to relearn another language such as PL/SQL.

Is there a translator for PL/SQL Stored Procedures/Packages into Java?

Note that Oracle is committed to continuing support and development of PL/SQL - there is no reason to convert PL/SQL into Java for future compatibility. Also, PL/SQL and Java are fully interoperable: you can call PL/SQL stored procedures from Java using JDBC or SQLJ and vice versa.

There are third-party products (for example from Quintessence Systems http://www.in2j.com) that provide for an automated migration from PL/SQL to Java. At this point, however, there is no tool that translates directy into SQLJ.

Does SQLJ implement the ANSI SQLJ specification?

Yes, although some minor features, while recognized by Oracle SQLJ, may not be supported by the Oracle JDBC drivers or Oracle database (such as the sensitivity, holdability, and returnability constants you can set in a with clause, and positioned UPDATE/DELETE/INSERT operations -see also "Does Oracle SQLJ support the WHERE CURRENT OF construct?").

Does SQLJ implement the ISO SQLJ specification?

Yes, Oracle SQLJ 8.1.7 and later supports the ISO specification. Note that the SQLJ ISO standard requires full support for JDK 1.2 or later. Thus, in order to be fully ISO compliant you would need to use JDK 1.2 or later and translate as well as run your SQLJ program with runtime12.zip (or runtime12ee.zip). Additional features, while recognized by Oracle SQLJ, are not supported by the Oracle JDBC drivers or Oracle database: the path and transformGroup attributes on connection contexts, as well as type map property entries of the kinds JAVA_OBJECT and DISTINCT are not supported presently by Oracle. Furthermore, some minor SQLJ features that were already part of the ANSI standard, while recognized by Oracle SQLJ, are not supported by the Oracle JDBC drivers or Oracle database. These features include the sensitivity, holdability, and returnability constants you can set in a with clause, and positioned UPDATE/DELETE/INSERT operations -see also "Does Oracle SQLJ support the WHERE CURRENT OF construct?").

What software components are required to translate a SQLJ application? to run a SQLJ application?

To translate your application you need the following:

• the file translator.zip (typically in [ORACLE_HOME]/sqlj/lib) on your system and in your CLASSPATH
• one of the runtime files runtime.zip (any JDK, any JDBC driver), runtime11.zip (for JDK 1.1.x, JDBC 8.1.7), or runtime12.zip (for JDK 1.2, JDBC 8.1.7) which can typically be found in [ORACLE_HOME]/sqlj/lib) on your system and in your CLASSPATH
• For online checking, the JDBC driver classes�classes111.zip for JDK 1.1.x or classes12.zip for JDK 1.2 (also typically in [ORACLE_HOME]/jdbc/lib) on your system and in your CLASSPATH
• a Java compiler and execution environment compatible with JDK version 1.1.x or 1.2

• the JDBC driver classes�classes111.zip for JDK 1.1.x or classes12.zip for JDK 1.2 on your system and in your CLASSPATH
• one of the runtime files runtime.zip (any JDK, any JDBC driver), runtime11.zip (for JDK 1.1.x, JDBC 8.1.7), or runtime12.zip (for JDK 1.2, JDBC 8.1.7) which can typically be found in [ORACLE_HOME]/sqlj/lib) on your system and in your CLASSPATH.

If you have SQLJ 9.0.1 or later and are using a non-Oracle JDBC driver, you should specify runtime-nonoracle.zip.

IMPORTANT NOTE: In SQLJ 8.1.6 and earlier there was only one runtime.zip library. Furthermore, this library was also contained in translator.zip. Starting with 8.1.7, the runtime classes have been removed from the translator library and an appropriate runtime library must now be provided separately on the CLASSPATH.

Can I use the Oracle SQLJ translator to translate an application to run against a non-Oracle SQLJ runtime?

Yes, if you do not use Oracle-specific features in your code and do not use the default Oracle customizer (for example, if you set -profile=false for translation).

Is SQLJ really database neutral?

Yes. The SQLJ translator makes minimal assumptions about the SQL dialect. We assume, for example, that you can have the following:

#sql positer = { ... SQL operation ...};

if the SQL statement begins with SELECT, but not if it begins with INSERT.

The SQL in such constructs is simply passed to the JDBC driver. If your JDBC driver and database understand the SQL dialect you embed, SQLJ doesn't complain. When semantic analysis is done on a SELECT statement in a #sql construct, SQLJ does not make assumptions that your SELECT statement syntax is SQL92.
Nothing about the SQLJ language is JDBC-specific. It can in principle be implemented with interfaces other than JDBC. The Oracle implementation of SQLJ happens to be based on JDBC.

4.3 JDBC Drivers

What should I know about the Oracle JDBC drivers?

• The OCI drivers (OCI / OCI 8 and OCI 7), included with Oracle client installations, are intended for client-side applications in an Oracle client environment. There must be an Oracle client-side installation.
• The Thin driver is 100% Java and is intended for applets; however, it can also be used for client-side applications. It does not require an Oracle client-side installation..
• The server-side internal driver (sometimes cryptically referred to as the KPRB driver) is inside the Oracle server and is used for server-side Java applications--Java stored procedures, Enterprise JavaBeans, and CORBA objects.
• The server-side Thin driver, available with Oracle 8.1.6 and later, is used to connect to a remote server from inside a server-side Java application.
• Unlike the OCI drivers, the Thin drivers only work with TCP/IP-based networks.
• The Thin drivers re-implement SQL*Net protocol in Java. As a result, all necessary packing and unpacking of data packets across the network is done in Java. Performance is therefore typically slower than with the OCI driver, which implements all of this packing and unpacking in C.

Yes. Oracle SQLJ is based on the ANSI-standard SQLJ Reference Implementation. If you do not use Oracle-specific features and do not use the Oracle customizer (for example, if you set -profile=false for translation), then you do not have to use an Oracle JDBC driver. With SQLJ version 9.0.1 and later specify runtime-nonoracle.zip in your CLASSPATH.

4.4 SQLJ, JDBC, and JDK Versions

What are the different versions of Oracle SQLJ and how do I get them?
 

Version 9.0.1	Available with the Oracle9i 9.0.1 database release. Will also be made available as an OTN download.
Version 8.1.7	Available with the Oracle8i 8.1.7 database release or from the Oracle Technology Network (OTN) Web site (technet.oracle.com). Also available with Oracle JDeveloper 3.2.
Version 8.1.6	Available with the Oracle8i 8.1.6 database. Also part of Oracle JDeveloper 3.1.
Version 8.1.6 SDK Beta	Available as a patch from the OTN Web site. In particular, you can use this release to replace the 8.1.5 translator.zip file included in JDeveloper 3.0 (allowing JDeveloper to translate SQLJ applications under JDK 1.2), or you can patch SQLJ 8.0.5/7.3.4 (a.k.a. 8.1.5) to work with JDK 1.2.
Version 8.1.5	Available with the Oracle8i 8.1.5 database release and with JDeveloper 3.0.
Versions 8.0.5 / 7.3.4	Available from the OTN Web site. (Identical in functionality to version 8.1.5)

 

How do the Oracle SQLJ 8.0.5 and 7.3.4 versions differ from the 8.1.5 versions?

They don't. These versions differ in name and packaging only. Since SQLJ 8.1.5 is available with the Oracle 8.1.5 database we provide SQLJ 8.0.5/7.3.4 version for free download, so that programmers can use SQLJ in conjunction with 8.0.x or 7.3.4 JDBC drivers against an 8.0.x or 7.3.x database.

The main discrepancies to keep in mind are restrictions (and some changes) when using the 8.0.x and 7.3.x JDBC drivers:

• Oracle objects and object references are not supported.
• Oracle LOBs and ROWIDs are not supported in the Oracle 7.3.x JDBC drivers and are supported by the oracle.jdbc.driver.OracleBlob, OracleClob, OracleBfile, and OracleRowid classes in the Oracle 8.0.x JDBC drivers (as opposed to the oracle.sql.BLOB, CLOB, BFILE, and ROWID classes used in the Oracle 8.1.x or later JDBC drivers).
• The oracle.sql package (wrapper classes for SQL data) did not exist in the Oracle 8.0.x and 7.3.x JDBC drivers.

• All Oracle SQLJ drivers support Oracle JDBC 8.0.x and 7.3.4. However, if you use SQLJ 8.1.7 or later, you need to make sure to specify runtime.zip in your CLASSPATH. (The runtime11.zip and runtime12.zip files were made to work specifically with same-version JDBC drivers.)
• We recommend that you download and use the Oracle8i SQLJ Developer's Guide and Reference for 8.0.x and 7.3.x JDBC which was written especially for JDBC 8.0.x and 7.3.x users. You can obtain it from: http://technet.oracle.com/docs/tech/java/sqlj_jdbc/listing.htm.

Oracle SQLJ does not support JDK 1.0.2.

Notes:

• This table applies to both the SQLJ translator and the SQLJ runtime.
• During translation, Oracle SQLJ determines which version of the Oracle JDBC driver and which version of the Oracle database are being used and automatically uses the appropriate semantics-checker. Furthermore, if you have specified the runtime12.zip file during translation (in SQLJ 8.1.7 and later), then SQLJ will generate JDK 1.2-specific code, and you will also need JDK 1.2 and runtime12.zip to run your SQLJ program.
• None of the SQLJ versions prior to SQLJ 8.1.7 will work with the 8.1.7 JDBC drivers.

Do I need a different set of SQLJ class files depending on whether I'm running under JDK 1.1.x or JDK 1.2 or later?

There is only a single translator.zip file for all JDK and JDBC versions. Additionally, if you are using SQLJ 8.1.7 or later, you need to select one of several runtime libraries:

• runtime.zip supports all JDBC drivers � not just the one that corresponds in version to your SQLJ version. For example, the runtime.zip file from SQLJ 8.1.7 can be used in conjunction with any one of the following JDBC versions: 7.3.4, 8.0.5, 8.1.5, 8.1.6, or 8.1.7.
• runtime11.zip supports the JDK 1.1.x environment and the same-version JDBC driver. For example, you would use the SQLJ 8.1.7 runtime11.zip together with JDK 1.1.x and the Oracle 8.1.7 JDBC driver. This runtime provides a slightly better performance and is also the recommended runtime for applets.
• runtime12.zip supports the JDK 1.2 or later environment and the same-version JDBC driver. For example, you would use the SQLJ 8.1.7 runtime12.zip together with JDK 1.2 and the Oracle 8.1.7 JDBC driver. JDK 1.3 is not fully supported by SQLJ 8.1.7 - you should use SQLJ version 9.0.1 or later.

You can also use runtime12ee.zip if your SQLJ and JDBC versions match and you are running in a JDK 1.2 or later environment that provides support for the packages javax.naming and javax.sql.

5. SQLJ Language and Programming

In the sample programs, I see the following import clauses at the beginning of the source code. What are these packages? Do I need to use the same import statements in my own SQLJ files?

import java.sql.*;
import sqlj.runtime.*;
import sqlj.runtime.ref.*;
import oracle.sqlj.runtime.*;

These packages belong to the standard JDBC API (java.sql.*) and the SQLJ runtime API (sqlj.runtime.*). It's often simplest to import the entire packages; however, you may choose instead to import only those classes that your application will use directly.

• Package java.sql includes:

• the SQLException class�this class and its subclasses are used for all exceptions raised by SQLJ at runtime;
• the JDBC type classes such as java.sql.Date, java.sql.ResultSet, and so on.

• Package sqlj.runtime includes the ExecutionContext class (execution contexts are discussed below) as well as wrapper classes for SQLJ stream types (BinaryStream, for example) and abstract classes that are implemented by connection context classes and iterator classes.
• Package sqlj.runtime.ref includes implementations of some of the sqlj.runtime interfaces and abstract classes�for example, the DefaultContext class (which is the only connection context class you will need if you are connecting to just one kind of schema and if you are not using the Oracle class below).
• Package oracle.sqlj.runtime contains the Oracle class, used in getting and closing connections (discussed later in this FAQ).

Discussion of connection contexts can get a little deep, but you can think of a connection context as a framework for a set of connections for SQL operations that use a particular set of database resources. This mechanism allows you to implement more robust semantics-checking during translation.

In SQLJ, each database connection uses its own instance of a connection context class. Each connection of a particular connection context uses an instance of a single connection context class. SQLJ provides one connection context class--sqlj.runtime.ref.DefaultContext--and you can declare additional connection context classes as needed.

Each connection context uses its own class, so has its own "type". Such "strong typing" is one of the key advantages of SQLJ, allowing for rigorous semantics-checking during translation.

The fact is, however, that many (even most) applications need only one connection context and so can get by using only the DefaultContext class without declaring any additional classes.

(In case you do want to use multiple connection context classes, see How do I create iterator classes and connection context classes? later in this FAQ.)

What's an iterator?

An iterator is SQLJ's version of a result set, but a strongly typed version--column types and (optionally) column names are specified.

You declare an iterator class for each kind of iterator you want to use (where "kind of iterator" refers to iterators with a given set of columns). As with connection contexts, this strong typing is a key advantage of SQLJ. There are two categories of iterators--"named iterators", where you specify column names as well as column types, and "positional iterators", where you specify only column types (which SQLJ references by position instead of by name).

How do I create iterator classes and connection context classes?

SQLJ provides syntax for declaring (i.e., creating) iterator classes and connection context classes.

Here is an example of a named iterator class declaration (with a String column named ename and a double column named sal):

#sql public iterator NamedIterClass (String ename, double sal);

Here is an example of a positional iterator class declaration:

#sql public iterator PosIterClass (String, sal);

And here is an example of a connection context class declaration:

#sqlj public context MyContextClass; You can declare an iterator or connection context class wherever it would be legal to define a class of any kind. When the SQLJ translator encounters an iterator declaration or connection context declaration, it inserts a class definition into the .java output file.

Note: If you declare an iterator class or connection context class at the class level or nested-class level, it might be advisable to declare it public static as opposed to simply public.

What's an execution "context"?

Execution contexts provide a means of exerting control and checking status of your SQL operations. Each execution context instance is an instance of the standard sqlj.runtime.ExecutionContext class. There is an implicit execution context instance with each connection context instance, but you can explicitly create and use execution context  instances as well.
 Examples of execution context class methods include:

• getUpdateCount(), which returns the number of rows affected by an update, insert, or delete
• setQueryTimeout(), which sets the timeout limit for your queries

(See the next question regarding how to specify which connection context instance and/or execution context instance to use for a given statement.)

How do I specify which connection context instance and/or execution context instance a SQLJ statement should use?

Suppose you have instantiated a connection context instance connctxt and an execution context instance execctxt. Consider the following examples.

#sql [connctxt] { ...SQL operation...};

The preceding specifies that connctxt should be used for this statement. The default execution context instance will be used.

#sql [execctxt] { ...SQL operation...};

The preceding specifies that exectxt should be used for this statement. The default connection context instance will be used.

#sql [connctxt, execctxt] { ...SQL operation...};

The preceding specifes that connctxt and exectxt should both be used (the connection context instance must precede the execution context instance when specifying both).

What exactly does the fetch size correspond to?

Does anyone know what exactly the fetch size corresponds to, (the number of rows returned?)?

Yes, it is a hint as to how many rows are supposed to be returned in a single round trip when you read through a result set. The default is 10 rows. If the result sets contains certain kinds of columns (namely LONG, or LONG RAW) then the JDBC driver always uses a fetch size of 1 implicitly.

Where is the "Oracle" class and what does it provide?

The Oracle class is in the oracle.sqlj.runtime package for release 8.1.5 and higher and provides convenient static methods to create and close instances of the standard sqlj.runtime.ref.DefaultContext class, used for database connections.

Use Oracle.connect() to create a DefaultContext instance and establish it as your default connection.

Use Oracle.getConnection() to simply create a DefaultContext instance.

Use Oracle.close() (available in release 8.1.6 and higher) to close your default connection.

For earlier versions of Oracle SQLJ, you will have to use equivalent functionality (constructors and methods) of the standard sqlj.runtime.ref.DefaultContext class. See the SQLJ Developer's Guide and Reference for more information.

Can I use SQLJ to write multithreaded applications?

The short answer is Yes. The long answer involves connection context instances and execution context instances.

• As discussed earlier, each database connection is represented by a connection context instance--an instance of the standard DefaultContext class or a user-declared connection context class. An instance of the standard ExecutionContext class can be used for SQL status and control operations. By default, each connection context instance has an implicit execution context instance; however, you can also explicitly declare and use an execution context instance.
• In a multithreaded application, each thread must use its own execution context instance (that is, all SQLJ statements using a thread must use the same execution context instance, and no statements in any other threads should use that execution context instance). You can do this by declaring and specifying separate execution context instances, or by declaring and using separate connection context instances (which would satisfy the requirement, since each connection context instance has its own execution context instance).
• If an execution context were shared between threads, then the results of a SQL operation performed in one thread would be visible in the other thread. If both threads are executing SQL operations, a race condition would occur in which the results of an execution in one thread would be overwritten by the results of an execution in the next thread before the first thread has processed the original results. Furthermore, if a thread attempts to execute a SQL operation using an execution context that is currently being used to execute an operation in another thread, a runtime exception is raised. To avoid such problems, each thread should use a distinct execution context whenever a SQL operation is executed on a shared connection context.

Given that SQLJ supports only static SQL, can I intermix SQLJ and JDBC statements in my source code so that I can use dynamic SQL in the same application?

Yes--you can have JDBC statements in your SQLJ source code. Furthermore, features are built into SQLJ to allow convenient interoperability between SQLJ iterators and JDBC result sets and between SQLJ connections and JDBC connections.

If you are using SQLJ version 9.0.1 or later, you got the best of both worlds: you can embed dynamic SQL code into SQLJ statements using the syntax ":{ java String expression }" - see sqlj/demo/DynamicDemo.sqlj in your SQLJ distribution for more details. The remainder discusses how to use the general SQLJ-JDBC interaoperability.

To create a JDBC result set rs from a SQLJ iterator iter, use the getResultSet() method of your iterator instance:

ResultSet rs = iter.getResultSet();

To create a SQLJ iterator iter from a JDBC result set rs, use a SQLJ cast statement:

#sql iter = { CAST : rs };

To create a JDBC connection instance conn from a SQLJ connection context instance ctx (inheriting the same underlying connection to the database), use the getConnection() method of your connection context instance:

Connection conn = ctx.getConnection();

To create a SQLJ connection context instance ctx from a JDBC connection instance conn (again inheriting the same underlying connection to the database), use the connection context class constructor that takes a JDBC connection instance as input:

DefaultContext defctx = new DefaultContext(conn);

(This example uses DefaultContext, the default connection context class provided with SQLJ.)

Note: Another way to use dynamic SQL in an Oracle SQLJ application is through PL/SQL, as discussed under Can SQLJ interact with PL/SQL in my source code? later in this FAQ.

6. Oracle-Specific Features

Do I need to do anything special to use Oracle-specific extensions?

No--just do a standard Oracle SQLJ installation, use an Oracle JDBC driver at translation and runtime (as is typical), and use the default settings of the Oracle SQLJ translator.

The Oracle semantics-checkers (in oracle.sqlj.checker) and Oracle customizer (in oracle.sqlj.runtime.util) are included with a standard installation. The  SQLJUTL package, required for online checking of stored procedures in an Oracle database, is also included with a standard installation.

By default, when you run the translator it will use the oracle.sqlj.checker.OracleChecker semantics-checker front end, which in turn will run an Oracle semantics checker that is appropriate for your offline/online settings, JDBC driver, and database version.

Also by default, the translator will run the Oracle customizer so that your application can use Oracle-specific features at runtime.

What Oracle-specific features does Oracle SQLJ support?

Oracle SQLJ supports the following Oracle-specific types as host variables and iterator columns:

• Oracle objects, collections, and object references (the Java implementations can either be strongly typed, as produced by the Oracle JPublisher utility, or weakly typed as in the JDBC standard)
• Oracle ROWIDs
• Oracle REF CURSOR types
• Oracle LOBs (BLOBs or CLOBs) and external files (BFILEs)
• Java wrapper classes in the oracle.sql package for raw SQL data (such as the oracle.sql.CHAR class to wrap character data, the NUMBER class for numeric data, and the RAW class for raw binary data

In addition to the preceding type extensions, Oracle SQLJ offers the following extended functionality:

• extended SQLJ stream functionality (as output variables and function return values) extended result set and iterator functionality (as output variables and nested in iterator columns)
• extended BigDecimal Support (mapping to non-default SQL datatypes)
• PL/SQL anonymous blocks and stored procedure or function calls (see the next question)
• performance enhancements (SQLJ release 8.1.6 and later)--statement caching, update batching, and parameter definitions
• support for FETCH CURRENT to fetch from an iterator without performing an associated movement (SQLJ release 8.1.7 and later)
• support for FETCH from a ResultSetIterator or ScrollableResultSetIterator. This permits SQLJ programming without having to declare strongly typed iterator types, but also foregoes some of the associated checking at translate time (SQLJ release 9.0.1 and later)
• support for embedding dynamic SQL code directly in SQLJ statements (SQLJ release 9.0.1 and later)
• support for direct generation of Oracle JDBC code, bypassing generation and customization of SQLH profile (.ser) files (SQLJ release 9.0.1 and later)

"Can you briefly summarize how to use the CustomDatum feature? I am entirely new to this."

CustomDatum classes are used (most often) to treat SQL Object type instances as instances of a Java class. From Java you read and write instances of the CustomDatum implementation, and these get translated in the database to SQL objects. The easiest is to generate your CustomDatum Java classes with the JPublisher tool. Consider the following example.

Note: In Oracle 9.0.1 and later CustomDatum has been superseded by ORAData, though the former is still supported. The functionality of ORAData is nearly indentical to CustomDatum.

• You have the SQL Object types ADDRESS and PERSON in the schema SCOTT.
• You can run sqlj (i.e. have the SQLJ translator.zip and runtime.zip and the JDBC classes111.zip in the CLASSPATH, and can use the JDBC-OCI driver).
• In order to generate the CustomDatum Java wrapper classes for your SQL types you would say:

• Now you can use instances of these classes as follows (for example in some file test.sqlj).

• To compile and run, do: sqlj test.sqlj; java test.

Or, go to the full documentation set at http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/index.htm and look at the JDBC, SQLJ, and JPublisher manuals.

How can I pass objects between my SQLJ program and Oracle Stored Procedures?

You have two basic choices:

• Define corresponding SQL object types to your Java object types. Given an existing SQL Object type it is easiest to use the JPublisher tool to create a Java wrapper class for that type. (You may want to look in the SQLJ demo/jpub area for examples of this.) Then you can write instances of the Java class to/from the stored procedure as SQL object instances. The stored procedure can be written in PL/SQL or in Java. The advantage of this approach is that your SQL objects can be fully queried and manipulated in SQL. The disadvantage is that you must start with the SQL type definition, not with some Java class.
• You can serialize or deserialize instances of your (serializable) Java class and essentially send a RAW or a BLOB parameter to the database or receive one from the database. In this case you need to implement the stored procedure in Java, and this stored procedure will use the original definition of your Java class. In SQLJ 8.1.7 and later there is some special support to simplify serialization and deserialization of Java objects. You may want to run the SQLJ example demo/jdbc20/JavaSerialization.sqlj. The advantage is that you are only dealing with the Java class. The disadvantage is that the RAW parameter type has length limitations and the BLOB parameter type can only be written to from a JDBC-OCI client.

Yes, and in fact this is another way (in addition to using JDBC code) to employ dynamic SQL in an Oracle SQLJ application. (Of course using PL/SQL is not a feature of standard SQLJ; your application would not be portable to other platforms.)
 Within your SQLJ statements, you can use PL/SQL anonymous blocks and call PL/SQL stored procedures and stored functions, as in the following examples:  Anonymous block:

#sql {
   DECLARE
      n NUMBER;
   BEGIN
      n := 1;
      WHILE n <= 100 LOOP
         INSERT INTO emp (empno) VALUES(2000 + n);
         n := n + 1;
      END LOOP;
   END
};

Stored procedure call (returns the maximum deadline as an output parameter into an output host expression):

#sql { CALL MAX_DEADLINE(:out maxDeadline) };

Stored function call (returns the maximum deadline as a function return into a result expression):

#sql maxDeadline = { VALUES(GET_MAX_DEADLINE) };

Does Oracle SQLJ support PL/SQL BOOLEAN, RECORD, and TABLE types as input and output parameters?

Oracle SQLJ will support theses types as soon as the Oracle JDBC drivers do, but it is unclear when or if JDBC will support them. In the meantime, however, there are workarounds - you can create wrapper procedures that handle the data as types supported by JDBC.

For example, say that in your SQLJ application you want to call a stored procedure that takes a PL/SQL BOOLEAN input parameter. You can create a stored procedure that takes a character or number from JDBC and passes it to the original stored procedure as a BOOLEAN. If the original procedure is PROC, for example, you can create a wrapper procedure MY_PROC that takes a 0 and converts it to FALSE or takes a 1 and converts it to TRUE, and then calls PROC.

Similarly, to wrap a stored procedure that uses PL/SQL records, you can create a stored procedure that handles a record in its individual components (such as CHAR and NUMBER). To wrap a stored procedure that uses PL/SQL tables, you can break the data into components or perhaps use Oracle collection types.

Does Oracle SQLJ support the WHERE CURRENT OF construct?

Not as of release 9.0.1, though possibly in the future. As a workaround, you can explicitly select the ROWID column into your iterator, and then use WHERE ROWID=xxx in place of WHERE CURRENT OF in the UPDATE statement. This workaround would go along the following lines.

Standard SQLJ code:

Does Oracle SQLJ support DML returning?

Not currently. As a workaround, use a PL/SQL block, for example as follows:

#sql {
   BEGIN
      UPDATE ...
      RETURNING x, y, z INTO :OUT x, :OUT y, :OUT z;
   END
};

Can I use the SQL object features with a JDBC 8.1.x driver against an 8.0.x database?

No. Even though the 8.1.x JDBC drivers are backward compatible to 8.0.x databases, they require an 8.1.x database to support SQL object features. In a configuration using an 8.1.x driver and 8.0.x database, you might be able to compile a SQLJ application that uses SQL objects, but it will not work at runtime.

Can I use SQLJ to return PL/SQL arrays from stored procedures?

Although SQL VARRAYs have been supported by SQLJ and JDBC, PL/SQL index tables are not supported by SQLJ or by JDBC at this time. You would have to use a PL/SQL stored procedure wrapper to convert a PL/SQL index table to of from a VARRAY or nested table to access the argument from Java.

For example, if you are trying to call procedure proc01, defined as follows:

package pack01 is
  type rec01 is record(n1 number, d1 date);
  procedure proc01 (r rec01);
  ...
end;

you can create a wrapper method as follows:

package pack01_wrapper is
  procedure proc01_wrapper (n1 number, d1 date);
  ...
end;

package body pack01_wrapper is
  procedure proc01_wrapper (n1 number, d1 date) is
    r pack01.rec01;
  begin
    r.n1 := n1;
    r.d1 := d1;
    pack01.proc01;
  end;
  ...
end;

With Oracle 8i, new object types and new collection types (VARRAYs and nested tables) are available in JDBC. Thus, your wrapper package could use an object type with the same attributes as the record, rather than "exploding" the record into individual components as shown here.

Does Oracle SQLJ support REF CURSORS?

Oracle SQLJ supports REF CURSORS along the same lines that Oracle's JDBC drivers do.

Specifically, a REF CURSOR that is a SELECT column, a function return, or an OUT parameter of a procedure can be materialized in SQLJ as a JDBC ResultSet or a SQLJ iterator instance. In Oracle 9.0.1 and later JDBC stored procedures permit passing a JDBC ResultSet out as a REF CURSOR argument (with an Oracle-specific API to enable this). However, at this point SQLJ stored procedures do not permit passing SQLJ iterators in this way.

7. Translation (and Compilation and Customization) Process

7.1 Basic Functionality

How do I run the SQLJ translator?

The Oracle SQLJ installation includes a front-end utility that automatically runs the Oracle SQLJ translator, your Java compiler, and the Oracle profile customizer.

For example, on Solaris you run the UNIX command-line utility sqlj:

sqlj Foo.sqlj

What are the fundamental steps in translating a SQLJ application? What is input and output during the translation process?

There are three basic steps: translation, compilation, and customization (optional). When you run the SQLJ front-end utility, by default all three steps are executed automatically.
 In the translation step, the SQLJ translator processes, checks, and translates your .sqlj source file. It checks it for rudimentary semantics errors and optionally connects to a target database to verify your SQL instructions against the actual resources in the specified database schema. The translator produces the following:

• one or more .java file(s) with calls to the SQLJ runtime (the runtime, in turn, calls the chosen JDBC driver when the application is executed)
• one or more .ser serialized resource file(s) containing the application "profile(s)", which contain information about all of your SQL instructions

Note: if you use Oracle SQLJ 9.0.1 or later you can specify -codegen=oracle. In this case, the SQLJ translator generates Oracle JDBC code directly, and skips the generation and customization of .ser files.

Do I have to customize my application? Where do I get the customizer utility?

You only need to customize your application if you are using features that are specific to a particular database or JDBC driver, such as vendor-specific datatypes or performance enhancements. If you are using only standard features, then you can disable the command-line -profile flag to skip the customization step.
 Vendors provide vendor-specific customizers. The Oracle customizer is provided with the Oracle SQLJ product and by default is executed automatically when you run the front-end SQLJ utility.

For basic use of SQLJ, how much do I need to know about profiles (.ser files)?

Unless you plan to get fancy, you only need to be aware of their existence and naming conventions. A profile contains all of the information about your SQL statements--commands, input parameters, and output parameters.
 If your source file is Foo.sqlj, the profile will be in Foo_SJProfile0.ser. Any additional profiles would be in Foo_SJProfile1.ser, Foo_SJProfile2.ser, and so on.

You will have more than one profile for an application if you use more than one connection context class, but that is an advanced topic. Most applications use only one connection context class and therefore have only one profile. (Connection contexts are explained under What's a connection "context"? earlier in this FAQ.)

Can I compile my SQLJ program without customization? What do I need to know to create an application that can be deployed to multiple platforms?

SQLJ is very interesting because of the possibility of interoperability and including vendor customizations. According to the Oracle SQLJ programmer's guide, Oracle customization is performed by default. This requires that the Oracle customization classes and Oracle JDBC driver is needed to deploy on any platform when using a customized .ser file.

• Is it possible to turn off the customizer and still use the SQLJ translator?

• Why is Oracle JDBC driver required if Oracle customizations are present in .ser file? I can see the need for OraCustomizer, but why the driver?

• If deploying an app with an .ser file that includes Oracle customization on, for example, a DB2 system, what is the most lightweight way to include the oracle customization and JDBC? Currently, I'm appending both entire .jar files to the end of the CLASSPATH.

• Is anyone else writing a customizer other than the three that are included in 8.1.6?

• The Oracle SQLJ (8.1.7) release supports the SQLJ ISO standard, which is based on JDBC 2.0. This results in an incompatibility between SQLJ customizers/customizations from the ANSI specification (such as the 8.1.6 and earlier Oracle customizers and the IBM customizer) and from ISO. Thus, if you want to plug and play customizers you may be restricted to using Oracle SQLJ 8.1.6 or earlier for the time being.

Online checking uses a connection to a specified database to check your SQL operations against database resources.

Both kinds of checking do the following: 1) analyze the types of Java expressions in your SQLJ executable statements; 2) categorize your embedded SQL operations (based on SQL keywords such as SELECT and INSERT).

In addition, online checking (but not offline checking) does the following: 3) analyzes your embedded SQL operations and checks their syntax against the database; 4) checks the Java types in your SQLJ executable statements against SQL types of corresponding database columns and stored procedure and function parameters.

I know I can enable online checking by giving the option -user=scott. How can I disable online checking?

Specify an empty user name: -user=

Similarly, to enable online checking for a particular connection context: -user@Ctx=scott

And to disable online checking for a particular connection context: -user@Ctx=

7.2 Java Configurations

Can I use Java compilers other than javac (the standard compiler included with the Sun JDKs)?

Yes. the SQLJ translator defaults to the standard javac compiler, but lets you specify an alternative compiler through the command-line -compiler-executable option. Any compiler you use, however, must behave as follows:

• It must return a non-zero exit code to the operating system whenever a compilation error occurs.
• When it encounters errors, the source-code line information it provides must be in either Sun javac format or Microsoft jvc format.

Is it possible to use the JDK 1.0.2 version of the javac compiler to compile .java files generated by the SQLJ translator?

No. You need a Java compiler from JDK 1.1.x or 1.2.

7.3 National Language Support

Does the Oracle SQLJ translator provide NLS support?

Yes, SQLJ provides NLS support in the following areas:

• character encoding for reading and generating .sqlj and .java source files during translation, through the SQLJ translator -encoding option
• character encoding for error and status messages during SQLJ translation or when your application runs, through the Java file.encoding property
• locale for error and status messages during SQLJ translation or when your application runs, through the Java user.language property
• comprehensive globalization/multibyte character support, through support of national language character sets

With SQLJ release 9.0.1 and later additional support is provided for SQL NATIONAL LANGUAGE CHARACTER SET unicode columns through additional SQLJ-specific types: oracle.sql.NCHAR / NCLOB / NString, etc.

Can I specify the encoding for SQLJ to use?

Yes. You can use the command-line -encoding option to specify the NLS encoding that the SQLJ translator will apply to .sqlj and .java files being input and .java files that it outputs. The default is whatever is in your file.encoding system property.

The translator also passes the -encoding value to the Java compiler it will use, unless you have instructed it not to do so by disabling -compiler-encoding-flag.

8. Development Environments

Can I develop and debug SQLJ programs with Oracle JDeveloper?

Yes, JDeveloper fully incorporates Oracle SQLJ.

Developing: Creating SQLJ code in JDeveloper is no different than creating Java code. SQLJ files (.sqlj) can be included in your projects as well as Java files (.java).

Compiling: When you compile a SQLJ source file (identified by the .sqlj file extension), the Oracle SQLJ translator and Oracle profile customizer are automatically invoked. Prior to compiling, you can use JDeveloper to set some of the SQLJ command-line translation options.

Debugging: SQLJ statements can be debugged in-line as your application executes, as with any Java statements. Reported line numbers map back to the original SQLJ source code (as opposed to the Java code that the translator generates).

Note: JDeveloper version 3.0 (and 2.0), were packaged with Oracle SQLJ version 8.1.5. While JDeveloper 3.0 supports JDK 1.2, Oracle SQLJ 8.1.5 does not. If you want to develop a JDK 1.2 application that uses SQLJ in JDeveloper 3.0, you need to download and apply the SQLJ 8.1.6 SDK patch release from the Oracle Technology Network (OTN) Web site (technet.oracle.com). SQLJ versions provided with JDeveloper 3.1 and higher do not have this limitation.

Can I develop and debug SQLJ programs with other IDEs such as VisualAge for Java, Visual Café, or Visual J++?

SQLJ is fully integrated with Oracle JDeveloper. In addition, VisualAge For Java supports an IBM-specific version of SQLJ in the IDE.

The SQLJ standard incorporates a set of interfaces and APIs designed to ensure that IDE vendors can easily integrate SQLJ development and debugging capabilities.

Almost all of the current IDEs provide hooks to integrate preprocessors. You may already be able to incorporate SQLJ into your development environment. Consult your IDE documentation for information.

Compiling sqlj via ant (jakarta)

"How do you configure jakarta ant to compile .sqlj programs?"

Note that the source for the sqlj wrapper executable sqlj.exe is available on the SQLJ downloads at http://technet.oracle.com/. This executable is NT equivalent to the Unix sqlj shell script, and it expands wildcards, performs some transformations on options in the command line options, and it also looks up some environment variable settings. Essentially, it boils down to calling:

java sqlj.tools.Sqlj <options and files>

The following are some snippets from an ant build.xml. This omits many details and the sqlj target does not work, since SQLJ expects a list of files rather than being able to accept wildcards.

Can SQLJ be used in Java applets?

Yes. The SQLJ runtime environment consists of a thin layer of pure Java code together with the JDBC driver being used. Oracle offers the 100% Java JDBC Thin driver, which is roughly 150K compressed and can be downloaded into a client browser along with the applet. In your applet code, specify the Oracle JDBC Thin driver for your database connections. See the SQLJ Developer's Guide and Reference for more information.

Notes:

• JDK 1.1.x or higher is still required. If the end-user's browser uses an older JDK, then a plug-in or some other means of using JDK 1.1.x or 1.2 becomes necessary.
• With SQLJ 8.1.7 and higher, we are providing the SQLJ runtime11.zip class library that is well-suited for applet deployment, since it minimizes use of the Reflection API (java.lang.reflect.*). By contrast, the runtime.zip library uses reflection extensively in order to provide cross-compatibility with all Oracle JDBC drivers. The runtime11.zip library also requires a same-version JDBC driver. For example, if you use SQLJ 8.1.7 runtime11.zip in your applet, you would also use the Oracle 8.1.7 thin JDBC driver provided in classes111.zip.
• Certain SQLJ statements, such as the CAST statement, or statements that use Oracle SQLJ type extensions, such as REF CURSOR, or oracle.sql.CustomDatum types still require use of Java reflection, which may not be enabled in all browser environments.
• If your applet does not use Oracle-specific features, you may want to set -profile=false during translation so that your application is not customized. If it is not customized, then there will be no requirement for the Oracle runtime (that is, you can remove the oracle.* class hierarchy from the runtime.zip library).
• If you are using SQLJ release 9.0.1 or later you should use the setting -codegen=oracle and distribute your applet with the runtime11.zip library. This code generation setting fully eliminates any use of reflection.

No, this is prevented by Java applet security. However, you could use the Oracle Net8 Connection Manager product to work around this limitation. Alternatively, you may be able to use browser-specific APIs to obtain the required privileges.

For end-users, what browsers will work with SQLJ applets?

Netscape Communicator 4.x and Microsoft Internet Explorer 4.x include JDK 1.1.x and are known to work. Communicator 5.x and Internet Explorer 5.x include JDK 1.2 and are presumed to work

Netscape Navigator 3.x and Microsoft Internet Explorer 3.x use JDK 1.0.2. To use these browser versions, you must use a plug-in or some other means of employing JDK 1.1.x or above.

Can SQLJ be used in middle-tier Java applications?

Yes, middle-tier SQLJ applications can be executed in any JDK 1.1.x-compliant or 1.2-compliant Java Web server or Java application server, including the Oracle Application Server. In a middle-tier environment, SQLJ applications can use either the JDBC OCI driver or the JDBC Thin driver.

Can SQLJ be used inside servlets?

Yes. Write it as any servlet, but with the .sqlj file name extension for your source file. Then run the SQLJ translator as usual.

Can SQLJ be used inside JavaServer Pages?

No, as of OC4J 10.1.3.1, this is no longer supported.

Can SQLJ applications work across firewalls?

Yes--there are no firewall limitations specific to SQLJ. Because the runtime environment for a SQLJ application consists of a thin layer of pure Java code on top of a JDBC driver, a SQLJ application will work with any firewall with which the chosen JDBC driver will work.

The Oracle JDBC OCI driver and Thin driver can work in either an intranet or extranet setting. In an extranet deployment, the drivers can be used with firewalls which have been SQL*Net certified.

The following firewalls have been certified with SQL*Net:

• stateful inspection firewalls from Checkpoint, SunSoft, and Cisco Systems
• proxy-based firewalls from Milkyway Networks, Trusted Information Systems, Raptor, Secure Computing Corporation, and Global Internet

Can I use operating system authentication to connect from SQLJ?

You can logon using external credentials with the OCI driver by passing in nulls as username and password.

10. Running SQLJ Programs

What debugging support does SQLJ offer?

At the simple end of the spectrum are the translator -linemap option and the server-side debug option.

The -linemap option instructs the SQLJ translator to map line numbers in the SQLJ source code to line numbers in the generated .class file (the .class file produced by the compiler during compilation of the .java file that was generated by the translator). This way, you can trace runtime errors to lines of code in your SQLJ source file.

If you are using the SQLJ translator that is embedded in the server, there is no -linemap option but the same functionality is implemented automatically. You can also use the server-side debug option, which is similar in nature to the -g option of the javac compiler.

At the more complex end of the spectrum is a special profile customizer known as the "auditor installer"--sqlj.runtime.profile.util.AuditorInstaller. This customizer inserts debugging statements--called auditors--into profiles that you specify on the SQLJ command line. The debugging statements will execute during runtime as you execute the application, displaying a trace of method calls and values returned. (Some of the questions under "Basic Functionality" earlier in this FAQ touch on profiles and customizers.)

The -P-debug command-line option will instruct SQLJ to run the auditor installer.

SQLJ debugging support is also built into Oracle JDeveloper. (See Can I develop and debug SQLJ programs with Oracle JDeveloper? earlier in this FAQ.)

If I translate an application with one version of the Oracle SQLJ translator, will I be able to run the application against a future version of the Oracle SQLJ runtime?

Yes, Oracle will maintain this sort of backward compatibility. For example, an application that you translate with the 8.1.5 version of the translator will run against the 8.1.6 (or the 9.0.1) version of the SQLJ runtime.

Naturally, you must also consider Java version compatibilities. For example, you may not be able to run a particular JDK 1.2 application in a JDK 1.1 environment.