Contents

Title and Copyright Information

Send Us Your Comments

Preface

Intended Audience

Documentation Accessibility

Organization

Related Documentation

Conventions

1 Overview

Introduction to SQLJ

Basic Concepts

Oracle-Specific Code Generation Versus ISO Standard Code Generation

Java and SQLJ Versus PL/SQL

Overview of SQLJ Components

SQLJ Translator and SQLJ Runtime

SQLJ Profiles (ISO Standard Code)

Overview of Oracle Extensions to the SQLJ Standard

Basic Translation Steps and Runtime Processing

SQLJ Translation Steps

Summary of Translator Input and Output

SQLJ Runtime Processing

JDBC Versus SQLJ Sample Code

JDBC Version of the Sample Code

SQLJ Version of the Sample Code

Alternative Deployment Scenarios

Running SQLJ in Applets

Introduction to SQLJ in the Server

Using SQLJ with Oracle9i Lite

Alternative Development Scenarios

SQLJ Globalization Support

SQLJ in Oracle9i JDeveloper and Other IDEs

Windows Considerations

2 Getting Started

Assumptions and Requirements

Assumptions About Your Environment

Requirements for Using Oracle SQLJ

Oracle SQLJ Environment: Key Scenarios and Guidelines

Environment Issues and Limitations

Oracle SQLJ Backward Compatibility

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 (ISO Code Generation)

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

Using Savepoints

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

Oracle-Specific Code Generation (No Profiles)

Advantages and Disadvantages of Oracle-Specific Code Generation

Environment Requirements for Oracle-Specific Code Generation

Code Considerations and Limitations with Oracle-Specific Code Generation

SQLJ Usage Changes with Oracle-Specific Code Generation

Server-Side Considerations with Oracle-Specific Code Generation

Requirements and Restrictions for Naming

Java Namespace: Local Variable and Class Naming Restrictions

SQLJ Namespace

SQL Namespace

File Name Requirements and Restrictions

Considerations for SQLJ in the Middle Tier

5 Type Support

Supported Types for Host Expressions

Summary of Supported Types

Supported Types and Requirements for JDBC 2.0

Using PL/SQL BOOLEAN, RECORD Types, and TABLE Types

Backward Compatibility for Previous Oracle JDBC Releases

Support for Streams

General Use of SQLJ Streams

Key Aspects of Stream Support Classes

Using SQLJ Streams to Send Data

Retrieving Data into Streams: Precautions

Using SQLJ Streams to Retrieve Data

Stream Class Methods

Examples of Retrieving and Processing Stream Data

SQLJ Stream Objects as Output Parameters and Function Return Values

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, Collections, and OPAQUE Types

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

Oracle OPAQUE Types

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

Using the IMPLEMENTS Clause in Connection Context Declarations

Semantics-Checking of Your Connection Context Usage

Standard Data Source Support

SQLJ-Specific Data Sources

SQLJ-Specific Connection JavaBeans for JavaServer Pages

Execution Contexts

Relation of Execution Contexts to Connection Contexts

Creating and Specifying Execution Context Instances

Execution Context Synchronization

Execution Context Methods

Relation of Execution Contexts to Multithreading

Multithreading in SQLJ

Iterator Class Implementation and Advanced Functionality

Implementation and Functionality of Iterator Classes

Using 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 the Command Line Only

Options for Output Files and Directories

Connection Options

Options for Reporting and Line-Mapping

Options for Code Generation, Optimizations, and CHAR Comparisons

Advanced Translator Options

Prefixes that Pass Option Settings to Other Executables

Flags for Special Processing

Semantics-Checking and Offline-Parsing Options

Translator Support and Options for Alternative Environments

Java and Compiler Options

Customization Options

9 Translator and Runtime Functionality

Internal Translator Operations

Java and SQLJ Code-Parsing and Syntax-Checking

SQL Semantics-Checking and Offline Parsing

Code Generation

Java Compilation

Profile Customization (ISO Code Generation)

Functionality of Translator Errors, Messages, and Exit Codes

Translator Error, Warning, and Information Messages

Translator Status Messages

Translator Exit Codes

SQLJ Runtime

SQLJ 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 Performance and Debugging

Performance Enhancement Features

Row Prefetching

Statement Caching

Update Batching

Column Definitions

Parameter Size Definitions

SQLJ Debugging Features

SQLJ -linemap Flag for Debugging

Server-Side debug Option

Introduction to the AuditorInstaller Specialized Customizer

Introduction to Developing and Debugging in Oracle9i JDeveloper

11 SQLJ in the Server

Introduction to Server-Side SQLJ

Creating SQLJ Code for Use in 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

Naming of 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

Naming of 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

A Customization and Specialized Customizers

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

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 Translator Options for Profile Customization

JAR Files for Profiles

JAR File Requirements

JAR File Logistics

SQLCheckerCustomizer for Profile Semantics-Checking

Invoking SQLCheckerCustomizer with the Customizer Harness verify Option

SQLCheckerCustomizer Options

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

B SQLJ Error Messages

SQLJ Translation Time Messages

SQLJ Runtime Messages

Index