Oracle8i SQLJ Developer's Guide and Reference

Introduction to SQLJ

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

Runtime Processing

Summary of Translator Input and Output

Alternative Deployment Scenarios

Running SQLJ in Applets

Introduction to SQLJ in the Server

Using SQLJ with an Oracle Lite Database

Alternative Development Scenarios

SQLJ NLS Support

SQLJ in JDeveloper and Other IDEs

Windows Considerations

Assumptions and Requirements

Assumptions About Your Environment

Requirements for Using Oracle SQLJ

JServer 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

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 Expression

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

Examples of SELECT INTO Statements

Examples with Host Expressions in SELECT-List

Multi-Row Query Results--SQLJ Iterators

Iterator Concepts

General Steps in Using an Iterator

Named Iterators vs. Positional 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

Naming Requirements and Restrictions

Java Namespace--Local Variable and Class Naming Restrictions

SQLJ Namespace

SQL Namespace

File Name Requirements and Restrictions

Selection of the JDBC Driver

About Oracle JDBC Drivers

Driver Selection for Translation

Driver Selection and Registration for Runtime

Connection Considerations

Single Connection or Multiple Connections Using DefaultContext

Multiple Connections Using Declared Connection Context Classes

More About the Oracle Class

More About the DefaultContext Class

Connection for Translation and Runtime

Null-Handling

Wrapper Classes for Null-Handling

Examples of Null-Handling

Exception-Handling Basics

SQLJ/JDBC Exception-Handling Requirements

Processing Exceptions

Using SQLException Subclasses

Basic Transaction Control

About Transactions

Automatic Commits vs. Manual Commits

Specifying Auto-Commit As You Define a Connection

Modifying Auto-Commit in an Existing Connection

Using Manual COMMIT and ROLLBACK

Effect of Commit and Rollback 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 Named Iterator

Example of Multiple-Row Query Using Named Iterator

Supported Types for Host Expressions

Supported Types for Oracle8i

Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types

Backwards Compatibility for Oracle 8.0.5 and 7.3.4

Support for Streams

General Use of SQLJ Streams

Using SQLJ Streams to Send Data to the Database

Retrieving Data into Streams--Precautions

Using SQLJ Streams to Retrieve Data from the Database

Processing SQLJ Streams

Examples of Retrieving and Processing Stream Data

SQLJ Stream Objects as Output Parameters and Function Return Values

Stream Class Methods

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 Oracle8i Datatypes

Extended Support for BigDecimal

Introduction

About Oracle Objects and Collections

Oracle Object Fundamentals

Oracle Collection Fundamentals

Object and Collection Datatypes

About Custom Java Classes and the CustomDatum Interface

CustomDatum and CustomDatumFactory Specifications

Custom Java Class Support for Object Methods

Custom Java Class Requirements

Compiling Custom Java Classes

Reading and Writing Custom Data

Additional Uses for Custom Java Classes

User-Defined Types in the Database

Creating Object Types

Creating Collection Types

JPublisher and the Creation of Custom Java Classes

What JPublisher Produces

Generating Custom Java Classes

Using 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 or Wrapping 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

Serializing Java Objects through Custom Java Classes

SerializableDatum (Step by Step Example)

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

Connection Contexts

Connection Context Concepts

When to Declare Connection Contexts

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

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

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

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

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

Required Compiler Behavior

Java and Compiler Options

Customization Options

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

NLS Support in the Translator and Runtime

Character Encoding and Language Support

SQLJ and Java Settings for Character Encoding and Language Support

NLS Manipulation Outside of SQLJ

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

General Customizer Harness Options

Customizer-Specific Options (Oracle Customizer)

SQLJ Options for Profile Customization

Use of .jar Files for Profiles

.jar File Requirements

.jar File Results

Introduction

Creating SQLJ Code for Use in the Server

Database Connections in the Server

Coding Issues Relating to the JDBC Server-Side Driver

Server-Side Default Output Device

Translating SQLJ Source on Client and Loading Components

Loading Classes and Resources into the Server

Naming of Class and Resource Schema Objects

Additional Steps After Loading Class and Resource Files

Steps in 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

Generated Output from loadjava and the Server Embedded Translator

Error Output from the Server Embedded Translator

Additional Steps After Loading Source Files

Checking Java Uploads

Full Names vs. Short Names in the Server

Using the USER_OBJECTS View

About Class Loading, Compiling, and Name Resolution

SQL Names vs. Java Names

Java Name Resolution

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

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 CustomDatum 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 CustomDatum--BetterDate.java

Advanced Samples

REF CURSOR--RefCursDemo.sqlj

Multithreading--MultiThreadDemo.sqlj

Interoperability with JDBC--JDBCInteropDemo.sqlj

Multiple Connection Schemas--MultiSchemaDemo.sqlj

Data Manipulation and Multiple Connection Contexts--QueryDemo.sqlj

Prefetch Demo--PrefetchDemo.sqlj

Server-Side Samples

SQLJ in the Server--ServerDemo.sqlj

JDBC versus SQLJ Sample Code

JDBC Version of the Sample Code

SQLJ Version of the Sample Code

Performance Enhancements

Row Prefetching

Oracle SQL Optimizer

AuditorInstaller Customizer for Debugging

About Auditors and Code Layers

Invoking AuditorInstaller with the Customizer Harness -debug Option

AuditorInstaller Options

Full Command-Line Examples

AuditorInstaller Runtime Output

Additional SQLJ Debugging Considerations

SQLJ -linemap Flag

Server-Side debug Option

Developing and Debugging in JDeveloper

Translation Time Messages

Runtime Messages

Contents

Title and Copyright Information

Send Us Your Comments

Preface

1 Overview

2 Getting Started

3 Basic Language Features

4 Key Programming Considerations

5 Type Support

6 Objects and Collections

7 Advanced Language Features

8 Translator Command Line and Options

9 Translator and Runtime Functionality

10 Profiles and Customization

11 SQLJ in the Server

12 Sample Applications

A Performance and Debugging

B SQLJ Error Messages