Oracle Database SQLJ Developer's Guide and Reference, 10g Release 2 (10.2)

10g Release 2 (10.2)

B16018-02

Contents

List of Examples

List of Tables

Title and Copyright Information

Preface

- Audience

- Documentation Accessibility

- Related Documents

- Conventions

1 Overview

- Introduction to SQLJ

- Basic Concepts

- Oracle-Specific Code Generation Versus ISO Standard Code Generation

- Overview of SQLJ Components

- SQLJ Translator and SQLJ Run Time

- SQLJ Profiles (ISO Standard Code)

- Overview of Profiles

- Binary Portability

- Overview of Oracle Extensions to the SQLJ Standard

- SQLJ Type Extensions

- SQLJ Functionality Extensions

- Basic Translation Steps and Run-Time Processing

- SQLJ Translation Steps

- Summary of Translator Input and Output

- Translator Input

- Translator Output

- Output File Locations

- SQLJ Run-Time Processing

- Processing for Oracle-Specific Generated Code

- Processing for ISO Standard Generated Code

- JDBC Versus SQLJ Sample Code

- JDBC Version of the Sample Code

- SQLJ Version of the Sample Code

- Alternative Deployment Scenarios

- Running SQLJ in Applets

- General Development and Deployment Considerations

- General End User Considerations

- Java Environment and the Java Plug-In

- Introduction to SQLJ in the Server

- Alternative Development Scenarios

- SQLJ Globalization Support

- SQLJ in Oracle JDeveloper 10g and Other IDEs

- Windows Considerations

2 Getting Started

- Assumptions and Requirements

- Assumptions About Your Environment

- Requirements for Using the Oracle SQLJ Implementation

- SQLJ Environment: Key Scenarios and Guidelines

- Environment Considerations

- SQLJ Backward Compatibility

- Checking the Installation and Configuration

- Check for Availability of SQLJ and Demo Applications

- Check for Installed Directories and Files

- Set the Path and Classpath

- Verify Installation of the sqljutl Package

- Testing the Setup

- Set Up the Run Time Connection

- Create a Table to Verify the Database

- Verify the JDBC Driver

- Verify the SQLJ Translator and Run Time

- Verify the SQLJ Translator Connection to the Database

3 Key Programming Considerations

- Selection of the JDBC Driver

- Overview of the Oracle JDBC Drivers

- Driver Selection for Translation

- Driver Selection and Registration for Run Time

- 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

- 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

4 Basic Language Features

- Overview of SQLJ Declarations

- Rules for SQLJ Declarations

- Iterator Declarations

- Connection Context Declarations

- Declaration IMPLEMENTS Clause

- Declaration WITH Clause

- Standard WITH Clause Usage

- Oracle-Specific WITH Clause Usage

- Example: Returnability

- 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, Context, 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 Run Time

- Examples of Evaluation of Java Expressions at Run Time (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

- Multirow Query Results: SQLJ Iterators

- Iterator Concepts

- Introduction to Strongly Typed Iterators

- Introduction to Weakly Typed Iterators

- General Steps in Using an Iterator

- Named, Positional, and 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

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 Oracle Database 11g Data Types

- 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 Data Types

- 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

- 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

- Status Methods

- Control Methods

- Cancellation Method

- Update Batching Methods

- Savepoint Methods

- Close Method

- Example: Using ExecutionContext 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 Extending 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 DMS

- 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 Run Time 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 Run Time

- SQLJ Run Time Packages

- Categories of Run-Time Errors

- Globalization Support in the Translator and Run Time

- Character Encoding and Language Support

- SQLJ and Java Settings for Character Encoding and Language Support

- 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 Oracle10g JDeveloper

- SQLJ Support for Oracle Performance Monitoring

- Overview of SQLJ DMS Support

- Summary of SQLJ Command-Line Options for DMS

- SQLJ Run Time Commands and Properties File Settings for DMS

- SQLJ DMS Sensors and Metrics

- SQLJ DMS Examples

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 Server-Side 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

- SQLJ Executable Statement

- Corresponding SQLJ 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 Run Time

- Customization Options and Choosing a Customizer

- Overview of Customizer Harness Options

- Syntax for Customizer Harness Options

- Options Supported by the Customizer Harness

- General Customizer Harness Options

- Profile Backup Option (backup)

- Customization Connection Context Option (context)

- Customizer Option (customizer)

- Customization JAR File Digests Option (digests)

- Customization Help Option (help)

- Customization Verbose Option (verbose)

- Customizer Harness Options for Connections

- Customization User Option (user)

- Customization Password Option (password)

- Customization URL Option (url)

- Customization JDBC Driver Option (driver)

- Customizer Harness Options that Invoke Specialized Customizers

- Specialized Customizer: Profile Debug Option (debug)

- Specialized Customizer: Profile Print Option (print)

- Specialized Customizer: Profile Semantics-Checking Option (verify)

- Overview of Customizer-Specific Options

- Oracle Customizer Options

- Options Supported by the Oracle Customizer

- Oracle Customizer Version Compatibility Option (compat)

- Oracle Customizer Force Option (force)

- Oracle Customizer Column Definition Option (optcols)

- Oracle Customizer Parameter Definition Option (optparams)

- Oracle Customizer Parameter Default Size Option (optparamdefaults)

- Oracle Customizer CHAR Comparisons with Blank Padding (fixedchar)

- Oracle Customizer Show-SQL Option (showSQL)

- Oracle Customizer Statement Cache Size Option (stmtcache)

- Oracle Customizer Summary Option (summary)

- 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

- Command-line syntax

- Command-line example

- Properties file syntax

- Properties file example

- Default value

- SQLCheckerCustomizer Options

- SQLCheckerCustomizer Semantics-Checker Option (checker)

- SQLCheckerCustomizer Warnings Option (warn)

- AuditorInstaller Customizer for Debugging

- Overview of Auditors and Code Layers

- Invoking AuditorInstaller with the Customizer Harness debug Option

- Command-line syntax

- Command-line example

- Properties file syntax

- Properties file example

- Default value

- AuditorInstaller Run Time Output

- AuditorInstaller Options

- AuditorInstaller Depth Option (depth)

- AuditorInstaller Log File Option (log)

- AuditorInstaller Prefix Option (prefix)

- AuditorInstaller Return Arguments Option (showReturns)

- AuditorInstaller Thread Names Option (showThreads)

- AuditorInstaller Uninstall Option (uninstall)

- Full Command-Line Examples

Index

List of Examples

- 5-1 Selecting LONG Data into CharacterStream Column of Named Iterator

- 5-2 : Selecting LONG RAW Data into BinaryStream Column of Positional Iterator

- 5-3 Use of oracle.sql.BFILE File-Handling Methods with BFILE

- 5-4 Use of DBMS_LOB File-Handling Routines with BFILE

- 5-5 Example: Use of oracle.sql.CLOB Read Methods with CLOB

- 5-6 Example: Use of DBMS_LOB Read Routines with CLOB

- 5-7 Example: Use of oracle.sql.BLOB Write Routines with BLOB

- 5-8 Example: Use of DBMS_LOB Write Routines with BLOB