Oracle SQLJ Frequently Asked Questions

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

Have General Questions?

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


Contents

Part A. Troubleshooting

1. Troubleshooting Checklist

2. Problems Translating and Compiling SQLJ Programs

2.1 Errors When Starting the Translator

"Error in sqlj stub: invalid argument"
"Unable to initialize threads: ..."
"An application error has occurred... Exception:access violation ..." (in trying to run Sun's JDK 1.1.x on Windows NT/Windows 95)
"ExceptionInInitializerError: NullPointerException"
"NoClassDefFoundError: sqlj/tools/Sqlj"
"NoClassDefFoundError: sun/io/CharToByteConverter"
"Error: SQLJ runtime library is missing"
"The following character string is too long:"
"Oracle is not installed properly on your system. ..."
2.2 Error Messages Encountered During Translation JDK 1.3: SQLJ translator hangs and/or does not show any error messages from the Java compiler
"Cannot load JDBC driver class oracle.jdbc.driver.OracleDriver"
"Return type XXXX ... is not a visible Java type"
"Missing equal sign in assignment"
"Warning: Cannot determine default arguments for stored procedures and functions. May need to install SYS.SQLJUTL."
"Error: unable to load ... java.lang.NoClassDefFoundError"
"Unable to convert ...Xxx.ser to a class file"
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"
"Error in Java compilation: CreateProcess: javac"
2.3 Additional Translation Issues 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
Is there a way to speed up translation of a .sqlj file with online checking enabled?
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?
Type class_name of host item #nn is not permitted in JDBC
Why was the profile-key class not generated?
3. Problems Deploying and Running SQLJ Applications and Applets

3.1 Error Messages Encountered During Deployment or Runtime

"SQLException: No suitable driver"
"SQLException: unable to load connect properties file: connect.properties"
"SQLException: The network adapter could not establish the connection"
"SQLException: Connection refused"
"SQLException: ORA-01017: invalid username/password; logon denied"
"InvalidClassException: Serializable is incompatible with Externalizable"
"Profile not found, SQL state 46130"
"ClassNotFoundException: xxx.yyy_SJProfile0 for class xxx.yyy_SJProfileKeys"
"ORA-29541: class class_name could not be resolved"
"SQLNullException: cannot fetch null into primitive data type"
"SQLException: Invalid column type error"
"SQLException: unable to convert database class ... to client class ..."
"java.security.AccessControlException (accessDeclaredMembers)"
"java.lang.ClassCastException: weblogic.jdbc20.rmi.SerialXxxx"
"java.lang.NoClassDefFoundError: oracle/jdbc/OraclePreparedStatement"
"NullPointerException at java.util.zip.ZipFile.read"
JDK 1.2.1: Other "NullPointerException"
ORA-01000: maximum open cursors exceeded
ORA-01000 when using OracleConnectionCacheImpl
ORA-01000 when processing REF CURSORS on the client
3.2 Deploying and Running in the Oracle Server JavaVM Performance of Java and SQLJ in Stored Procedures
User defined and predefined PL/SQL exceptions in SQLJ Stored Procedures
"ORA-29531 - no method ... in class ..."
Container-Managed EJB does not commit SQLJ updates
"java.security.AccessControlException: the Permission (java.net.SocketPermission) has not been granted"
3.3 Using REF Cursors Error: class cannot be constructed as an iterator: classname
How to write a PL/SQL Stored Procedure that returns a result set or an iterator?
Is it possible to return a result set from a Java Stored Procedure?
REF CURSORs returned from Stored Procedures lose scrollability
3.4 Additional Deployment and Runtime Issues How can I speed up execution of my SQLJ application?
I'm having problems with retrieval of CHAR fields in SELECT statements.
Character comparison works with "" but not with NULL
Using "WHERE column IN (value_list)" with a value_list of unknown size
Is SQLJ ignoring my UPDATE statements?
Can I use host variables in SQLJ DDL statements?
Does SQLJ support connection objects obtained from JDBC Connection Pools?
I developed a SQLJ application with Oracle JDeveloper 3.0 but can't get it to run in my JDK 1.2 environment
I am running against an Oracle 8.0 database and my SQL object updates are not working.
How can I use SQLJ in middle-tier (or EJB, or XXX) environment Y?
How can I use SQLJ with Oracle's BC4J?
How can I use SQLJ with JservApache?
Part B. General Questions

4. SQLJ (and JDBC) Basics

4.1 SQLJ Resources

Are there SQLJ books and other resources that would help a newcomer?
Is there an Oracle e-mail address for SQLJ questions?
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?
Where can I download the SQLJ translator for a given platform?
Why do I find information mentioning JSQL, I thought it is called SQLJ
Can SQLJ be faster than JDBC?
4.2 SQLJ Overview Is SQLJ Y2K-compliant?
What are the pros and cons of choosing Java and SQLJ over C/C++?
I want to write a Stored Procedure. Can you explain the difference between SQLJ, JDBC, and PL/SQL?

Is there a reason to write PL/SQL stored procedures instead of Java stored procedures?
Is there a translator for PL/SQL Stored Procedures/Packages into Java?
Does SQLJ implement the ANSI SQLJ specification?
Does SQLJ implement the ISO SQLJ specification?
What software components are required to translate a SQLJ application? to run a SQLJ application?
Can I use the Oracle SQLJ translator to translate an application to run against a non-Oracle SQLJ runtime?
Is SQLJ really database neutral?

4.3 JDBC Drivers What should I know about the Oracle JDBC drivers?
Can Oracle SQLJ be used with non-Oracle JDBC drivers?
4.4 SQLJ, JDBC, and JDK Versions What are the different versions of Oracle SQLJ and how do I get them?
How do the Oracle SQLJ 8.0.5 and 7.3.4 versions differ from the 8.1.5 versions?
Which versions of Oracle JDBC and the Sun JDK does each version of Oracle SQLJ support?
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?
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?
What's a connection "context"?
What's an iterator?
How do I create iterator classes and connection context classes?
What's an execution "context"?
How do I specify which connection context instance and/or execution context instance a SQLJ statement should use?
What exactly does the fetch size correspond to?
Where is the "Oracle" class and what does it provide?
Can I use SQLJ to write multithreaded applications?
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?
6. Oracle-Specific Features Do I need to do anything special to use Oracle-specific extensions?
What Oracle-specific features does Oracle SQLJ support?
What is CustomDatum and how do I use it?
How can I pass objects between my SQLJ program and Oracle Stored Procedures?
Can SQLJ interact with PL/SQL in my source code?
Does Oracle SQLJ support PL/SQL BOOLEAN, RECORD, and TABLE types as input and output parameters?
Does Oracle SQLJ support the WHERE CURRENT OF construct?
Does Oracle SQLJ support DML returning?
Can I use the SQL object features with a JDBC 8.1.x driver against an 8.0.x database?
Can I use SQLJ to return PL/SQL arrays from stored procedures?
Does Oracle SQLJ support REF CURSORS?
7. Translation (and Compilation and Customization) Process

7.1 Basic Functionality

How do I run the SQLJ translator?
What are the fundamental steps in translating a SQLJ application? What is input and output during the translation process?
Do I have to customize my application? Where do I get the customizer utility?
For basic use of SQLJ, how much do I need to know about profiles (.ser files)?
customizationinterop
What's the difference between online semantics-checking and offline semantics-checking? What kinds of checking are done?
I know I can enable online checking by giving the option -user=scott. How can I disable online checking?
7.2 Java Configurations Can I use Java compilers other than javac (the standard compiler included with the Sun JDKs)?
Is it possible to use the JDK 1.0.2 version of the javac compiler to compile .java files generated by the SQLJ translator?
7.3 National Language Support Does the Oracle SQLJ translator provide NLS support?
Can I specify the encoding for SQLJ to use?
8. Development Environments Can I develop and debug SQLJ programs with Oracle JDeveloper?
Can I develop and debug SQLJ programs with other IDEs such as Visual Café or Visual J++?
Compiling sqlj via ant (jakarta)
9. Deploying SQLJ Programs Can SQLJ be used in Java applets?
Can an SQLJ Applet open a connection to a third machine?
For end-users, what browsers will work with SQLJ applets?
Can SQLJ be used in middle-tier Java applications?
Can SQLJ be used inside servlets?
Can SQLJ be used inside JavaServer Pages?
Can SQLJ applications work across firewalls?
Can I use operating system authentication to connect from SQLJ?
10. Running SQLJ Programs What debugging support does SQLJ offer?
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?

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

  2.      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.
  3. Do you have the Oracle JDBC drivers installed and in your PATH/CLASSPATH? Can you compile and run JDBC programs?

  4. 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".
  5. Do you have the SQLJ translator.zip and runtime.zip (or runtime11.zip, runtime12.zip) files in your CLASSPATH? Can you issue

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

  8.      sqlj -version-long
    See also "Error in sqlj stub: invalid argument", "ExceptionInInitializerError: NullPointerException", "Unable to initialize threads: ...".
  9. 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.

  10. 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".
  11. Can you translate and compile .sqlj programs online?

  12. 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.".
  13. Can you run your SQLJ program?

  14. 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".
  15. Are you trying to deploy and run your application in the middle tier?

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

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

  20. See also Deploying and Running in the Oracle Server JavaVM.
If your issue is not resolved by this FAQ, you may want to use Oracle support (http://support.oracle.com/) or the SQLJ&JDBC discussion forum on http://technet.oracle.com. Please make sure to provide sufficient information about your environment and about the problem you have. Below is a list of information you may want to supply.

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:

"Unable to initialize threads: ..."

(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:

  4.      [Oracle Home]/jdbc/lib/classes12.zip
    or the following on Windows NT:
         [Oracle Home]\jdbc\lib\classes12.zip
  5. Add the SQLJ ZIP file translator.zip. This file is in the sqlj/lib directory, as with the JDBC classesXX.zip files above.
  6. Add the SQLJ ZIP file runtime.zip. This file is in the sqlj/lib directory.
Notes: "An application error has occurred... Exception:access violation ..." (in trying to run Sun's JDK 1.1.x on Windows NT/Windows 95)

(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:

unexpected error occurred... java.lang.ExceptionInInitializerError: java.lang.NullPointerException
at sqlj.framework.ClassFileReader.attribute_info(Compiled Code)
...
at sqlj.tools.Sqlj.statusMain(Compiled Code)
at sqlj.tools.Sqlj.main(Sqlj.java:117)
then you are likely running SQLJ version 8.1.5 or earlier under JDK 1.2.x or later.

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.

java.lang.NoClassDefFoundError: sun/io/CharToByteConverter
at sqlj.tools.Sqlj.initializeErrors(Sqlj.java:519)
This happens if you are running under a JRE environment, and not a full JDK environment. You need to download and install a full JDK environment. Note that the Oracle database installation will only set up a Java Runtime Environment. Oracle does not distribute the Java Development Kit - you have to get it from http://java.sun.com

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

"The following character string is too long:"

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.

"Oracle is not installed properly on your system. ..."

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.

2.2 Error Messages Encountered During Translation

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.

If you are running on a Windows platform, you need to use a shell that is able to redirect standard error to standard output. If you are running in a DOS shell, you will not be able to perform this redirection. In this case, you should omit the -compiler-executable flag shown above but still use the -passes flag. This will ensure that you can still see the javac error messages. However, in this case errors will be shown in terms of the generated .java files and not the original .sqlj source. Note that using the -passes option incurs a slight performance penalty: a JavaVM is started with the SQLJ translator, terminated, the javac compiler is run, and then the translator is started in a new JavaVM to process the output of javac. Without the -passes option, only a single JavaVM with the SQLJ translator is started. The javac compiler is called as a subprocess from that VM.

"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:

#sql iterator MyIter (String brand_name); Change this declaration to read as follows (assuming the iterator is declared as a nested class): #sql public static iterator MyIter (String brand_name); The problem is that the SQLJ runtime has to manufacture an instance of the MyIter iterator type. This only works if the iterator type is a public class.

"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:

#sql (context_instance) { DROP TABLE test }; use: #sql [context_instance] { DROP TABLE test }; "Warning: Cannot determine default arguments for stored procedures and functions. May need to install SYS.SQLJUTL."

(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:

describe sqljutl This will provide a brief description of the package if it can be found. If not, you will have to install it manually in the SYS schema, which on Solaris (for example) can be accomplished using the following script: [Oracle Home]/sqlj/lib/sqljutl.sql You must connect to the SYS schema, such as by logging in as INTERNAL, SYS, or CONNECT/AS SYSDBA to install this package. Consult your installation instructions if necessary.

"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:

void method() throws SQLException
{
   #sql iterator BlockIter (int col1, String col2);
   BlockIter i;
   #sql i = { SELECT col1, col2 FROM tab };
   while (i.next()) { ... }
}
The error location is somewhere within the code generated for the SELECT operation. The problem is that block-level classes can be referenced in the same block as they were declared, but not from within blocks within the declaring block. SQLJ generates code for the above SELECT operation as a statement block, and thus cannot instantiate a BlockIter instance within the generated block. A workaround is to declare the iterator at the class-level rather than the block-level. (This problem does not occur with JDK 1.2.)

"Error in Java compilation: CreateProcess: javac"

If the following error occurs during translation.

Error in Java compilation: CreateProcess:
javac -J-Djavac.pipe.output=true <filename> error=2
Try one or more of the following. Note: If you want to use a non-JDK command line environment to compile your SQLJ programs, please refer to "Can I use Java compilers other than javac (the standard compiler included with the Sun JDKs)"

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:

sqlj -status .... If, after doing this, you find that the last message issued by the SQLJ translator is the following line: [Compiling] then the problem occurs during invocation of the Java compiler by the SQLJ translator. In this case you need to always add the -passes option to the SQLJ command line: sqlj -passes ... Alternatively, you can add -passes to the SQLJ_OPTIONS environment variable: SQLJ_OPTIONS=-passes ... If, however, the hang occurs before the SQLJ compilation process, you might be translating with a 7.3.x JDBC driver with online semantics-checking enabled. In this case, you must either forego online checking, or upgrade to a newer JDBC driver version (at least an 8.0.x JDBC driver instead of a 7.3.x driver).

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.

I encounter a core-dump/stack-trace/out-of-memory condition during translation.

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

I encounter an out-of-memory condition during translation.

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?

Type package_name.class_name of host item #1 is not permitted in JDBC. This will not be portable. This warning is issued if you use Java types that are supported by Oracle's JDBC driver (and thus in the Oracle SQLJ runtime), but that are not JDBC types. You would only care about this warning if you want to write fully portable code. You can turn off portability warnings in JDeveloper under project properties on the SQLJ tab. On the SQLJ command line you could say: -warn=noportable.

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:

#sql ... { ... some SQL statement ... }; in your .sqlj file. If, on the other hand, you only have declarations, such as: #sql public iterator MyIter (...); or #sql public context MyContext; in your .sqlj source (and no statements), then no profile needs to be generated. Additionally, the FETCH statement: #sql { FETCH :iter INTO :x, :y, :x }; is translated directly into Java and needs no profile information, either.

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:

java.sql.SQLException: No suitable driver
at java.sql.DriverManager.getConnection(Compiled Code) ...
at oracle.sqlj.runtime.Oracle.getConnection(Compiled Code)
at oracle.sqlj.runtime.Oracle.connect(Compiled Code)
then you probably have not added the Oracle JDBC driver ZIP file to your CLASSPATH. Either use: [Oracle Home]/jdbc/lib/classes111.zip (for JDK 1.1) or: [Oracle Home]/jdbc/lib/classes12.zip (for JDK 1.2) Furthermore, you need to ensure that your JDBC driver is registered with the java.sql.DriverManger. If you are using the server-side (kprb) JDBC driver, or if you used one of the methods connect() or getConnection() on oracle.sqlj.runtime.Oracle to obtain a SQLJ connection context, then this will happen automatically. Otherwise you need to register the Oracle JDBC driver, for example as follows: java.sql.DriverManager.registerDriver
          (new oracle.jdbc.driver.OracleDriver());
Additionally, you need to ensure that you are specifying a correct URL. For example, if you are using the connect method: oracle.sqlj.runtime.Oracle.connect
          (MyApp.class,"connect.properties");
then you should verify that connect.properties is available in the same directory as MyApp.class, and contains something equivalent to the following: sqlj.url=jdbc:oracle:thin:@localhost:1521:orcl
sqlj.user=scott
sqlj.password=tiger
(Use the name of your host instead of localhost, your port number instead of 1521, and the name of your database instance instead of orcl.)

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

When trying to establish a SQLJ connection using the code

Oracle.connect(SomeClass.class, "connect.properties"); you encounter the message "java.sql.SQLException: unable to load connect properties file: connect.properties" In this situation you need to verify the following properties of the Oracle.connect method: sqlj.user=<schema name>
sqlj.password=<user password>
sqlj.url=<JDBC URL>
"SQLException: The network adapter could not establish the connection"

(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:

  3.      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
"SQLException: Connection refused"

(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:

java.sql.SQLException: Io exception: Connection refused(...) "SQLException: ORA-01017: invalid username/password; logon denied"

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

java.sql.SQLException:
ORA-01017: invalid username/password; logon denied
"InvalidClassException: Serializable is incompatible with Externalizable"

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

If you see the exception:

java.sql.SQLException: profile xxx.yyy_SJProfile0 not found:
java.io.InvalidClassException:
[java.lang.Object; Serializable is incompatible with Externalizable
then you may have a discrepancy between the Java environment that you use to translate SQLJ and the environment that you use to customize/run SQLJ. "Profile not found, SQL state 46130"

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:

java.sql.SQLException: profile xxx.yyy_SJProfile0 not found:
java.lang.ClassNotFoundException:
xxx.yyy_SJProfile0 for class xxx.yy_SJProfileKeys
then you must ensure that the SQLJ profile(s), such as xxx/yyy_SJProfile0.ser, is available in the SQLJ runtime environment. This includes JARing this file as part of an applet deployment, or publishing it to the server via loadjava.

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:

"ORA-29541: class class_name could not be resolved"

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:

ORA-29541: class class_name could not be resolved Any of the following things should help: See also the question "ClassNotFoundException: xxx.yyy_SJProfile0 for class xxx.yyy_SJProfileKeys" above.

"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:

#sql public static iterator MyIter(..., int rate, ...); with: #sql public static iterator MyIter(..., Integer rate, ...); Then you can use it as follows, for example: if (iter.rate()==null)
   { ... // null processing }
else
   { value = iter.rate().toString(); ... }
"SQLException: Invalid column type"

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:

"SQLException: unable to convert database class ... to client class ..."

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

sqlj *.ser In this case you will not be able to use Oracle-specific features and you will have to limit your application to using standard JDBC types. "java.security.AccessControlException (accessDeclaredMembers)" when running SQLJ (EJB) code in Sun's J2EE server

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.security.AccessControlException:
access denied (java.lang.RuntimePermission accessDeclaredMembers)
It appears that of Java reflection may not be permitted in your server environment. Note that SQLJ 8.1.6 and earlier makes heavy use of reflection to work with 8.1.6 as well as all earlier JDBC drivers. You should use SQLJ 8.1.7 (available from the donwload area of http://technet.oracle.com/), specifically the SQLJ runtime library runtime12.zip, or -even better- runtime12ee.zip. These SQLJ runtimes minimize the use of Java reflection. (There are still a few cases where reflection is used, such as in dealing with CustomDatum object wrappers, or when materializing a REF CURSOR as a SQLJ iterator. In 9i we will permit direct generation of Oracle-JDBC specific code that eliminates use of reflection in all these cases.)

"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:

(weblogic.jdbc20.rmi.SerialConnection)
RMI "serial connection" failed
and -when using the CommercePoolConnection- the command ((OracleCallableStatement)cstmt).getCursor(1); fails with: java.lang.ClassCastException: weblogic.jdbc20.rmi.SerialCallableStatement The problem is that the Weblogic JDBC Pooling functionality wraps/replaces the Oracle JDBC driver, and therefore does not support native Oracle functionality. Please contact Weblogic support about this issue.

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:

java.lang.NoClassDefFoundError: oracle/jdbc/OraclePreparedStatement Try to use an 8.1.7 or higher JDBC driver and make sure you have the SQLJ runtime12.zip in your CLASSPATH. Furthermore, you can replace the above method invocation with the equivalent code: sqlj.runtime.ref.DefaultContext.getDefaultContext().close(); "NullPointerException at java.util.zip.ZipFile.read"

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

If you encounter the error message:

java.lang.NullPointerException
at java.util.zip.ZipFile.read(ZipFile.java)
...
then you may want to try one or more of the following: Other "NullPointerException" (with JDK 1.2.1)

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:"

ORA-01000: maximum open cursors exceeded try { ...
} finally {
  try { rs.close(); rs=null;
        stmt.close(); stmt=null;
  } catch (Exception e){}
}
open_cursors=200 OracleCallableStatement cstmt = (OracleCallableStatement)
conn.prepareCall
   ("BEGIN DBMS_LOB.OPEN( ?, DBMS_LOB.LOB_READWRITE); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
...
cstmt = (OracleCallableStatement) conn.prepareCall
   ("BEGIN DBMS_LOB.CLOSE(?); END;");
cstmt.setCLOB(1, lob_loc);
cstmt.execute();
cstmt.close();
The above code opens two cursors with the cstmt but ends up closing only one. In SQLJ you would have written much more correctly and concisely: #sql { BEGIN DBMS_LOB.OPEN(:lob_loc, DBMS_LOB.LOB_READWRITE); END; };
...
#sql { BEGIN DBMS_LOB.CLOSE(:lob_loc); END; };
Or, consider the following subtly buggy code: switch (mode)
{
   case READ_ONLY :
    dbStatement =
      conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_READ_ONLY);
   case READ_WRITE :
    dbStatement =
      conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                            ResultSet.CONCUR_UPDATABLE);
}
Notice the missing break statement which causes the leaking of cursors. Cleaning up the SQLJ connection context is particularly important in server-side code, since closing the server-side default connection is performed as a no-op and will not result in any statement (or cursor) cleanup. Alternatively, you may just want to use a single static SQLJ connection context, such as the default context, in your server-side code. ORA-01000 when using OracleConnectionCacheImpl

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

-P-Cstmtcache=0 There are two types of SELECT...INTO statements you can write with SQLJ.

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

This creates a JDBC result set, calls next() on it, retrieves the column values, and them attempts another next() to ensure you got but a single row back. Finally the result set is closed, and the statement either closed directly (if compiled with -P-Cstmtcache=0) or returned to the statement cache (which caches the last 5 statements by default). (b) You can also issue a SELECT INTO statement in PL/SQL:

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

Statement caching applies as above, but no JDBC result set is created. ORA-01000 when processing REF CURSORS on the client

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?

ORA-1000 Maximum open cursors exceeded Please refer to the 8.1.6 JDBC documentation at http://technet.oracle.com/doc/oracle8i_816/java.816/a81354/oraint4.htm#1058744 It reads: Beginning with release 8.1.6, the cursor associated with a REF CURSOR is closed whenever the statement object that produced the REF CURSOR is closed. Unlike in previous releases, the cursor associated with a REF CURSOR is not closed when the result set object in which the REF CURSOR was materialized is closed. If you are using the 8.1.6 driver and would like to close the cursor before closing the Statement object where you retrieve the REF CURSORs from, you can do the following: refcursor_resultset.getStatement().close(); 3.2 Deploying and Running in the Oracle Server JavaVM

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:

User defined and predefined PL/SQL exceptions in SQLJ Stored Procedures

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:

ORA-29531 - no method ... in class ..." Also, when I use loadjava no errors are returned, but the status of the objects in the USER_OBJECTS table is INVALID for the type of CLASS and SOURCE." When publishing a Java method to SQL you may want to keep the following in mind. FUNCTION fun(x OUT NUMBER, y DATE) return VARCHAR2 AS
LANGUAGE JAVA
NAME
'JavaClass.javaFun(int[], java.sql.Date) return java.lang.String';
Container-Managed EJB does not commit SQLJ updates

"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:

DefaultContext ctx1 = Oracle.getConnection
     ("jdbc:oracle:thin:@host:port:sid",user, password);
I receive the following exception - what do I need to do?" java.security.AccessControlException:
the Permission (java.net.SocketPermission machine_name resolve)
has not been granted by dbms_java.grant_permission
to SchemaProtectionDomain(userid|PolicyTableProxy(userid))
When you connect from a Java program inside the server to other databases, you must have permission to open sockets. You can grant this permission 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?"

#sql rs = { VALUES(fun(..)) }; #sql { CALL proc(:OUT rs,...) }; #sql it = { SELECT * FROM TAB }; Also note that SQLJ 8.1.6 and later permits the subclassing of iterator classes and using the subclass instead of the generated iterator class. How to write a PL/SQL Stored Procedure that returns a result set or an iterator?

"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?"

sqlj/demo/RefCursDemo.{sql,sqlj} Is it possible to return a result set from a Java Stored Procedure?

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.

Refer to the SQLJ Developer's Guide and Reference, Appendix A-Performance and Debugging for full information. Statement caching and batching can provide significant performance improvements.

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:

SELECT ... FROM tab WHERE ... AND col IS NULL AND ... However, the SQLJ query String x = ...;
#sql it = { SELECT ... FROM tab WHERE ... AND col = :x AND ... }
does not return any rows when x has been initialized to null, but it does return the expected number of rows when x has been initialized to "". Is there a problem with sending a null value to the database?"

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

SELECT ... FROM tab WHERE ... col = :x ... with the value x==null, you should write: SELECT ... FROM tab WHERE ... col IS NULL ... This means that you need to use a different query (or change the query string dynamically), depending on the value of the bind variable.

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:

Is SQLJ ignoring my UPDATE statements?

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

A couple of notes on this.

Can I use host variables in SQLJ DDL statements?

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:

Connection conn = sqlj.runtime.ref.
         DefaultContext.getDefaultContext().getConnection();
PreparedStatement ps = conn.prepareStatement
         ("...statement..."+increment+"...rest of stmt…");
ps.execute();
ps.close();
If you are using SQLJ 9.0.1 or later you can write this more directly by embedding dynamic SQL in your SQLJ statements: #sql { ...statement... :{increment} ...rest of stmt... }; Does SQLJ support connection objects obtained from JDBC Connection Pools or from DataSources?

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:

import sqlj.runtime.ref.DefaultContext;
DefaultContext dc = new DefaultContext(jdbcConnection);
#sql [dc] { ... };
DefaultContext.setDefaultContext(dc);
#sql { ... };
You need to keep in mind the following caveats, however: I developed a SQLJ application with Oracle JDeveloper 3.0 but can't get it to run in my JDK 1.2 environment.

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.

If you are using these features with a non-Oracle JDBC driver or with a wrappered Oracle JDBC driver, you will likely encounter an "SQLException: Invalid column type error" or other errors. If you have a non-wrappered Oracle JDBC driver, a stack trace would only contain references to oracle.jdbc.driver.OracleXxxx implementations for java.sql statements, result sets, etc. Note: If you use vendor-specific features and a vendor-specific SQLJ runtime you likely have to compile the .java files and perform vendor-specific customization of .ser files between step (a) and (b).

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:

Currently, the following link should get you the Oracle 8.1.6 Java documentation: http://technet.oracle.com/doc/oracle8i_816/java.816/index.htm, and the Oracle 8.1.7 Java documentation http://technet.oracle.com/docs/products/oracle8i/doc_library/817_doc/java.817/index.htm. The OTN site also includes additional examples, articles, and discussions.

In addition, you can reference the following:

Also visit the http://www.oracle.com/java Web site.

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:

Cons: I want to write a Stored Procedure. Can you explain the difference between SQLJ, JDBC, and PL/SQL?

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.

JDBC is a well-known part of the Java language APIs. SQLJ, on the other hand, permits a more concise and productive way to write SQL statements for accessing the database. For server-side code it is very natural to program with the SQLJ notion of a default connection that is not mentioned explicitly. Both, JDBC and SQLJ can support connection pooling and distributed transactions through calling APIs on the underlying Oracle JDBC connection (for SQLJ you need version 8.1.7 or higher).

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

Using Java does have some additional cost.

In general, the choice comes down to what works for you, what kind of application you have, whether you want to be able to deploy the application outside of the database, and which language you prefer. Also note that regardless of the route you choose, SQL expertise still remains a requirement.

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:

To run your application, you need (or the end user needs) the following: The classes from one of the runtimeXX.zip files must be available during translation.

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?

Can Oracle SQLJ be used with non-Oracle JDBC drivers?

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:

Notes: Which versions of Oracle JDBC and the Sun JDK does each version of Oracle SQLJ support?
 
Oracle SQLJ Version Oracle JDBC versions supported JDK versions supported
SQLJ 9.0.1 JDBC 9.0.1, 8.1.x, 8.0.x, 7.3.4 JDK 1.3, 1.2, 1.1.x
SQLJ 8.1.7 JDBC 8.1.7, 8.1.6, 8.1.5, 8.0.x, 7.3.4 JDK 1.2, 1.1.x
SQLJ 8.1.6 JDBC 8.1.6, 8.1.5, 8.0.x, 7.3.4 JDK 1.2, 1.1.x
SQLJ 8.1.5 JDBC 8.1.5, 8.0.x, 7.3.4 JDK 1.1.x
SQLJ 8.0.5 / 7.3.4 JDBC 8.0.x, 7.3.4 JDK 1.1.x

Oracle SQLJ does not support JDK 1.0.2.

Notes:

For information about which Oracle database versions are supported by which JDBC driver versions, see the JDBC Developer's Guide and Reference or the JDBC FAQ list.

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:

If you are using SQLJ 8.1.6 or earlier, then there are just single versions of the translator.zip and runtime.zip files, for either JDK 1.1.x or JDK 1.2.

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.

What's a connection "context"?

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:

Note that you can use connection context instances and execution context instances independently of each other. Statements that use the same connection context instance can use different execution context instances, and statements that use the same execution context instance can use different connection context instances.

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

Note: SQLJ programs are subject to synchronization limitations imposed by the underlying JDBC driver implementation. If a JDBC driver mandates explicit synchronization of statements executed on the same JDBC connection, then a SQLJ program using that driver would require similar synchronization of SQL operations executed using the same 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:

Note: Prior to Oracle JDBC release 8.1.5, LOB support was through classes oracle.jdbc.driver.OracleBlob, OracleClob, and OracleBfile in combination with the PL/SQL DBMS_LOB package. Beginning with JDBC 8.1.5, the DBMS_LOB package is still available and supported, but the old LOB classes have been replaced with classes oracle.sql.BLOB, CLOB, and BFILE, which offer methods equivalent to the DBMS_LOB functionality.

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

What is CustomDatum and how do I use it?

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

jpub -user=scott/tiger -sql=ADDRESS:JAddress,PERSON:Jperson
sqlj JAddress*.* JPerson*.*
JPub generates the Java/SQLJ source files and SQLJ compiles them.
public class test {
public static void main(String[]args) throws java.sql.SQLException
{ // Set default SQLJ connection context
oracle.sqlj.runtime.Oracle.connect
("jdbc:oracle:oci8:@","scott","tiger");
JAddress a;
JPerson p = new JPerson();
p.setName("John");
#sql { INSERT INTO PERSON_TABLE VALUES(:p) };
#sql { SELECT ADDR INTO :a FROM ADDRESS_TABLE WHERE ZIP='12345' };
}}
Some introductory information can be found at http://technet.oracle.com/tech/java/sqlj_jdbc/ . There is a white paper on using Oracle objects with SQLJ. Also, the SQLJ download contains examples for using JPublisher under demos/jpub.

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:

Can SQLJ interact with PL/SQL in my source code?

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:

#sql iterator EIter implements sqlj.runtime.ForUpdate
                     (String ename, int empno);
...
EIter it;
#sql it = { SELECT empno, ename FROM emp };
...some it.next() movements...
#sql { update emp where current of :it set sal = :newSal };
Oracle SQLJ workaround: #sql iterator EIter(String ename, int empno, oracle.sql.ROWID rowid);
...
EIter it;
#sql it = { SELECT empno, ename, rowid FROM emp };
... some it.next() movements...
#sql { update emp where rowid = :(it.rowid()) set sal = :newSal };
One caveat: if you use a scrollable iterator, then Oracle JDBC already does use the same trick underneath. In this case you will probably want to use an alias name for the ROWID column that you are explicitly selecting out.

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:

In the compilation step, the Java compiler (typically javac) compiles the .java file(s) output by the translator, producing .class file(s).
 In the customization step, a default or specified customizer processes the .ser file(s) (profiles) to allow use of vendor-specific features.

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.

Yes, you can turn off profile customization, or set a different default profile customizer.

-profile=false - turns off customization

-default-customizer=classname -sets the default customizer. If classname is empty this in fact turns off customization.

If an Oracle customization exists, and the statement is executed on an Oracle connection then we enable Oracle-specific types and other enhancements. We use Java reflection to link to particular Oracle JDBC driver present. If the connection is not an Oracle connection, then we just use the JDBC-generic runtime. The Oracle customization of the .ser file adds information to the existing profile. I do not believe that this information directly uses Oracle JDBC-driver-specific classes. However, it does reference certain classes from the Oracle-specific SQLJ runtime in oracle.sqlj.runtime. You could create an application that is customized for all target platforms (customizations are additive) and distribute it as-is. (You may not need the entire JDBC driver, just the transitive closure of the classes referenced by the .ser file, which will in no small part depend on whether the original program references Oracle or other vendor-specific types or not.) Alternatively, you can create the application without customization and then individually customize for each platform. This will be more work but your application's .ser files will be smaller. There is a DB2 customizer that comes as a separate program, rather than a command line switch in the translator (i.e. the -P-customizer option). Informix and Sybase also provide versions of SQLJ. What's the difference between online semantics-checking and offline semantics-checking? What kinds of checking are done?

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:

Additional relevant command-line options are -compiler-encoding-flag (if you don't want SQLJ to pass its encoding flag to the compiler), -compiler-output-flag (to specify a file name if the compiler output is going to a file), and -compiler-pipe-output-flag (relating to the javac.pipe.output system property and whether compiler output will go to STDERR or STDOUT).

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:

Additionally, SQLJ fully supports Java's Unicode escape sequences. Naturally, you can also use Unicode characters inside any SQL code fragment. Usage of such characters may be checked during online translation.

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.

<property name="sqlj.main" value="sqlj.tools.Sqlj" />

<target name="sqlj>
<java classname="${sqlj.main}"
args="-props=${sqlj.propfile} gen/*"
fork="yes" failonerror="yes">
<classpath>
<pathelement location="translator.zip"/>
<pathelement location="runtime12.zip"/>
<pathelement location="classes12.zip"/>
</classpath>
</java>
</target>

9. Deploying SQLJ Programs

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:

Can an SQLJ Applet open a connection to a third machine?

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?

Yes. When you download the Oracle JSP translator (ojsp) from the OTN Web site (technet.oracle.com), you will also receive a SQLJ translator.zip file.

The JSP translator accomplishes the following for source files with the .sqljsp extension:

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:

For more information, see the JDBC Developer's Guide and Reference.

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.