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

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

Master Index


Go to previous page Go to next page


This chapter provides a general overview of SQLJ features and scenarios. The following topics are discussed:

Introduction to SQLJ

This section introduces the basic concepts of SQLJ and discusses the complementary relationship between Java and PL/SQL in Oracle applications.

Basic Concepts

SQLJ enables applications programmers to embed SQL operations in Java code in a way that is compatible with the Java design philosophy. A SQLJ program is a Java program containing embedded SQL statements that comply with the ISO-standard SQLJ Language Reference syntax. Oracle9i SQLJ supports the SQLJ ISO standard specification. The standard covers only static SQL operations--those that are predefined and do not change in real-time as a user runs the application (although the data values that are transmitted can change dynamically). Oracle SQLJ also offers extensions to support dynamic SQL operations--those that are not predefined, where the operations themselves can change in real-time. (It is also possible to use dynamic SQL operations through JDBC code or PL/SQL code within a SQLJ application.) Typical applications contain much more static SQL than dynamic SQL.

SQLJ consists of both a translator and a runtime component and is smoothly integrated into your development environment. The developer runs the translator, with translation, compilation, and customization taking place in a single step when the sqlj front-end utility is run. The translation process replaces embedded SQL with calls to the SQLJ runtime, which implements the SQL operations. In standard SQLJ this is typically, but not necessarily, performed through calls to a JDBC driver. To access an Oracle database, you would typically use an Oracle JDBC driver. When the end user runs the SQLJ application, the runtime is invoked to handle the SQL operations.

The Oracle SQLJ translator is conceptually similar to other Oracle precompilers and allows the developer to check SQL syntax, verify SQL operations against what is available in the schema, and check the compatibility of Java types with corresponding database types. In this way, errors can be caught by the developer instead of by a user at runtime. The translator checks the following:

The SQLJ methodology of embedding SQL operations directly in Java code is much more convenient and concise than the JDBC methodology. In this way, SQLJ reduces development and maintenance costs in Java programs that require database connectivity.

Java and SQLJ versus PL/SQL

Java (including SQLJ) in Oracle applications does not replace PL/SQL. Java and PL/SQL are complementary to each other in the needs they serve.

While PL/SQL and Java can both be used to build database applications, the two languages were designed with different intents and, as a result, are suited for different kinds of applications:

Oracle provides easy interoperability between PL/SQL and Java, ensuring that you can take advantage of the strengths of both languages. PL/SQL programs can transparently call Java stored procedures, enabling you to build component-based Enterprise JavaBeans and CORBA applications. PL/SQL programs can have transparent access to a wide variety of existing Java class libraries through trivial PL/SQL call specifications.

Java programs can call PL/SQL stored procedures and anonymous blocks through JDBC or SQLJ. In particular, SQLJ provides syntax for calling stored procedures and functions from within a SQLJ statement, and also supports embedded PL/SQL anonymous blocks within a SQLJ statement.


Using PL/SQL anonymous blocks within SQLJ statements is one way to support dynamic SQL in a SQLJ application. (See "Dynamic SQL--DynamicDemo.sqlj" for a sample.) However, Oracle9i SQLJ includes extensions to support dynamic SQL directly. (See "Support for Dynamic SQL"

Overview of SQLJ Components

This section introduces the main SQLJ components and the concept of SQLJ profiles.

SQLJ Translator and SQLJ Runtime

Oracle SQLJ consists of two major components:

In addition to the translator and runtime, there is a component known as the customizer. A customizer tailors your SQLJ profiles (if any) for a particular database implementation and vendor-specific features and datatypes. By default, the Oracle SQLJ front end invokes an Oracle customizer to tailor your profiles for an Oracle database and Oracle-specific features and datatypes.

When you use the Oracle customizer during translation, your application will require the Oracle SQLJ runtime and an Oracle JDBC driver when it runs.

SQLJ Profiles

With standard SQLJ code generation, SQLJ profiles are serialized Java resources (or, optionally, classes) generated by the SQLJ translator, which contain details about the embedded SQL operations in your SQLJ source code. The translator creates these profiles, then either serializes them and puts them into binary resource files, or puts them into .class files (according to your translator option settings).


As an alternative, Oracle SQLJ supports a setting for Oracle-specific code generation. In this case, the translator generates Oracle JDBC calls directly, and details of your embedded SQL operations are embodied in the JDBC calls. There are no profiles in this case. See "Oracle-Specific Code Generation (No Profiles)"

Overview of Profiles

SQLJ profiles are used (assuming standard SQLJ code generation) in implementing the embedded SQL operations in your SQLJ executable statements. Profiles contain information about your SQL operations and the types and modes of data being accessed. A profile consists of a collection of entries, where each entry maps to one SQL operation. Each entry fully specifies the corresponding SQL operation, describing each of the parameters used in executing this instruction.

SQLJ generates a profile for each connection context class in your application, where, typically, each connection context class corresponds to a particular set of SQL entities you use in your database operations. (There is one default connection context class, and you can declare additional classes.) The SQLJ standard requires that the profiles be of standard format and content. Therefore, for your application to use vendor-specific extended features, your profiles must be customized. By default, this occurs automatically, with your profiles being customized to use Oracle-specific extended features.

Profile customization allows vendors to add value in two ways:

For example, you must customize your profile to use Oracle objects in your SQLJ application.


  • By default, SQLJ profile file names end in the .ser extension, but this does not mean that all .ser files are profiles. Any serialized object uses that extension, and a SQLJ program unit can use serialized objects other than its profiles. (Optionally, profiles can be converted to .class files instead of .ser files.)

  • A SQLJ profile is not produced if there are no SQLJ executable statements in the source code.


Binary Portability

SQLJ-generated profile files feature binary portability. That is, you can port them as is and use them with other kinds of databases or in other environments if you have not employed vendor-specific datatypes or features. This is true of generated .class files as well.

Overview of Oracle Extensions to the SQLJ Standard

With Oracle9i (and in Oracle8i release 8.1.7), Oracle SQLJ supports the SQLJ ISO specification. Because the SQLJ ISO standard is a superset of the SQLJ ANSI standard, it requires a JDK 1.2 or later environment that complies with J2EE. The SQLJ ANSI standard requires only JDK 1.1.x. The Oracle SQLJ translator accepts a broader range of SQL syntax than the ANSI SQLJ Standard specifies.

The ANSI standard addresses only the SQL92 dialect of SQL, but allows extension beyond that. Oracle SQLJ supports Oracle's SQL dialect, which is a superset of SQL92. If you need to create SQLJ programs that work with other DBMS vendors, avoid using SQL syntax and SQL types that are not in the standard and, therefore, may not be supported in other environments. (On your product CD, the directory [Oracle Home]/sqlj/demo/components includes a semantics-checker that you can use to verify that your SQLJ statements contain only standard SQL.)

For general information about Oracle SQLJ extensions, see Chapter 5, "Type Support", and Chapter 6, "Objects and Collections".

Oracle SQLJ Type Extensions

Oracle SQLJ supports the Java types listed below as extensions to the SQLJ standard. Do not use these or other types if you may want to use your code in other environments. To ensure that your application is portable, use the Oracle SQLJ -warn=portable flag. (See "Translator Warnings (-warn)".)

Using any of the following extensions requires Oracle customization or Oracle-specific code generation during translation, as well as the Oracle SQLJ runtime and an Oracle JDBC driver when your application runs.

Oracle SQLJ Functionality Extensions

In addition to the type extensions listed above, Oracle SQLJ supports the following extended functionality:

Basic Translation Steps and Runtime Processing

This section introduces the following:

For more detailed information about the translation steps, see "Internal Translator Operations".

SQLJ source code contains a mixture of standard Java source together with SQLJ class declarations and SQLJ executable statements containing embedded SQL operations.

SQLJ source files have the .sqlj file name extension. The file name must be a legal Java identifier. If the source file declares a public class (maximum of one), then the file name must match the name of this class. If the source file does not declare a public class, then the file name should match the first defined class.

Translation Steps

After you have completed your .sqlj file, you must run SQLJ to process the files. (For coding the .sqlj file, basic SQLJ programming features and key considerations are discussed in Chapter 3 and Chapter 4.) The following example, for the source file Foo.sqlj whose first public class is Foo, shows SQLJ being run in its simplest form, with no command-line options:

sqlj Foo.sqlj

What this command actually runs is a front-end script or utility (depending on the platform) that reads the command line, invokes a Java virtual machine (JVM), and passes arguments to it. The JVM invokes the SQLJ translator and acts as a front end.

This document refers to running the front end as "running SQLJ" and to its command line as the "SQLJ command line". For information about command-line syntax, see "Command-Line Syntax and Operations".

From this point the following sequence of events occurs, presuming each step completes without fatal error.

  1. The JVM invokes the SQLJ translator.

  2. The translator parses the source code in the .sqlj file, checking for proper SQLJ syntax and looking for type mismatches between your declared SQL datatypes and corresponding Java host variables. (Host variables are local Java variables used as input or output parameters in your SQL operations. "Java Host Expressions, Context Expressions, and Result Expressions" describes them.)

  3. The translator invokes the semantics-checker, which checks the syntax and semantics of embedded SQL statements. It also can optionally check the use of database elements in your code against an appropriate database schema.

    The developer can use online or offline checking, according to SQLJ option settings. If online checking is performed, then SQLJ will connect to a specified database schema to verify that the database supports all the database tables, stored procedures, and SQL syntax that the application uses, and that the host variable types in the SQLJ application are compatible with datatypes of corresponding database columns.

  4. For standard SQLJ code generation, the translator processes your SQLJ source code, converts SQL operations to SQLJ runtime calls, and generates Java output code and one or more SQLJ profiles. A separate profile is generated for each connection context class in your source code, where a different connection context class is typically used for each interrelated set of SQL entities that you use in your operations.

    For Oracle-specific code generation, SQL operations are converted directly into Oracle JDBC calls and no profiles are produced. See "Oracle-Specific Code Generation (No Profiles)".

    Generated Java code is put into a .java output file containing the following:

    • any class definitions and Java code from your .sqlj source file

    • class definitions created as a result of your SQLJ iterator and connection context declarations (see "Overview of SQLJ Declarations")

    • a class definition for a specialized class (known as the profile-keys class) that SQLJ generates and uses in conjunction with your profiles (standard SQLJ code generation only)

    • calls to the SQLJ runtime (for standard SQLJ code generation) or to Oracle JDBC (for Oracle-specific code generation) to implement the actions of your embedded SQL operations

      (The SQLJ runtime, in turn, uses the JDBC driver to access the database. See "SQLJ Runtime" for more information.)

    Generated profiles (for standard SQLJ code generation only) contain information about all the embedded SQL statements in your SQLJ source code, such as actions to take, datatypes being manipulated, and tables being accessed. When your application is run, the SQLJ runtime accesses the profiles to retrieve your SQL operations and pass them to the JDBC driver.

    By default, profiles are put into .ser serialized resource files, but SQLJ can optionally convert the .ser files to .class files as part of the translation.

  5. The JVM invokes the Java compiler, which is usually, but not necessarily, the standard javac provided with the Sun Microsystems JDK.

  6. The compiler compiles the Java source file generated in step 4 and produces Java .class files as appropriate. This will include a .class file for each class you defined, a .class file for each of your SQLJ declarations, and a .class file for the profile-keys class.

  7. For standard SQLJ code generation, the JVM invokes the Oracle SQLJ customizer or other specified customizer.

  8. For standard SQLJ code generation, the customizer customizes the profiles generated in step 4.


    • The preceding is a very generic example. It is also possible to specify pre-existing .java files on the command line to be compiled (and to be available for type resolution as well), or to specify pre-existing profiles to be customized, or to specify .jar files containing profiles to be customized. See "Translator Command Line and Properties Files" for more information.

    • SQLJ generates profiles and the profile-keys class only if your source code includes SQLJ executable statements, and only if you use standard SQLJ code generation.

    • For standard SQLJ code generation, if you use the Oracle customizer during translation, your application will require the Oracle SQLJ runtime and an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features.

    • For Oracle-specific code generation, your application will require an Oracle JDBC driver when it runs, even if your code does not use Oracle-specific features.


Summary of Translator Input and Output

This section summarizes what the SQLJ translator takes as input, what it produces as output, and where it puts its output.


This discussion mentions iterator class and connection context class declarations. Iterators are similar to JDBC result sets; connection contexts are used for database connections. For more information about these class declarations, see "Overview of SQLJ Declarations"


In its most basic operation, the SQLJ translator takes one or more .sqlj source files as input in its command line. The name of your main .sqlj file is based on the public class it defines, if it defines one, or else on the first class it defines if there are no public class definitions. Each public class you define must be in its own .sqlj file.

If your main .sqlj file defines class MyClass, then the source file name must be:


This must also be the file name if there are no public class definitions but MyClass is the first class defined.

When you run SQLJ, you can also specify numerous SQLJ options in the command line or properties files.

For more information about SQLJ input, including additional types of files you can specify in the command line, see "Translator Command Line and Properties Files".


The translation step produces a Java source file for each .sqlj file in your application, and (with standard SQLJ code generation) at least one application profile (presuming your source code uses SQLJ executable statements).

SQLJ generates source files and profiles as follows:

The compilation step compiles the Java source file into multiple class files. For standard SQLJ code generation there are at least two class files: one for each class you define in your .sqlj source file (minimum of one), and one for a class, known as the profile-keys class, that the translator generates and uses with the profiles to implement your SQL operations (presuming your source code uses SQLJ executable statements). Additional .class files are produced if you declared any SQLJ iterators or connection contexts (see "Overview of SQLJ Declarations"). Also, separate .class files will be produced for any inner classes or anonymous classes in your code. For Oracle-specific code generation, no profiles or profile-keys class are produced. For information about Oracle-specific code generation, see "Oracle-Specific Code Generation (No Profiles)".

The .class files are named as follows:

The customization step alters the profiles but produces no additional output.


It is not necessary to reference SQLJ profiles or the profile-keys class directly. This is all handled automatically. 

Output File Locations

By default, SQLJ places generated .java files in the same directory as your .sqlj file. You can specify a different .java file location, however, using the SQLJ -dir option.

By default, SQLJ places generated .class and .ser files in the same directory as the generated .java files. You can specify a different .class and .ser file location, however, using the SQLJ -d option. This option setting is passed to the Java compiler so that .class files and .ser files will be in the same location.

For either the -d or -dir option, you must specify a directory that already exists. For more information about these options, see "Options for Output Files and Directories".

Runtime Processing

This section discusses runtime processing during program execution, considering both standard SQLJ code generation and Oracle-specific code generation.

Processing for Standard SQLJ Generated Code

When a user runs the application, the SQLJ runtime reads the profiles and creates "connected profiles", which incorporate database connections. Then the following occurs each time the application must access the database:

  1. SQLJ-generated application code uses methods in a SQLJ-generated profile-keys class to access the connected profile and read the relevant SQL operations. There is a mapping between SQLJ executable statements in the application and SQL operations in the profile.

  2. The SQLJ-generated application code calls the SQLJ runtime, which reads the SQL operations from the profile.

  3. The SQLJ runtime calls the JDBC driver and passes the SQL operations to it.

  4. The SQLJ runtime passes any input parameters to the JDBC driver.

  5. The JDBC driver executes the SQL operations.

  6. If any data is to be returned, the database sends it to the JDBC driver, which sends it to the SQLJ runtime for use by your application.


    Passing input parameters (step 4) can also be referred to as "binding input parameters" or "binding host expressions". The terms host variables, host expressions, bind variables, and bind expressions are all used to describe Java variables or expressions that are used as input or output for SQL operations. 

Processing for Oracle-Specific Generated Code

When you translate with the translator setting -codegen=oracle, your program at runtime will execute the following:

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

Alternative Deployment Scenarios

Although this manual mainly discusses writing for client-side SQLJ applications, you may find it useful to run SQLJ code in the following scenarios:

Running SQLJ in Applets

Because the SQLJ runtime is pure Java, you can use SQLJ source code in applets as well as applications. There are, however, a few considerations, as discussed below.

For an example, see "Applet Sample".

For applet issues that apply more generally to the Oracle JDBC drivers, see the Oracle9i JDBC Developer's Guide and Reference, which includes discussion of firewalls and security issues as well.

General Development and Deployment Considerations

The following general considerations apply to the use of Oracle SQLJ applets.

General End User Considerations

When end users run your SQLJ applet, classes in their classpath may conflict with classes that are downloaded with the applet.

Oracle, therefore, recommends that end users clear their classpath before running the applet.

Java Environment and the Java Plug-in

Here are some additional considerations regarding the Java environment and use of Oracle-specific features.

The preceding issues can be summarized as follows, focusing on users with Internet Explorer and Netscape browsers:

Introduction to SQLJ in the Server

In addition to its use in client applications, SQLJ code can run within a target Oracle9i database or middle-tier database cache in stored procedures, stored functions, triggers, Enterprise JavaBeans, or CORBA objects. Server-side access occurs through an Oracle JDBC driver that runs inside the server itself. Additionally, the Oracle9i database has an embedded SQLJ translator so that SQLJ source files for server-side use can optionally be translated directly in the server.

The two main areas to consider, which Chapter 11, "SQLJ in the Server", discusses in detail are:

Using SQLJ with Oracle Lite

You can use SQLJ on top of Oracle Lite. This section provides a brief overview of this functionality. For more information, refer to the Oracle Lite Java Developer's Guide.

Overview of Oracle Lite and Java Support

Oracle Lite is a lightweight database that offers flexibility and versatility that larger databases cannot. It requires only 350K to 750K of memory for full functionality, natively synchronizes with the Palm Computing platform, and can run on Windows NT (3.51 or higher), Windows 95, and Windows 98. It offers an embedded environment that requires no background or server processes.

Oracle Lite is compatible with Oracle9i, Oracle8i, Oracle8, and Oracle7. It provides comprehensive support for Java, including JDBC, SQLJ, and Java stored procedures. There are two alternatives for access to Oracle Lite from Java programs:

There is interoperability between Oracle Lite JDBC and JAC, with JAC supporting all types that JDBC supports, and JDBC supporting JAC types that meet certain requirements.

Requirements to Run Java on Oracle Lite

Note the following requirements if you intend to run a Java program on top of Oracle Lite:

Support for Oracle Extensions

Oracle Lite 4.0.x. includes an Oracle-specific JDBC driver and Oracle-specific SQLJ runtime classes (including the Oracle semantics-checkers and customizer), allowing use of Oracle-specific features and type extensions.

Alternative Development Scenarios

The discussion in this book assumes that you are coding manually in a UNIX environment for English-language deployment. However, you can use SQLJ on other platforms and with IDEs. There is also globalization support for deployment to other languages. This section introduces these topics:

SQLJ Globalization Support

Oracle SQLJ support for native languages and character encodings is based on Java's built-in globalization support capabilities.

The standard user.language and file.encoding properties of the JVM determine appropriate language and encoding for translator and runtime messages. The SQLJ -encoding option determines encoding for interpreting and generating source files during translation.

For information, see "Globalization Support in the Translator and Runtime".

SQLJ in JDeveloper and Other IDEs

Oracle SQLJ includes a programmatic API so that it can be embedded in integrated development environments (IDEs) such as Oracle JDeveloper. The IDE takes on a role similar to that of the sqlj script used as a front end in Solaris, invoking the translator, semantics-checker, compiler, and customizer.

Oracle JDeveloper is a Windows NT-based visual development environment for Java programming. The JDeveloper Suite enables developers to build multi-tier, scalable Internet applications using Java across the Oracle Internet Platform. The core product of the suite--the JDeveloper Integrated Development Environment--excels in creating, debugging, and deploying component-based applications.

The Oracle JDBC OCI and Thin drivers are included with JDeveloper, as well as drivers to access Oracle Lite.

JDeveloper's compilation functionality includes an integrated Oracle SQLJ translator so that your SQLJ application is translated automatically as it is compiled.

Information about JDeveloper is available at the following URL:

Windows Considerations

Note the following if you are using a Windows platform instead of Solaris:

Refer to the Windows platform README file for additional information.

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

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

Master Index