Oracle9i SQLJ Developer's Guide and Reference
Release 1 (9.0.1)

Part Number A90212-01
Go To Documentation Library
Go To Product List
Solution Area
Go To Table Of Contents
Go To Index

Go to previous page Go to next page

Translator and Runtime Functionality

This chapter discusses internal operations and functionality of the Oracle SQLJ translator and runtime.

The following topics are covered:

Internal Translator Operations

The following topics summarize the operations executed by the SQLJ translator during a translation:

Code-Parsing and Syntax-Checking

In this first phase of SQLJ translation, a SQLJ parser and a Java parser are used to process all the source code and check syntax.

As the SQLJ translator parses the .sqlj file, it invokes a Java parser to check the syntax of Java statements and a SQLJ parser to check the syntax of SQLJ constructs (anything preceded by #sql). The SQLJ parser also invokes the Java parser to check the syntax of Java host variables and expressions within SQLJ executable statements.

The SQLJ parser checks the grammar of SQLJ constructs according to the SQLJ language specification. It does not check the grammar of the embedded SQL operations, however. SQL syntax is not checked until the semantics-checking step.

This syntax-check will discover errors such as missing semi-colons, mismatched curly braces, and obvious type mismatches (such as multiplying a number by a string).

If the parsers discover any syntax errors or type mismatches during this phase, then the translation is aborted, and the errors are reported to the user.


Once the SQLJ application source code is verified as syntactically correct, the translator enters into the semantics-checking phase and invokes a semantics-checker, according to user option settings. The semantics-checker verifies the validity of Java types in SQL operations (result expressions or host expressions) and optionally connects to a database to check compatibility between Java types and SQL types.

The -user option specifies online checking, and the -password and -url options finish specifying the database connection if the password and URL were not specified in the -user option. The -offline or -online option specifies which checker to use. The default is a checker front end called OracleChecker, which chooses the most appropriate checker, according to whether you have enabled online checking and which JDBC driver you are using. For more information, see "Connection Options" and "Semantics-Checking Options".


Semantics-checking can also be performed on a profile that was produced during a previous execution of the SQLJ translator. See "SQLCheckerCustomizer for Profile Semantics-Checking"

The following two tasks are always performed during semantics-checking, whether offline or online:

  1. SQLJ analyzes the types of Java expressions in your SQLJ executable statements.

    This includes examining the SQLJ source files being translated, any .java files entered on the command-line, and any imported Java classes whose .class files or .java files can be found through the classpath. SQLJ examines whether and how stream types are used in SELECT or CAST statements, what Java types are used in iterator columns or INTO-lists, what Java types are used as input host variables, and what Java types are used as output host variables.

    SQLJ also processes FETCH, CAST, CALL, SET TRANSACTION, VALUES, and SET statements syntactically.

    Any Java expression in a SQLJ executable statement must have a Java type valid for the given situation and usage. For example, consider the following statement:

    #sql [myCtx] { UPDATE ... };

    The myCtx variable, which might be used to specify a connection context instance or execution context instance for this statement, must actually resolve to a SQLJ connection context type or execution context type.

    Now consider the following example:

    #sql { UPDATE emp SET sal = :newSal };

    If newSal is a variable (as opposed to a field), then an error is generated if newSal was not previously declared. In any case, an error is generated if it cannot be assigned to a valid Java type, or its Java type cannot be used in a SQL statement (a java.util.Vector, for example).


    Remember that semantics-checking of Java types is performed only for Java expressions within SQLJ executable statements. Such errors in your standard Java statements will not be detected until compilation by the Java compiler. 

  2. SQLJ tries to categorize your embedded SQL operations--each operation must have a recognizable keyword, such as SELECT or INSERT, so that SQLJ knows what kind of operation it is. For example, the following statement will generate an error:

    #sql { foo };

The following two tasks are performed only if online checking is enabled:

  1. SQLJ analyzes your embedded SQL operations and checks their syntax against the database.

  2. SQLJ checks the types of Java expressions in SQLJ executable statements against: 1) SQL types of corresponding columns in the database; 2) SQL types of corresponding arguments and return variables of stored procedures and functions.

    In the process of doing this, SQLJ verifies that the SQL objects used in your SQLJ executable statements (such as tables, views, and stored procedures) actually exist in the database. SQLJ also checks nullability of database columns whose data is being selected into iterator columns of Java primitive types, which cannot process null data. (Nullability is not checked for stored procedure and function output parameters and return values, however.)

If the semantics-checker discovers any syntax or semantics errors during this phase, then the translation is aborted and the errors are reported.

Oracle supplies Oracle-specific offline checkers, a generic offline checker, Oracle-specific online checkers, and a generic online checker. For more information about checkers, see "Offline Semantics-Checker (-offline)" and "Online Semantics-Checker (-online)".

The generic checkers assume you use only standard SQL92 and standard JDBC features. Oracle recommends that you use the Oracle-specific checkers when using an Oracle database.


The following is not checked against the database during online semantics-checking:

  • DDL statements (such as CREATE, ALTER, and DROP) and transaction control statements (such as COMMIT and ROLLBACK)

  • compatibility of data corresponding to weakly typed host expressions (those using the oracle.sql package STRUCT, REF, and ARRAY classes, which are discussed in "Weakly Typed Objects, References, and Collections")

  • mode compatibility (IN, OUT, or IN OUT) of expressions in PL/SQL anonymous blocks


Code Generation

For your .sqlj application source file, the SQLJ translator generates a .java file and, for standard SQLJ code generation, at least one profile (either in .ser or .class files). A .java file is created for your translated application source code, class definitions for private iterators and connection contexts you declared, and a profile-keys class definition generated and used internally by SQLJ.


Profiles and a profile-keys class are not generated under the following circumstances:

  • if you do not use any SQLJ executable statements in your code

  • if you use Oracle-specific code generation (-codegen=oracle)

See "Oracle-Specific Code Generation (No Profiles)"

Generated Application Code in .java File

Once your application source code has passed the preceding syntax and semantics checks, it is translated and output to a .java file. For standard SQLJ code generation, SQLJ executable statements are replaced by calls to the SQLJ runtime, which in turn contains calls to the JDBC driver to access the database. For Oracle-specific code generation, SQLJ executable statements are replaced by direct calls to Oracle JDBC (there are also calls to an Oracle-specific SQLJ runtime).

The generated .java file contains all your generic Java code, your iterator class and connection context class definitions, and calls to the SQLJ runtime.

For convenience, generated .java files also include a comment for each of your #sql statements, repeating the statement in its entirety for reference.

The generated .java file will have the same base name as the input .sqlj file, which would be the name of the public class defined in the .sqlj file (or the first class defined if there are no public classes). For example, Foo.sqlj defines class Foo, and source file will be generated by the translator.

The location of the generated .java file depends on whether and how the SQLJ -dir option is set. By default, the .java file will be placed in the directory of the .sqlj input file. See "Output Directory for Generated .java Files (-dir)" for more information.

Generated Profile-Keys Class in .java File

During translation with standard SQLJ code generation, SQLJ generates a profile-keys class that it uses internally during runtime to load and access the serialized profile. This class contains mapping information between the SQLJ runtime calls in your translated application and the SQL operations placed in the serialized profile. It also contains methods to access the serialized profile.


If you use Oracle-specific code generation, through the SQLJ translator -codegen=oracle setting, no profiles or profile-keys classes are generated. 

The profile-keys class is defined in the same .java output file that has your translated application source code, with a class name based on the base name of your .sqlj source file as follows:


For example, translating Foo.sqlj defines the following profile-keys class in the generated .java file:


If your application is in a package, this is reflected appropriately. For example, translating Foo.sqlj in the package a.b defines the following class:


Generated Profiles in .ser or .class Files

When you use standard SQLJ code generation, SQLJ generates profiles that it uses to store information about the SQL operations found in the input file. A profile is generated for each connection context class that you use in your application. It describes the operations to be performed using instances of the associated connection context class, such as SQL operations to execute, tables to access, stored procedures and functions to call.


If you use Oracle-specific code generation, through the SQLJ translator -codegen=oracle setting, then information about the SQL operations is embedded in the generated code, which calls Oracle JDBC directly. In this case, SQLJ does not generate profiles. 

Profiles are generated in .ser serialized resource files. If, however, you enable the SQLJ -ser2class option, they are automatically converted to .class files as part of the translation. (In this case, no further customization of the profile is possible. You would have to delete the .class file and rerun the SQLJ translator to regenerate the profile.)

Profile base names are generated similarly to the profile-keys class name. They are fully qualified with the package name, followed by the .sqlj file base name, followed by the string:


Where n is a unique number, starting with 0, for each profile generated for a particular .sqlj input file.

Again using the example of the input file Foo.sqlj, if two profiles are generated, then they will have the following base names (presuming no package):


If Foo.sqlj is in the package a.b, then the profile base names will be:


Physically, a profile exists as a Java serialized object contained within a resource file. Resource files containing profiles use the .ser extension and are named according to the base name of the profile (excluding package names). Resource files for the two previously mentioned profiles will be named:


Or they will be named Foo_SJProfile0.class and Foo_SJProfile1.class if you enable the -ser2class option. If you choose this option, the conversion to .class takes place after the customization step below. See "Conversion of .ser File to .class File (-ser2class)" for more information.

The location of these files depends on how the SQLJ -d option is set, which determines where all generated .ser and .class files are placed. See "Output Directory for Generated .ser and .class Files (-d)" for more information.

In a later step in the SQLJ process, your profiles are customized for use with your particular database. See "Profile Customization".

More About Generated Calls to SQLJ Runtime

When your #sql statements are replaced by calls to the SQLJ runtime (for standard SQLJ code generation) or Oracle JDBC (for Oracle-specific code generation), these calls implement the following steps:

Table 9-1 Steps for Generated Calls, Standard Versus Oracle-Specific  
Steps for Standard Code Generation  Steps for Oracle Code Generation 

Get a SQLJ statement object, using information stored in the associated profile entry. 

Get an Oracle JDBC statement object. 

Bind inputs into the statement, using setXXX() methods of the statement object. 

Bind inputs using Oracle JDBC statement methods and, if necessary, register output parameters. 

Execute the statement, using the executeUpdate() or executeQuery() method of the statement object. 

Execute the Oracle statement. 

Create iterator instances, if applicable. 

Create iterator instances, if applicable. 

Retrieve outputs from the statement, using getXXX() methods of the statement object.  

Retrieve outputs from the statement using appropriate Oracle JDBC getter methods. 

Close the SQLJ statement object (by default, recycling it through the SQLJ statement cache). 

Close the Oracle JDBC statement object (by default, recycling it through the JDBC statement cache). 

A SQLJ runtime uses SQLJ statement objects that are similar to JDBC statement objects, although a particular implementation of SQLJ might or might not employ JDBC statement classes directly. SQLJ statement classes add functionality particular to SQLJ. For example:

Java Compilation

After code generation, SQLJ invokes the Java compiler to compile the generated .java file. This produces a .class file for each class you defined in your application, including iterator and connection context declarations, as well as a .class file for the generated profile-keys class (presuming your application uses SQLJ executable statements). Any .java files you specified directly on the SQLJ command line (for type-resolution, for example) are compiled at this time as well.

In the example used in "Code Generation", the following .class files would be produced in the appropriate directory (given package information in the source code):

To ensure that .class files generated by the compiler and profiles generated by SQLJ (whether .ser or .class) will be located in the same directory, SQLJ passes its -d option to the Java compiler. If the -d option is not set, then .class files and profiles are placed in the same directory as the generated .java file (which is placed according to the -dir option setting).

In addition, so that SQLJ and the Java compiler will use the same encoding, SQLJ passes its -encoding option to the Java compiler (unless the SQLJ -compiler-encoding-flag is turned off). If the -encoding option is not set, SQLJ and the compiler will use the setting in the JVM file.encoding property.

By default, SQLJ invokes the standard javac compiler of the Sun Microsystems JDK, but other compilers can be used instead. You can request that an alternative Java compiler be used by setting the SQLJ -compiler-executable option.


If you are using the SQLJ -encoding option but using a compiler that does not have an -encoding option, turn off the SQLJ -compiler-encoding-flag. (Otherwise, SQLJ will attempt to pass the -encoding option to the compiler.) 

For information about compiler-related SQLJ options, see the following:

Profile Customization

After Java compilation, the generated profiles (which contain information about your embedded SQL instructions) are customized so that your application can work efficiently with your database and use vendor-specific extensions.


If you use Oracle-specific code generation, SQLJ produces no profiles and skips the customization step. Your code will support Oracle-specific features through direct calls to Oracle JDBC APIs. See "Oracle-Specific Code Generation (No Profiles)" for more information.  

To accomplish customization, SQLJ invokes a front end called the customizer harness, which is a Java class that functions as a command-line utility. The harness, in turn, invokes a particular customizer, either the default Oracle customizer or a customizer that you specify by SQLJ option settings.

During customization, profiles are updated in two ways:

Without customization, you can access and use only standard JDBC types.

For example, the Oracle customizer can update a profile to support an Oracle9i PERSON type that you had defined. You could then use PERSON as you would any other supported datatype.

You also must customize with the Oracle customizer to utilize any of the oracle.sql type extensions.

Customization Notes

Be aware of the following:

For more information about profile customization, see Chapter 10, "Profiles and Customization".

Also see the following for information about SQLJ options related to profile customization:

Functionality of Translator Errors, Messages, and Exit Codes

This section provides an overview of SQLJ translator messages and exit codes.

Translator Error, Warning, and Information Messages

There are three major levels of SQLJ messages you might encounter during the translation phase: error, warning, and information. Warning messages can be further broken down into two levels: non-suppressible and suppressible. Therefore, there are four message categories (in order of seriousness):

  1. errors

  2. non-suppressible warnings

  3. suppressible warnings

  4. information

You can control suppressible warnings and information by using the SQLJ -warn option, as described below.

Error messages, prefixed by Error:, indicate that one of the following has been encountered:

If errors are encountered during SQLJ translation, then no output is produced (.java file or profiles), and compilation and customization are not executed.

Non-suppressible warning messages, prefixed by Warning:, indicate that one of the following has been encountered:

SQLJ translation will complete if a non-suppressible warning is encountered, but you should analyze the problem and determine if it should be fixed before running the application. If online checking is specified but cannot be completed, offline checking is performed instead.


For logistical reasons, the parser that the SQLJ translator employs to analyze SQL operations is not the same top-level SQL parser that will be used at runtime. Therefore, errors might occasionally be detected during translation that will not actually cause problems when your application runs. Accordingly, such errors are reported as non-suppressible warnings, rather than fatal errors. 

Suppressible warning messages, also prefixed by Warning:, indicate that there is a problem with a particular aspect of your application, such as portability. An example of this is using an Oracle-specific type such as oracle.sql.NUMBER to read from or write to Oracle9i.

Informational or status messages prefixed by Info: do not indicate an error condition. They merely provide additional information about what occurred during the translation phase.

Suppressible warning and status messages can be suppressed by using the various -warn option flags:

See "Translator Warnings (-warn)" for more information about the -warn option and how to set the flags.

If you receive warnings during your SQLJ translation, then you can try running the translator again with -warn=none to see if any of the warnings are of the more serious (non-suppressible) variety.


For information about particular error, warning, and information messages, see "Translation Time Messages" and "Runtime Messages"

Table 9-2 summarizes the categories of error and status messages generated by the SQLJ translator.

Table 9-2 SQLJ Translator Error Message Categories  
Message Category  Prefix  Indicates  Suppressed By 



fatal error that will cause compilation failure or runtime failure (translation is aborted) 


Non-suppressible warning 


condition that prevents proper translation or might cause runtime failure (translation is completed) 


Suppressible warning 


problem regarding a particular aspect of your application (translation is completed)  

-warn option flags:

Informational/status message 


information regarding the translation process 

-warn option flags:

Translator Status Messages

In addition to the translator's error, warning, and information messages, SQLJ can produce status messages throughout all phases of SQLJ operation--translation, compilation, and customization. Status messages are output as each file is processed and at each phase of SQLJ operation.

You can control status messages by using the SQLJ -status option. This option is described in "Real-Time Status Messages (-status)".

Translator Exit Codes

The following exit codes are returned by the SQLJ translator to the operating system upon completion:

SQLJ Runtime

This section presents information about the Oracle SQLJ runtime, which is a thin layer of pure Java code that runs above the JDBC driver. When Oracle SQLJ translates your SQLJ source code using standard SQLJ code generation, embedded SQL commands in your Java application are replaced by calls to the SQLJ runtime. Runtime classes act as wrappers for equivalent JDBC classes, providing special SQLJ functionality. When the end user runs the application, the SQLJ runtime acts as an intermediary, reading information about your SQL operations from your profile and passing instructions along to the JDBC driver.

If you use Oracle-specific code generation, the SQLJ runtime layer becomes even thinner. Most of the runtime functionality is compiled directly into Oracle JDBC calls. This requires an Oracle JDBC driver, as opposed to a JDBC driver from some other vendor. See "Oracle-Specific Code Generation (No Profiles)".

Generally speaking, however, a SQLJ runtime can be implemented to use any JDBC driver or vendor-proprietary means of accessing the database. The Oracle SQLJ runtime requires a JDBC driver but can use any standard JDBC driver. To use Oracle-specific datatypes and features, however, you must use an Oracle JDBC driver. For the purposes of this document, it is generally assumed that you are using an Oracle database (or perhaps the middle-tier database cache) and one of the Oracle JDBC drivers.


  • The generic SQLJ runtime will be used if your application has no customizations, or none suitable for the connection.

  • For standard SQLJ code generation, the Oracle SQLJ runtime and an Oracle JDBC driver will be required by your application whenever you use the Oracle customizer during translation, even if you do not use Oracle extensions in your code.

  • For Oracle-specific code generation, a SQLJ runtime subset will be used in conjunction with an Oracle JDBC driver.


Runtime Packages

The Oracle SQLJ runtime includes packages you will likely import and use directly, and others that are used only indirectly.


These packages are included in the runtime libraries-- runtime12, runtime12ee, runtime11, runtime, and runtime-nonoracle

Packages Used Directly

This section lists packages containing classes you can import and use directly in your application. Packages whose names begin with oracle are for Oracle-specific SQLJ features.

Packages Used Indirectly

This section lists packages containing classes that are for internal use by SQLJ.

Categories of Runtime Errors

Runtime errors can be generated by any of the following:

In any of these cases, a SQL exception is generated as an instance of the java.sql.SQLException class or a subclass (such as sqlj.runtime.SQLNullException).

Depending on where the error came from, there might be meaningful information you can retrieve from an exception using the getSQLState(), getErrorCode(), and getMessage() methods. SQLJ errors, for example, include meaningful SQL states and messages. For information, see "Retrieving SQL States and Error Codes".

If errors are generated by the Oracle JDBC driver or RDBMS at runtime, look at the prefix and consult the appropriate documentation:

For a list of SQLJ runtime errors, see "Runtime Messages".

Globalization Support in the Translator and Runtime

Oracle SQLJ uses Java's built-in capabilities for globalization support (otherwise known as National Language Support, or NLS). This section discusses the following:

Some prior knowledge of Oracle Globalization Support is assumed, particularly regarding character encoding and locales. For information, see the Oracle9i Globalization Support Guide.

Character Encoding and Language Support

There are two main areas of SQLJ globalization support:

Globalization support at runtime is transparent to the user, presuming your SQLJ source code and SQL character data use only characters that are within the database character set. SQL character data is transparently mapped into and out of Unicode.

Note that for multi-language applications, it is advisable to use one of the following options:


Overview of Character Encoding

The character encoding setting for source files tells Oracle SQLJ two things:

By default, SQLJ uses the encoding indicated by the JVM file.encoding property. If your source files use other encodings, then you must indicate this to SQLJ so that appropriate conversion can be performed.

Use the SQLJ -encoding option to accomplish this. SQLJ also passes the -encoding setting to the compiler for it to use in reading .java files (unless the SQLJ -compiler-encoding-flag is off).


Do not alter the file.encoding system property to specify encodings for source files. This might impact other aspects of your Java operation and might offer only a limited number of encodings, depending on platform or operating system considerations. 

The system character-encoding setting also determines how SQLJ error and status messages are represented when output to the user, either during translation or during runtime when the end user is running the application. This is set according to the file.encoding property and is unaffected by the SQLJ -encoding option.

For source file encoding, you can use the -encoding option to specify any character encoding supported by your Java environment. If you are using the Sun Microsystems JDK, these are listed in the native2ascii documentation, which you can find at the following Web site:

Dozens of encodings are supported by the Sun Microsystems JDK. These include 8859_1 through 8859_9 (ISO Latin-1 through ISO Latin-9), JIS (Japanese), SJIS (shift-JIS, Japanese), and UTF8.

Character Encoding Notes

Be aware of the following:

Overview of Language Support

SQLJ error and status reporting, either during translation or during runtime, uses the Java locale setting in the JVM user.language property. Users typically do not have to alter this setting.

Language support is implemented through message resources that use key/value pairs. For example, where an English-language resource has a key/value pair of "OkKey", "Okay", a German-language resource has a key/value pair of "OkKey", "Gut". The locale setting determines which message resources are used.

SQLJ supports locale settings of en (English), de (German), fr (French), and ja (Japanese).


Java locale settings can support country and variant extensions in addition to language extensions. For example, consider ErrorMessages_de_CH_var1, where CH is the Swiss country extension of German, and var1 is an additional variant. SQLJ, however, currently supports only language extensions (de in this example), ignoring country and variant extensions. 

SQLJ and Java Settings for Character Encoding and Language Support

Oracle SQLJ provides syntax that allows you to set the following:

Setting Character Encoding for Source Code

Use the SQLJ -encoding option to determine the character encoding used in representing .sqlj files read by the translator, .java files generated by the translator, and .java files read by the compiler. (The option setting is passed by SQLJ to the compiler, unless the SQLJ -compiler-encoding-flag is off.)

This option can be set on the command line or SQLJ_OPTIONS environment variable, as in the following example:


Or it can be set in a SQLJ properties file as follows:


If the encoding option is not set, then both the translator and compiler will use the encoding specified in the JVM file.encoding property. This can also be set through the SQLJ command line, as discussed in "Setting Character Encoding and Locale for SQLJ Messages".

For more information, see "Encoding for Input and Output Source Files (-encoding)" and "Compiler Encoding Support (-compiler-encoding-flag)".


If your -encoding is to be set routinely to the same value, then it is most convenient to specify it in a properties file, as in the second example above. For more information, see "Properties Files for Option Settings"

Setting Character Encoding and Locale for SQLJ Messages

Character encoding and locale for SQLJ error and status messages output to the user, during both translation and runtime, are determined by the Java file.encoding and user.language properties. Although it is typically not necessary, you can set these and other JVM properties in the SQLJ command line by using the SQLJ -J prefix. Options marked by this prefix are passed to the JVM.

Set the character encoding as in the following example (which specifies shift-JIS Japanese character encoding):



Only a limited number of encodings might be available, depending on platform or operating system considerations. 

Set the locale as in the following example (which specifies Japanese locale):


The -J prefix can be used on the command line or SQLJ_OPTIONS environment variable only. It cannot be used in a properties file, because properties files are read after the JVM is invoked.


  • If your file.encoding, user.language, or any other Java property is to be set routinely to the same value, it is most convenient to specify -J settings in the SQLJ_OPTIONS environment variable. This way, you do not have to repeatedly specify them on the command line. The syntax is essentially the same as on the command line. For more information, refer to "SQLJ_OPTIONS Environment Variable for Option Settings".

  • Remember that if you do not set the SQLJ -encoding option, then setting file.encoding will affect encoding for source files as well as error and status messages.

  • Be aware that altering the file.encoding property might have unforeseen consequences on other aspects of your Java operations; furthermore, any new setting must be compatible with your operating system.


For additional information about the SQLJ -J prefix, see "Command-Line Syntax and Operations" and "Options to Pass to the Java Virtual Machine (-J)".

SQLJ Command-Line Example: Setting Encoding and Locale

Following is a complete SQLJ command line, including JVM file.encoding and user.language settings:

sqlj -encoding=8859_1 -J-Dfile.encoding=SJIS -J-Duser.language=ja Foo.sqlj

This example uses the SQLJ -encoding option to specify 8859_1 (Latin-1) encoding for source code representation during SQLJ translation. This encoding is used by the translator in reading the .sqlj input file and in generating the .java output file. The encoding is then passed to the Java compiler to be used in reading the generated .java file. (The -encoding option, when specified, is always passed to the Java compiler unless the SQLJ -compiler-encoding-flag is disabled.)

For error and status messages output during translation of Foo.sqlj, the SQLJ translator uses the SJIS encoding and the ja locale.

Oracle SQLJ Extended Globalization Support

Oracle9i SQLJ adds support for Java types (Unicode character types) derived from existing character and stream types that convey expected usage for globalization support in the server. (In SQLJ it is not possible to use JDBC statement or result set methods directly that otherwise serve the purpose of globalization support, but if you are interested in information about those methods, refer to the Oracle9i JDBC Developer's Guide and Reference.)

If the database natively supports Unicode , then the types described in "Java Types for Globalization Support" below are unnecessary--globalization support will be handled transparently. It is when the database does not natively support Unicode, but has a national language character set that does support Unicode, that you will typically use these types (for columns that employ the national language character set).

Java Types for Globalization Support

Oracle9i SQLJ provides a number of Java types for globalization support. Table 9-3 notes the correspondence between these globalization support types and general-use JDBC and SQLJ character and stream types. Each globalization support type, except for NString, is a subclass of its corresponding JDBC or SQLJ type.

Table 9-3 JDBC and SQLJ Types and Corresponding Globalization Types  
JDBC and SQLJ Types  Globalization Support Types 

JDBC types: 








SQLJ types: 






In situations where your application must handle national language character strings as input parameters to the database or as output parameters from the database, use the globalization support types instead of the corresponding general-use types.


  • All globalization support types add automatic registration of intended usage for IN and OUT parameters, but are otherwise identical in usage to the corresponding JDBC or SQLJ type (including constructors).

  • Use of globalization support types is unnecessary in iterator columns, because the underlying network protocol supports national language characters implicitly for the underlying result sets.

  • In Oracle9i there is no direct support for these globalization support types in Oracle JDBC.

  • In Oracle9i there is no support for national language character attributes within Oracle objects.


NString Class Usage and Notes

Because the oracle.sql.CHAR class (and therefore its NCHAR subclass) provides only constructors that require explicit knowledge of the database character set, the oracle.sql.NString class--a wrapper for java.lang.String--is preferable in most circumstances.

The NString class provides simpler constructors and ensures that the national language character form of use is registered with the JDBC driver.

Following are the key NString methods:

The toString() method allows you to employ the NString instance in string concatenation expressions (such as "a"+b, where b is a string). The getString() method, provided in the CHAR superclass, is supported as well for uniformity. In addition, the member methods of the String class are carried over to the NString wrapper class to allow you to write more concise code.

Globalization Support Examples

The following examples show use of the NString class.

NString as IN Argument

This example uses an NString instance as an input parameter to the database.

import oracle.sql.NString;
NString nc_name = new NString("Name with strange characters");
#sql { update PEOPLE
       set city = :(new NString("\ufff2")), name = :nc_name
       where num= :n };

NString as OUT Argument

This example uses an NString instance as an output parameter from the database.

import oracle.sql.NString;
NString nstr;
#sql { call foo(:out nstr) };
System.out.println("Result is: "+nstr);
// or, explicitly: System.out.println("Result is: "+nstr.toString());

NString as Result Set Column

This example uses the NString type for an iterator column. Such usage is superfluous (given that the underlying network protocol supports national language characters implicitly), but harmless. This example also shows use of one of the String methods, substring(), that is carried over to NString.

import oracle.sql.NString;
import oracle.sql.NCLOB;
#sql iterator NIter(NString title, NCLOB article);

NIter nit;
#sql nit = { SELECT article, title FROM page_table };
while (
  nit.article().substring(0, 1000); ...


Using the NCHAR type instead of the NString type for the preceding examples requires the following changes:

  • Use the appropriate NCHAR constructor. NCHAR constructors mirror CHAR constructors, such as the following:

    NCHAR(String str, oracle.sql.CharacterSet charset)
  • Although you have the option of using either toString() or getString() to retrieve the underlying String instance from an NString instance, for an NCHAR instance you must use the getString() method. (When using the NString type, the toString() method is used automatically for string concatenation, such as in "NString as OUT Argument" above.)


Manipulation Outside of SQLJ for Globalization Support

This section discusses ways to manipulate your Oracle Globalization Support configuration outside of SQLJ.

Setting Encoding and Locale at Application Runtime

As with any end user running any Java application, those running your SQLJ application can specify JVM properties such as file.encoding and user.language directly, as they invoke the JVM to run your application. This determines the encoding and locale used for message output as your application executes.

They can accomplish this as in the following example:

java -Dfile.encoding=SJIS -Duser.language=ja Foo

This will use SJIS encoding and Japanese locale.

Using API to Determine Java Properties

In Java code, you can determine values of Java properties by using the java.lang.System.getProperty() method, specifying the appropriate property. For example:

public class Settings
   public static void main (String[] args)
      System.out.println("Encoding: " + System.getProperty("file.encoding")
                       + ", Language: " + System.getProperty("user.language"));

You can compile this and run it as a standalone utility.

There is also a getProperties() method that returns the values of all properties, but this will raise a security exception if you try to use it in code that runs in the server.

You can get information about the java.lang.System class at the following Web site:

Using native2ascii for Source File Encoding

If you are using a Sun Microsystems JDK, there is an alternative to having SQLJ do the character encoding for your source files. You can use the utility native2ascii to convert sources with native encoding to sources in 7-bit ASCII with Unicode escape sequences.


To use SQLJ to translate source created by native2ascii, ensure that the JVM that invokes SQLJ has a file.encoding setting that supports some superset of 7-bit ASCII. This is not the case with settings for EBCDIC or Unicode encoding. 

Run native2ascii as follows:

% native2ascii <options> <inputfile> <outputfile>

Standard input or standard output are used if you omit the input file or output file. Two options are supported:

For example:

% native2ascii -encoding SJIS Foo.sqlj Temp.sqlj

For more information see the following Web site:

Go to previous page Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Solution Area
Go To Table Of Contents
Go To Index