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.
-
Do you have a JDK installed in your system, and can you say
javac? You can determine your JDK version
by issuing
java
-version
See also "Unable to initialize
threads: ...", "Exception:access violation",
"Error in Java compilation: CreateProcess:
javac", "NoClassDefFoundError: sun/io/CharToByteConverter".
Also note that JDK 1.3 is not supported with SQLJ 8.1.7 or earlier - see
JDK 1.3: SQLJ translator hangs and/or does not show
any error messages from the Java compiler.
-
Do you have the Oracle JDBC drivers installed and in your
PATH/CLASSPATH?
Can you compile and run JDBC programs?
See also "Cannot load
JDBC driver class oracle.jdbc.driver.OracleDriver",, "SQLException:
No suitable driver", "SQLException:
The network adapter could not establish the connection", "SQLException:
Connection refused", "SQLException: ORA-01017:
invalid username/password; logon denied".
-
Do you have the SQLJ translator.zip
and runtime.zip (or runtime11.zip,
runtime12.zip) files in your CLASSPATH?
Can you issue
javap
sqlj.tools.Sqlj
See also "NoClassDefFoundError:
sqlj/tools/Sqlj", "Error: SQLJ runtime
library is missing".
-
Can you start the SQLJ translator and have it print out its
environment by issuing
sqlj
-version-long
See also "Error in sqlj stub:
invalid argument", "ExceptionInInitializerError:
NullPointerException", "Unable to initialize
threads: ...".
-
Can you translate and compile .sqlj
programs offline? You need to have "."
(dot) in your CLASSPATH to compile and run the demo programs supplied with
SQLJ. Additionally, if you use the translator option -d
directory, you need to have directory in your CLASSPATH so
that the SQLJ translator can properly resolve Java classes during all translation
phases.
See also SQLJ hangs during translation,
SQLJ translates but does not produce any
.class files, I encounter a core-dump/stack-trace/out-of-memory
condition during translation, I encounter
an out-of-memory condition during translation, "NullPointerException
at java.util.zip.ZipFile.read".
-
Can you translate and compile .sqlj
programs online?
See also "Cannot load
JDBC driver class oracle.jdbc.driver.OracleDriver", "Warning:
Cannot determine default arguments for stored procedures and functions.
May need to install SYS.SQLJUTL.".
-
Can you run your SQLJ program?
See also Error Messages
Encountered During Deployment or Runtime, unabletoloadconnectproperties,
"InvalidClassException:
Serializable is incompatible with Externalizable", profilenotfound46130,
"ClassNotFoundException: xxx.yyy_SJProfile0
for class xxx.yyy_SJProfileKeys", "ORA-29541: class
class_name could not be resolved", JDK
1.2.1: Other "NullPointerException".
-
Are you trying to deploy and run your application in the
middle tier?
See also "SQLException:
Invalid column type error" and "How can
I use SQLJ in a middle-tier environment".
-
Are you tring to return REF CURSORs from the server to the
client and process them there?
See also Using REF Cursors
-
Are you trying to deploy and run your application in the
Oracle server-side JavaVM or in the Oracle iAS JavaVM?
See also Deploying and Running
in the Oracle Server JavaVM.
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:
-
Obtain a different sqlj.exe
stub (such as from the SQLJ 8.1.7 or later distribution, or from SQLJ 8.1.5
or another earlier SQLJ download)
Also note that the SQLJ downloads on OTN contain the
C-source code for the wrapper executable.
-
You can try to reduce the size of the CLASSPATH
to a minimum, for example to "." (dot),
the JDBC classesXXX.zip,
the SQLJ translator.zip, and the SQLJ runtime.zip
(see also "Unable to initialize threads:
...").
-
As a workaround, you can also issue
java
sqlj.tools.Sqlj <options>
"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:
-
Clear the CLASSPATH.
-
Add "." (current directory)
to the CLASSPATH and then make sure you
can run java and javac.
-
Add the appropriate JDBC ZIP file--classes111.zip
for JDK 1.1.x, or classes12.zip for JDK
1.2. These files are in the JDBC lib directory,
such as the following on Solaris:
[Oracle
Home]/jdbc/lib/classes12.zip
or the following on Windows NT:
[Oracle
Home]\jdbc\lib\classes12.zip
-
Add the SQLJ ZIP file translator.zip.
This file is in the sqlj/lib directory,
as with the JDBC classesXX.zip files above.
-
Add the SQLJ ZIP file runtime.zip.
This file is in the sqlj/lib directory.
Notes:
-
You do not need to add the runtime zip if you are using SQLJ
8.1.6 or earlier, but it will not hurt, either.
-
For Sun JDK 1.1.3 or higher, you should not need the JDK
directory to appear in the CLASSPATH (in
fact, it may be advisable to not have it in the CLASSPATH).
(This discussion is regarding the CLASSPATH
environment variable, as opposed to the Java/SQLJ -classpath
option. When using the -classpath option,
you do have to include the JDK directory.)
-
A convenient approach to going back and forth between your
normal CLASSPATH and your CLASSPATH
for SQLJ would be to create a script (for Solaris) or a batch file (for
Windows) to set up each configuration.
"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/.
-
Add runtime.zip to your CLASSPATH
if you want to use SQLJ with any JDBC driver — not just the one that corresponds
in version to your SQLJ version. For example, if you have SQLJ 8.1.7 and
want to use it with any one of the following JDBC versions: 7.3.4, 8.0.5,
8.1.5, 8.1.6, or 8.1.7.
-
Add runtime11.zip to your
CLASSPATH if your SQLJ and JDBC versions
match and you are running in a JDK 1.1.x environment. This runtime provides
a slightly better performance and is also the recommended runtime for applet
environments.
-
Add runtime12.zip if your
SQLJ and JDBC versions match and you are running in a JDK 1.2 or later
environment. This is necessary for certain JDBC 2.0 features, such as support
for java.sql.SQLData through type maps.
You can also use runtime12ee.zip
if your SQLJ and JDBC versions match and you are running in a JDK 1.2 or
later environment that provides support for the packages javax.naming
and javax.sql.
"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.
-
Do not provide .java files
on the command line but rely on the implicit make
capability in both, the SQLJ translator and the Java compiler. Note, however,
that you must mention all of the .sqlj
files that your program requires, do not rely on implicit make
to find all of those.
-
You can perform the -ser2class
conversion (if required), as well as Java compilation and profile customization
in separate steps. Use the setting -compile=false
to turn off Java compilation.
-
Place as many command line options as possible into the sqlj.properties
file. Note that options in the SQLJ_OPTIONS
environment variable are passed on the command line, while options in the
sqlj.properties file are not.
-
Your operating system may permit you to increase the size
of the environment (and this may or may not increase the maximum command
line length as well).
"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.
-
Try the following as a sanity check:
- add the translator.zip
library of your Oracle installation (OracleHome/sqlj/lib/translator.zip)
to your CLASSPATH, as well as the classes111.zip
(or classes12.zip) from the Oracle JDBC
driver
- run: java sqlj.tools.Sqlj
-version-long to see whether SQLJ finds everything it needs.
-
If SQLJ appears to work fine without the sqlj.exe
wrapper, you should use the original wrapper that came with SQLJ 8.1.5/8.0.5/7.3.4
(and that is also distributed with SQLJ 8.1.7 and later). Download SQLJ
8.0.5, or 8.1.7 or later from the OTN download page at http://technet.oracle.com/,
unzip it, and use that sqlj.exe file instead
of the one that came with your Oracle installation.
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.
-
First, create a new shell script that invokes javac
but that also sends stderr to stdout. For example, if you can invoke the
Unix shell sh, you might create the following
executable script called myjavac and place
it in your PATH:
#!/bin/sh
# sh
script myjavac to redirect stderr to stdout
javac
"$@" 2>&1
-
Secondly, you need to tell SQLJ to use the -passes
option and also to invoke the myjavac shell
script instead of calling the javac compiler:
sqlj
-passes -compiler-executable=myjavac Foo.sqlj
Note that you cannot put the -passes
option into the sqlj.properties file, since
it must be processed by the sqlj wrapper
script/executable. Since you probably do not want to type these two options
by hand every time you invoke SQLJ, you may prefer to put them in the SQLJ_OPTIONS
environment variable. Depending on your operating system and command line
shell, you might say something along the following line (here shown for
a csh-like shell):
setenv
SQLJ_OPTIONS "-passes -compiler-executable=myjavac"
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.
-
Ensure that you have installed the Java Development Kit from
http://java.sun.com . Specifically,
you should be able to run javac from the
command line.
-
Reduce your PATH and CLASSPATH setting to the bare minimum
to exclude possible conflicts of JavaVM's (you can do that most easily
with a script or .bat file - see below; also see "Unable
to initialize threads: ...").
-
Add the -passes option to
invoke the Java compiler in a different way.
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.
-
Ensure that you have installed the Java Development Kit from
http://java.sun.com .
-
Reduce your PATH and CLASSPATH setting to the bare minimum
to exclude possible conflicts of JavaVM's (you can do that most easily
with a script or .bat file - see below; also see ).
-
Add the -passes option to
invoke the Java compiler in a different way.
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.
-
Turn off your JavaVM's JIT (just-in-time) compiler. You might
be able to use one of the following to accomplish this:
Add the -J-Djava.compiler=none
command-line flag to the translator invocation.
or:
Set the JAVA_COMPILER=NONE
environment variable.
-
Re-set your CLASSPATH variable
to only contain . (current directory), the JDBC driver (either classes111.zip,
or classes12.zip), the SQLJ translator
(translator.zip), for SQLJ 8.1.7 or higher
a SQLJ runtime (runtime.zip, runtime11.zip,
or runtime12.zip), and any other classes
that are actually referenced by your source, other than classes that are
part of your JDK (the JDK classes do not have to be in your CLASSPATH
for JDK 1.1.3 or higher).
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:
-
The connect.properties resource
must be found either on the CLASSPATH,
or -if deployed- in a .jar file in the
same package as the class SomeClass.class.
In other words, assuming you are deploying your application in a .jar
file.
-
If SomeClass is a top-level class, then connect.properties
should be in the .jar's root directory.
-
If SomeClass is actually of the form A.B.C.SomeClass,
then connect.properties must be in the
/A/B/C subdirectory of the .jar
file.
-
The entries in the connect.properties
file must use the following SQLJ-specific format:
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:
-
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.
-
The host does not have a TNS listener running on the specified
IP address. You can use the Net8 Configuration assistant to configure a
database listener for TCP/IP. Alternarively -for example under Unix- you
can do this by editing your listener.ora
file, adding an additional line to:
LISTENER
= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=ipc)(KEY=oracle-sid)) )
as follows:
LISTENER
= (ADDRESS_LIST=
(ADDRESS=(PROTOCOL=ipc)(KEY=oracle-sid))
(ADDRESS=(PROTOCOL=tcp)(HOST=hostname)(PORT=port)) )
Now you must stop and then re-start your listener to
pick up the new settings:
lsnrctl
stop; lsnrctl start
"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.
-
If you are using SQLJ from JDeveloper 3.0, download the SQLJ
8.1.6 SDK patch, which contains a new translator.zip
version for replacing the translator.zip
file from the JDeveloper 3.0 installation. You must also ensure that the
following all match: the JDK version (such as JDK 1.2.2) with which JDeveloper
is started, the JDK version used by your project (see your project properties),
and the JDK-compatible JDBC ZIP file (such as classes111.zip
or classes12.zip).
-
If you are using the command-line version of SQLJ, download
and install SQLJ 8.1.6 or later or the SQLJ 8.1.6 SDK patch release.
-
If you are deploying compiled SQLJ code into an Oracle database
(for example, as a Java Stored Procedure, or as an Enterprise Java Bean),
then you may want to use the same JDK and SQLJ versions for client compilation
that are used in your server JavaVM. Specifically, for Oracle 8.1.7 or
higher you may want to use the corresponding runtime12.zip
and JDK 1.2, for Oracle 8.1.6 you want to compile under JDK 1.2, and for
Oracle 8.1.5 you want to compile using JDK 1.1.x.
"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:
-
In JDeveloper 3.0 and earlier the -ser2class
flag is not supported. You will have to use the sqlj
command-line translator to obtain this functionality. Additionally, SQLJ
provides a stand-alone tool ("profconv")
for converting .ser files into .java
files (and subsequently .class files) that
permits full control over the conversion process. Issue the following to
obtain a synopsis of the tool:
java
sqlj.runtime.profile.util.SerProfileToClass
-
In SQLJ 8.1.6 and earlier only .ser
files of less than 32KB can be transformed in this way.
"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:
-
Add the generated .ser file(s)
to be loaded by loadjava.
-
Use the -ser2class option
when translating your SQLJ source and add the additional .class files to
be loaded by loadjava.
-
Use loadjava to load SQLJ
source rather than .class (and .ser)
files.
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:
-
You may have changed the way you obtain a connection to using
a DataSource connection pool or to wrapping
OracleConnections in some other way. The
solution is to use Oracle SQLJ version 8.1.7 or later, or —if you need
to continue using an earlier version of SQLJ— to obtain a patch from http://technet.oracle.com.
Note, however if you are
-
The profile (.ser file) was
not customized to use the Oracle SQLJ runtime. Make sure that you set the
option -profile=true when translating.
Also, if you use the option -compile=false,
so that you can control compilation of the generated .java files, you need
to perform a separate customization step after Java compilation by issuing
sqlj *.ser on all of the generated profile
files.
"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
-
The conversion to an Oracle-specific non-standard class failed
because no Oracle customization was installed. If you use the command line
setting -profile=false or -compile=false,
then this would be the case. You may be able to perform the customization
step sperateley (here *.ser refers to all
of your application's .ser files):
sqlj *.ser
-
The Oracle customization was properly installed, but the
Oracle runtime was not used. This can happen if you run the SQLJ program
using a non-Oracle JDBC connection. This would also be the case if the
original Oracle JDBC driver is wrappered, for example in the middle tier,
by some other vendors' driver adaptation (see also "How
can I use SQLJ in middle-tier environments"). You can tell from the
stack trace whether this situation is the case:
-
You will not see any Oracle SQLJ specific runtime (oracle.sqlj.runtime.Xxxx)
on the stack, and
-
You will see some non-Oracle classes that implement JDBC connections, statements,
or result sets. If you are using an Oracle JDBC driver you should only
see classes of the form oracle.jdbc.driver.OracleXxxx.
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.
-
Finally, you may also see this message if you have mapped a SQL type hierarchy
to a hierarchy of Java wrapper classes (generated with JPublisher), but
the type map for the wrapper classes has not been initialized properly.
Make sure to initialize the type map by creating an instance of each of
the wrapper classes.
"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:
-
You may have superfluous, conflicting CLASSPATH
components in your environment. Try to only have the SQLJ runtime.zip,
the JDBC classesXXX.zip,
and your application's zips in your CLASSPATH.
If your program now works, you can add the other class libraries back step
by step to determine who the culprit is.
-
Turn off the Java Just-In-Time compiler (JAVA_COMPILER=NONE).
-
Try another JavaVM version.
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
-
The error ORA-01000: maximum open
cursors exceeded happens when not all of the statement and result
set objects are closed. In almost all cases when this error occurs, the
programmer discovers that he or she has inadvertently missed the close()
methods. Please make sure that all your statement, result set, and connection
objects are explicitly closed after you have finished processing to avoid
this error. The following is a snippet for such code:
try { ...
} finally {
try { rs.close(); rs=null;
stmt.close(); stmt=null;
} catch (Exception e){}
}
-
Note that if -for other reasons- you need to increase the
maximum number of cursors, you can do so in the initxxx.ora
by specifying (assuming the default is 50 or so)
open_cursors=200
-
You should consider using SQLJ from the start. SQLJ will
automatically manage statement objects (though you still must take care
to close your result sets and connection contexts). Then you will not have
to look at code such as the following from the Oracle 8.1.5 Application
Developer's Guide - Large Objects (LOBs):
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.
-
You also need to make sure to close all of the SQLJ connection
context objects, rather than waiting for them to be garbage-collected.
SQLJ 8.1.6 and later performs automatic caching of the last five SQLJ statements
that have been executed. Whenever you close the SQLJ connection context
you ensure that the statements cached on it are being closed as well. You
can use the method close(sqlj.runtime.ConnectionContext.KEEP_CONNECTION)
if you just want to clean up the SQLJ statement cache but not close the
underlying JDBC connection.
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.
-
A final tip: In order to find the bad cursors, debug/break
in the exception catcher, and then look at all opened cursors in TOP SESSIONS.
In every case of "max cursors exceeded", the same cursor was present hundreds
of times, and easily found.
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."
-
You have to ensure that you are closing all result sets and
statements. In SQLJ you do have to close your iterators (this closes the
underlying result set). However, you cannot close statements, since the
SQLJ runtime manages these automatically.
-
Depending on the setting of the SQLJ statement cache size
(option -P-Cstmtcache=nnn,
available with SQLJ 8.1.6 and higher, default cache setting is 5), a statement
is either closed immediately after reading outputs (if any), or the statement
is returned to the cache. In order to turn off SQLJ statement caching,
you have to use the following command line setting during SQLJ translation:
-P-Cstmtcache=0
-
Because of SQLJ statement caching you must ensure that rather
than just closing the JDBC pooled connection, you are closing the corresponding
SQLJ connection context. This will also ensure that all cached statements
are closed. Otherwise these statements would remain open, and your program
will run out of cursors.
-
Also, if you issue SELECT * FROM V$OPEN_CURSOR you may still
see cursors remaining open for optimization in a server-side LRU cursor
cache after being closed.
-
Finally, if you want to employ connection caching, you should
use SQLJ version 8.1.7 or later. Previous versions do not properly support
connection caches and pooled connections.
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:
-
Conversions from SQL data into and out of Java types are
rather costly. If you do not need Java native representations - for example,
if you are just copying data from one place to another- you should use
instances of oracle.sql.XXXX
types (NUMBER, DATE,
CHAR, RAW,
etc.)
-
If you are using Oracle 8.1.6 or later, several additional
performance improvements are available to your SQLJ programs. Statement
caching: by default, the last five SQLJ statements are being reused.
Batching of inserts/updates: you can enable batching of DML statements
that are executed in a loop. Please refer to the documentation for the
details. Statement caching as well as batching can provide very significant
performance improvements.
-
Consider adjusting the number of rows that are automatically
prefetched on the OracleConnection. This is available through Oracle's
JDBC driver - see sqlj/demo/PrefetchDemo.sqlj
for more details. In SQLJ 8.1.7 and later this is also known as the Fetch
Limit and can be set directly on the execution context.
-
Avoid creating and destroying a multitude of connections.
Every connection from the client corresponds to a database session, and
establishing these sessions is time consuming. Furthermore, the first time
you are calling a Java Stored Procedure in that session, that session's
"virtual JavaVM" must be initialized. And the first time you are using
SQLJ in that JavaVM, the SQLJ runtime also has to be initialized. In Oracle
8.1.6 and earlier the SQLJ runtime initialization performs an extensive
number of class and method lookups to dynamically link with the JDBC driver;
whereas in Oracle 8.1.7 and later a statically linked runtime avoids most
of this overhead.
-
Oracle 8.1.7 and later database versions support native compilation
of Java code. You can to use this feature for an extra performance boost.
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."
-
If you want loadjava to immediately
resolve the classes, then use the -resolve
flag (and also use -verbose to get more
information during the uploading and resolution process).
When publishing a Java method to SQL you may want to keep
the following in mind.
-
The published methods must be public
static.
-
All names in the Java signature are case sensitive and must
match the actual class names.
-
You must use the full class name. Specifically you must write
java.lang.String, java.lang.Integer,
and so on. Using String, Integer,
... will result in an error.
-
For every OUT or IN OUT argument, the Java parameter must
be an array of the underlying type. All OUT or IN OUT arguments are passed
as one-element arrays, where the called method can change the array element
at index 0. Otherwise you would have no way of modifying primitive or immutable
Java values in the body of a method.
-
Consider the following example declaration for a SQL wrapper
of a JavaClass.javaFun method.
FUNCTION fun(x OUT NUMBER,
y DATE) return VARCHAR2 AS
LANGUAGE JAVA
NAME
'JavaClass.javaFun(int[], java.sql.Date)
return java.lang.String';
-
You must write java.sql.Date
(not java.sql.DATE, etc.) - the case-sensitive
name of the class.
-
You must write java.sql.String
(not String) - the fully qualified class
name.
-
You must use int[] for an
OUT (or IN OUT) argument, and not int.
-
You may want to try using JDeveloper for uploading and publishing
Java Stored Procedures. It has wizards that allow you to select the methods
to publish and it automatically issues the appropriate SQL code.
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?"
-
In SQLJ the following are permitted as OUT parameters or
as stored function returns, for example:
#sql rs = { VALUES(fun(..))
}; #sql { CALL proc(:OUT rs,...) };
-
java.sql.ResultSet
-
sqlj.runtime.ResultSetIterator
-
a SQLJ iterator type
-
The following are permitted in the left hand side of an assignment
to a query, such as for example:
#sql it = { SELECT * FROM TAB
};
-
sqlj.runtime.ResultSetIterator
-
a SQLJ iterator type
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.
-
At this point, however, SQLJ does not support JDBC 2.0 rowsets.
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?"
-
Please look at the following demo in the SQLJ distribution:
sqlj/demo/RefCursDemo.{sql,sqlj}
-
Also note that in Oracle -unlike with some other vendors-
you cannot call a stored procedure that performs queries and then subsequently
access the returned side-channel result sets from the statement object.
In Oracle you always need some sort of cursor. The cursor must be explicitly
declared on the stored function/procedure signature, either as the return
parameter, or as one of the OUT argument parameters.
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.
-
Conversions from SQL data into and out of Java types are rather costly.
If you do not need Java native representations - for example, you
are just copying data from one place to another, you should use
instances of oracle.sql.XXXX
types (NUMBER, DATE,
CHAR, RAW,
etc.)
-
Beginning with release 8.1.6, Oracle SQLJ mirrors performance
enhancements available with Oracle JDBC:
— statement caching: by default, the last five
SQLJ statements are being reused.
— batching of inserts/updates: you can enable
batching of DML statements that are executed in a loop.
— registration of parameter and column sizes.
-
Also, consider adjusting the number of rows that are automatically
prefetched on result sets. This is available through the method setFetchSize()
on sqlj.runtime.ExecutionContext - see
sqlj/demo/jdbc20/FetchSize.sqlj. If you
are using SQLJ 8.1.6 or earlier, you have to set this directly through
the Oracle JDBC driver - see sqlj/demo/PrefetchDemo.sqlj
for more details.
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:
-
Use JDBC and build the query string dynamically.
String
nums = ...; //comma-separated list of numbers
Connection
conn = DefaultContext.getDefaultContext()
.getConnection();
Statement
s = conn.createStatement();
ResultSet
rs = s.executeQuery
("SELECT * FROM table WHERE nr IN ("+nums+")");
If you want to use the result set as a SQLJ iterator,
you can do so as follows.
MySqljIterator
it;
#sql
it = { CAST :rs };
...
it.close();
// also closes rs
-
If you have set a maximum number of different values and
you do have fewer actual values, then you can use a single SQLJ statement
and re-bind the last value multiple times.
For example, if there are at most 5 bind variables, you
could write the following.
int
a[] = new int[4];
… //
populate mynumbers
#sql
{ SELECT * FROM tab WHERE col in (:(a[0]),:(a[1]),…};
If there are fewer than 5 actual ints,
then would just repeat the same int in
the rest of the array.
-
Another -much less elegant- possibility is to have a different
SQLJ statement for every bind variable count.
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.
-
JDBC connections have auto-commit on, but SQLJ connections
usually have auto-commit off by default. Thus you need to remember to issue
a COMMIT to make the update visible.
#sql { COMMIT
};
-
You can use an ExecutionContext
to see how many rows got updated.
import
sqlj.runtime.ExecutionContext;
...
ExecutionContext
ec=new ExecutionContext();
#sql
[ec] { UPDATE table SET ... WHERE ... };
System.out.println("Updated
"+ec.getUpdateCount()+" rows.");
-
Remember that if the WHERE clause selects no rows, then 0
rows get updated and you will also not see a SQLException.
If your WHERE clause compares any CHAR columns to Java
strings, it very likely will not work: you have to blank-pad the Java strings
if you want the comparison to succeed. Or you can declare the column as
VARCHAR2. Or you could use a LIKE comparison and append a "%" to the Java
string.
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:
-
Your SQLJ context inherits the autocommit behavior from the
underlying connection. Usually you want to make sure it's turned off.
-
By default, SQLJ employs a statement cache, keeping up to
5 most recently executes statements open. You can change the caching behavior
and size with the -P-Cstmtcache=nnn
command line flag. It is important to close the SQLJ connection context,
rather than just the underlying JDBC connection, in order to fully clean
up this SQLJ statement cache when it is no longer needed. This is particularly
important when you run code server-side. Otherwise, since the server-side
connection never gets "really closed", you'll start leaking cursor.
-
There are some third-party products (such as the Weblogic server) that
provide their own connection caching implementation. This is not compatible
with using Oracle-specific features, such as returning REF CURSOR parameters,
using Oracle object type wrappers -such as those generated by JPublisher-,
or using oracle.sql.XXXX
types in SQLJ and -in many cases- in JDBC as well. In this case you
need to treat the third party driver as a generic JDBC driver, omit profile
customization (set the option -profile=false),
and avoid all Oracle-specific enhancements.
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.
-
The SQLJ runtime classes must be available in the environment.
-
Obtaining connections. If you can use the Oracle JDBC
driver in the environment, this is straightforward. If the environment
uses a non-Oracle JDBC driver, then you establish and use SQLJ connection
contexts as follows. (You can supply the DefaultContext constructor with
an existing connection.)
import
sqlj.runtime.ref.DefaultContext;
...
<load/register
JDBC driver, if necessary>
DefaultContext
ctx = new DefaultContext(...);
#sql
[ctx] { ..SQL statement.. };
-
If you do not use an Oracle JDBC driver, then add the following
flag to your sqlj command line:
-profile=false. This
omits Oracle customization and makes the .ser
file smaller. In this case you can also remove the whole oracle.*
hierarchy from runtime.zip.
Also remember that you cannot use any features specific
to Oracle JDBC or another vendor's JDBC, unless you use a vendor-specific
SQLJ runtime and customization step.
-
Oracle-specific JDBC functionality that is not supported
under non-Oracle JDBC drivers or under wrappered Oracle JDBC drivers:
-
Retrieving result sets or iterators from queries or as OUT-parameters
from stored procedures.
-
Using Oracle-specific types from oracle.sql.XXXX..
-
Using classes that implement the oracle.sql.CustomDatum or
the oracle.sql.ORAData interface.
-
Using JDBC 2.0 functionality, such as batching, fetch limit,
and scrollable iterators in a JDK 1.1 environment.
-
Using JDBC object features: in order to use these, it appears
that the Oracle JDBC connection object must not be wrappered.
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.
-
BEA/Weblogic middle tier: It appears that the Oracle
JDBC driver is being fully wrappered by a BEA/Weblogic middle tier server.
As a consequence, when you use features that are not generic JDBC functionality,
such as retrieval of JDBC result sets or SQLJ iterators from SELECT columns,
or as OUT parameters, you may encounter an "SQLException:
Invalid column type error" or other errors.
-
You need to ensure that the .ser
files get deployed alongside the class files into the environment. Or you
can use the -ser2class option during translation
and then deploy .class files.
-
Another question is whether your deployment tool has any
knowledge about .sqlj files - it may only
care about .java files. In this case you
would have to translate all .sqlj files
(and their corresponding .ser files) into
.java files before deploying them into
the environment. This can be accomplished as follows.
(a) run SQLJ over the .sqlj
source with the -compile=false option.
This produces .java and .ser
files.
(b) turn the .ser files
into .java files by running
java
sqlj.runtime.profile.util.SerProfileToClass -nc *.ser
Issue the line without arguments to get an option synopsis.
You would want to do (a) and (b) via a makefile
and not by hand.
(c) During deployment time and runtime you must
have runtime.zip in the respective CLASSPATHS
of the deployment tool and of the middle-tier environment.
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:
-
the exhaustive SQLJ Developer's Guide and Reference,
which contains a "Getting Started" chapter that should help you to get
up and running.
Notes:
-
This is also available on technet.oracle.com: click on "Documentation",
then "Data Servers -> Oracle8i", then "General Documentation", and
finally "Oracle8i Java Developer's Documentation", which should
take you to a menu with all of the Java related manuals, including SQLJ,
JDBC, and JPublisher.
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 examples in the demo
directory hierarchy in the SQLJ distribution
-
the primer "SQLJ: Tips, Traps, and Gems"
The OTN site also includes additional examples, articles,
and discussions.
In addition, you can reference the following:
-
N. Morisseau-Leroy, M. Solomon, and G. Momplaisir: Oracle8i
SQLJ Programming, published by Osborne/McGraw-Hill
-
Chapters on SQLJ programming appear in the following books:
-
J. Melton, A. Eisenberg, and R. Cattell: Understanding
SQL and Java Together : A Guide to SQLJ, JDBC, and Related Technologies,
-
Orfali, Harkey, et. al: Client/Server Programming.,
and
-
Oracle8i for Dummies
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:
-
a safer and more productive language
-
same database API for client, server, and middle tier
-
ability to write Java stored procedures in the database
-
support for CORBA and EJB in all tiers, including the database
-
tighter Java/SQL coupling through typed iterators
-
multi-schema abstraction (through connection context types)
-
full binary compatibility for all platforms, vendors, and databases (SQLJ
only requires JDBC)
Cons:
-
lack of support so far for parameter definitions (offline schema definition)
-
poorer raw performance
-
an additional step between user code and executable code - this step may
be all but transparent, depending on the development environment you use
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.
-
PL/SQL is an Oracle-proprietary language and has some resemblance
to Ada. It supports SQL types and SQL statements directly as part of the
language.
-
JDBC is an API specified by Sun as part of Java and supports
the use of dynamic SQL from the start.
-
SQLJ is an ANSI as well as ISO standard for embedding SQL
in Java. It primarily supports static SQL, though it also easily interoperates
with JDBC.
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.
-
Space: The overhead of running Java in a database session
is a couple of tens of kBytes per session in addition to any memory that
is used directly by the Java program.
-
Time: Every connection (database session) that you establish,
requires a one-time startup time for the JavaVM (and for the SQLJ runtime,
see also Performance of Java and SQLJ in
Stored Procedures). Moreover, if your code is mostly issuing SQL statements,
then the time required for conversion between the SQL and Java formats
will dominate your Java performance, whereas PL/SQL understands the SQL
data format natively. On the other hand, the more computations and logic
you need to perform in your stored procedure the more you should see Java
gaining an edge over PL/SQL.
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.
-
There is no need to toss out PL/SQL code for Java. All PL/SQL
code will continue to work and interoperate seamlessly with current or
future server-side code written in Java.
-
If you start out with Java expertise, there is no need to
relearn another language such as PL/SQL.
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:
-
the file translator.zip (typically
in [ORACLE_HOME]/sqlj/lib) on your system
and in your CLASSPATH
-
one of the runtime files runtime.zip
(any JDK, any JDBC driver), runtime11.zip
(for JDK 1.1.x, JDBC 8.1.7), or runtime12.zip
(for JDK 1.2, JDBC 8.1.7) which can typically be found in [ORACLE_HOME]/sqlj/lib)
on your system and in your CLASSPATH
-
For online checking, the JDBC driver classes—classes111.zip
for JDK 1.1.x or classes12.zip for JDK
1.2 (also typically in [ORACLE_HOME]/jdbc/lib)
on your system and in your CLASSPATH
-
a Java compiler and execution environment compatible with
JDK version 1.1.x or 1.2
To run your application, you need (or the end user needs)
the following:
-
the JDBC driver classes—classes111.zip
for JDK 1.1.x or classes12.zip for JDK
1.2 on your system and in your CLASSPATH
-
one of the runtime files runtime.zip
(any JDK, any JDBC driver), runtime11.zip
(for JDK 1.1.x, JDBC 8.1.7), or runtime12.zip
(for JDK 1.2, JDBC 8.1.7) which can typically be found in [ORACLE_HOME]/sqlj/lib)
on your system and in your CLASSPATH.
If you have SQLJ 9.0.1 or later and are using a non-Oracle
JDBC driver, you should specify runtime-nonoracle.zip.
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?
-
The OCI drivers (OCI / OCI 8 and OCI 7), included with Oracle
client installations, are intended for client-side applications in an Oracle
client environment. There must be an Oracle client-side installation.
-
The Thin driver is 100% Java and is intended for applets;
however, it can also be used for client-side applications. It does not
require an Oracle client-side installation..
-
The server-side internal driver (sometimes cryptically referred
to as the KPRB driver) is inside the Oracle server and is used for server-side
Java applications--Java stored procedures, Enterprise JavaBeans, and CORBA
objects.
-
The server-side Thin driver, available with Oracle 8.1.6
and later, is used to connect to a remote server from inside a server-side
Java application.
-
Unlike the OCI drivers, the Thin drivers only work with TCP/IP-based
networks.
-
The Thin drivers re-implement SQL*Net protocol in Java. As
a result, all necessary packing and unpacking of data packets across the
network is done in Java. Performance is therefore typically slower than
with the OCI driver, which implements all of this packing and unpacking
in C.
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:
-
Oracle objects and object references are not supported.
-
Oracle LOBs and ROWIDs are not supported in the Oracle 7.3.x
JDBC drivers and are supported by the oracle.jdbc.driver.OracleBlob,
OracleClob, OracleBfile,
and OracleRowid classes in the Oracle 8.0.x
JDBC drivers (as opposed to the oracle.sql.BLOB,
CLOB, BFILE,
and ROWID classes used in the Oracle 8.1.x
or later JDBC drivers).
-
The oracle.sql package (wrapper
classes for SQL data) did not exist in the Oracle 8.0.x and 7.3.x JDBC
drivers.
Notes:
-
All Oracle SQLJ drivers support Oracle JDBC 8.0.x and 7.3.4.
However, if you use SQLJ 8.1.7 or later, you need to make sure to specify
runtime.zip in your CLASSPATH.
(The runtime11.zip and runtime12.zip
files were made to work specifically with same-version JDBC drivers.)
-
We recommend that you download and use the Oracle8i SQLJ
Developer's Guide and Reference for 8.0.x and 7.3.x JDBC which was
written especially for JDBC 8.0.x and 7.3.x users. You can obtain it from:
http://technet.oracle.com/docs/tech/java/sqlj_jdbc/listing.htm.
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:
-
This table applies to both the SQLJ translator and the SQLJ
runtime.
-
During translation, Oracle SQLJ determines which version
of the Oracle JDBC driver and which version of the Oracle database are
being used and automatically uses the appropriate semantics-checker. Furthermore,
if you have specified the runtime12.zip
file during translation (in SQLJ 8.1.7 and later), then SQLJ will generate
JDK 1.2-specific code, and you will also need JDK 1.2 and runtime12.zip
to run your SQLJ program.
-
None of the SQLJ versions prior to SQLJ 8.1.7 will work with
the 8.1.7 JDBC drivers.
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:
-
runtime.zip supports all
JDBC drivers — not just the one that corresponds in version to your SQLJ
version. For example, the runtime.zip file
from SQLJ 8.1.7 can be used in conjunction with any one of the following
JDBC versions: 7.3.4, 8.0.5, 8.1.5, 8.1.6, or 8.1.7.
-
runtime11.zip supports the
JDK 1.1.x environment and the same-version JDBC driver. For example, you
would use the SQLJ 8.1.7 runtime11.zip
together with JDK 1.1.x and the Oracle 8.1.7 JDBC driver. This runtime
provides a slightly better performance and is also the recommended runtime
for applets.
-
runtime12.zip supports the
JDK 1.2 or later environment and the same-version JDBC driver. For example,
you would use the SQLJ 8.1.7 runtime12.zip
together with JDK 1.2 and the Oracle 8.1.7 JDBC driver. JDK 1.3 is not
fully supported by SQLJ 8.1.7 - you should use SQLJ version 9.0.1 or later.
You can also use runtime12ee.zip
if your SQLJ and JDBC versions match and you are running in a JDK 1.2 or
later environment that provides support for the packages javax.naming
and javax.sql.
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.
-
Package java.sql includes:
-
the SQLException class—this
class and its subclasses are used for all exceptions raised by SQLJ at
runtime;
-
the JDBC type classes such as java.sql.Date,
java.sql.ResultSet, and so on.
-
Package sqlj.runtime includes
the ExecutionContext class (execution contexts
are discussed below) as well as wrapper classes for SQLJ stream types (BinaryStream,
for example) and abstract classes that are implemented by connection context
classes and iterator classes.
-
Package sqlj.runtime.ref
includes implementations of some of the sqlj.runtime
interfaces and abstract classes—for example, the DefaultContext
class (which is the only connection context class you will need if you
are connecting to just one kind of schema and if you are not using the
Oracle class below).
-
Package oracle.sqlj.runtime
contains the Oracle class, used in getting
and closing connections (discussed later in this FAQ).
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:
-
getUpdateCount(), which returns
the number of rows affected by an update, insert, or delete
-
setQueryTimeout(), which
sets the timeout limit for your queries
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.
-
As discussed earlier, each database connection is represented
by a connection context instance--an instance of the standard DefaultContext
class or a user-declared connection context class. An instance of the standard
ExecutionContext class can be used for
SQL status and control operations. By default, each connection context
instance has an implicit execution context instance; however, you can also
explicitly declare and use an execution context instance.
-
In a multithreaded application, each thread must use its
own execution context instance (that is, all SQLJ statements using a thread
must use the same execution context instance, and no statements in any
other threads should use that execution context instance). You can do this
by declaring and specifying separate execution context instances, or by
declaring and using separate connection context instances (which would
satisfy the requirement, since each connection context instance has its
own execution context instance).
-
If an execution context were shared between threads, then
the results of a SQL operation performed in one thread would be visible
in the other thread. If both threads are executing SQL operations, a race
condition would occur in which the results of an execution in one thread
would be overwritten by the results of an execution in the next thread
before the first thread has processed the original results. Furthermore,
if a thread attempts to execute a SQL operation using an execution context
that is currently being used to execute an operation in another thread,
a runtime exception is raised. To avoid such problems, each thread should
use a distinct execution context whenever a SQL operation is executed on
a shared connection context.
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:
-
Oracle objects, collections, and object references (the Java
implementations can either be strongly typed, as produced by the Oracle
JPublisher utility, or weakly typed as in the JDBC standard)
-
Oracle ROWIDs
-
Oracle REF CURSOR types
-
Oracle LOBs (BLOBs or CLOBs) and external files (BFILEs)
-
Java wrapper classes in the oracle.sql
package for raw SQL data (such as the oracle.sql.CHAR
class to wrap character data, the NUMBER
class for numeric data, and the RAW class
for raw binary data
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:
-
extended SQLJ stream functionality (as output variables and
function return values) extended result set and iterator functionality
(as output variables and nested in iterator columns)
-
extended BigDecimal Support (mapping to non-default SQL datatypes)
-
PL/SQL anonymous blocks and stored procedure or function
calls (see the next question)
-
performance enhancements (SQLJ release 8.1.6 and later)--statement
caching, update batching, and parameter definitions
-
support for FETCH CURRENT to fetch from an iterator without
performing an associated movement (SQLJ release 8.1.7 and later)
-
support for FETCH from a ResultSetIterator
or ScrollableResultSetIterator. This permits
SQLJ programming without having to declare strongly typed iterator types,
but also foregoes some of the associated checking at translate time (SQLJ
release 9.0.1 and later)
-
support for embedding dynamic SQL code directly in SQLJ statements
(SQLJ release 9.0.1 and later)
-
support for direct generation of Oracle JDBC code, bypassing
generation and customization of SQLH profile (.ser)
files (SQLJ release 9.0.1 and later)
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.
-
You have the SQL Object types ADDRESS and PERSON in the schema
SCOTT.
-
You can run sqlj (i.e. have
the SQLJ translator.zip and runtime.zip
and the JDBC classes111.zip in the CLASSPATH,
and can use the JDBC-OCI driver).
-
In order to generate the CustomDatum
Java wrapper classes for your SQL types you would say:
jpub -user=scott/tiger -sql=ADDRESS:JAddress,PERSON:Jperson
sqlj JAddress*.* JPerson*.*
JPub generates the Java/SQLJ source files and SQLJ compiles
them.
-
Now you can use instances of these classes as follows (for
example in some file test.sqlj).
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' };
}}
-
To compile and run, do: sqlj test.sqlj;
java test.
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:
-
Define corresponding SQL object types to your Java object
types. Given an existing SQL Object type it is easiest to use the JPublisher
tool to create a Java wrapper class for that type. (You may want to look
in the SQLJ demo/jpub area for examples
of this.) Then you can write instances of the Java class to/from the stored
procedure as SQL object instances. The stored procedure can be written
in PL/SQL or in Java. The advantage of this approach is that your SQL objects
can be fully queried and manipulated in SQL. The disadvantage is that you
must start with the SQL type definition, not with some Java class.
-
You can serialize or deserialize instances of your (serializable)
Java class and essentially send a RAW or a BLOB parameter to the database
or receive one from the database. In this case you need to implement the
stored procedure in Java, and this stored procedure will use the original
definition of your Java class. In SQLJ 8.1.7 and later there is some special
support to simplify serialization and deserialization of Java objects.
You may want to run the SQLJ example demo/jdbc20/JavaSerialization.sqlj.
The advantage is that you are only dealing with the Java class. The disadvantage
is that the RAW parameter type has length limitations and the BLOB parameter
type can only be written to from a JDBC-OCI client.
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:
-
one or more .java file(s)
with calls to the SQLJ runtime (the runtime, in turn, calls the chosen
JDBC driver when the application is executed)
-
one or more .ser serialized
resource file(s) containing the application "profile(s)", which contain
information about all of your SQL instructions
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.
-
Is it possible to turn off the customizer and still use the
SQLJ translator?
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.
-
Why is Oracle JDBC driver required if Oracle customizations are present
in .ser file? I can
see the need for OraCustomizer, but why
the driver?
If 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.
-
If deploying an app with an .ser
file that includes Oracle customization on, for example, a DB2 system,
what is the most lightweight way to include the oracle customization and
JDBC? Currently, I'm appending both entire .jar
files to the end of the CLASSPATH.
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.
-
Is anyone else writing a customizer other than the three
that are included in 8.1.6?
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.
-
The Oracle SQLJ (8.1.7) release supports the SQLJ ISO standard,
which is based on JDBC 2.0. This results in an incompatibility between
SQLJ customizers/customizations from the ANSI specification (such as the
8.1.6 and earlier Oracle customizers and the IBM customizer) and from ISO.
Thus, if you want to plug and play customizers you may be restricted to
using Oracle SQLJ 8.1.6 or earlier for the time being.
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:
-
It must return a non-zero exit code to the operating system
whenever a compilation error occurs.
-
When it encounters errors, the source-code line information
it provides must be in either Sun javac
format or Microsoft jvc format.
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:
-
character encoding for reading and generating .sqlj
and .java source files during translation,
through the SQLJ translator -encoding option
-
character encoding for error and status messages during SQLJ
translation or when your application runs, through the Java file.encoding
property
-
locale for error and status messages during SQLJ translation
or when your application runs, through the Java user.language
property
-
comprehensive globalization/multibyte character support,
through support of national language character sets
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:
-
JDK 1.1.x or higher is still required. If the end-user's
browser uses an older JDK, then a plug-in or some other means of using
JDK 1.1.x or 1.2 becomes necessary.
-
With SQLJ 8.1.7 and higher, we are providing the SQLJ runtime11.zip
class library that is well-suited for applet deployment, since it minimizes
use of the Reflection API (java.lang.reflect.*).
By contrast, the runtime.zip library uses
reflection extensively in order to provide cross-compatibility with all
Oracle JDBC drivers. The runtime11.zip
library also requires a same-version JDBC driver. For example, if you use
SQLJ 8.1.7 runtime11.zip in your applet,
you would also use the Oracle 8.1.7 thin JDBC driver provided in classes111.zip.
-
Certain SQLJ statements, such as the CAST
statement, or statements that use Oracle SQLJ type extensions, such as
REF CURSOR, or
oracle.sql.CustomDatum types still require use of Java reflection,
which may not be enabled in all browser environments.
-
If your applet does not use Oracle-specific features, you
may want to set -profile=false during translation
so that your application is not customized. If it is not customized, then
there will be no requirement for the Oracle runtime (that is, you can remove
the oracle.* class hierarchy from the runtime.zip
library).
-
If you are using SQLJ release 9.0.1 or later you should use
the setting -codegen=oracle and distribute
your applet with the runtime11.zip library.
This code generation setting fully eliminates any use of reflection.
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:
-
Generates a .sqlj file. (If
you look at the .sqlj file, you will see
that the HTML tags are gone but the #sql
constructs are still there.)
-
Calls the SQLJ translator on the .sqlj
file.
Can SQLJ applications
work across firewalls?
Yes--there are no firewall limitations specific to SQLJ.
Because the runtime environment for a SQLJ application consists of a thin
layer of pure Java code on top of a JDBC driver, a SQLJ application will
work with any firewall with which the chosen JDBC driver will work.
The Oracle JDBC OCI driver and Thin driver can work in
either an intranet or extranet setting. In an extranet deployment, the
drivers can be used with firewalls which have been SQL*Net certified.
The following firewalls have been certified with SQL*Net:
-
stateful inspection firewalls from Checkpoint, SunSoft, and
Cisco Systems
-
proxy-based firewalls from Milkyway Networks, Trusted Information
Systems, Raptor, Secure Computing Corporation, and Global Internet
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.