Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Structure

Related Documents

Conventions

Documentation Accessibility

1 Overview

Introduction to SQLJ

Basic Concepts

Java and SQLJ versus PL/SQL

Overview of SQLJ Components

SQLJ Translator and SQLJ Runtime

SQLJ Profiles

Overview of Oracle Extensions to the SQLJ Standard

Basic Translation Steps and Runtime Processing

Translation Steps

Summary of Translator Input and Output

Runtime Processing

Alternative Deployment Scenarios

Running SQLJ in Applets

Introduction to SQLJ in the Server

Using SQLJ with Oracle Lite

Alternative Development Scenarios

SQLJ Globalization Support

SQLJ in JDeveloper and Other IDEs

Windows Considerations

2 Getting Started

Assumptions and Requirements

Assumptions About Your Environment

Requirements for Using Oracle SQLJ

Supported JDK Versions

Oracle SQLJ Backwards Compatibility

Oracle JVM Configuration

Checking the Installation and Configuration

Check for Installed Directories and Files

Set the Path and Classpath

Verify Installation of sqljutl Package

Testing the Setup

Set Up the Runtime Connection

Create a Table to Verify the Database

Verify the JDBC Driver

Verify the SQLJ Translator and Runtime

Verify the SQLJ Translator Connection to the Database

3 Basic Language Features

Overview of SQLJ Declarations

Rules for SQLJ Declarations

Iterator Declarations

Connection Context Declarations

Declaration IMPLEMENTS Clause

Declaration WITH Clause

Overview of SQLJ Executable Statements

Rules for SQLJ Executable Statements

SQLJ Clauses

Specifying Connection Context Instances and Execution Context Instances

Executable Statement Examples

PL/SQL Blocks in Executable Statements

Java Host Expressions, Context Expressions, and Result Expressions

Overview of Host Expressions

Basic Host Expression Syntax

Examples of Host Expressions

Overview of Result Expressions and Context Expressions

Evaluation of Java Expressions at Runtime

Examples of Evaluation of Java Expressions at Runtime

Restrictions on Host Expressions

Single-Row Query Results--SELECT INTO Statements

SELECT INTO Syntax

Examples of SELECT INTO Statements

Examples with Host Expressions in SELECT-List

SELECT INTO Error Conditions

Multi-Row Query Results--SQLJ Iterators

Iterator Concepts

General Steps in Using an Iterator

Named Iterators Versus Positional Iterators Versus Result Set Iterators

Using Named Iterators

Using Positional Iterators

Using Iterators and Result Sets as Host Variables

Using Iterators and Result Sets as Iterator Columns

Assignment Statements (SET)

Stored Procedure and Function Calls

Calling Stored Procedures

Calling Stored Functions

Using Iterators and Result Sets as Stored Function Returns

4 Key Programming Considerations

Selection of the JDBC Driver

Overview of the Oracle JDBC Drivers

Driver Selection for Translation

Driver Selection and Registration for Runtime

Connection Considerations

Single Connection or Multiple Connections Using DefaultContext

Closing Connections

Multiple Connections Using Declared Connection Context Classes

More About the Oracle Class

More About the DefaultContext Class

Connection for Translation

Connection for Customization

Null-Handling

Wrapper Classes for Null-Handling

Examples of Null-Handling

Exception-Handling Basics

SQLJ and JDBC Exception-Handling Requirements

Processing Exceptions

Using SQLException Subclasses

Basic Transaction Control

Overview of Transactions

Automatic Commits versus Manual Commits

Specifying Auto-Commit as You Define a Connection

Modifying Auto-Commit in an Existing Connection

Using Manual COMMIT and ROLLBACK

Effect of Commits and Rollbacks on Iterators and Result Sets

Summary: First Steps in SQLJ Code

Import Required Classes

Register JDBC Drivers and Set Default Connection

Set Up Exception Handling

Set Up Host Variables, Execute SQLJ Clause, Process Results

Example of Single-Row Query using SELECT INTO

Set Up a Named Iterator

Example of Multiple-Row Query Using Named Iterator

Other Programming Considerations

Naming Requirements and Restrictions

Statement Caching Methods

5 Type Support

Supported Types for Host Expressions

Summary of Supported Types

Supported Types and Requirements for JDBC 2.0

Unsupported Types

Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

Backwards Compatibility for Previous Oracle JDBC Releases

Support for Streams

General Use of SQLJ Streams

Using SQLJ Streams to Send Data

Retrieving Data into Streams--Precautions

Using SQLJ Streams to Retrieve Data

Processing SQLJ Streams

Examples of Retrieving and Processing Stream Data

SQLJ Stream Objects as Output Parameters and Function Return Values

Stream Class Methods

Support for JDBC 2.0 LOB Types and Oracle Type Extensions

Package oracle.sql

Support for BLOB, CLOB, and BFILE

Support for Oracle ROWID

Support for Oracle REF CURSOR Types

Support for Other Oracle9i Datatypes

Extended Support for BigDecimal

6 Objects and Collections

Oracle Objects and Collections

Introduction to Objects and Collections

Oracle Object Fundamentals

Oracle Collection Fundamentals

Object and Collection Datatypes

Custom Java Classes

Custom Java Class Interface Specifications

Custom Java Class Support for Object Methods

Custom Java Class Requirements

Compiling Custom Java Classes

Reading and Writing Custom Data

Additional Uses for ORAData Implementations

User-Defined Types

Creating Object Types

Creating Collection Types

JPublisher and the Creation of Custom Java Classes

What JPublisher Produces

Generating Custom Java Classes

JPublisher INPUT Files and Properties Files

Creating Custom Java Classes and Specifying Member Names

JPublisher Implementation of Wrapper Methods

JPublisher Custom Java Class Examples

Extending Classes Generated by JPublisher

Strongly Typed Objects and References in SQLJ Executable Statements

Selecting Objects and Object References into Iterator Columns

Updating an Object

Inserting an Object Created from Individual Object Attributes

Updating an Object Reference

Strongly Typed Collections in SQLJ Executable Statements

Accessing Nested Tables--TABLE syntax and CURSOR syntax

Inserting a Row that Includes a Nested Table

Selecting a Nested Table into a Host Expression

Manipulating a Nested Table Using TABLE Syntax

Selecting Data from a Nested Table Using a Nested Iterator

Selecting a VARRAY into a Host Expression

Inserting a Row that Includes a VARRAY

Serialized Java Objects

Serializing Java Classes to RAW and BLOB Columns

SerializableDatum--An ORAData Implementation

SerializableDatum in SQLJ Applications

SerializableDatum (Complete Class)

Weakly Typed Objects, References, and Collections

Support for Weakly Typed Objects, References, and Collections

Restrictions on Weakly Typed Objects, References, and Collections

7 Advanced Language Features

Connection Contexts

Connection Context Concepts

Connection Context Logistics

More About Declaring and Using a Connection Context Class

Example of Multiple Connection Contexts

Implementation and Functionality of Connection Context Classes

Use of the IMPLEMENTS Clause in Connection Context Declarations

Semantics-Checking of Your Connection Context Usage

Data Source Support

Execution Contexts

Relation of Execution Contexts to Connection Contexts

Creating and Specifying Execution Context Instances

Execution Context Synchronization

ExecutionContext Methods

Relation of Execution Contexts to Multithreading

Multithreading in SQLJ

Iterator Class Implementation and Advanced Functionality

Implementation and Functionality of Iterator Classes

Use of the IMPLEMENTS Clause in Iterator Declarations

Support for Subclassing of Iterator Classes

Result Set Iterators

Scrollable Iterators

Advanced Transaction Control

SET TRANSACTION Syntax

Access Mode Settings

Isolation Level Settings

Using JDBC Connection Class Methods

SQLJ and JDBC Interoperability

SQLJ Connection Context and JDBC Connection Interoperability

SQLJ Iterator and JDBC Result Set Interoperability

Support for Dynamic SQL

Meta Bind Expressions

SQLJ Dynamic SQL Examples

8 Translator Command Line and Options

Translator Command Line and Properties Files

SQLJ Options, Flags, and Prefixes

Command-Line Syntax and Operations

Properties Files for Option Settings

SQLJ_OPTIONS Environment Variable for Option Settings

Order of Precedence of Option Settings

Basic Translator Options

Basic Options for Command Line Only

Options for Output Files and Directories

Connection Options

Reporting and Line-Mapping Options

Options for Code Generation, Column Optimizations, and Parameter Optimizations

Advanced Translator Options

Prefixes that Pass Option Settings to Other Executables

Flags for Special Processing

Semantics-Checking Options

Translator Support and Options for Alternative Environments

Java and Compiler Options

Customization Options

9 Translator and Runtime Functionality

Internal Translator Operations

Code-Parsing and Syntax-Checking

Semantics-Checking

Code Generation

Java Compilation

Profile Customization

Functionality of Translator Errors, Messages, and Exit Codes

Translator Error, Warning, and Information Messages

Translator Status Messages

Translator Exit Codes

SQLJ Runtime

Runtime Packages

Categories of Runtime Errors

Globalization Support in the Translator and Runtime

Character Encoding and Language Support

SQLJ and Java Settings for Character Encoding and Language Support

Oracle SQLJ Extended Globalization Support

Manipulation Outside of SQLJ for Globalization Support

10 Profiles and Customization

More About Profiles

Creation of a Profile During Code Generation

Sample Profile Entry

More About Profile Customization

Overview of the Customizer Harness and Customizers

Steps in the Customization Process

Creation and Registration of a Profile Customization

Customization Error and Status Messages

Functionality of a Customized Profile at Runtime

Oracle-Specific Code Generation (No Profiles)

Advantages and Disadvantages of Oracle-Specific Code Generation

Environment Requirements for Oracle-Specific Code Generation

Coding Considerations and Limitations with Oracle-Specific Code Generation

Translator/Customizer Usage Changes with Oracle-Specific Code Generation

Server-Side Considerations with Oracle-Specific Code Generation

Customization Options and Choosing a Customizer

Overview of Customizer Harness Options

General Customizer Harness Options

Customizer Harness Options for Connections

Customizer Harness Options that Invoke Specialized Customizers

Overview of Customizer-Specific Options

Oracle Customizer Options

Options for Other Customizers

SQLJ Options for Profile Customization

Use of JAR Files for Profiles

JAR File Requirements

JAR File Results

SQLCheckerCustomizer for Profile Semantics-Checking

Invoking SQLCheckerCustomizer with the Customizer Harness verify Option

SQLCheckerCustomizer Options

11 SQLJ in the Server

Introduction to Server-Side SQLJ

Creating SQLJ Code for Use within the Server

Database Connections within the Server

Coding Issues within the Server

Default Output Device in the Server

Name Resolution in the Server

SQL Names Versus Java Names

Translating SQLJ Source on a Client and Loading Components

Loading Classes and Resources into the Server

Loaded Class and Resource Schema Objects

Publishing the Application After Loading Class and Resource Files

Summary: Running a Client Application in the Server

Loading SQLJ Source and Translating in the Server

Loading SQLJ Source Code into the Server

Option Support in the Server Embedded Translator

Loaded Source and Generated Class and Resource Schema Objects

Error Output from the Server Embedded Translator

Publishing the Application After Loading Source Files

Dropping Java Schema Objects

Additional Considerations

Java Multithreading in the Server

Recursive SQLJ Calls in the Server

Verifying that Code is Running in the Server

Additional Vehicles for SQLJ in the Server

Enterprise JavaBeans

CORBA Server Objects

12 Sample Applications

Demo Directories

Properties Files

Runtime Connection Properties File

SQLJ Translator Properties File

Basic Samples

Named Iterator--NamedIterDemo.sqlj

Positional Iterator--PosIterDemo.sqlj

Host Expressions--ExprDemo.sqlj

Object, Collection, and ORAData Samples

Definition of Object and Collection Types

Oracle Objects--ObjectDemo.sqlj

Oracle Nested Tables--NestedDemo1.sqlj and NestedDemo2.sqlj

Oracle VARRAYs--VarrayDemo1.sqlj and VarrayDemo2.sqlj

General Use of ORAData--BetterDate.java

Advanced Samples

REF CURSOR--RefCursDemo.sqlj

Multithreading--MultiThreadDemo.sqlj

Interoperability with JDBC--JDBCInteropDemo.sqlj

Multiple Connection Contexts--MultiSchemaDemo.sqlj

Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

Subclassing Iterators--SubclassIterDemo.sqlj

Dynamic SQL--DynamicDemo.sqlj

Performance Enhancement Samples

Prefetch Demo--PrefetchDemo.sqlj

Update Batching--BatchDemo.sqlj

Applet Sample

Generic Applet HTML Page--Applet.html

Generic Applet SQLJ Source--AppletMain.sqlj

Server-Side Sample

SQLJ in the Server--ServerDemo.sqlj

JDBC Versus SQLJ Sample Code

JDBC Version of the Sample Code

SQLJ Version of the Sample Code

A Performance and Debugging

Performance Enhancement Features

Row Prefetching

Statement Caching

Update Batching

Column Definitions

Parameter Size Definitions

AuditorInstaller Customizer for Debugging

Overview of Auditors and Code Layers

Invoking AuditorInstaller with the Customizer Harness debug Option

AuditorInstaller Runtime Output

AuditorInstaller Options

Full Command-Line Examples

Additional SQLJ Debugging Considerations

SQLJ -linemap Flag

Server-Side debug Option

Developing and Debugging in JDeveloper

B SQLJ Error Messages

Translation Time Messages

Runtime Messages