Skip Headers
Oracle® Database SQLJ Developer's Guide and Reference
11g Release 1 (11.1)
Part Number B31227-03
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in 11g Release 1 (11.1)
Support for JDK 1.5 and JDK 1.6
Enhanced Profile Print Option
Outline Generation
Support for Global Transactions
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 10
g
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 11
g
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
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
SQLJ Support for Global Transactions
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
Execution Plan Fixing
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 Oracle10
g
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