Oracle9i SQLJ Developer's Guide and Reference

744 Pages·2001·5.283 MB·English

by  Wright B.

Oracle9i
SQLJ Developer’s Guide and Reference
Release 1 (9.0.1)
June 2001
Part No. A90212-01
Oracle9i SQLJ Developer’s Guide and Reference, Release 1 (9.0.1)
Part No. A90212-01
Copyright © 1999, 2001 Oracle Corporation. All rights reserved.
Primary Author: Brian Wright
Contributing Authors: Janice Nygard, Ekkehard Rohwedder
Contributors: Brian Becker, Alan Thiesen, Lei Tang, Julie Basu, Pierre Dufour, Jerry Schwarz, Risto
Lankinen, Cheuk Chau, Vishu Krishnamurthy, Rafiul Ahad, Jack Melnick, Tim Smith, Thomas Pfaeffle,
Tom Portfolio, Ellen Barnes, Susan Kraft, Sheryl Maring, Angie Long
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent, and other intellectual and industrial property
laws. Reverse engineering, disassembly, or decompilation of the Programs is prohibited.
The information contained in this document is subject to change without notice. If you find any problems
in the documentation, please report them to us in writing. Oracle Corporation does not warrant that this
document is error free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR
52.227-19, Commercial Computer Software - Restricted Rights (June, 1987). Oracle Corporation, 500
Oracle Parkway, Redwood City, CA 94065.
The Programs are not intended for use in any nuclear, aviation, mass transit, medical, or other inherently
dangerous applications. It shall be the licensee's responsibility to take all appropriate fail-safe, backup,
redundancy, and other measures to ensure the safe use of such applications if the Programs are used for
such purposes, and Oracle Corporation disclaims liability for any damages caused by such use of the
Programs.
Oracle is a registered trademark, and JDeveloper, Oracle Net, Oracle Objects, Oracle9i, Oracle8i, Oracle8,
Oracle7, Oracle9i Lite, PL/SQL, Pro*C, SQL*Net, and SQL*Plus are trademarks or registered trademarks
of Oracle Corporation. Other names may be trademarks of their respective owners.
Contents
Send Us Your Comments.................................................................................................................. xv
Preface......................................................................................................................................................... xvii
Intended Audience............................................................................................................................. xvii
Structure............................................................................................................................................... xvii
Related Documents.............................................................................................................................. xix
Conventions......................................................................................................................................... xxii
Documentation Accessibility............................................................................................................ xxiii
1 Overview
Introduction to SQLJ.......................................................................................................................... 1-2
Basic Concepts............................................................................................................................... 1-2
Java and SQLJ versus PL/SQL................................................................................................... 1-3
Overview of SQLJ Components...................................................................................................... 1-4
SQLJ Translator and SQLJ Runtime........................................................................................... 1-4
SQLJ Profiles.................................................................................................................................. 1-5
Overview of Oracle Extensions to the SQLJ Standard................................................................ 1-7
Basic Translation Steps and Runtime Processing......................................................................... 1-9
Translation Steps.......................................................................................................................... 1-9
Summary of Translator Input and Output............................................................................. 1-12
Runtime Processing.................................................................................................................... 1-15
Alternative Deployment Scenarios............................................................................................... 1-16
Running SQLJ in Applets.......................................................................................................... 1-16
Introduction to SQLJ in the Server........................................................................................... 1-20
iii
Using SQLJ with Oracle Lite..................................................................................................... 1-22
Alternative Development Scenarios............................................................................................. 1-24
SQLJ Globalization Support...................................................................................................... 1-24
SQLJ in JDeveloper and Other IDEs........................................................................................ 1-24
Windows Considerations.......................................................................................................... 1-25
2 Getting Started
Assumptions and Requirements...................................................................................................... 2-2
Assumptions About Your Environment................................................................................... 2-2
Requirements for Using Oracle SQLJ........................................................................................ 2-3
Supported JDK Versions.............................................................................................................. 2-4
Oracle SQLJ Backwards Compatibility...................................................................................... 2-6
Oracle JVM Configuration........................................................................................................... 2-7
Checking the Installation and Configuration............................................................................... 2-8
Check for Installed Directories and Files.................................................................................. 2-8
Set the Path and Classpath.......................................................................................................... 2-8
Verify Installation of sqljutl Package....................................................................................... 2-10
Testing the Setup............................................................................................................................... 2-11
Set Up the Runtime Connection............................................................................................... 2-11
Create a Table to Verify the Database...................................................................................... 2-13
Verify the JDBC Driver.............................................................................................................. 2-13
Verify the SQLJ Translator and Runtime................................................................................ 2-14
Verify the SQLJ Translator Connection to the Database....................................................... 2-14
3 Basic Language Features
Overview of SQLJ Declarations....................................................................................................... 3-2
Rules for SQLJ Declarations........................................................................................................ 3-2
Iterator Declarations..................................................................................................................... 3-3
Connection Context Declarations............................................................................................... 3-4
Declaration IMPLEMENTS Clause............................................................................................ 3-5
Declaration WITH Clause............................................................................................................ 3-6
Overview of SQLJ Executable Statements..................................................................................... 3-9
Rules for SQLJ Executable Statements....................................................................................... 3-9
SQLJ Clauses................................................................................................................................ 3-10
Specifying Connection Context Instances and Execution Context Instances.................... 3-11
iv
Executable Statement Examples............................................................................................... 3-12
PL/SQL Blocks in Executable Statements............................................................................... 3-14
Java Host Expressions, Context Expressions, and Result Expressions................................... 3-15
Overview of Host Expressions................................................................................................. 3-15
Basic Host Expression Syntax................................................................................................... 3-16
Examples of Host Expressions.................................................................................................. 3-18
Overview of Result Expressions and Context Expressions.................................................. 3-20
Evaluation of Java Expressions at Runtime............................................................................ 3-21
Examples of Evaluation of Java Expressions at Runtime..................................................... 3-23
Restrictions on Host Expressions............................................................................................. 3-32
Single-Row Query Results—SELECT INTO Statements......................................................... 3-33
SELECT INTO Syntax................................................................................................................ 3-33
Examples of SELECT INTO Statements.................................................................................. 3-34
Examples with Host Expressions in SELECT-List................................................................. 3-34
SELECT INTO Error Conditions.............................................................................................. 3-35
Multi-Row Query Results—SQLJ Iterators................................................................................. 3-36
Iterator Concepts........................................................................................................................ 3-36
General Steps in Using an Iterator........................................................................................... 3-40
Named Iterators Versus Positional Iterators Versus Result Set Iterators........................... 3-40
Using Named Iterators.............................................................................................................. 3-42
Using Positional Iterators.......................................................................................................... 3-47
Using Iterators and Result Sets as Host Variables................................................................. 3-51
Using Iterators and Result Sets as Iterator Columns............................................................. 3-54
Assignment Statements (SET)........................................................................................................ 3-57
Stored Procedure and Function Calls........................................................................................... 3-59
Calling Stored Procedures......................................................................................................... 3-59
Calling Stored Functions........................................................................................................... 3-60
Using Iterators and Result Sets as Stored Function Returns................................................ 3-62
4 Key Programming Considerations
Selection of the JDBC Driver........................................................................................................... 4-2
Overview of the Oracle JDBC Drivers....................................................................................... 4-2
Driver Selection for Translation................................................................................................. 4-4
Driver Selection and Registration for Runtime........................................................................ 4-5
Connection Considerations.............................................................................................................. 4-6
v
Single Connection or Multiple Connections Using DefaultContext..................................... 4-6
Closing Connections................................................................................................................... 4-10
Multiple Connections Using Declared Connection Context Classes.................................. 4-12
More About the Oracle Class.................................................................................................... 4-12
More About the DefaultContext Class.................................................................................... 4-14
Connection for Translation........................................................................................................ 4-17
Connection for Customization.................................................................................................. 4-18
Null-Handling................................................................................................................................... 4-19
Wrapper Classes for Null-Handling........................................................................................ 4-19
Examples of Null-Handling...................................................................................................... 4-20
Exception-Handling Basics............................................................................................................. 4-22
SQLJ and JDBC Exception-Handling Requirements............................................................. 4-22
Processing Exceptions................................................................................................................ 4-23
Using SQLException Subclasses............................................................................................... 4-25
Basic Transaction Control................................................................................................................ 4-26
Overview of Transactions.......................................................................................................... 4-26
Automatic Commits versus Manual Commits....................................................................... 4-26
Specifying Auto-Commit as You Define a Connection......................................................... 4-27
Modifying Auto-Commit in an Existing Connection............................................................ 4-28
Using Manual COMMIT and ROLLBACK............................................................................. 4-28
Effect of Commits and Rollbacks on Iterators and Result Sets............................................ 4-29
Summary: First Steps in SQLJ Code............................................................................................. 4-30
Import Required Classes............................................................................................................ 4-30
Register JDBC Drivers and Set Default Connection.............................................................. 4-31
Set Up Exception Handling....................................................................................................... 4-31
Set Up Host Variables, Execute SQLJ Clause, Process Results............................................ 4-32
Example of Single-Row Query using SELECT INTO............................................................ 4-33
Set Up a Named Iterator............................................................................................................ 4-34
Example of Multiple-Row Query Using Named Iterator..................................................... 4-35
Other Programming Considerations............................................................................................. 4-38
Naming Requirements and Restrictions................................................................................. 4-38
Statement Caching Methods..................................................................................................... 4-41
5 Type Support
Supported Types for Host Expressions........................................................................................... 5-2
vi
Summary of Supported Types.................................................................................................... 5-2
Supported Types and Requirements for JDBC 2.0.................................................................. 5-7
Unsupported Types...................................................................................................................... 5-8
Wrapping PL/SQL BOOLEAN, RECORD, and TABLE Types............................................. 5-9
Backwards Compatibility for Previous Oracle JDBC Releases............................................ 5-10
Support for Streams......................................................................................................................... 5-12
General Use of SQLJ Streams.................................................................................................... 5-12
Using SQLJ Streams to Send Data............................................................................................ 5-13
Retrieving Data into Streams—Precautions........................................................................... 5-16
Using SQLJ Streams to Retrieve Data...................................................................................... 5-17
Processing SQLJ Streams........................................................................................................... 5-19
Examples of Retrieving and Processing Stream Data........................................................... 5-20
SQLJ Stream Objects as Output Parameters and Function Return Values........................ 5-22
Stream Class Methods................................................................................................................ 5-24
Support for JDBC 2.0 LOB Types and Oracle Type Extensions............................................... 5-25
Package oracle.sql....................................................................................................................... 5-26
Support for BLOB, CLOB, and BFILE...................................................................................... 5-26
Support for Oracle ROWID....................................................................................................... 5-33
Support for Oracle REF CURSOR Types................................................................................ 5-36
Support for Other Oracle9i Datatypes..................................................................................... 5-38
Extended Support for BigDecimal........................................................................................... 5-38
6 Objects and Collections
Oracle Objects and Collections........................................................................................................ 6-2
Introduction to Objects and Collections.................................................................................... 6-2
Oracle Object Fundamentals....................................................................................................... 6-4
Oracle Collection Fundamentals................................................................................................ 6-4
Object and Collection Datatypes................................................................................................ 6-5
Custom Java Classes........................................................................................................................... 6-6
Custom Java Class Interface Specifications.............................................................................. 6-6
Custom Java Class Support for Object Methods.................................................................... 6-10
Custom Java Class Requirements............................................................................................ 6-11
Compiling Custom Java Classes............................................................................................... 6-17
Reading and Writing Custom Data......................................................................................... 6-18
Additional Uses for ORAData Implementations................................................................... 6-18
vii
User-Defined Types.......................................................................................................................... 6-20
Creating Object Types................................................................................................................ 6-20
Creating Collection Types......................................................................................................... 6-22
JPublisher and the Creation of Custom Java Classes................................................................ 6-25
What JPublisher Produces......................................................................................................... 6-25
Generating Custom Java Classes.............................................................................................. 6-29
JPublisher INPUT Files and Properties Files.......................................................................... 6-39
Creating Custom Java Classes and Specifying Member Names.......................................... 6-41
JPublisher Implementation of Wrapper Methods.................................................................. 6-42
JPublisher Custom Java Class Examples................................................................................. 6-43
Extending Classes Generated by JPublisher........................................................................... 6-47
Strongly Typed Objects and References in SQLJ Executable Statements............................. 6-53
Selecting Objects and Object References into Iterator Columns.......................................... 6-54
Updating an Object..................................................................................................................... 6-55
Inserting an Object Created from Individual Object Attributes.......................................... 6-57
Updating an Object Reference.................................................................................................. 6-57
Strongly Typed Collections in SQLJ Executable Statements................................................... 6-59
Accessing Nested Tables—TABLE syntax and CURSOR syntax........................................ 6-60
Inserting a Row that Includes a Nested Table........................................................................ 6-60
Selecting a Nested Table into a Host Expression................................................................... 6-61
Manipulating a Nested Table Using TABLE Syntax............................................................. 6-63
Selecting Data from a Nested Table Using a Nested Iterator............................................... 6-64
Selecting a VARRAY into a Host Expression......................................................................... 6-66
Inserting a Row that Includes a VARRAY.............................................................................. 6-67
Serialized Java Objects.................................................................................................................... 6-68
Serializing Java Classes to RAW and BLOB Columns.......................................................... 6-68
SerializableDatum—An ORAData Implementation............................................................. 6-71
SerializableDatum in SQLJ Applications................................................................................ 6-74
SerializableDatum (Complete Class)....................................................................................... 6-75
Weakly Typed Objects, References, and Collections................................................................. 6-77
Support for Weakly Typed Objects, References, and Collections....................................... 6-77
Restrictions on Weakly Typed Objects, References, and Collections................................. 6-78
7 Advanced Language Features
Connection Contexts.......................................................................................................................... 7-2
viii
Connection Context Concepts.................................................................................................... 7-2
Connection Context Logistics..................................................................................................... 7-4
More About Declaring and Using a Connection Context Class............................................ 7-5
Example of Multiple Connection Contexts............................................................................... 7-7
Implementation and Functionality of Connection Context Classes...................................... 7-9
Use of the IMPLEMENTS Clause in Connection Context Declarations............................. 7-11
Semantics-Checking of Your Connection Context Usage..................................................... 7-12
Data Source Support.................................................................................................................. 7-13
Execution Contexts........................................................................................................................... 7-16
Relation of Execution Contexts to Connection Contexts...................................................... 7-16
Creating and Specifying Execution Context Instances......................................................... 7-17
Execution Context Synchronization......................................................................................... 7-18
ExecutionContext Methods....................................................................................................... 7-19
Relation of Execution Contexts to Multithreading................................................................ 7-23
Multithreading in SQLJ................................................................................................................... 7-25
Iterator Class Implementation and Advanced Functionality................................................... 7-27
Implementation and Functionality of Iterator Classes.......................................................... 7-27
Use of the IMPLEMENTS Clause in Iterator Declarations................................................... 7-28
Support for Subclassing of Iterator Classes............................................................................ 7-29
Result Set Iterators...................................................................................................................... 7-30
Scrollable Iterators...................................................................................................................... 7-30
Advanced Transaction Control....................................................................................................... 7-38
SET TRANSACTION Syntax.................................................................................................... 7-38
Access Mode Settings................................................................................................................. 7-39
Isolation Level Settings.............................................................................................................. 7-39
Using JDBC Connection Class Methods................................................................................. 7-40
SQLJ and JDBC Interoperability................................................................................................... 7-42
SQLJ Connection Context and JDBC Connection Interoperability..................................... 7-42
SQLJ Iterator and JDBC Result Set Interoperability.............................................................. 7-47
Support for Dynamic SQL.............................................................................................................. 7-52
Meta Bind Expressions............................................................................................................... 7-52
SQLJ Dynamic SQL Examples.................................................................................................. 7-55
8 Translator Command Line and Options
Translator Command Line and Properties Files........................................................................... 8-2
ix
SQLJ Options, Flags, and Prefixes.............................................................................................. 8-3
Command-Line Syntax and Operations.................................................................................. 8-10
Properties Files for Option Settings......................................................................................... 8-14
SQLJ_OPTIONS Environment Variable for Option Settings............................................... 8-18
Order of Precedence of Option Settings.................................................................................. 8-18
Basic Translator Options.................................................................................................................. 8-20
Basic Options for Command Line Only.................................................................................. 8-20
Options for Output Files and Directories................................................................................ 8-27
Connection Options.................................................................................................................... 8-31
Reporting and Line-Mapping Options.................................................................................... 8-42
Options for Code Generation, Column Optimizations, and Parameter Optimizations... 8-49
Advanced Translator Options......................................................................................................... 8-56
Prefixes that Pass Option Settings to Other Executables...................................................... 8-56
Flags for Special Processing...................................................................................................... 8-61
Semantics-Checking Options.................................................................................................... 8-66
Translator Support and Options for Alternative Environments.............................................. 8-72
Java and Compiler Options....................................................................................................... 8-72
Customization Options.............................................................................................................. 8-80
9 Translator and Runtime Functionality
Internal Translator Operations......................................................................................................... 9-2
Code-Parsing and Syntax-Checking.......................................................................................... 9-2
Semantics-Checking..................................................................................................................... 9-2
Code Generation........................................................................................................................... 9-5
Java Compilation.......................................................................................................................... 9-9
Profile Customization................................................................................................................ 9-11
Functionality of Translator Errors, Messages, and Exit Codes................................................. 9-13
Translator Error, Warning, and Information Messages........................................................ 9-13
Translator Status Messages....................................................................................................... 9-16
Translator Exit Codes................................................................................................................. 9-16
SQLJ Runtime.................................................................................................................................... 9-17
Runtime Packages....................................................................................................................... 9-18
Categories of Runtime Errors.................................................................................................... 9-19
Globalization Support in the Translator and Runtime............................................................. 9-21
Character Encoding and Language Support.......................................................................... 9-21
x