1/20

Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Related Documents
• Conventions

Changes in This Release for Oracle Database SQLJ Developer's Guide

• Changes in Oracle Database 12c Release 1 (12.1)

1 Getting Started

• Assumptions and Requirements
  • Assumptions About Your Environment
  • Requirements for Using the Oracle SQLJ Implementation
  • SQLJ Environment
  • 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

2 Introduction to SQLJ

• Overview of SQLJ
• Overview of SQLJ Components
  • SQLJ Translator
  • SQLJ Run Time
• 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
• SQLJ Sample Code
  • SQLJ Version of the Sample Code
  • JDBC 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
  • Overview of SQLJ in the Server
• Alternative Development Scenarios
  • SQLJ Globalization Support
  • SQLJ in Oracle JDeveloper 10g and Other IDEs
  • Windows Considerations

3 Key Programming Considerations

• Selection of the JDBC Driver
  • Overview of 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)
  • 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
  • Advantages and Disadvantages of Oracle-Specific Code Generation
• ISO Standard Code Generation
  • Environment Requirements for ISO Standard Code Generation
  • SQLJ Translator and SQLJ Run Time
  • SQLJ Profiles
    • Overview of Profiles
    • Binary Portability
  • SQLJ Translation Steps
  • Summary of Translator Input and Output
    • Translator Input
    • Translator Output
    • Output File Locations
  • SQLJ Run-Time Processing
  • Deployment Scenarios
• Oracle-Specific Code Generation Versus ISO Standard 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
    • Overview of Strongly Typed Iterators
    • Overview of 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
• 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
  • Overview of 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
  • 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
  • SQLJ Support for Global Transactions
  • Connecting to PDBs
• 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
• Using Stored Outlines
• Using Plan Baselines
  • Generating Plan Baselines
  • Command-Line and Property File Options
    • plan_baseline
    • plan_prefix
    • plan_run
    • plan_fixed
    • plan_enabled
  • Generated SQL File
    • Generated SQL File Name
    • Generated SQL File Format
  • Generated Log File
    • Generated Log File Name
    • Generated Log File Format
  • Generated Java File

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
  • Overview of the AuditorInstaller Specialized Customizer
  • Overview of 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

• Overview of 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 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
  • 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
  • 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