Oracle Database Application Developer’s Guide Fundamentals Developer 10g Release 2

Application%20Developer's%20Guide%20%E2%80%93%20Fundamentals

User Manual: Pdf

Open the PDF directly: View PDF PDF.
Page Count: 440 [warning: Documents this large are best viewed by clicking the View PDF Link!]

Oracle® Database
Application Developer's Guide - Fundamentals
10g Release 2 (10.2)
B14251-01
November 2005
Oracle Database Application Developer’s Guide - Fundamentals, 10g Release 2 (10.2)
B14251-01
Copyright © 1996, 2005, Oracle. All rights reserved.
Primary Author: Lance Ashdown
Contributing Authors: D. Adams, M. Cowan, R. Moran, J. Melnick, E. Paapanen, J. Russell, R. Strohm
Contributors: D. Alpern, G. Arora, C. Barclay, D. Bronnikov, T. Chang, M. Davidson, G. Doherty, D. Elson,
A. Ganesh, M. Hartstein, J. Huang, N. Jain, R. Jenkins Jr., S. Kotsovolos, S. Kumar, C. Lei, D. Lorentz, V.
Moore, J. Muller, R. Murthy, R. Pang, B. Sinha, S. Vemuri, W. Wang, D. Wong, A. Yalamanchi, Q. Yu
The Programs (which include both the software and documentation) contain proprietary information; 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, except to the extent required to obtain interoperability with other
independently created software or as specified by law, 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. This document is not warranted to be 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.
If the Programs are delivered to the United States Government or anyone licensing or using the Programs on
behalf of the United States Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data
delivered to U.S. Government customers are "commercial computer software" or "commercial technical data"
pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As
such, use, duplication, disclosure, modification, and adaptation of the Programs, including documentation
and technical data, shall be subject to the licensing restrictions set forth in the applicable Oracle license
agreement, and, to the extent applicable, the additional rights set forth 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 we disclaim liability for any damages caused by such use of the Programs.
Oracle, JD Edwards, PeopleSoft, and Retek are registered trademarks of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
The Programs may provide links to Web sites and access to content, products, and services from third
parties. Oracle is not responsible for the availability of, or any content provided on, third-party Web sites.
You bear all risks associated with the use of such content. If you choose to purchase any products or services
from a third party, the relationship is directly between you and the third party. Oracle is not responsible for:
(a) the quality of third-party products or services; or (b) fulfilling any of the terms of the agreement with the
third party, including delivery of products or services and warranty obligations related to purchased
products or services. Oracle is not responsible for any loss or damage of any sort that you may incur from
dealing with any third party.
iii
Contents
Preface ............................................................................................................................................................... xix
Audience..................................................................................................................................................... xix
Documentation Accessibility................................................................................................................... xix
Related Documents ................................................................................................................................... xx
Conventions ............................................................................................................................................... xxi
What's New in Application Development? ................................................................................ xxiii
Oracle Database 10g Release 2 (10.2) New Features........................................................................... xxiii
Oracle Database 10g Release 1 (10.1) New Features........................................................................... xxiv
1 Orientation to Oracle Programmatic Environments
Overview of Oracle Application Development ................................................................................. 1-1
Overview of PL/SQL................................................................................................................................ 1-2
What Is PL/SQL? ............................................................................................................................... 1-2
Advantages of PL/SQL..................................................................................................................... 1-3
Integration with Oracle Database............................................................................................. 1-4
High Performance....................................................................................................................... 1-4
High Productivity ....................................................................................................................... 1-4
Scalability ..................................................................................................................................... 1-4
Manageability.............................................................................................................................. 1-5
Object-Oriented Programming Support.................................................................................. 1-5
Portability..................................................................................................................................... 1-5
Security......................................................................................................................................... 1-5
Built-In Packages......................................................................................................................... 1-6
PL/SQL Web Development Tools................................................................................................... 1-6
Overview of Java Support Built Into the Database ........................................................................... 1-6
Overview of Oracle JVM................................................................................................................... 1-7
Overview of Oracle Extensions to JDBC......................................................................................... 1-8
JDBC Thin Driver........................................................................................................................ 1-8
JDBC OCI Driver......................................................................................................................... 1-8
JDBC Server-Side Internal Driver............................................................................................. 1-9
Oracle Database Extensions to JDBC Standards .................................................................... 1-9
Sample JDBC 2.0 Program ......................................................................................................... 1-9
Sample Pre-2.0 JDBC Program............................................................................................... 1-10
JDBC in SQLJ Applications..................................................................................................... 1-10
iv
Overview of Oracle SQLJ............................................................................................................... 1-11
Benefits of SQLJ........................................................................................................................ 1-12
Comparing SQLJ with JDBC .................................................................................................. 1-12
SQLJ Stored Procedures in the Server................................................................................... 1-13
Overview of Oracle JPublisher...................................................................................................... 1-13
Overview of Java Stored Procedures............................................................................................ 1-13
Overview of Oracle Database Web Services ............................................................................... 1-14
Oracle Database as a Web Service Provider ........................................................................ 1-14
Overview of Writing Procedures and Functions in Java........................................................... 1-15
Overview of Writing Database Triggers in Java ................................................................. 1-15
Why Use Java for Stored Procedures and Triggers?........................................................... 1-15
Overview of Pro*C/C++ ....................................................................................................................... 1-15
Implementing a Pro*C/C++ Application.................................................................................... 1-16
Highlights of Pro*C/C++ Features............................................................................................... 1-17
Overview of Pro*COBOL .................................................................................................................... 1-18
How You Implement a Pro*COBOL Application ...................................................................... 1-18
Highlights of Pro*COBOL Features ............................................................................................. 1-19
Overview of OCI and OCCI................................................................................................................ 1-19
Advantages of OCI.......................................................................................................................... 1-20
Parts of the OCI ............................................................................................................................... 1-20
Procedural and Non-Procedural Elements ................................................................................. 1-21
Building an OCI Application......................................................................................................... 1-21
Overview of Oracle Data Provider for .NET (ODP.NET) .............................................................. 1-22
Using ODP.NET in a Simple Application ................................................................................... 1-22
Overview of Oracle Objects for OLE (OO4O)................................................................................. 1-23
OO4O Automation Server ............................................................................................................. 1-24
OO4O Object Model ....................................................................................................................... 1-25
OraSession................................................................................................................................. 1-25
OraServer .................................................................................................................................. 1-25
OraDatabase.............................................................................................................................. 1-26
OraDynaset ............................................................................................................................... 1-26
OraField..................................................................................................................................... 1-26
OraMetaData and OraMDAttribute...................................................................................... 1-27
OraParameters and OraParameter........................................................................................ 1-27
OraParamArray........................................................................................................................ 1-27
OraSQLStmt.............................................................................................................................. 1-27
OraAQ ....................................................................................................................................... 1-27
OraAQMsg................................................................................................................................ 1-28
OraAQAgent............................................................................................................................. 1-28
Support for Oracle LOB and Object Datatypes........................................................................... 1-28
OraBLOB and OraCLOB......................................................................................................... 1-29
OraBFILE................................................................................................................................... 1-29
Oracle Data Control........................................................................................................................ 1-29
Oracle Objects for OLE C++ Class Library.................................................................................. 1-29
Additional Sources of Information............................................................................................... 1-29
Choosing a Programming Environment ........................................................................................... 1-30
Choosing Whether to Use OCI or a Precompiler ....................................................................... 1-30
v
Using Built-In Packages and Libraries......................................................................................... 1-30
Java Compared to PL/SQL............................................................................................................ 1-31
PL/SQL Is Optimized for Database Access ......................................................................... 1-31
PL/SQL Is Integrated with the Database............................................................................. 1-31
Both Java and PL/SQL Have Object-Oriented Features.................................................... 1-31
Java Is Used for Open Distributed Applications................................................................. 1-32
Part I SQL for Application Developers
2 SQL Processing for Application Developers
Grouping Operations into Transactions .............................................................................................. 2-1
Deciding How to Group Operations in Transactions................................................................... 2-1
Improving Transaction Performance ............................................................................................. 2-2
Committing Transactions.................................................................................................................. 2-2
Managing Commit Redo Behavior........................................................................................... 2-3
Rolling Back Transactions ................................................................................................................ 2-4
Defining Transaction Savepoints .................................................................................................... 2-5
An Example of COMMIT, SAVEPOINT, and ROLLBACK ................................................. 2-5
Ensuring Repeatable Reads with Read-Only Transactions ............................................................ 2-5
Using Cursors within Applications ...................................................................................................... 2-6
Declaring and Opening Cursors ..................................................................................................... 2-6
Using a Cursor to Execute Statements Again ................................................................................ 2-7
Closing Cursors ................................................................................................................................. 2-7
Cancelling Cursors ............................................................................................................................ 2-8
Locking Data Explicitly .......................................................................................................................... 2-8
Choosing a Locking Strategy ........................................................................................................... 2-9
When to Lock with ROW SHARE and ROW EXCLUSIVE Mode....................................... 2-9
When to Lock with SHARE Mode............................................................................................ 2-9
When to Lock with SHARE ROW EXCLUSIVE Mode....................................................... 2-10
When to Lock in EXCLUSIVE Mode..................................................................................... 2-11
Privileges Required ................................................................................................................. 2-11
Letting Oracle Database Control Table Locking......................................................................... 2-11
Explicitly Acquiring Row Locks .................................................................................................. 2-12
About User Locks .................................................................................................................................. 2-13
When to Use User Locks ................................................................................................................ 2-13
Example of a User Lock.................................................................................................................. 2-13
Viewing and Monitoring Locks .................................................................................................... 2-14
Using Serializable Transactions for Concurrency Control ........................................................... 2-14
How Serializable Transactions Interact ....................................................................................... 2-15
Setting the Isolation Level of a Transaction ................................................................................ 2-16
The INITRANS Parameter ..................................................................................................... 2-16
Referential Integrity and Serializable Transactions .................................................................. 2-17
Using SELECT FOR UPDATE................................................................................................ 2-18
READ COMMITTED and SERIALIZABLE Isolation ............................................................... 2-18
Transaction Set Consistency .................................................................................................. 2-18
Comparison of READ COMMITTED and SERIALIZABLE Transactions....................... 2-19
vi
Choosing an Isolation Level for Transactions...................................................................... 2-20
Application Tips for Transactions ................................................................................................ 2-20
Autonomous Transactions ................................................................................................................... 2-20
Examples of Autonomous Transactions...................................................................................... 2-22
Entering a Buy Order .............................................................................................................. 2-23
Example: Making a Bank Withdrawal.................................................................................. 2-23
Defining Autonomous Transactions ............................................................................................ 2-26
Restrictions on Autonomous Transactions.................................................................................. 2-27
Resuming Execution After a Storage Error Condition................................................................... 2-27
What Operations Can Be Resumed After an Error Condition?................................................ 2-27
Limitations on Resuming Operations After an Error Condition ............................................. 2-27
Writing an Application to Handle Suspended Storage Allocation.......................................... 2-28
Example of Resumable Storage Allocation ................................................................................. 2-28
3 Using SQL Datatypes in Application Development
Representing Data with SQL Datatypes: Overview.......................................................................... 3-1
Representing Character Data ................................................................................................................. 3-2
Representing Character Data: Overview........................................................................................ 3-2
Specifying Column Lengths as Bytes or Characters ..................................................................... 3-3
Choosing Between the CHAR and VARCHAR2 Datatypes........................................................ 3-3
Using Character Literals in SQL Statements.................................................................................. 3-4
Quoting Character Literals........................................................................................................ 3-5
Representing Numeric Data................................................................................................................... 3-5
What Are the Numeric Datatypes? ................................................................................................. 3-5
Using Floating-Point Number Formats .......................................................................................... 3-6
Using a Floating-Point Binary Format..................................................................................... 3-7
Representing Special Values with Native Floating-Point Formats ..................................... 3-8
Using Comparison Operators for Native Floating-Point Datatypes.......................................... 3-9
Performing Arithmetic Operations with Native Floating-Point Datatypes ........................... 3-10
Using Conversion Functions with Native Floating-Point Datatypes...................................... 3-10
Client Interfaces for Native Floating-Point Datatypes .............................................................. 3-11
OCI Native Floating-Point Datatypes SQLT_BFLOAT and SQLT_BDOUBLE.............. 3-11
Native Floating-Point Datatypes Supported in Oracle OBJECT Types........................... 3-12
Pro*C/C++ Support for Native Floating-Point Datatypes................................................ 3-12
Representing Datetime Data............................................................................................................... 3-12
Representing Datetime Data: Overview...................................................................................... 3-12
Using the DATE Datatype...................................................................................................... 3-12
Using the TIMESTAMP Datatype ......................................................................................... 3-12
Using the TIMESTAMP WITH TIME ZONE Datatype...................................................... 3-12
Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype....................................... 3-13
Representing the Difference Between Datetime Values..................................................... 3-13
Manipulating the Date Format ..................................................................................................... 3-13
Changing the Default Date Format ....................................................................................... 3-13
Displaying the Current Date and Time ................................................................................ 3-14
Manipulating the Time Format .................................................................................................... 3-14
Performing Date Arithmetic.......................................................................................................... 3-14
Converting Between Datetime Types .......................................................................................... 3-15
vii
Importing and Exporting Datetime Types .................................................................................. 3-15
Representing Specialized Data........................................................................................................... 3-16
Representing Geographic Data ..................................................................................................... 3-16
Representing Multimedia Data..................................................................................................... 3-16
Representing Large Amounts of Data.......................................................................................... 3-16
Using RAW and LONG RAW Datatypes............................................................................. 3-17
Representing Searchable Text ....................................................................................................... 3-18
Representing XML .......................................................................................................................... 3-18
Representing Dynamically Typed Data....................................................................................... 3-19
Representing Data with ANSI/ISO, DB2, and SQL/DS Datatypes ....................................... 3-21
Representing Conditional Expressions as Data .............................................................................. 3-22
Identifying Rows by Address ............................................................................................................ 3-23
Querying the ROWID Pseudocolumn ......................................................................................... 3-24
Accessing the ROWID Datatype................................................................................................... 3-24
Restricted ROWID ................................................................................................................... 3-24
Extended ROWID .................................................................................................................... 3-24
External Binary ROWID.......................................................................................................... 3-25
Accessing the UROWID Datatype................................................................................................ 3-25
How Oracle Database Converts Datatypes ..................................................................................... 3-25
Datatype Conversion During Assignments................................................................................ 3-25
Datatype Conversion During Expression Evaluation .............................................................. 3-26
4 Using Regular Expressions in Oracle Database
Using Regular Expressions with Oracle Database: Overview ........................................................ 4-1
What Are Regular Expressions?....................................................................................................... 4-1
How Are Oracle Database Regular Expressions Useful?............................................................. 4-2
Oracle Database Implementation of Regular Expressions........................................................... 4-2
Oracle Database Support for the POSIX Regular Expression Standard..................................... 4-3
Regular Expression Metacharacters in Oracle Database .................................................................. 4-4
POSIX Metacharacters in Oracle Database Regular Expressions................................................ 4-4
Regular Expression Operator Multilingual Enhancements......................................................... 4-6
Perl-Influenced Extensions in Oracle Regular Expressions......................................................... 4-7
Using Regular Expressions in SQL Statements: Scenarios .............................................................. 4-9
Using an Integrity Constraint to Enforce a Phone Number Format........................................... 4-9
Using Back References to Reposition Characters....................................................................... 4-10
5 Using Indexes in Application Development
Guidelines for Application-Specific Indexes ..................................................................................... 5-1
Create Indexes After Inserting Table Data .................................................................................... 5-2
Switch Your Temporary Tablespace to Avoid Space Problems Creating Indexes................... 5-2
Index the Correct Tables and Columns .......................................................................................... 5-2
Limit the Number of Indexes for Each Table................................................................................. 5-3
Choose the Order of Columns in Composite Indexes .................................................................. 5-3
Gather Statistics to Make Index Usage More Accurate ................................................................ 5-4
Drop Indexes That Are No Longer Required ............................................................................... 5-4
Privileges Required to Create an Index ......................................................................................... 5-5
viii
Creating Indexes: Basic Examples ........................................................................................................ 5-5
When to Use Domain Indexes ............................................................................................................... 5-6
When to Use Function-Based Indexes.................................................................................................. 5-6
Advantages of Function-Based Indexes.......................................................................................... 5-6
Examples of Function-Based Indexes.............................................................................................. 5-7
Example: Function-Based Index for Case-Insensitive Searches........................................... 5-8
Example: Precomputing Arithmetic Expressions with a Function-Based Index............... 5-8
Example: Function-Based Index for Language-Dependent Sorting.................................... 5-8
Restrictions for Function-Based Indexes......................................................................................... 5-8
6 Maintaining Data Integrity in Application Development
Overview of Integrity Constraints........................................................................................................ 6-1
When to Enforce Business Rules with Integrity Constraints ...................................................... 6-1
Example of an Integrity Constraint for a Business Rule ....................................................... 6-2
When to Enforce Business Rules in Applications ......................................................................... 6-2
Creating Indexes for Use with Constraints .................................................................................... 6-2
When to Use NOT NULL Integrity Constraints ........................................................................... 6-2
When to Use Default Column Values ............................................................................................ 6-3
Setting Default Column Values ....................................................................................................... 6-4
Choosing a Table's Primary Key ..................................................................................................... 6-4
When to Use UNIQUE Key Integrity Constraints ........................................................................ 6-5
Constraints On Views: for Performance, Not Data Integrity....................................................... 6-5
Enforcing Referential Integrity with Constraints.............................................................................. 6-6
About Nulls and Foreign Keys......................................................................................................... 6-7
Defining Relationships Between Parent and Child Tables .......................................................... 6-8
Rules for Multiple FOREIGN KEY Constraints ............................................................................ 6-9
Deferring Constraint Checks............................................................................................................ 6-9
Guidelines for Deferring Constraint Checks .......................................................................... 6-9
Managing Constraints That Have Associated Indexes .................................................................. 6-10
Minimizing Space and Time Overhead for Indexes Associated with Constraints................ 6-10
Guidelines for Indexing Foreign Keys.............................................................................................. 6-11
About Referential Integrity in a Distributed Database................................................................. 6-11
When to Use CHECK Integrity Constraints..................................................................................... 6-11
Restrictions on CHECK Constraints ............................................................................................ 6-12
Designing CHECK Constraints .................................................................................................... 6-12
Rules for Multiple CHECK Constraints ...................................................................................... 6-12
Choosing Between CHECK and NOT NULL Integrity Constraints ...................................... 6-13
Examples of Defining Integrity Constraints ................................................................................... 6-13
Example: Defining Integrity Constraints with the CREATE TABLE Command .................. 6-13
Example: Defining Constraints with the ALTER TABLE Command...................................... 6-14
Privileges Required to Create Constraints ................................................................................. 6-14
Naming Integrity Constraints ...................................................................................................... 6-14
Enabling and Disabling Integrity Constraints ............................................................................... 6-14
Why Disable Constraints? ............................................................................................................. 6-15
About Exceptions to Integrity Constraints.................................................................................. 6-15
Enabling Constraints ..................................................................................................................... 6-15
Creating Disabled Constraints ..................................................................................................... 6-16
ix
Enabling and Disabling Existing Integrity Constraints............................................................. 6-16
Enabling Existing Constraints ............................................................................................... 6-16
Disabling Existing Constraints .............................................................................................. 6-16
Tip: Using the Data Dictionary to Find Constraints........................................................... 6-17
Guidelines for Enabling and Disabling Key Integrity Constraints.......................................... 6-17
Fixing Constraint Exceptions ....................................................................................................... 6-17
Altering Integrity Constraints ............................................................................................................ 6-17
Renaming Integrity Constraints.................................................................................................... 6-18
Dropping Integrity Constraints.......................................................................................................... 6-19
Managing FOREIGN KEY Integrity Constraints ........................................................................... 6-19
Datatypes and Names for Foreign Key Columns....................................................................... 6-19
Limit on Columns in Composite Foreign Keys .......................................................................... 6-19
Foreign Key References Primary Key by Default....................................................................... 6-20
Privileges Required to Create FOREIGN KEY Integrity Constraints...................................... 6-20
Choosing How Foreign Keys Enforce Referential Integrity .................................................... 6-20
Viewing Definitions of Integrity Constraints ................................................................................ 6-21
Examples of Defining Integrity Constraints................................................................................ 6-21
Part II PL/SQL for Application Developers
7 Coding PL/SQL Procedures and Packages
Overview of PL/SQL Program Units ................................................................................................... 7-1
Anonymous Blocks ........................................................................................................................... 7-2
Stored Program Units (Procedures, Functions, and Packages) .................................................. 7-3
Naming Procedures and Functions ......................................................................................... 7-4
Parameters for Procedures and Functions ............................................................................. 7-4
Creating Stored Procedures and Functions ........................................................................... 7-7
Altering Stored Procedures and Functions ............................................................................ 7-8
Dropping Procedures and Functions ...................................................................................... 7-8
External Procedures ................................................................................................................... 7-9
PL/SQL Packages ...................................................................................................................... 7-9
PL/SQL Object Size Limitation ............................................................................................ 7-11
Creating Packages ................................................................................................................... 7-11
Naming Packages and Package Objects .............................................................................. 7-12
Package Invalidations and Session State ............................................................................. 7-12
Packages Supplied With Oracle Database ........................................................................... 7-12
Overview of Bulk Binds.......................................................................................................... 7-12
When to Use Bulk Binds ......................................................................................................... 7-13
Triggers ..................................................................................................................................... 7-15
Compiling PL/SQL Procedures for Native Execution .................................................................... 7-15
Remote Dependencies ......................................................................................................................... 7-15
Timestamps ..................................................................................................................................... 7-16
Disadvantages of the Timestamp Model ............................................................................. 7-16
Signatures ........................................................................................................................................ 7-16
When Does a Signature Change?........................................................................................... 7-18
Examples of Changing Procedure Signatures .................................................................... 7-19
x
Controlling Remote Dependencies .............................................................................................. 7-20
Dependency Resolution ......................................................................................................... 7-21
Suggestions for Managing Dependencies ........................................................................... 7-22
Cursor Variables ................................................................................................................................... 7-22
Declaring and Opening Cursor Variables .................................................................................. 7-22
Examples of Cursor Variables ...................................................................................................... 7-23
Fetching Data ........................................................................................................................... 7-23
Implementing Variant Records ............................................................................................. 7-24
Handling PL/SQL Compile-Time Errors ......................................................................................... 7-24
Handling Run-Time PL/SQL Errors .................................................................................................. 7-26
Declaring Exceptions and Exception Handling Routines ........................................................ 7-27
Unhandled Exceptions .................................................................................................................. 7-28
Handling Errors in Distributed Queries ..................................................................................... 7-28
Handling Errors in Remote Procedures ...................................................................................... 7-28
Debugging Stored Procedures............................................................................................................ 7-29
Calling Stored Procedures ................................................................................................................... 7-31
A Procedure or Trigger Calling Another Procedure.................................................................. 7-32
Interactively Calling Procedures From Oracle Database Tools ............................................... 7-32
Calling Procedures within 3GL Applications ............................................................................ 7-33
Name Resolution When Calling Procedures .............................................................................. 7-33
Privileges Required to Execute a Procedure .............................................................................. 7-33
Specifying Values for Procedure Arguments ............................................................................. 7-34
Calling Remote Procedures ................................................................................................................ 7-34
Remote Procedure Calls and Parameter Values......................................................................... 7-35
Referencing Remote Objects.......................................................................................................... 7-35
Synonyms for Procedures and Packages .................................................................................... 7-36
Calling Stored Functions from SQL Expressions ........................................................................... 7-36
Using PL/SQL Functions .............................................................................................................. 7-37
Syntax for SQL Calling a PL/SQL Function ............................................................................... 7-37
Naming Conventions ..................................................................................................................... 7-37
Name Precedence .................................................................................................................... 7-38
Arguments ............................................................................................................................... 7-39
Using Default Values .............................................................................................................. 7-39
Privileges .................................................................................................................................. 7-39
Requirements for Calling PL/SQL Functions from SQL Expressions .................................... 7-39
Controlling Side Effects ................................................................................................................. 7-40
Restrictions................................................................................................................................ 7-40
Declaring a Function ............................................................................................................... 7-41
Parallel Query and Parallel DML ......................................................................................... 7-42
PRAGMA RESTRICT_REFERENCES – for Backward Compatibility ............................. 7-43
Serially Reusable PL/SQL Packages ........................................................................................... 7-46
Package States .......................................................................................................................... 7-47
Why Serially Reusable Packages? ......................................................................................... 7-47
Syntax of Serially Reusable Packages ................................................................................... 7-47
Semantics of Serially Reusable Packages.............................................................................. 7-47
Examples of Serially Reusable Packages .............................................................................. 7-48
Returning Large Amounts of Data from a Function....................................................................... 7-51
xi
Coding Your Own Aggregate Functions ........................................................................................... 7-52
8 Coding Dynamic SQL
What Is Dynamic SQL? ........................................................................................................................... 8-1
Programming with Dynamic SQL................................................................................................... 8-1
Why Use Dynamic SQL?......................................................................................................................... 8-2
Executing DDL and SCL Statements in PL/SQL .......................................................................... 8-3
Executing Dynamic Queries............................................................................................................. 8-4
Referencing Database Objects that Do Not Exist at Compilation............................................... 8-4
Optimizing Execution Dynamically ............................................................................................... 8-5
Executing Dynamic PL/SQL Blocks ............................................................................................... 8-5
Performing Dynamic Operations Using Invoker's Rights .......................................................... 8-6
Developing with Native Dynamic SQL: Scenario............................................................................. 8-7
Sample DML Operation Using Native Dynamic SQL.................................................................. 8-7
Sample DDL Operation Using Native Dynamic SQL................................................................... 8-8
Sample Single-Row Query Using Native Dynamic SQL.............................................................. 8-8
Sample Multiple-Row Query with Native Dynamic SQL............................................................ 8-9
Choosing Between Native Dynamic SQL and the DBMS_SQL Package ..................................... 8-9
Advantages of Native Dynamic SQL........................................................................................... 8-10
Native Dynamic SQL is Easy to Use ..................................................................................... 8-10
Native Dynamic SQL is Faster than DBMS_SQL................................................................ 8-11
Native Dynamic SQL Supports User-Defined Types......................................................... 8-12
Native Dynamic SQL Supports Fetching into Records...................................................... 8-12
Advantages of the DBMS_SQL Package...................................................................................... 8-13
DBMS_SQL is Supported in Client-Side Programs ............................................................ 8-13
DBMS_SQL Supports Statements with Unknown Number of Inputs or Outputs ........ 8-13
DBMS_SQL Supports SQL Statements Larger than 32 KB ................................................ 8-13
DBMS_SQL Lets You Reuse SQL Statements...................................................................... 8-13
Examples of DBMS_SQL Package Code and Native Dynamic SQL Code............................. 8-14
Querying with Dynamic SQL: Example............................................................................... 8-14
Performing DML with Dynamic SQL: Example ................................................................. 8-15
Performing DML with RETURNING Clause Using Dynamic SQL: Example ............... 8-16
Avoiding SQL Injection in PL/SQL ................................................................................................... 8-17
Overview of SQL Injection Techniques ....................................................................................... 8-17
Statement Modification........................................................................................................... 8-18
Statement Injection .................................................................................................................. 8-19
Guarding Against SQL Injection................................................................................................... 8-21
Using Bind Variables to Guard Against SQL Injection ...................................................... 8-21
Using Validation Checks to Guard Against SQL Injection................................................ 8-22
9 Coding Triggers
Designing Triggers................................................................................................................................... 9-1
Creating Triggers ..................................................................................................................................... 9-2
Types of Triggers ............................................................................................................................... 9-3
Overview of System Events....................................................................................................... 9-3
Getting the Attributes of System Events ................................................................................. 9-3
xii
Naming Triggers ............................................................................................................................... 9-3
When Is the Trigger Fired? .............................................................................................................. 9-3
Do Import and SQL*Loader Fire Triggers?............................................................................. 9-4
How Column Lists Affect UPDATE Triggers ........................................................................ 9-4
Controlling When a Trigger Is Fired (BEFORE and AFTER Options) ...................................... 9-4
Ordering of Triggers ......................................................................................................................... 9-5
Modifying Complex Views (INSTEAD OF Triggers)................................................................... 9-6
Views that Require INSTEAD OF Triggers............................................................................. 9-6
INSTEAD OF Trigger Example................................................................................................. 9-7
Object Views and INSTEAD OF Triggers .............................................................................. 9-8
Triggers on Nested Table View Columns ............................................................................... 9-8
Firing Triggers One or Many Times (FOR EACH ROW Option) .............................................. 9-9
Firing Triggers Based on Conditions (WHEN Clause) ............................................................. 9-10
Coding the Trigger Body ..................................................................................................................... 9-10
Accessing Column Values in Row Triggers ............................................................................... 9-12
Example: Modifying LOB Columns with a Trigger............................................................ 9-12
INSTEAD OF Triggers on Nested Table View Columns ................................................... 9-13
Avoiding Name Conflicts with Triggers (REFERENCING Option) ............................... 9-13
Detecting the DML Operation That Fired a Trigger........................................................... 9-14
Error Conditions and Exceptions in the Trigger Body ...................................................... 9-14
Triggers on Object Tables............................................................................................................... 9-14
Triggers and Handling Remote Exceptions ............................................................................... 9-15
Restrictions on Creating Triggers ................................................................................................ 9-16
Who Is the Trigger User? ............................................................................................................... 9-19
Privileges Needed to Work with Triggers .................................................................................. 9-19
Compiling Triggers .............................................................................................................................. 9-20
Dependencies for Triggers ............................................................................................................ 9-20
Recompiling Triggers .................................................................................................................... 9-20
Modifying Triggers .............................................................................................................................. 9-21
Debugging Triggers ....................................................................................................................... 9-21
Enabling and Disabling Triggers ....................................................................................................... 9-21
Enabling Triggers ........................................................................................................................... 9-21
Disabling Triggers .......................................................................................................................... 9-21
Viewing Information About Triggers ............................................................................................... 9-22
Examples of Trigger Applications ..................................................................................................... 9-23
Responding to System Events through Triggers ............................................................................ 9-37
How Events Are Published Through Triggers ........................................................................... 9-37
Publication Context......................................................................................................................... 9-38
Error Handling ................................................................................................................................ 9-38
Execution Model.............................................................................................................................. 9-38
Event Attribute Functions.............................................................................................................. 9-38
List of Database Events .................................................................................................................. 9-41
System Events........................................................................................................................... 9-41
Client Events............................................................................................................................. 9-42
10 Developing Flashback Applications
Overview of Flashback Features ....................................................................................................... 10-1
xiii
Application Development Features.............................................................................................. 10-2
Database Administration Features............................................................................................... 10-2
Database Administration Tasks Before Using Flashback Features ............................................. 10-3
Using Flashback Query (SELECT ... AS OF) ................................................................................... 10-4
Examining Past Data: Example..................................................................................................... 10-5
Tips for Using Flashback Query ................................................................................................... 10-5
Using the DBMS_FLASHBACK Package......................................................................................... 10-6
Using ORA_ROWSCN......................................................................................................................... 10-7
Using Flashback Version Query ........................................................................................................ 10-8
Using Flashback Transaction Query................................................................................................ 10-10
Flashback Transaction Query and Flashback Version Query: Example............................... 10-10
Flashback Tips ..................................................................................................................................... 10-12
Flashback Tips – Performance..................................................................................................... 10-12
Flashback Tips – General ............................................................................................................. 10-13
11 Developing Applications with the PL/SQL Web Toolkit
Developing PL/SQL Web Applications: Overview ........................................................................ 11-1
Invoking a PL/SQL Web Application.......................................................................................... 11-1
Implementing a PL/SQL Web Application ................................................................................ 11-2
PL/SQL Web Toolkit............................................................................................................... 11-2
Using the mod_plsql Gateway ........................................................................................................... 11-3
Generating HTML Output with PL/SQL.......................................................................................... 11-4
Passing Parameters to a PL/SQL Web Application......................................................................... 11-5
Passing List and Dropdown List Parameters from an HTML Form ....................................... 11-5
Passing Radio Button and Checkbox Parameters from an HTML Form................................ 11-6
Passing Entry Field Parameters from an HTML Form.............................................................. 11-6
Passing Hidden Parameters from an HTML Form.................................................................... 11-8
Uploading a File from an HTML Form........................................................................................ 11-8
Submitting a Completed HTML Form......................................................................................... 11-8
Handling Missing Input from an HTML Form .......................................................................... 11-9
Maintaining State Information Between Web Pages ................................................................. 11-9
Performing Network Operations within PL/SQL Stored Procedures....................................... 11-10
Sending E-Mail from PL/SQL..................................................................................................... 11-10
Getting a Host Name or Address from PL/SQL...................................................................... 11-10
Working with TCP/IP Connections from PL/SQL ................................................................. 11-11
Retrieving the Contents of an HTTP URL from PL/SQL ....................................................... 11-11
Working with Tables, Image Maps, Cookies, and CGI Variables from PL/SQL ............... 11-13
12 Developing PL/SQL Server Pages
PL/SQL Server Pages: Overview ........................................................................................................ 12-1
What Are PL/SQL Server Pages and Why Use Them?............................................................. 12-1
Prerequisites for Developing and Deploying PL/SQL Server Pages...................................... 12-2
PSP and the HTP Package.............................................................................................................. 12-3
PSP and Other Scripting Solutions ............................................................................................... 12-3
Writing a PL/SQL Server Page ............................................................................................................ 12-4
Specifying Basic Server Page Characteristics.............................................................................. 12-5
xiv
Specifying the Scripting Language........................................................................................ 12-5
Returning Data to the Client .................................................................................................. 12-5
Handling Script Errors............................................................................................................ 12-7
Accepting User Input...................................................................................................................... 12-7
Naming the PL/SQL Stored Procedure....................................................................................... 12-8
Including the Contents of Other Files.......................................................................................... 12-8
Declaring Global Variables in a PSP Script ................................................................................. 12-9
Specifying Executable Statements in a PSP Script...................................................................... 12-9
Substituting an Expression Result in a PSP Script ................................................................... 12-10
Quoting and Escaping Strings in a PSP Script.......................................................................... 12-11
Including Comments in a PSP Script ......................................................................................... 12-11
Loading a PL/SQL Server Page into the Database ........................................................................ 12-12
Querying PSP Source Code ......................................................................................................... 12-13
Executing a PL/SQL Server Page Through a URL......................................................................... 12-14
Examples of PL/SQL Server Pages ................................................................................................... 12-15
Setup for PL/SQL Server Pages Examples................................................................................ 12-15
Printing the Sample Table with a Loop ..................................................................................... 12-16
Allowing a User Selection............................................................................................................ 12-17
Using an HTML Form to Call a PL/SQL Server Page............................................................. 12-18
Including JavaScript in a PSP File ....................................................................................... 12-19
Debugging PL/SQL Server Page Problems .................................................................................... 12-20
Putting PL/SQL Server Pages into Production .............................................................................. 12-21
13 Developing Applications with Database Change Notification
What Is Database Change Notification?........................................................................................... 13-1
Using Database Change Notification in the Middle Tier ............................................................. 13-2
Registering Queries for Database Change Notification................................................................ 13-5
Privileges .......................................................................................................................................... 13-5
What Is a Database Change Registration?................................................................................... 13-5
Supported Query Types................................................................................................................. 13-6
Registration Properties................................................................................................................... 13-6
Drop Table........................................................................................................................................ 13-7
Interfaces for Database Change Registration.............................................................................. 13-8
Creating a PL/SQL Stored Procedure as the Change Notification Recipient........................ 13-8
Registering Queries for Change Notification Through PL/SQL ............................................. 13-9
Creating a CHNF$_REG_INFO Object................................................................................. 13-9
Creating a Registration with DBMS_CHANGE_NOTIFICATION................................ 13-11
Adding Objects to an Existing Registration....................................................................... 13-12
Querying Change Notification Registrations................................................................................ 13-12
Interpreting a Database Change Notification................................................................................ 13-13
Interpreting a CHNF$_DESC Object.......................................................................................... 13-13
Interpreting a CHNF$_TDESC Object ................................................................................ 13-13
Interpreting a CHNF$_RDESC Object................................................................................ 13-14
Configuring Database Change Notification: Scenario ................................................................ 13-14
Creating a PL/SQL Callback Procedure.................................................................................... 13-15
Registering the Query................................................................................................................... 13-16
Best Practices ........................................................................................................................................ 13-17
xv
Troubleshooting................................................................................................................................... 13-18
Part III Advanced Topics for Application Developers
14 Calling External Procedures
Overview of Multi-Language Programs ........................................................................................... 14-1
What Is an External Procedure? .......................................................................................................... 14-2
Overview of The Call Specification for External Procedures....................................................... 14-3
Loading External Procedures .............................................................................................................. 14-3
Loading Java Class Methods ......................................................................................................... 14-3
Loading External C Procedures .................................................................................................... 14-4
Publishing External Procedures ......................................................................................................... 14-8
The AS LANGUAGE Clause for Java Class Methods ............................................................... 14-9
The AS LANGUAGE Clause for External C Procedures .......................................................... 14-9
LIBRARY .................................................................................................................................. 14-9
NAME ..................................................................................................................................... 14-10
LANGUAGE .......................................................................................................................... 14-10
CALLING STANDARD ....................................................................................................... 14-10
WITH CONTEXT .................................................................................................................. 14-10
PARAMETERS ...................................................................................................................... 14-10
AGENT IN .............................................................................................................................. 14-10
Publishing Java Class Methods........................................................................................................ 14-10
Publishing External C Procedures ................................................................................................... 14-11
Locations of Call Specifications ....................................................................................................... 14-11
Passing Parameters to External C Procedures with Call Specifications ................................... 14-14
Specifying Datatypes .................................................................................................................... 14-15
External Datatype Mappings....................................................................................................... 14-16
BY VALUE/REFERENCE for IN and IN OUT Parameter Modes ....................................... 14-18
The PARAMETERS Clause.......................................................................................................... 14-18
Overriding Default Datatype Mapping..................................................................................... 14-19
Specifying Properties.................................................................................................................... 14-19
INDICATOR ........................................................................................................................... 14-20
LENGTH and MAXLEN....................................................................................................... 14-21
CHARSETID and CHARSETFORM.................................................................................... 14-21
Repositioning Parameters..................................................................................................... 14-22
Using SELF.............................................................................................................................. 14-22
Passing Parameters by Reference ........................................................................................ 14-24
WITH CONTEXT................................................................................................................... 14-25
Inter-Language Parameter Mode Mappings...................................................................... 14-25
Executing External Procedures with the CALL Statement .......................................................... 14-25
Preconditions for External Procedures ...................................................................................... 14-26
Privileges of External Procedures........................................................................................ 14-26
Managing Permissions.......................................................................................................... 14-27
Creating Synonyms for External Procedures..................................................................... 14-27
CALL Statement Syntax............................................................................................................... 14-27
Calling Java Class Methods ......................................................................................................... 14-28
xvi
How the Database Server Calls External C Procedures .......................................................... 14-28
Handling Errors and Exceptions in Multi-Language Programs................................................. 14-29
Using Service Procedures with External C Procedures................................................................ 14-29
Doing Callbacks with External C Procedures................................................................................ 14-35
Object Support for OCI Callbacks............................................................................................... 14-37
Restrictions on Callbacks ............................................................................................................. 14-37
Debugging External Procedures ................................................................................................. 14-38
Using Package DEBUG_EXTPROC .................................................................................... 14-38
Demo Program .............................................................................................................................. 14-39
Guidelines for External C Procedures........................................................................................ 14-39
Restrictions on External C Procedures....................................................................................... 14-40
15 Developing Applications with Oracle XA
X/Open Distributed Transaction Processing (DTP)........................................................................ 15-1
DTP Terminology............................................................................................................................ 15-2
Required Public Information......................................................................................................... 15-4
Oracle XA Library Interface Subroutines......................................................................................... 15-4
XA Library Subroutines ................................................................................................................. 15-5
Extensions to the XA Interface ...................................................................................................... 15-5
Developing and Installing XA Applications ................................................................................... 15-6
Responsibilities of the DBA or System Administrator .............................................................. 15-6
Responsibilities of the Application Developer ........................................................................... 15-7
Defining the xa_open() String ....................................................................................................... 15-7
Syntax of the xa_open() String ............................................................................................... 15-7
Required Fields for the xa_open() String.............................................................................. 15-8
Optional Fields for the xa_open() String .............................................................................. 15-8
Interfacing XA with Precompilers and OCI.............................................................................. 15-10
Using Precompilers with the Oracle XA Library .............................................................. 15-10
Using OCI with the Oracle XA Library .............................................................................. 15-11
Managing Transaction Control with XA ................................................................................... 15-12
Examples of Precompiler Applications .............................................................................. 15-13
Migrating Precompiler or OCI Applications to TPM Applications....................................... 15-14
Managing XA Library Thread Safety......................................................................................... 15-15
Specifying Threading in the Open String........................................................................... 15-15
Restrictions on Threading in XA ......................................................................................... 15-15
Troubleshooting XA Applications ................................................................................................... 15-15
Accessing XA Trace Files ............................................................................................................. 15-16
The xa_open() String DbgFl.................................................................................................. 15-16
Trace File Locations ............................................................................................................... 15-17
Managing In-Doubt or Pending Transactions .......................................................................... 15-17
Using SYS Account Tables to Monitor XA Transactions......................................................... 15-17
XA Issues and Restrictions ................................................................................................................ 15-18
Using Database Links in XA Applications................................................................................ 15-18
Managing Transaction Branches in XA Applications.............................................................. 15-19
Using XA with Oracle Real Application Clusters .................................................................... 15-19
Managing Transaction Branches on Oracle Real Application Clusters (RAC)............. 15-19
Managing Instance Recovery in Real Application Clusters ............................................ 15-20
xvii
Global Uniqueness of XIDs in Real Application Clusters................................................ 15-21
SQL-Based XA Restrictions.......................................................................................................... 15-21
Rollbacks and Commits ........................................................................................................ 15-21
DDL Statements ..................................................................................................................... 15-22
Session State............................................................................................................................ 15-22
EXEC SQL ............................................................................................................................... 15-22
Miscellaneous Restrictions........................................................................................................... 15-22
16 Developing Applications on the Publish-Subscribe Model
Introduction to Publish-Subscribe .................................................................................................... 16-1
Publish-Subscribe Architecture ......................................................................................................... 16-2
Publish-Subscribe Concepts ............................................................................................................... 16-3
Examples of a Publish-Subscribe Mechanism ................................................................................ 16-5
Index
xviii
xix
Preface
The Oracle Database Application Developer's Guide - Fundamentals describes basic
application development features of Oracle Database 10g. Information in this guide
applies to features that work the same on all supported platforms, and does not
include system-specific information.
Audience
Oracle Database Application Developer's Guide - Fundamentals is intended for
programmers developing new applications or converting existing applications to run
in the Oracle Database environment. This book will also be valuable to systems
analysts, project managers, and others interested in the development of database
applications.
To use this document, you need a working knowledge of application programming,
and that you are acquainted with using the Structured Query Language (SQL) to
access information in relational database systems. Some sections of this guide assume
a familiar with object-oriented programming.
Documentation Accessibility
Our goal is to make Oracle products, services, and supporting documentation
accessible, with good usability, to the disabled community. To that end, our
documentation includes features that make information available to users of assistive
technology. This documentation is available in HTML format, and contains markup to
facilitate access by the disabled community. Accessibility standards will continue to
evolve over time, and Oracle is actively engaged with other market-leading
technology vendors to address technical obstacles so that our documentation can be
accessible to all of our customers. For more information, visit the Oracle Accessibility
Program Web site at
http://www.oracle.com/accessibility/
Accessibility of Code Examples in Documentation
Screen readers may not always correctly read the code examples in this document. The
conventions for writing code require that closing braces should appear on an
otherwise empty line; however, some screen readers may not always read a line of text
that consists solely of a bracket or brace.
xx
Accessibility of Links to External Web Sites in Documentation
This documentation may contain links to Web sites of other companies or
organizations that Oracle does not own or control. Oracle neither evaluates nor makes
any representations regarding the accessibility of these Web sites.
TTY Access to Oracle Support Services
Oracle provides dedicated Text Telephone (TTY) access to Oracle Support Services
within the United States of America 24 hours a day, seven days a week. For TTY
support, call 800.446.2398.
Related Documents
For more information, see these Oracle resources:
Oracle Database PL/SQL User's Guide and Reference to learn PL/SQL and to get a
complete description of the PL/SQL high-level programming language, which is
Oracle's procedural extension to SQL.
Oracle Call Interface Programmer's Guide and Oracle C++ Call Interface Programmer's
Guide to learn about the Oracle Call Interface (OCI). You can use the OCI to build
third-generation language (3GL) applications that access the Oracle Database.
Oracle Database Security Guide to learn about security features of the database that
application developers and database administrators need to be aware of.
The Oracle documentation for the Pro* series of precompilers, which allow you to
embed SQL and PL/SQL in your programs. If you write 3GL application
programs in C, C++, COBOL, or FORTRAN that incorporate embedded SQL, then
refer to the corresponding precompiler manual. For example, if you program in C
or C++, then refer to the Pro*C/C++ Programmer's Guide.
Oracle JDeveloper 10g is an Integrated Development Environment (IDE) for
building applications and Web services using the latest industry standards for
Java, XML, and SQL. You can access the JDeveloper documentation at the
following product page:
http://www.oracle.com/technology/products/jdev
Oracle Database SQL Reference and Oracle Database Administrator's Guide for SQL
information.
Oracle Database Concepts for basic Oracle Database concepts
Oracle XML Developer's Kit Programmer's Guide and Oracle XML DB Developer's
Guide for developing applications that manipulate XML data.
Many of the examples in this book use the sample schemas, which are installed by
default when you select the Basic Installation option with an Oracle Database
installation. Refer to Oracle Database Sample Schemas for information on how these
schemas were created and how you can use them yourself.
Printed documentation is available for sale in the Oracle Store at
http://oraclestore.oracle.com/
To download free release notes, installation documentation, white papers, or other
collateral, please visit the Oracle Technology Network (OTN). You must register online
before using OTN; registration is free and can be done at
http://www.oracle.com/technology/membership/
xxi
If you already have a username and password for OTN, then you can go directly to the
documentation section of the OTN Web site at
http://www.oracle.com/technology/documentation/
For additional information, see:
Oracle JDeveloper 10g: Empowering J2EE Development by Harshad Oak. Apress, 2004.
Oracle JDeveloper 10g Handbook by Avrom Faderman, Peter Koletzke, and Paul
Dorsey. Oracle Press, 2004.
Oracle PL/SQL Tips and Techniques by Joseph C. Trezzo. Oracle Press, 1999.
Oracle PL/SQL Programming by Steven Feuerstein. 3rd Edition. O'Reilly &
Associates, 2002.
Oracle PL/SQL Developer's Workbook by Steven Feuerstein. O'Reilly & Associates,
2000.
Oracle PL/SQL Best Practices by Steven Feuerstein. O'Reilly & Associates, 2001.
Conventions
The following text conventions are used in this document:
Convention Meaning
boldface Boldface type indicates graphical user interface elements associated
with an action, or terms defined in text or the glossary.
italic Italic type indicates book titles, emphasis, or placeholder variables for
which you supply particular values.
monospace Monospace type indicates commands within a paragraph, URLs, code
in examples, text that appears on the screen, or text that you enter.
xxii
xxiii
What's New in Application Development?
This section describes new features of the Oracle Database 10g Release 2 (10.2) and
provides pointers to additional information. New features information from previous
releases is also retained to help those users migrating to the current release.
The following sections describe the new features in Oracle Oracle Database 10g:
Oracle Database 10g Release 2 (10.2) New Features
Oracle Database 10g Release 1 (10.1) New Features
Oracle Database 10g Release 2 (10.2) New Features
This section discusses new features introduced in Oracle Database 10g Release 1 (10.1).
Regular expression enhancements
This release adds SQL support for common Perl-based extensions that are not
included but do not conflict with the POSIX standard.
Unicode datatype literal enhancement
You can avoid data loss if the database character set cannot represent all characters
in the client character set.
Database Change Notification
Database Change Notification enables client applications to receive notifications
when the result set of a registered query changes. For example, if the client
registers a query of the hr.employees table, and if a user adds an employee,
then the application can receive a database change notification when a new row is
added to the table. A new query of hr.employees returns the changed result set.
Database Change Notification supports both mid-tier caches and server-side
stored procedures. For example, a stored procedure that performs DML on
registered tables can automatically send change notifications to a mid-tier
application, which can keep cached data up to date.
See Also: Chapter 4, "Using Regular Expressions in Oracle
Database" for more information
See Also: "Using Character Literals in SQL Statements" on page 3-4
See Also: Chapter 13, "Developing Applications with Database
Change Notification"
xxiv
Asynchronous Commit
Oracle Database enables you to change the handling of commit redo depending on
the needs of your application. You can change the default COMMIT options so that
the application does not need to wait for Oracle Database to write data to the
online redo logs.
Automatic Undo Retention Enhancement
This feature provides maximum retention for the fixed-size undo tablespace,
thereby improving flashback capability as well as other statistic collection queries
in which the query length is unknown.
Failover Improvements for Real Application Clusters (RAC) Distributed
Transactions
Distributed Transactions in a RAC environment detect failures and start the
failover and failback processes automatically.
XML DB Web Services
Enables direct access to the Oracle database through a native Web Service.
Developers can write and deploy web services that can query the database with
SQL or XQuery or execute stored procedures. You can access web services through
the Oracle XML DB listener.
Oracle Database 10g Release 1 (10.1) New Features
This section discusses new features introduced in Oracle Database 10g Release 1 (10.1).
Regular Expression Support
A set of SQL functions introduced in this release let you perform queries and
manipulate string data by means of regular expressions. Refer to Chapter 4, "Using
Regular Expressions in Oracle Database" for more information.
Oracle Expression Filter
Oracle Expression Filter lets you store conditional expressions in a column that
you can use in the WHERE clause of a database query. Refer to "Representing
Conditional Expressions as Data" on page 3-22 for more information.
Native floating-point datatypes
Column datatypes BINARY_FLOAT and BINARY_DOUBLE are introduced in this
release. These datatypes provide an alternative to using the Oracle NUMBER
datatype, with the following benefits:
See Also: "Managing Commit Redo Behavior" on page 2-3
See Also: "Database Administration Tasks Before Using Flashback
Features" on page 10-3
See Also: "Using XA with Oracle Real Application Clusters" on
page 15-19
See Also: "Overview of Oracle Database Web Services" on page 1-14
See Also: Oracle Database SQL Reference
xxv
More efficient use of storage resources
Faster arithmetic operations
Support for numerical algorithms specified in the IEEE 754 Standard
Support for native floating-point datatypes in bind and fetch operations is
provided for the following client interfaces:
SQL
PL/SQL
OCI
OCCI
Pro*C/C++
JDBC
Terabyte-Size Large Object (LOB) support
This release provides support for terabyte-size LOB values (from 8 to 128
terabytes) in the following programmatic environments:
Java (JDBC)
OCI
PL/SQL (package DBMS_LOB)
You can store and manipulate LOB (BLOB, CLOB, and NCLOB) datatypes larger
than 4GB.
Flashback
This release has new and enhanced flashback features. You can now do the
following:
Query the transaction history of a row.
Obtain the SQL undo syntax for a row and perform row-level flashback
operations.
Perform remote queries of past data.
Oracle Data Provider for .NET
Oracle Data Provider for .NET (ODP.NET) is a new programmatic environment
that implements a data provider for Oracle Database. It uses APIs native to Oracle
Database to offer fast and reliable access from any .NET application to database
features and data. ODP.NET also uses and inherits classes and interfaces available
in the Microsoft .NET Framework Class Library.
See Also: "Representing Numeric Data" on page 3-5
See Also: For details on terabyte-size LOB support:
Oracle Database Application Developer's Guide - Large Objects
Oracle Call Interface Programmer's Guide
See Also: Chapter 10, "Developing Flashback Applications"
See Also: Oracle Data Provider for .NET Developer's Guide
xxvi
Orientation to Oracle Programmatic Environments 1-1
1
Orientation to Oracle Programmatic
Environments
This chapter contains these topics:
Overview of Oracle Application Development
Overview of PL/SQL
Overview of Java Support Built Into the Database
Overview of Pro*C/C++
Overview of Pro*COBOL
Overview of OCI and OCCI
Overview of Oracle Data Provider for .NET (ODP.NET)
Overview of Oracle Objects for OLE (OO4O)
Choosing a Programming Environment
Overview of Oracle Application Development
As an application developer, you have many choices when writing a program to
interact with an Oracle database.
Client/Server Model
In a traditional client/server program, the code of your application runs on a machine
other than the database server. Database calls are transmitted from this client machine
to the database server. Data is transmitted from the client to the server for insert and
update operations and returned from the server to the client for query operations. The
data is processed on the client machine. Client/server programs are typically written
by using precompilers, whereas SQL statements are embedded within the code of
another language such as C, C++, or COBOL.
Server-Side Coding
You can develop application logic that resides entirely inside the database by using
triggers that are executed automatically when changes occur in the database or stored
procedures that are called explicitly. Off-loading the work from your application lets
you reuse code that performs verification and cleanup and control database operations
from a variety of clients. For example, by making stored procedures callable through a
Web server, you can construct a Web-based user interface that performs the same
functions as a client/server application.
Overview of PL/SQL
1-2 Oracle Database Application Developer’s Guide - Fundamentals
Two-Tier Versus Three-Tier Models
Client/server computing is often referred to as a two-tier model: your application
communicates directly with the database server. In the three-tier model, a separate
application server processes the requests. The application server might be a basic Web
server, or might perform advanced functions like caching and load-balancing.
Increasing the processing power of this middle tier lets you lessen the resources
needed by client systems, resulting in a thin client configuration in which the client
machine might need only a Web browser or other means of sending requests over the
TCP/IP or HTTP protocols.
User Interface
The interface that your application displays to end users depends on the technology
behind the application as well as the needs of the users themselves. Experienced users
can enter SQL commands that are passed on to the database. Novice users can be
shown a graphical user interface that uses the graphics libraries of the client system
(such as Windows or X-Windows). Any of these traditional user interfaces can also be
provided in a Web browser by means of HTML and Java.
Stateful Versus Stateless User Interfaces
In traditional client/server applications, the application can keep a record of user
actions and use this information over the course of one or more sessions. For example,
past choices can be presented in a menu so that they do not have to be entered again.
When the application is able to save information in this way, the application is
considered stateful.
Web or thin-client applications that are stateless are easier to develop. Stateless
applications gather all the required information, process it using the database, and
then start over with the next user. This is a popular way to process single-screen
requests such as customer registration.
There are many ways to add stateful behavior to Web applications that are stateless by
default. For example, an entry form on one Web page can pass information to
subsequent Web pages, allowing you to construct a wizard-like interface that
remembers the user's choices through several different steps. Cookies can be used to
store small items of information on the client machine, and retrieve them when the
user returns to a Web site. Servlets can be used to keep a database session open and
store variables between requests from the same client.
Overview of PL/SQL
This section contains the following topics:
What Is PL/SQL?
Advantages of PL/SQL
PL/SQL Web Development Tools
What Is PL/SQL?
PL/SQL is Oracle's procedural extension to SQL, the standard database access
language. It is an advanced 4GL (fourth-generation programming language), which
means that it is an application-specific language. PL/SQL and SQL have built-in
treatment of the relational database domain.
In PL/SQL, you can manipulate data with SQL statements and control program flow
with procedural constructs such as loops. You can also do the following:
Overview of PL/SQL
Orientation to Oracle Programmatic Environments 1-3
Declare constants and variables
Define procedures and functions
Use collections and object types
Trap runtime errors
Applications written in any of the Oracle programmatic interfaces can call PL/SQL
stored procedures and send blocks of PL/SQL code to Oracle Database for execution.
3GL applications can access PL/SQL scalar and composite datatypes through host
variables and implicit datatype conversion. A 3GL language is easier than assembler
language for a human to understand and includes features such as named variables.
Unlike 4GL, it is not specific to an application domain.
Example 1–1 provides an example of a simple PL/SQL procedure. The procedure
debit_account withdraws money from a bank account. It accepts an account
number and an amount of money as parameters. It uses the account number to
retrieve the account balance from the database, then computes the new balance. If this
new balance is less than zero, then the procedure jumps to an error routine; otherwise,
it updates the bank account.
Example 1–1 Simple PL/SQL Example
PROCEDURE debit_account (p_acct_id INTEGER, p_debit_amount REAL)
IS
v_old_balance REAL;
v_new_balance REAL;
e_overdrawn EXCEPTION;
BEGIN
SELECT bal
INTO v_old_balance
FROM accts
WHERE acct_no = p_acct_id;
v_new_balance := v_old_balance - p_debit_amount;
IF v_new_balance < 0 THEN
RAISE e_overdrawn;
ELSE
UPDATE accts SET bal = v_new_balance
WHERE acct_no = p_acct_id;
END IF;
COMMIT;
EXCEPTION
WHEN e_overdrawn THEN
-- handle the error
END debit_account;
Advantages of PL/SQL
PL/SQL is a portable, high-performance transaction processing language with the
following advantages:
Integration with Oracle Database
High Performance
High Productivity
See Also:
Oracle Database PL/SQL User's Guide and Reference
Oracle Database SQL Reference
Overview of PL/SQL
1-4 Oracle Database Application Developer’s Guide - Fundamentals
Scalability
Manageability
Object-Oriented Programming Support
Portability
Security
Built-In Packages
Integration with Oracle Database
PL/SQL enables you use all of the Oracle Database SQL data manipulation, cursor
control, and transaction control statements. PL/SQL also supports the SQL functions,
operators, and pseudocolumns. So, you can manipulate data in Oracle Database
flexibly and safely.
PL/SQL supports all SQL datatypes. Combined with the direct access that SQL
provides, these shared datatypes integrate PL/SQL with the Oracle Database data
dictionary.
PL/SQL supports Dynamic SQL, which is a programming technique that enables you
to build and process SQL statements "on the fly" at run time. It gives PL/SQL
flexibility comparable to scripting languages such as Perl, Korn shell, and Tcl.
The %TYPE and %ROWTYPE attributes enable your code to adapt as table definitions
change. For example, the %TYPE attribute declares a variable based on the type of a
database column. If the column datatype changes, then the variable uses the correct
type at runtime. This provides data independence and reduces maintenance costs.
High Performance
If your application is database intensive, then you can use PL/SQL blocks to group
SQL statements before sending them to Oracle Database for execution. This coding
strategy can drastically reduce the communication overhead between your application
and Oracle Database.
PL/SQL stored procedures are compiled once and stored in executable form, so
procedure calls are quick and efficient. A single call can start a compute-intensive
stored procedure, reducing network traffic and improving round-trip response times.
Executable code is automatically cached and shared among users, lowering memory
requirements and invocation overhead.
High Productivity
PL/SQL adds procedural capabilities such as Oracle Forms and Oracle Reports. For
example, you can use an entire PL/SQL block in an Oracle Forms trigger instead of
multiple trigger steps, macros, or user exits.
PL/SQL is the same in all environments. As soon as you master PL/SQL with one
Oracle tool, you can transfer your knowledge to others, and so multiply the
productivity gains. For example, scripts written with one tool can be used by other
tools.
Scalability
PL/SQL stored procedures increase scalability by centralizing application processing
on the server. Automatic dependency tracking helps you develop scalable
applications.
Overview of PL/SQL
Orientation to Oracle Programmatic Environments 1-5
The shared memory facilities of the shared server enable Oracle Database to support
many thousands of concurrent users on a single node. For more scalability, you can
use the Oracle Connection Manager to multiplex network connections.
Manageability
After being validated, you can use a PL/SQL stored procedure in any number of
applications. If its definition changes, then only the procedure is affected, not the
applications that call it. This simplifies maintenance and enhancement. Also,
maintaining a procedure on the Oracle Database is easier than maintaining copies on
various client machines.
Object-Oriented Programming Support
You can use PL/SQL object types and collections for object-oriented programming.
Object Types An object type is a user-defined composite datatype that encapsulates a
data structure along with the functions and procedures needed to manipulate the data.
The variables that form the data structure are called attributes. The functions and
procedures that characterize the behavior of the object type are called methods, which
you can implement in PL/SQL.
Object types are an ideal object-oriented modeling tool, which you can use to reduce
the cost and time required to build complex applications. Besides allowing you to
create software components that are modular, maintainable, and reusable, object types
allow different teams of programmers to develop software components concurrently.
Collections A collection is an ordered group of elements, all of the same type (for
example, the grades for a class of students). Each element has a unique subscript that
determines its position in the collection. PL/SQL offers two kinds of collections:
nested tables and varrays (variable-size arrays).
Collections work like the set, queue, stack, and hash table data structures found in
most third-generation programming languages. Collections can store instances of an
object type and can also be attributes of an object type. Collections can be passed as
parameters. So, you can use them to move columns of data into and out of database
tables or between client-side applications and stored subprograms. You can define
collection types in a PL/SQL package, then use the same types across many
applications.
Portability
Applications written in PL/SQL can run on any operating system and hardware
platform on which Oracle Database runs. You can write portable program libraries and
reuse them in different environments.
Security
PL/SQL stored procedures enable you to divide application logic between the client
and the server, which prevents client applications from manipulating sensitive Oracle
Database data. Database triggers written in PL/SQL can prevent applications from
making specified updates and can audit user queries.
You can restrict access to Oracle Database data by allowing users to manipulate it only
through stored procedures that have a restricted set of privileges. For example, you
can grant users access to a procedure that updates a table but not grant them access to
the table itself.
Overview of Java Support Built Into the Database
1-6 Oracle Database Application Developer’s Guide - Fundamentals
Built-In Packages
A package is an encapsulated collection of related program objects stored together in
the database. Program objects are procedures, functions, variables, constants, cursors,
and exceptions.
The following packages are especially useful in application development for Oracle
Database:
DBMS_PIPE is used to communicate between sessions.
DBMS_ALERT is used to broadcast alerts to users.
DBMS_LOCK and DBMS_TRANSACTION are used for lock and transaction
management.
DBMS_AQ is used for Advanced Queuing.
DBMS_LOB is used to manipulate large objects.
DBMS_ROWID is used for employing ROWID values.
UTL_RAW is the RAW facility.
UTL_REF is for work with REF values.
The following packages are useful for server manageability:
DBMS_SESSION is for session management by DBAs.
DBMS_SPACE and DBMS_SHARED_POOL provide space information and reserve
shared pool resources.
DBMS_JOB is used to schedule jobs in the server.
PL/SQL Web Development Tools
Oracle Database provides built-in tools and technologies that enable you to deploy
PL/SQL applications over the Web. Thus, PL/SQL serves as an alternative to Web
application frameworks such as CGI.
The PL/SQL Web Toolkit is a set of PL/SQL packages that you can use to develop
stored procedures that can be invoked by a Web client. The PL/SQL Gateway enables
an HTTP client to invoke a PL/SQL stored procedure through mod_plsql, which is a
plug-in to Oracle HTTP Server. This module performs the following actions:
1. Translates a URL passed by a browser client
2. Calls an Oracle Database stored procedure with the parameters in the URL
3. Returns output (typically HTML) to the client
Overview of Java Support Built Into the Database
This section provides an overview of built-in database features that support Java
applications. The database includes the core JDK libraries such as java.lang,
java.io, and so on. The database supports client-side Java standards such as JDBC
See Also: Oracle Database Security Guide for details on database
security features
See Also: Chapter 11, "Developing Applications with the PL/SQL
Web Toolkit" to learn how to use PL/SQL in Web development
Overview of Java Support Built Into the Database
Orientation to Oracle Programmatic Environments 1-7
and SQLJ, and provides server-side JDBC and SQLJ drivers that allow data-intensive
Java code to run within the database.
This section contains the following topics:
Overview of Oracle JVM
Overview of Oracle Extensions to JDBC
Overview of Oracle SQLJ
Overview of Oracle JPublisher
Overview of Java Stored Procedures
Overview of Oracle Database Web Services
Overview of Writing Procedures and Functions in Java
Overview of Oracle JVM
Oracle JVM, the Java Virtual Machine provided with the Oracle Database, is compliant
with the J2SE version 1.4.x specification and supports the database session
architecture.
Any database session can activate a dedicated JVM. All sessions share the same JVM
code and statics; however, private states for any given session are held, and
subsequently garbage collected, in an individual session space.
This design provides the following benefits:
Java applications have the same session isolation and data integrity as SQL
operations.
There is no need to run Java in a separate process for data integrity.
Oracle JVM is a robust JVM with a small memory footprint.
The JVM has the same linear Symmetric Multiprocessing (SMP) scalability as the
database and can support thousands of concurrent Java sessions.
Oracle JVM works consistently with every platform supported by Oracle Database.
Java applications that you develop with Oracle JVM can easily be ported to any
supported platform.
Oracle JVM includes a deployment-time native compiler that enables Java code to be
compiled once, stored in executable form, shared among users, and invoked more
quickly and efficiently.
Security features of the database are also available with Oracle JVM. Java classes must
be loaded in a database schema (by using Oracle JDeveloper, a third-party IDE,
SQL*Plus, or the loadjava utility) before they can be invoked. Java class invocation is
secured and controlled through database authentication and authorization, Java 2
security, and invoker's or definer's rights.
See Also:
Oracle Database Concepts for background information about Java
and how the database supports it
Oracle Database Java Developer's Guide
Oracle Database JDBC Developer's Guide and Reference
Oracle Database JPublisher User's Guide
Overview of Java Support Built Into the Database
1-8 Oracle Database Application Developer’s Guide - Fundamentals
Overview of Oracle Extensions to JDBC
JDBC (Java Database Connectivity) is an API (Applications Programming Interface)
that allows Java to send SQL statements to an object-relational database such as Oracle
Database.
The JDBC standard defines four types of JDBC drivers:
Type 1. A JDBC-ODBC bridge. Software must be installed on client systems.
Type 2. Native methods (calls C or C++) and Java methods. Software must be
installed on the client.
Type 3. Pure Java. The client uses sockets to call middleware on the server.
Type 4. The most pure Java solution. Talks directly to the database by using Java
sockets.
JDBC is based on the X/Open SQL Call Level Interface, and complies with the SQL92
Entry Level standard.
You can use JDBC to do dynamic SQL. In dynamic SQL, the embedded SQL statement
to be executed is not known before the application is run and requires input to build
the statement.
The drivers that are implemented by Oracle have extensions to the capabilities in the
JDBC standard that was defined by Sun Microsystems. Oracle's implementations of
JDBC drivers are described in the following sections. Oracle Database support of and
extensions to various levels of the JDBC standard are described in "Oracle Database
Extensions to JDBC Standards" on page 1-9.
JDBC Thin Driver
The JDBC thin driver is a Type 4 (100% pure Java) driver that uses Java sockets to
connect directly to a database server. It has its own implementation of a Two-Task
Common (TTC), a lightweight implementation of TCP/IP from Oracle Net. It is
written entirely in Java and is therefore platform-independent.
The thin driver does not require Oracle software on the client side. It does need a
TCP/IP listener on the server side. Use this driver in Java applets that are downloaded
into a Web browser or in applications for which you do not want to install Oracle
client software. The thin driver is self-contained, but it opens a Java socket, and thus
can only run in a browser that supports sockets.
JDBC OCI Driver
The OCI driver is a Type 2 JDBC driver. It makes calls to the OCI (Oracle Call
Interface) written in C to interact with Oracle Database, thus using native and Java
methods.
The OCI driver allows access to more features than the thin driver, such as Transparent
Application Fail-Over, advanced security, and advanced LOB manipulation.
The OCI driver provides the highest compatibility between different Oracle Database
versions. It also supports all installed Oracle Net adapters, including IPC, named
pipes, TCP/IP, and IPX/SPX.
Because it uses native methods (a combination of Java and C) the OCI driver is
platform-specific. It requires a client installation of version Oracle8i or later including
Oracle Net, OCI libraries, CORE libraries, and all other dependent files. The OCI
driver usually executes faster than the thin driver.
Overview of Java Support Built Into the Database
Orientation to Oracle Programmatic Environments 1-9
The OCI driver is not appropriate for Java applets, because it uses a C library that is
platform-specific and cannot be downloaded into a Web browser. It is usable in J2EE
components running in middle-tier application servers, such as Oracle Application
Server. Oracle Application Server provides middleware services and tools that support
access between applications and browsers.
JDBC Server-Side Internal Driver
The JDBC server-side internal driver is a Type 2 driver that runs inside the database
server, reducing the number of round-trips needed to access large amounts of data.
The driver, the Java server VM, the database, the Java native compiler which speeds
execution by as much as 10 times, and the SQL engine all run within the same address
space.
This driver provides server-side support for any Java program used in the database:
SQLJ stored procedures, functions, triggers, and Java stored procedures. You can also
call PL/SQL stored procedures, functions, and triggers.
The server driver fully supports the same features and extensions as the client-side
drivers.
Oracle Database Extensions to JDBC Standards
Oracle Database includes the following extensions to the JDBC 1.22 standard:
Support for Oracle datatypes
Performance enhancement by row prefetching
Performance enhancement by execution batching
Specification of query column types to save round-trips
Control of DatabaseMetaData calls
Oracle Database supports all APIs from the JDBC 2.0 standard, including the core
APIs, optional packages, and numerous extensions. Some of the highlights include
datasources, JTA, and distributed transactions.
Oracle Database supports the following features from the JDBC 3.0 standard:
Support for JDK 1.4.
Toggling between local and global transactions.
Transaction savepoints.
Reuse of prepared statements by connection pools.
Sample JDBC 2.0 Program
The following example shows the recommended technique for looking up a data
source using JNDI in JDBC 2.0:
// import the JDBC packages
import java.sql.*;
import javax.sql.*;
import oracle.jdbc.pool.*;
...
InitialContext ictx = new InitialContext();
DataSource ds = (DataSource)ictx.lookup("jdbc/OracleDS");
Connection conn = ds.getConnection();
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT last_name FROM employees");
Overview of Java Support Built Into the Database
1-10 Oracle Database Application Developer’s Guide - Fundamentals
while ( rs.next() ) {
out.println( rs.getString("ename") + "<br>");
}
conn.close();
Sample Pre-2.0 JDBC Program
The following source code registers an Oracle JDBC thin driver, connects to the
database, creates a Statement object, executes a query, and processes the result set.
The SELECT statement retrieves and lists the contents of the last_name column of
the hr.employees table.
import java.sql.*
import java.math.*
import java.io.*
import java.awt.*
class JdbcTest {
public static void main (String args []) throws SQLException {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
// Connect to the local database
Connection conn =
DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl",
"hr", "hr");
// Query the employee names
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("SELECT last_name FROM employees");
// Print the name out
while (rset.next ())
System.out.println (rset.getString (1));
// Close the result set, statement, and the connection
rset.close();
stmt.close();
conn.close();
}
}
One Oracle Database extension to the JDBC drivers is a form of the
getConnection() method that uses a Properties object. The Properties object
lets you specify user, password, and database information as well as row prefetching
and execution batching.
To use the OCI driver in this code, replace the Connection statement with the
following, where MyHostString is an entry in the tnsnames.ora file:
Connection conn = DriverManager.getConnection ("jdbc:oracle:oci8:@MyHostString",
"hr", "hr");
If you are creating an applet, then the getConnection() and registerDriver()
strings will be different.
JDBC in SQLJ Applications
JDBC code and SQLJ code (see "Overview of Oracle SQLJ" on page 1-11) interoperate,
allowing dynamic SQL statements in JDBC to be used with both static and dynamic
SQL statements in SQLJ. A SQLJ iterator class corresponds to the JDBC result set.
Overview of Java Support Built Into the Database
Orientation to Oracle Programmatic Environments 1-11
Overview of Oracle SQLJ
SQLJ is an ANSI SQL-1999 standard for embedding SQL statements in Java source
code. SQLJ provides a simpler alternative to JDBC for both client-side and server-side
SQL data access from Java.
A SQLJ source file contains Java source with embedded SQL statements. Oracle SQLJ
supports dynamic as well as static SQL. Support for dynamic SQL is an Oracle
extension to the SQLJ standard.
Oracle Database provides a translator and a run time driver to support SQLJ. The SQLJ
translator is 100% pure Java and is portable to any JVM that is compliant with JDK
version 1.1 or higher.
The Oracle SQLJ translator performs the following tasks:
Translates SQLJ source to Java code with calls to the SQLJ run time driver. The
SQLJ translator converts the source code to pure Java source code and can check
the syntax and semantics of static SQL statements against a database schema and
verify the type compatibility of host variables with SQL types.
Compiles the generated Java code with the Java compiler.
(Optional) Creates profiles for the target database. SQLJ generates "profile" files
with customization specific to Oracle Database.
Oracle Database supports SQLJ stored procedures, functions, and triggers which
execute in the Oracle JVM. SQLJ is integrated with JDeveloper. Source-level debugging
support for SQLJ is available in JDeveloper.
The following is an example of a simple SQLJ executable statement, which returns one
value because employee_id is unique in the employee table:
String name;
#sql { SELECT first_name INTO :name FROM employees WHERE employee_id=112 };
System.out.println("Name is " + name + ", employee number = " + employee_id);
Each host variable (or qualified name or complex Java host expression) included in a
SQL expression is preceded by a colon (:). Other SQLJ statements declare Java types.
For example, you can declare an iterator (a construct related to a database cursor) for
queries that retrieve many values, as follows:
#sql iterator EmpIter (String EmpNam, int EmpNumb);
See Also: Oracle Database JDBC Developer's Guide and Reference for
more information on JDBC
Note: The term "SQLJ," when used in this manual, refers to the
Oracle SQLJ implementation, including Oracle SQLJ extensions.
See Also: For more examples and details on Oracle SQLJ syntax:
Oracle Database JPublisher User's Guide
Sample SQLJ code available on the Oracle Technology Network
Web site: http://www.oracle.com/technology/
Overview of Java Support Built Into the Database
1-12 Oracle Database Application Developer’s Guide - Fundamentals
Benefits of SQLJ
Oracle SQLJ extensions to Java allow rapid development and easy maintenance of
applications that perform database operations through embedded SQL.
In particular, Oracle SQLJ does the following:
Provides a concise, legible mechanism for database access from static SQL. Most
SQL in applications is static. SQLJ provides more concise and less error-prone
static SQL constructs than JDBC does.
Provides an SQL Checker module for verification of syntax and semantics at
translate time.
Provides flexible deployment configurations, which makes it possible to
implement SQLJ on the client, server, or middle tier.
Supports a software standard. SQLJ is an effort of a group of vendors and will be
supported by all of them. Applications can access multiple database vendors.
Provides source code portability. Executables can be used with all of the vendor
DBMSs if the code does not rely on any vendor-specific features.
Enforces a uniform programming style for the clients and the servers.
Integrates the SQLJ translator with Oracle JDeveloper, a graphical IDE that
provides SQLJ translation, Java compilation, profile customizing, and debugging
at the source code level, all in one step.
Includes Oracle type extensions. Datatypes supported include: LOB datatypes,
ROWID, REF CURSOR, VARRAY, nested table, user-defined object types, RAW, and
NUMBER.
Comparing SQLJ with JDBC
JDBC provides a complete dynamic SQL interface from Java to databases. It gives
developers full control over database operations. SQLJ simplifies Java database
programming to improve development productivity.
JDBC provides fine-grained control of the execution of dynamic SQL from Java,
whereas SQLJ provides a higher-level binding to SQL operations in a specific database
schema. Following are some differences between JDBC and SQLJ:
SQLJ source code is more concise than equivalent JDBC source code.
SQLJ uses database connections to type-check static SQL code. JDBC, being a
completely dynamic API, does not.
SQLJ provides strong typing of query outputs and return parameters and allows
type-checking on calls. JDBC passes values to and from SQL without compile-time
type checking.
SQLJ programs allow direct embedding of Java bind expressions within SQL
statements. JDBC requires a separate get or set statement for each bind variable
and specifies the binding by position number.
SQLJ provides simplified rules for calling SQL stored procedures and functions.
For example, the following JDBC excerpt requires a generic call to a stored
procedure or function, in this case fun, to have the following syntax. (This
example shows SQL92 and Oracle JDBC syntaxes. Both are allowed.)
prepStmt.prepareCall("{call fun(?,?)}"); //stored procedure SQL92
prepStmt.prepareCall("{? = call fun(?,?)}"); //stored function SQL92
prepStmt.prepareCall("begin fun(:1,:2);end;"); //stored procedure Oracle
prepStmt.prepareCall("begin :1 := fun(:2,:3);end;");//stored func Oracle
Overview of Java Support Built Into the Database
Orientation to Oracle Programmatic Environments 1-13
Following is the SQLJ equivalent:
#sql {call fun(param_list) }; //Stored procedure
// Declare x
...
#sql x = {VALUES(fun(param_list)) }; // Stored function
// where VALUES is the SQL construct
The following benefits are common to SQLJ and JDBC:
SQLJ source files can contain JDBC calls. SQLJ and JDBC are interoperable.
Oracle JPublisher generates custom Java classes to be used in your SQLJ or JDBC
application for mappings to Oracle object types and collections.
Java and PL/SQL stored procedures can be used interchangeably.
SQLJ Stored Procedures in the Server
SQLJ applications can be stored and executed in the server by using the following
techniques:
Translate, compile, and customize the SQLJ source code on a client and load the
generated classes and resources into the server with the loadjava utility. The
classes are typically stored in a Java archive (.jar) file.
Load the SQLJ source code into the server, also using loadjava, where it is
translated and compiled by the server's embedded translator.
Overview of Oracle JPublisher
Oracle JPublisher is a code generator that automates the process of creating
database-centric Java classes by hand. Oracle JPublisher is a client-side utility and is
built into the database system. You can run Oracle JPublisher from the command line
or directly from the Oracle JDeveloper IDE.
Oracle JPublisher inspects PL/SQL packages and database object types such as SQL
object types, VARRAY types, and nested table types, and then generates a Java class
that is a wrapper around the PL/SQL package with corresponding fields and
methods.
The generated Java class can be incorporated and used by Java clients or J2EE
components to exchange and transfer object type instances to and from the database
transparently.
Overview of Java Stored Procedures
Java stored procedures enable you to implement programs that run in the database
server and which are independent of programs that run in the middle tier. Structuring
applications in this way reduces complexity and increases reuse, security,
performance, and scalability.
For example, you can create a Java stored procedure that performs operations that
require data persistence and a separate program to perform presentation or business
logic operations.
See Also: Oracle Database JPublisher User's Guide for more
information on using stored procedures with Oracle SQLJ
See Also: Oracle Database JPublisher User's Guide
Overview of Java Support Built Into the Database
1-14 Oracle Database Application Developer’s Guide - Fundamentals
Java stored procedures interface with SQL by using a similar execution model as
PL/SQL.
Overview of Oracle Database Web Services
Web services represent a distributed computing paradigm for Java application
development that is an alternative to earlier Java protocols such as JDBC. It allows
application-to-application interaction by means of the XML and Web protocols. For
example, an electronics parts vendor can provide a Web-based programmatic interface
to its suppliers for inventory management. The vendor can call a Web service as part
of a program and automatically order new stock based on the data returned.
The key technologies used in Web services are:
Web Services Description Language (WSDL), which is a standard format for
creating an XML document. WSDL describes what a web service can do, where it
resides, and how to invoke it. Specifically, it describes the operations and
parameters, including parameter types, provided by a Web service. In addition, a
WSDL document describes the location, the transport protocol, and the invocation
style for the Web service.
Simple Object Access Protocol (SOAP) messaging, which is an XML-based
message protocol used by Web services. SOAP does not prescribe a specific
transport mechanism such as HTTP, FTP, SMTP, or JMS; however, most Web
services accept messages that use HTTP or HTTPS.
Universal Description, Discovery, and Integration (UDDI) business registry, which
is a directory that lists Web services on the internet. The UDDI registry is often
compared to a telephone directory, listing unique identifiers (white pages),
business categories (yellow pages), and instructions for binding to a service
protocol (green pages).
Web services can use a variety of techniques and protocols. For example:
Dispatching can occur in a synchronous (typical) or asynchronous manner.
You can perform invocation in an RPC-style operation in which arguments are
sent and a response returned, or in a message style such as a one-way SOAP
document exchange.
You can use different encoding rules: literal or encoded.
You can invoke a Web service statically, in which case you may know everything about
it beforehand, or dynamically, in which case you can discover its operations and
transport endpoints on the fly.
Oracle Database as a Web Service Provider
Oracle Database can function as either a Web service provider or as a Web service
consumer. When used as a provider, the database enables sharing and disconnected
access to stored procedures, data, metadata, and other database resources such as the
queuing and messaging systems.
As a Web service provider, Oracle Database provides a disconnected and
heterogeneous environment that:
Exposes stored procedures independently of the language in which the procedures
are written
Exposes SQL Queries and XQuery
See Also: Oracle Database Java Developer's Guide
Overview of Pro*C/C++
Orientation to Oracle Programmatic Environments 1-15
Overview of Writing Procedures and Functions in Java
Functions and procedures are named blocks that encapsulate a sequence of statements.
They are like building blocks that you can use to construct modular, maintainable
applications. You write these named blocks and then define them by using the
loadjava command or SQL CREATE FUNCTION, CREATE PROCEDURE, or CREATE
PACKAGE statements. These Java methods can accept arguments and are callable from:
SQL CALL statements
Embedded SQL CALL statements
PL/SQL blocks, subprograms, and packages
DML statements (INSERT, UPDATE, DELETE, and SELECT)
Oracle development tools such as OCI, Pro*C/C++, and Oracle Developer
Oracle Java interfaces such as JDBC, SQLJ statements, CORBA, and Enterprise
Java Beans
Method calls from object types
Overview of Writing Database Triggers in Java
A database trigger is a stored procedure that Oracle Database invokes ("fires")
automatically when certain events occur, for example, when a DML operation
modifies a certain table. Triggers enforce business rules, prevent incorrect values from
being stored, and reduce the need to perform checking and cleanup operations in each
application.
Why Use Java for Stored Procedures and Triggers?
Stored procedures and triggers are compiled once, are easy to use and maintain,
and require less memory and computing overhead.
Network bottlenecks are avoided, and response time is improved. Distributed
applications are easier to build and use.
Computation-bound procedures run faster in the server.
Data access can be controlled by letting users have only stored procedures and
triggers that execute with their definer's privileges instead of invoker's rights.
PL/SQL and Java stored procedures can call each other.
Java in the server follows the Java language specification and can use the SQLJ
standard, so that databases other than Oracle Database are also supported.
Stored procedures and triggers can be reused in different applications as well as
different geographic sites.
Overview of Pro*C/C++
The Pro*C/C++ precompiler is a software tool that allows the programmer to embed
SQL statements in a C or C++ source file. Pro*C/C++ reads the source file as input and
outputs a C or C++ source file that replaces the embedded SQL statements with Oracle
runtime library calls and is then compiled by the C or C++ compiler.
When there are errors found during the precompilation or the subsequent compilation,
modify your precompiler input file and re-run the two steps.
Overview of Pro*C/C++
1-16 Oracle Database Application Developer’s Guide - Fundamentals
Implementing a Pro*C/C++ Application
The following is a simple code fragment from a C source file that queries the table
employees in the schema hr:
...
#define UNAME_LEN 10
...
int emp_number;
/* Define a host structure for the output values of a SELECT statement. */
/* No declare section needed if precompiler option MODE=ORACLE */
struct {
VARCHAR last_name[UNAME_LEN];
float salary;
float commission_pct;
} emprec;
/* Define an indicator structure to correspond to the host output structure. */
struct {
short emp_name_ind;
short sal_ind;
short comm_ind;
} emprec_ind;
...
/* Select columns last_name, salary, and commission_pct given the user's input
/* for employee_id. */
EXEC SQL SELECT last_name, salary, commission_pct
INTO :emprec INDICATOR :emprec_ind
FROM employees
WHERE employee_id = :emp_number;
...
The embedded SELECT statement is only slightly different from an interactive
(SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC
SQL. The colon (:), precedes every host (C) variable. The returned values of data and
indicators (set when the data value is NULL or character columns have been truncated)
can be stored in structs (such as in the preceding code fragment), in arrays, or in arrays
of structs. Multiple result set values are handled very simply in a manner that
resembles the case shown, where there is only one result, because of the unique
employee number. You use the actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or you can enter values which give you
control over the use of resources, how errors are reported, the formatting of output,
and how cursors (which correspond to a particular connection or SQL statement) are
managed. Cursors are used when there are multiple result set values.
Enter the options either in a configuration file, on the command line, or in-line inside
your source code with a special statement that begins with EXEC ORACLE. If there are
no errors found, you can then compile, link, and execute the output source file, like
any other C program that you write.
Use the precompiler to create server database access from clients that can be on many
different platforms. Pro*C/C++ allows you the freedom to design your own user
interfaces and to add database access to existing applications.
Before writing your embedded SQL statements, you may want to test interactive
versions of the SQL in SQL*Plus. You then make only minor changes to start testing
your embedded SQL application.
Overview of Pro*C/C++
Orientation to Oracle Programmatic Environments 1-17
Highlights of Pro*C/C++ Features
The following is a short subset of the capabilities of Pro*C/C++. For complete details,
refer to the Pro*C/C++ Precompiler Programmer's Guide.
You can write your application in either C or C++.
You can write multithreaded programs if your platform supports a threads
package. Concurrent connections are supported in either single-threaded or
multithreaded applications.
You can improve performance by embedding PL/SQL blocks. These blocks can
call functions or procedures in Java or PL/SQL that are written by you or
provided in Oracle Database packages.
Using precompiler options, you can check the syntax and semantics of your SQL
or PL/SQL statements during precompilation, as well as at runtime.
You can call stored PL/SQL and Java subprograms. Modules written in COBOL or
in C can be called from Pro*C/C++. External C procedures in shared libraries are
callable by your program.
You can conditionally precompile sections of your code so that they can execute in
different environments.
You can use arrays, or structures, or arrays of structures as host and indicator
variables in your code to improve performance.
You can deal with errors and warnings so that data integrity is guaranteed. As a
programmer, you control how errors are handled.
Your program can convert between internal datatypes and C language datatypes.
The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI), lower-level
C and C++ interfaces, are available for use in your precompiler source.
Pro*C/C++ supports dynamic SQL, a technique that allows users to input variable
values and statement syntax.
Pro*C/C++ can use special SQL statements to manipulate tables containing
user-defined object types. An Object Type Translator (OTT) will map the object
types and named collection types in your database to structures and headers that
you will then include in your source.
Two kinds of collection types, nested tables and VARRAY, are supported with a set
of SQL statements that allow a high degree of control over data.
Large Objects are accessed by another set of SQL statements.
A new ANSI SQL standard for dynamic SQL is supported for new applications, so
that you can execute SQL statements with a varying number of host variables. An
older technique for dynamic SQL is still usable by pre-existing applications.
Globalization support lets you use multibyte characters and UCS2 Unicode data.
Using scrollable cursors, you can move backward and forward through a result
set. For example, you can fetch the last row of the result set, or jump forward or
backward to an absolute or relative position within the result set.
A connection pool is a group of physical connections to a database that can be
shared by several named connections. Enabling the connection pool option can
help to optimize the performance of Pro*C/C++ application. The connection pool
option is not enabled by default.
Overview of Pro*COBOL
1-18 Oracle Database Application Developer’s Guide - Fundamentals
Overview of Pro*COBOL
The Pro*COBOL precompiler is a software tool that allows the programmer to embed
SQL statements in a COBOL source code file. Pro*COBOL reads the source file as
input and outputs a COBOL source file that replaces the embedded SQL statements
with Oracle Database runtime library calls, and is then compiled by the COBOL
compiler.
When there are errors found during the precompilation or the subsequent compilation,
modify your precompiler input file and re-run the two steps.
How You Implement a Pro*COBOL Application
Here is a simple code fragment from a source file that queries the table employees in
the schema hr:
...
WORKING-STORAGE SECTION.
*
* DEFINE HOST INPUT AND OUTPUT HOST AND INDICATOR VARIABLES.
* NO DECLARE SECTION NEEDED IF MODE=ORACLE.
*
01 EMP-REC-VARS.
05 EMP-NAME PIC X(10) VARYING.
05 EMP-NUMBER PIC S9(4) COMP VALUE ZERO.
05 SALARY PIC S9(5)V99 COMP-3 VALUE ZERO.
05 COMMISSION PIC S9(5)V99 COMP-3 VALUE ZERO.
05 COMM-IND PIC S9(4) COMP VALUE ZERO.
...
PROCEDURE DIVISION.
...
EXEC SQL
SELECT last_name, salary, commission_pct
INTO :EMP-NAME, :SALARY, :COMMISSION:COMM-IND
FROM employees
WHERE employee_id = :EMP-NUMBER
END-EXEC.
...
The embedded SELECT statement is only slightly different from an interactive
(SQL*Plus) SELECT statement. Every embedded SQL statement begins with EXEC
SQL. The colon (:) precedes every host (COBOL) variable. The SQL statement is
terminated by END-EXEC. The returned values of data and indicators (set when the
data value is NULL or character columns have been truncated) can be stored in group
items (such as in the preceding code fragment), in tables, or in tables of group items.
Multiple result set values are handled very simply in a manner that resembles the case
shown, where there is only one result, given the unique employee number. You use the
actual names of columns and tables in embedded SQL.
Use the default precompiler option values, or enter values that give you control over
the use of resources, how errors are reported, the formatting of output, and how
cursors are managed (cursors correspond to a particular connection or SQL statement).
Enter the options in a configuration file, on the command line, or in-line inside your
source code with a special statement that begins with EXEC ORACLE. If there are no
errors found, you can then compile, link, and execute the output source file, like any
other COBOL program that you write.
Overview of OCI and OCCI
Orientation to Oracle Programmatic Environments 1-19
Use the precompiler to create server database access from clients that can be on many
different platforms. Pro*COBOL allows you the freedom to design your own user
interfaces and to add database access to existing COBOL applications.
The embedded SQL statements available conform to an ANSI standard, so that you
can access data from many databases in a program, including remote servers
networked through Oracle Net.
Before writing your embedded SQL statements, you may want to test interactive
versions of the SQL in SQL*Plus. You then make only minor changes to start testing
your embedded SQL application.
Highlights of Pro*COBOL Features
The following is a short subset of the capabilities of Pro*COBOL.
You can call stored PL/SQL or Java subprograms. You can improve performance
by embedding PL/SQL blocks. These blocks can call PL/SQL functions or
procedures written by you or provided in Oracle Database packages.
Precompiler options allow you to define how cursors, errors, syntax-checking, file
formats, and so on, are handled.
Using precompiler options, you can check the syntax and semantics of your SQL
or PL/SQL statements during precompilation, as well as at runtime.
You can conditionally precompile sections of your code so that they can execute in
different environments.
Use tables, or group items, or tables of group items as host and indicator variables
in your code to improve performance.
You can program how errors and warnings are handled, so that data integrity is
guaranteed.
Pro*COBOL supports dynamic SQL, a technique that allows users to input
variable values and statement syntax.
Overview of OCI and OCCI
The Oracle Call Interface (OCI) and Oracle C++ Call Interface (OCCI) are application
programming interfaces (APIs) that allow you to create applications that use native
procedures or function calls of a third-generation language to access Oracle Database
and control all phases of SQL statement execution. These APIs provide:
Improved performance and scalability through the efficient use of system memory
and network connectivity
Consistent interfaces for dynamic session and transaction management in a
two-tier client/server or multitier environment
N-tiered authentication
Comprehensive support for application development using Oracle objects
Access to external databases
Ability to develop applications that service an increasing number of users and
requests without additional hardware investments
See Also: Pro*COBOL Programmer's Guide for complete details
Overview of OCI and OCCI
1-20 Oracle Database Application Developer’s Guide - Fundamentals
OCI lets you manipulate data and schemas in a database using a host programming
language, such as C. OCCI is an object-oriented interface suitable for use with C++.
These APIs provide a library of standard database access and retrieval functions in the
form of a dynamic runtime library (OCILIB) that can be linked in an application at
runtime. This eliminates the need to embed SQL or PL/SQL within 3GL programs.
Advantages of OCI
OCI provides significant advantages over other methods of accessing Oracle Database:
More fine-grained control over all aspects of the application design.
High degree of control over program execution.
Use of familiar 3GL programming techniques and application development tools
such as browsers and debuggers.
Support of dynamic SQL, method 4.
Availability on the broadest range of platforms of all the Oracle programmatic
interfaces.
Dynamic bind and define using callbacks.
Describe functionality to expose layers of server metadata.
Asynchronous event notification for registered client applications.
Enhanced array data manipulation language (DML) capability for array INSERTs,
UPDATEs, and DELETEs.
Ability to associate a commit request with an execute to reduce round-trips.
Optimization for queries using transparent prefetch buffers to reduce round-trips.
Thread safety, so you do not have to implement mutual exclusion (mutex) locks on
OCI handles.
The server connection in nonblocking mode means that control returns to the OCI
code when a call is still executing or could not complete.
Parts of the OCI
The OCI encompasses four main sets of functionality:
OCI relational functions, for managing database access and processing SQL
statements
OCI navigational functions, for manipulating objects retrieved from an Oracle
Database
OCI datatype mapping and manipulation functions, for manipulating data attributes
of Oracle types
OCI external procedure functions, for writing C callbacks from PL/SQL
See Also: For more information about OCI and OCCI calls:
Oracle Call Interface Programmer's Guide
Oracle C++ Call Interface Programmer's Guide
Oracle Streams Advanced Queuing User's Guide and Reference
Oracle Database Globalization Support Guide
Oracle Database Data Cartridge Developer's Guide
Overview of OCI and OCCI
Orientation to Oracle Programmatic Environments 1-21
Procedural and Non-Procedural Elements
The Oracle Call Interface (OCI) enables you to develop applications that combine the
non-procedural data access power of SQL with the procedural capabilities of most
programming languages, including C and C++. Note the following characteristics of
procedural and non-procedural languages:
In a non-procedural language program, the set of data to be operated on is
specified, but what operations will be performed and how the operations are to be
carried out is not specified. The non-procedural nature of SQL makes it an easy
language to learn and to use to perform database transactions. It is also the
standard language used to access and manipulate data in modern relational and
object-relational database systems.
In a procedural language program, the execution of most statements depends on
previous or subsequent statements and on control structures, such as loops or
conditional branches, which are not available in SQL. The procedural nature of
these languages makes them more complex than SQL, but it also makes them very
flexible and powerful.
The combination of both non-procedural and procedural language elements in an OCI
program provides easy access to Oracle Database in a structured programming
environment.
The OCI supports all SQL data definition, data manipulation, query, and transaction
control facilities that are available through Oracle Database. For example, an OCI
program can run a query against Oracle Database. The queries can require the
program to supply data to the database using input (bind) variables, as follows:
SELECT name FROM employees WHERE empno = :empnumber
In the preceding SQL statement,:empnumber is a placeholder for a value that will be
supplied by the application.
You can alternatively use PL/SQL, Oracle's procedural extension to SQL. The
applications you develop can be more powerful and flexible than applications written
in SQL alone. The OCI also provides facilities for accessing and manipulating objects
in Oracle Database.
Building an OCI Application
As Figure 1–1 shows, you compile and link an OCI program in the same way that you
compile and link a non-database application. There is no need for a separate
preprocessing or precompilation step.
Overview of Oracle Data Provider for .NET (ODP.NET)
1-22 Oracle Database Application Developer’s Guide - Fundamentals
Figure 1–1 The OCI Development Process
Overview of Oracle Data Provider for .NET (ODP.NET)
Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for
Oracle Database.
ODP.NET uses APIs native to Oracle Database to offer fast and reliable access from
any .NET application to database features and data. It also uses and inherits classes
and interfaces available in the Microsoft .NET Framework Class Library.
For programmers using Oracle Provider for OLE DB, ADO (ActiveX Data Objects)
provides an automation layer that exposes an easy programming model. ADO.NET
provides a similar programming model, but without the automation layer, for better
performance. More importantly, the ADO.NET model allows native providers such as
ODP.NET to expose specific features and datatypes specific to Oracle Database.
Using ODP.NET in a Simple Application
The following is a simple C# application that connects to Oracle Database and displays
its version number before disconnecting.
using System;
using Oracle.DataAccess.Client;
class Example
{
OracleConnection con;
void Connect()
Note: To properly link your OCI programs, it may be necessary on
some platforms to include other libraries, in addition to the OCI
library. Check your Oracle platform-specific documentation for
further information about extra libraries that may be required.
See Also: Oracle Data Provider for .NET Developer's Guide
Host Language Compiler
Source Files
Host Linker
Application
Object Files OCI Library
Object
Server
Overview of Oracle Objects for OLE (OO4O)
Orientation to Oracle Programmatic Environments 1-23
{
con = new OracleConnection();
con.ConnectionString = "User Id=hr;Password=hr;Data Source=oracle";
con.Open();
Console.WriteLine("Connected to Oracle" + con.ServerVersion);
}
void Close()
{
con.Close();
con.Dispose();
}
static void Main()
{
Example example = new Example();
example.Connect();
example.Close();
}
}
Overview of Oracle Objects for OLE (OO4O)
Oracle Objects for OLE (OO4O) is a product designed to allow easy access to data
stored in Oracle Database with any programming or scripting language that supports
the Microsoft COM Automation and ActiveX technology. This includes Visual Basic,
Visual C++, Visual Basic For Applications (VBA), IIS Active Server Pages (VBScript
and JavaScript), and others.
See the OO4O online help for detailed information about using OO4O.
Oracle Objects for OLE consists of the following software layers:
OO4O "In-Process" Automation Server
Oracle Data Control
Oracle Objects for OLE C++ Class Library
Figure 1–2, "Software Layers" illustrates the OO4O software components.
Note: Additional samples are provided in directory ORACLE_
BASE\ORACLE_HOME\ODP.NET\Samples.
Overview of Oracle Objects for OLE (OO4O)
1-24 Oracle Database Application Developer’s Guide - Fundamentals
Figure 1–2 Software Layers
OO4O Automation Server
The OO4O Automation Server is a set of COM Automation objects for connecting to
Oracle Database, executing SQL statements and PL/SQL blocks, and accessing the
results.
Unlike other COM-based database connectivity APIs, such as Microsoft ADO, the
OO4O Automation Server has been developed and evolved specifically for use with
Oracle Database.
It provides an optimized API for accessing features that are unique to Oracle Database
and are otherwise cumbersome or inefficient to use from ODBC or OLE
database-specific components.
OO4O provides key features for accessing Oracle Database efficiently and easily in
environments ranging from the typical two-tier client/server applications, such as
those developed in Visual Basic or Excel, to application servers deployed in
multitiered application server environments such as Web server applications in
Microsoft Internet Information Server (IIS) or Microsoft Transaction Server (MTS).
Features include:
Support for execution of PL/SQL and Java stored procedures, and PL/SQL
anonymous blocks. This includes support for Oracle datatypes used as parameters
to stored procedures, including PL/SQL cursors. Refer to "Support for Oracle LOB
and Object Datatypes" on page 1-28.
Support for scrollable and updatable cursors for easy and efficient access to result
sets of queries.
Thread-safe objects and Connection Pool Management Facility for developing
efficient Web server applications.
Full support for Oracle object-relational and LOB datatypes.
Data Aware
ActiveX
Controls
Oracle Data
Control
Oracle Client
Libraries
(OCI, CORE,
NLS)
Oracle
Database
OO4O
In-Process
Automation
Server
COM/DCOM
Automation
Controllers
(VB, Excel, ASP)
C++ Class
Libraries
Overview of Oracle Objects for OLE (OO4O)
Orientation to Oracle Programmatic Environments 1-25
Full support for Advanced Queuing.
Support for array inserts and updates.
Support for Microsoft Transaction Server (MTS).
OO4O Object Model
The Oracle Objects for OLE object model is illustrated in Figure 1–3, "Objects and Their
Relations".
Figure 1–3 Objects and Their Relations
OraSession
An OraSession object manages collections of OraDatabase, OraConnection, and
OraDynaset objects used within an application.
Typically, a single OraSession object is created for each application, but you can create
named OraSession objects for shared use within and between applications.
The OraSession object is the top-most object for an application. It is the only object
created by the CreateObject VB/VBA API and not by an Oracle Objects for OLE
method. The following code fragment shows how to create an OraSession object:
Dim OraSession as Object
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
OraServer
OraServer represents a physical network connection to Oracle Database.
The OraServer interface is introduced to expose the connection-multiplexing feature
provided in the Oracle Call Interface. After an OraServer object is created, multiple
user sessions (OraDatabase) can be attached to it by invoking the OpenDatabase
method. This feature is particularly useful for application components, such as
Internet Information Server (IIS), that use Oracle Objects for OLE in n-tier distributed
environments.
OraParameter
OraParameters
OraParamArray
OraSession
OraDatabase
OraField
OraMDAttribute
OraSQLStmt
OraDynaset
OraMetaData
OraAQ OraAQMsg
OraServer
Overview of Oracle Objects for OLE (OO4O)
1-26 Oracle Database Application Developer’s Guide - Fundamentals
The use of connection multiplexing when accessing Oracle Database with a large
number of user sessions active can help reduce server processing and resource
requirements while improving server scalability.
OraServer is used to share a single connection across multiple OraDatabase objects
(multiplexing), whereas each OraDatabase obtained from an OraSession has its own
physical connection.
OraDatabase
An OraDatabase interface adds additional methods for controlling transactions and
creating interfaces representing of Oracle object types. Attributes of schema objects can
be retrieved using the Describe method of the OraDatabase interface.
In releases prior to Oracle8i, an OraDatabase object is created by invoking the
OpenDatabase method of an OraSession interface. The Oracle Net alias, user
name, and password are passed as arguments to this method. In Oracle8i and later,
invocation of this method results in implicit creation of an OraServer object.
An OraDatabase object can also be created using the OpenDatabase method of the
OraServer interface.
Transaction control methods are available at the OraDatabase (user session) level.
Transactions may be started as Read-Write (default), Serializable, or
Read-only. Transaction control methods include:
BeginTrans
CommitTrans
RollbackTrans
For example:
UserSession.BeginTrans(OO4O_TXN_READ_WRITE)
UserSession.ExecuteSQL("delete emp where empno = 1234")
UserSession.CommitTrans
OraDynaset
An OraDynaset object permits browsing and updating of data created from a SQL
SELECT statement.
The OraDynaset object can be thought of as a cursor, although in actuality several
real cursors may be used to implement the semantics of OraDynaset. An
OraDynaset object automatically maintains a local cache of data fetched from the
server and transparently implements scrollable cursors within the browse data. Large
queries may require significant local disk space; application developers are
encouraged to refine queries to limit disk usage.
OraField
An OraField object represents a single column or data item within a row of a
dynaset.
If the current row is being updated, then the OraField object represents the currently
updated value, although the value may not yet have been committed to the database.
Assignment to the Value property of a field is permitted only if a record is being
edited (using Edit) or a new record is being added (using AddNew). Other attempts to
assign data to a field's Value property results in an error.
Overview of Oracle Objects for OLE (OO4O)
Orientation to Oracle Programmatic Environments 1-27
OraMetaData and OraMDAttribute
An OraMetaData object is a collection of OraMDAttribute objects that represent the
description information about a particular schema object in the database.
The OraMetaData object can be visualized as a table with three columns:
Metadata Attribute Name
Metadata Attribute Value
Flag specifying whether the Value is another OraMetaData object
The OraMDAttribute objects contained in the OraMetaData object can be accessed
by subscripting using ordinal integers or by using the name of the property.
Referencing a subscript that is not in the collection results in the return of a NULL
OraMDAttribute object.
OraParameters and OraParameter
An OraParameter object represents a bind variable in a SQL statement or PL/SQL
block.
OraParameter objects are created, accessed, and removed indirectly through the
OraParameters collection of an OraDatabase object. Each parameter has an
identifying name and an associated value. You can automatically bind a parameter to
SQL and PL/SQL statements of other objects (as noted in the object descriptions), by
using the parameter name as a placeholder in the SQL or PL/SQL statement. Such use
of parameters can simplify dynamic queries and increase program performance.
OraParamArray
An OraParamArray object represents an array-type bind variable in a SQL statement
or PL/SQL block, as opposed to a scalar-type bind variable represented by the
OraParameter object.
OraParamArray objects are created, accessed, and removed indirectly through the
OraParameters collection of an OraDatabase object. Each OraParamArray object
has an identifying name and an associated value.
OraSQLStmt
An OraSQLStmt object represents a single SQL statement. Use the CreateSQL
method to create an OraSQLStmt object from an OraDatabase object.
During create and refresh, OraSQLStmt objects automatically bind all relevant,
enabled input parameters to the specified SQL statement, using the parameter names
as placeholders in the SQL statement. This can improve the performance of SQL
statement execution without re-parsing the SQL statement.
The OraSQLStmt object can be used later to execute the same query using a different
value for the :SALARY placeholder. This is done as follows (updateStmt is the
OraSQLStmt object here):
OraDatabase.Parameters("SALARY").value = 200000
updateStmt.Parameters("ENAME").value = "KING"
updateStmt.Refresh
OraAQ
An OraAQ object is instantiated by invoking the CreateAQ method of the
OraDatabase interface. It represents a queue that is present in the database.
Overview of Oracle Objects for OLE (OO4O)
1-28 Oracle Database Application Developer’s Guide - Fundamentals
Oracle Objects for OLE provides interfaces for accessing Oracle Advanced Queuing
(AQ) feature. It makes AQ accessible from popular COM-based development
environments such as Visual Basic. For a detailed description of Oracle Advanced
Queuing, refer to Oracle Streams Advanced Queuing User's Guide and Reference.
OraAQMsg
The OraAQMsg object encapsulates the message to be enqueued or dequeued. The
message can be of any user-defined or raw type.
For a detailed description of Oracle Advanced Queuing, refer to Oracle Streams
Advanced Queuing User's Guide and Reference.
OraAQAgent
The OraAQAgent object represents a message recipient and is only valid for queues
that allow multiple consumers. It is a child of OraAQMsg.
An OraAQAgent object can be instantiated by invoking the AQAgent method. For
example:
Set agent = qMsg.AQAgent(name)
An OraAQAgent object can also be instantiated by invoking the AddRecipient
method. For example:
Set agent = qMsg.AddRecipient(name, address, protocol).
Support for Oracle LOB and Object Datatypes
Oracle Objects for OLE provides full support for accessing and manipulating instances
of object datatypes and LOBs in Oracle Database. Figure 1–4, "Supported Oracle
Datatypes" illustrates the datatypes supported by OO4O.
Instances of these types can be fetched from the database or passed as input or output
variables to SQL statements and PL/SQL blocks, including stored procedures and
functions. All instances are mapped to COM Automation Interfaces that provide
methods for dynamic attribute access and manipulation.
Figure 1–4 Supported Oracle Datatypes
OraAttribute
OraAttribute
Element Values
OraObject
OraRef
OraCollection
OraField
OraParameter
OraBLOB
OraCLOB
OraBFILE
Value of all other scalar types
OraParamArray
Overview of Oracle Objects for OLE (OO4O)
Orientation to Oracle Programmatic Environments 1-29
OraBLOB and OraCLOB
The OraBlob and OraClob interfaces in Oracle Objects for OLE provide methods for
performing operations on large database objects of datatype BLOB, CLOB, and NCLOB.
BLOB, CLOB, and NCLOB datatypes are also referred to here as LOB datatypes.
LOB data is accessed using Read and the CopyToFile methods.
LOB data is modified using Write, Append, Erase, Trim, Copy, CopyFromFile,
and CopyFromBFile methods. Before modifying the content of a LOB column in a
row, a row lock must be obtained. If the LOB column is a field of an OraDynaset,
object, then the lock is obtained by invoking the Edit method.
OraBFILE
The OraBFile interface in Oracle Objects for OLE provides methods for performing
operations on large database objects of datatype BFILE.
BFILE objects are large binary data objects stored in operating system files outside of
the database tablespaces.
Oracle Data Control
Oracle Data Control (ODC) is an ActiveX Control that is designed to simplify the
exchange of data between Oracle Database and visual controls such edit, text, list, and
grid controls in Visual Basic and other development tools that support custom
controls.
ODC acts as an agent to handle the flow of information from Oracle Database and a
visual data-aware control, such as a grid control, that is bound to it. The data control
manages various user interface (UI) tasks such as displaying and editing data. It also
executes and manages the results of database queries.
Oracle Data Control is compatible with the Microsoft data control included with
Visual Basic. If you are familiar with the Visual Basic data control, learning to use
Oracle Data Control is quick and easy. Communication between data-aware controls
and a Data Control is governed by a protocol that has been specified by Microsoft.
Oracle Objects for OLE C++ Class Library
Oracle Objects for OLE C++ Class Library is a collection of C++ classes that provide
programmatic access to the Oracle Object Server. Although the class library is
implemented using OLE Automation, neither the OLE development kit nor any OLE
development knowledge is necessary to use it. This library helps C++ developers
avoid the chore of writing COM client code for accessing the OO4O interfaces.
Additional Sources of Information
For detailed information about Oracle Objects for OLE refer to the online help
provided with the OO4O product:
Oracle Objects for OLE Help
Oracle Objects for OLE C++ Class Library Help
To view examples of how to use Oracle Objects for OLE, refer to the samples located in
the ORACLE_HOME\OO4O directory of the Oracle Database installation. Additional
OO4O examples can be found in the following Oracle publications:
Oracle Database Application Developer's Guide - Large Objects
Choosing a Programming Environment
1-30 Oracle Database Application Developer’s Guide - Fundamentals
Oracle Streams Advanced Queuing User's Guide and Reference
Oracle Database PL/SQL Packages and Types Reference
Choosing a Programming Environment
To choose a programming environment for a new development project:
Review the preceding overviews and the manuals for each environment.
Read the platform-specific manual that explains which compilers are approved for
use with your platforms.
If a particular language does not provide a feature you need, remember that
PL/SQL and Java stored procedures can both be called from code written in any of
the languages in this chapter. Stored procedures include triggers and object type
methods.
External procedures written in C can be called from OCI, Java, PL/SQL or SQL.
The external procedure itself can call back into the database using either SQL, OCI,
or Pro*C (but not C++).
The following examples illustrate easy choices:
Pro*COBOL does not support object types or collection types, while Pro*C/C++
does.
SQLJ does not support dynamic SQL the way that JDBC does.
Choosing Whether to Use OCI or a Precompiler
Precompiler applications typically contain less code than equivalent OCI applications,
which can help productivity.
Some situations require detailed control of the database and are suited for OCI
applications (either pure OCI or a precompiler application with embedded OCI calls):
OCI provides more detailed control over multiplexing and migrating sessions.
OCI provides dynamic bind and define using callbacks that can be used for any
arbitrary structure, including lists.
OCI has many calls to handle metadata.
OCI allows asynchronous event notifications to be received by a client application.
It provides a means for clients to generate notifications for propagation to other
clients.
OCI allows DML statements to use arrays to complete as many iterations as
possible before returning any error messages.
OCI calls for special purposes include Advanced Queuing, globalization support,
Data Cartridges, and support of the date and time datatypes.
OCI calls can be embedded in a Pro*C/C++ application.
Using Built-In Packages and Libraries
Both Java and PL/SQL have built-in packages and libraries.
PL/SQL and Java interoperate in the server. You can execute a PL/SQL package from
Java or wrap a PL/SQL class with a Java wrapper so that it can be called from
Choosing a Programming Environment
Orientation to Oracle Programmatic Environments 1-31
distributed CORBA and EJB clients. The following table shows PL/SQL packages and
their Java equivalents:
Java Compared to PL/SQL
Both Java and PL/SQL can be used to build applications in the database. Here are
some guidelines for their use:
PL/SQL Is Optimized for Database Access
PL/SQL uses the same datatypes as SQL. SQL datatypes are thus easier to use and
SQL operations are faster than with Java, especially when a large amount of data is
involved, when mostly database access is done, or when bulk operations are used.
PL/SQL Is Integrated with the Database
PL/SQL is an extension to SQL offering data encapsulation, information hiding,
overloading, and exception-handling.
Some advanced PL/SQL capabilities are not available for Java in Oracle9i. Examples
are autonomous transactions and the dblink facility for remote databases. Code
development is usually faster in PL/SQL than in Java.
Both Java and PL/SQL Have Object-Oriented Features
Java has inheritance, polymorphism, and component models for developing
distributed systems. PL/SQL has inheritance and type evolution, the ability to change
methods and attributes of a type while preserving subtypes and table data that use the
type.
Table 1–1 PL/SQL and Java Equivalent Software
PL/SQL Package Java Equivalent
DBMS_ALERT Call package with SQLJ or JDBC.
DBMS_DDL JDBC has this functionality.
DBMS_JOB Schedule a job that has a Java Stored procedure.
DBMS_LOCK Call with SQLJ or JDBC.
DBMS_MAIL Use JavaMail.
DBMS_OUTPUT Use subclass
oracle.aurora.rdbms.OracleDBMSOutputStream or Java
stored procedure DBMS_JAVA.SET_STREAMS.
DBMS_PIPE Call with SQLJ or JDBC.
DBMS_SESSION Use JDBC to execute an ALTER SESSION statement.
DBMS_SNAPSHOT Call with SQLJ or JDBC.
DBMS_SQL Use JDBC.
DBMS_TRANSACTION Use JDBC to execute an ALTER SESSION statement.
DBMS_UTILITY Call with SQLJ or JDBC.
UTL_FILE Grant the JAVAUSERPRIV privilege and then use Java I/O entry
points.
Choosing a Programming Environment
1-32 Oracle Database Application Developer’s Guide - Fundamentals
Java Is Used for Open Distributed Applications
Java has a richer type system than PL/SQL and is an object-oriented language. Java
can use CORBA (which can have many different computer languages in its clients) and
EJB. PL/SQL packages can be called from CORBA or EJB clients.
You can run XML tools, the Internet File System, or JavaMail from Java.
Many Java-based development tools are available throughout the industry.
Part I
SQL for Application Developers
This part contains the following chapters:
Chapter 2, "SQL Processing for Application Developers"
Chapter 3, "Using SQL Datatypes in Application Development"
Chapter 4, "Using Regular Expressions in Oracle Database"
Chapter 5, "Using Indexes in Application Development"
Chapter 6, "Maintaining Data Integrity in Application Development"
SQL Processing for Application Developers 2-1
2
SQL Processing for Application Developers
This chapter describes how Oracle Database processes SQL statements. Before reading
this chapter you should read the section "SQL Processing" in Oracle Database Concepts.
Topics include the following:
Grouping Operations into Transactions
Ensuring Repeatable Reads with Read-Only Transactions
Using Cursors within Applications
Locking Data Explicitly
About User Locks
Using Serializable Transactions for Concurrency Control
Autonomous Transactions
Resuming Execution After a Storage Error Condition
Grouping Operations into Transactions
This section contains the following topics:
Deciding How to Group Operations in Transactions
Improving Transaction Performance
Committing Transactions
Rolling Back Transactions
Defining Transaction Savepoints
Deciding How to Group Operations in Transactions
In general, only application designers who use the programming interfaces to Oracle
Database are concerned with which types of actions should be grouped together as
one transaction. You should use the following principles when deciding how to group
transactions:
Transactions must be defined properly so that work is accomplished in logical
units and data is kept consistent.
Data in all referenced tables should be in a consistent state before the transaction
begins and after it ends.
Transactions should consist of only the SQL statements or PL/SQL blocks that
comprise one consistent change to the data.
Grouping Operations into Transactions
2-2 Oracle Database Application Developer’s Guide - Fundamentals
For example, suppose that you write a Web application that enables users to transfer
funds between accounts. The transaction should include the debit to one account,
which is executed by one SQL statement, and the credit to another account, which is
executed by a second SQL statement. Both statements should fail or succeed together
as a unit of work; the credit should not be committed without the debit. Other
non-related actions, such as a new deposit to one account, should not be included in
the same transaction.
Improving Transaction Performance
As an application developer, you should consider whether you can improve
performance. Consider the following performance enhancements when designing and
writing your application:
Use the SET TRANSACTION command with the USE ROLLBACK SEGMENT clause to
explicitly assign a transaction to a rollback segment. This technique can eliminate
the need to allocate additional extents dynamically, which can reduce system
performance. Note that this clause is relevant and valid only if you use rollback
segments for undo. If you use automatic undo management, then Oracle Database
ignores this clause.
Establish standards for writing SQL statements so that you can take advantage of
shared SQL areas. Oracle Database recognizes identical SQL statements and allows
them to share memory areas. This reduces memory usage on the database server
and increases system throughput.
Use the ANALYZE command to collect statistics that can be used by Oracle
Database to implement a cost-based approach to SQL statement optimization. You
can supply additional "hints" to the optimizer as needed.
Call the DBMS_APPLICATION_INFO.SET_ACTION procedure before beginning a
transaction to register and name a transaction for later use when measuring
performance across an application. You should specify which type of activity a
transaction performs so that the system tuners can later see which transactions are
taking up the most system resources.
Increase user productivity and query efficiency by including user-written PL/SQL
functions in SQL expressions as described in "Calling Stored Functions from SQL
Expressions" on page 7-36.
Create explicit cursors when writing a PL/SQL application.
Reduce frequency of parsing and improve performance in precompiler programs
by increasing the number of cursors with MAX_OPEN_CURSORS.
Use the SET TRANSACTION command with the ISOLATION LEVEL set to
SERIALIZABLE to get ANSI/ISO serializable transactions.
Committing Transactions
To commit a transaction, use the COMMIT statement. The following two statements are
equivalent and commit the current transaction:
See Also:
"How Serializable Transactions Interact" on page 2-15
"Using Cursors within Applications" on page 2-6
Oracle Database Concepts for more information about transaction
tuning features
Grouping Operations into Transactions
SQL Processing for Application Developers 2-3
COMMIT WORK;
COMMIT;
The COMMIT statements lets you include the COMMENT parameter along with a
comment that provides information about the transaction being committed. This
option is useful for including information about the origin of the transaction when you
commit distributed transactions:
COMMIT COMMENT 'Dallas/Accts_pay/Trans_type 10B';
Managing Commit Redo Behavior
When a transaction updates the database, it generates a redo entry corresponding to
this update. Oracle Database buffers this redo in memory until the completion of the
transaction. When the transaction commits, the log writer (LGWR) process writes redo
for the commit, along with the accumulated redo of all changes in the transaction, to
disk. By default Oracle Database writes the redo to disk before the call returns to the
client. This behavior introduces a latency in the commit because the application must
wait for the redo to be persisted on disk.
Suppose that you are writing an application that requires very high transaction
throughput. If you are willing to trade commit durability for lower commit latency,
then you can change the default COMMIT options so that the application does not need
to wait for Oracle Database to write data to the online redo logs.
Oracle Database enables you to change the handling of commit redo depending on the
needs of your application. You can change the commit behavior in the following
locations:
COMMIT_WRITE initialization parameter at the system or session level
COMMIT statement
The options in the COMMIT statement override the current settings in the initialization
parameter. Table 21 describes redo persistence options that you can set in either
location.
The following example shows how to set the commit behavior to BATCH and NOWAIT
in the initialization parameter file:
COMMIT_WRITE = BATCH, NOWAIT
You can change the commit behavior at the system level by executing ALTER SYSTEM
as in the following example:
ALTER SYSTEM SET COMMIT_WRITE = BATCH, NOWAIT
Table 2–1 Initialization Parameter and COMMIT Options for Managing Commit Redo
Option Specifies that . . .
WAIT The commit does not return as successful until the redo corresponding to the
commit is persisted in the online redo logs (default).
NOWAIT The commit should return to the application without waiting for the redo to
be written to the online redo logs.
IMMEDIATE The log writer process should write the redo for the commit immediately
(default). In other words, this option forces a disk I/O.
BATCH Oracle Database should buffer the redo. The log writer process is permitted to
write the redo to disk in its own time.
Grouping Operations into Transactions
2-4 Oracle Database Application Developer’s Guide - Fundamentals
After the initialization parameter is set, a COMMIT statement with no options conforms
to the options specified in the parameter. Alternatively, you can override the current
initialization parameter setting by specifying options directly on the COMMIT
statement as in the following example:
COMMIT WRITE BATCH NOWAIT
In either case, your application specifies that log writer does not have to write the redo
for the commit immediately to the online redo logs and should not wait for
confirmation that the redo has been written to disk.
If your application uses OCI, then you can modify redo behavior by setting the
following flags in the OCITransCommit() function within your application:
OCI_TRANS_WRITEBATCH
OCI_TRANS_WRITENOWAIT
OCI_TRANS_WRITEIMMED
OCI_TRANS_WRITEWAIT
Note that the specification of the NOWAIT and BATCH options allows a small window
of vulnerability in which Oracle Database can roll back a transaction that your
application view as committed. Your application must be able to tolerate the following
scenarios:
The database host crashes, which causes the database to lose redo that was
buffered but not yet written to the online redo logs.
A file I/O problem prevents log writer from writing buffered redo to disk. If the
redo logs are not multiplexed, then the commit is lost.
Rolling Back Transactions
To roll back an entire transaction, or to roll back part of a transaction to a savepoint,
use the ROLLBACK statement. For example, either of the following statements rolls
back the entire current transaction:
ROLLBACK WORK;
ROLLBACK;
The WORK option of the ROLLBACK command has no function.
To roll back to a savepoint defined in the current transaction, use the TO option of the
ROLLBACK command. For example, either of the following statements rolls back the
current transaction to the savepoint named POINT1:
SAVEPOINT Point1;
...
Note: You cannot change the default IMMEDIATE and WAIT behavior
for distributed transactions.
See Also:
Oracle Database SQL Reference for information on the COMMIT
statement
Oracle Call Interface Programmer's Guide for information about the
OCITransCommit() function
Ensuring Repeatable Reads with Read-Only Transactions
SQL Processing for Application Developers 2-5
ROLLBACK TO SAVEPOINT Point1;
ROLLBACK TO Point1;
Defining Transaction Savepoints
To define a savepoint in a transaction, use the SAVEPOINT command. The following
statement creates the savepoint named ADD_EMP1 in the current transaction:
SAVEPOINT Add_emp1;
If you create a second savepoint with the same identifier as an earlier savepoint, the
earlier savepoint is erased. After creating a savepoint, you can roll back to the
savepoint.
There is no limit on the number of active savepoints for each session. An active
savepoint is one that has been specified since the last commit or rollback.
An Example of COMMIT, SAVEPOINT, and ROLLBACK
Table 24 shows a series of SQL statements that illustrates the use of COMMIT,
SAVEPOINT, and ROLLBACK statements within a transaction.
Ensuring Repeatable Reads with Read-Only Transactions
By default, the consistency model for Oracle Database guarantees statement-level read
consistency, but does not guarantee transaction-level read consistency (repeatable
reads). If you want transaction-level read consistency, and if your transaction does not
require updates, then you can specify a read-only transaction. After indicating that your
transaction is read-only, you can execute as many queries as you like against any
database table, knowing that the results of each query in the read-only transaction are
consistent with respect to a single point in time.
A read-only transaction does not acquire any additional data locks to provide
transaction-level read consistency. The multi-version consistency model used for
Table 2–2 Use of COMMIT, SAVEPOINT, and ROLLBACK
SQL Statement Results
SAVEPOINT a; First savepoint of this transaction
DELETE...; First DML statement of this transaction
SAVEPOINT b; Second savepoint of this transaction
INSERT INTO...; Second DML statement of this transaction
SAVEPOINT c; Third savepoint of this transaction
UPDATE...; Third DML statement of this transaction.
ROLLBACK TO c; UPDATE statement is rolled back, savepoint C remains defined
ROLLBACK TO b; INSERT statement is rolled back, savepoint C is lost, savepoint B remains
defined
ROLLBACK TO c; ORA-01086 error; savepoint C no longer defined
INSERT INTO...; New DML statement in this transaction
COMMIT; Commits all actions performed by the first DML statement (the DELETE
statement) and the last DML statement (the second INSERT statement)
All other statements (the second and the third statements) of the
transaction were rolled back before the COMMIT. The savepoint A is no
longer active.
Using Cursors within Applications
2-6 Oracle Database Application Developer’s Guide - Fundamentals
statement-level read consistency is used to provide transaction-level read consistency;
all queries return information with respect to the system change number (SCN)
determined when the read-only transaction begins. Because no data locks are acquired,
other transactions can query and update data being queried concurrently by a
read-only transaction.
Long-running queries sometimes fail because undo information required for consistent
read operations is no longer available. This happens when committed undo blocks are
overwritten by active transactions. Automatic undo management provides a way to
explicitly control when undo space can be reused; that is, how long undo information
is retained. Your database administrator can specify a retention period by using the
parameter UNDO_RETENTION.
For example, if UNDO_RETENTION is set to 30 minutes, then all committed undo
information in the system is retained for at least 30 minutes. This ensures that all
queries running for 30 minutes or less, under usual circumstances, do not encounter
the OER error, "snapshot too old."
A read-only transaction is started with a SET TRANSACTION statement that includes
the READ ONLY option. For example:
SET TRANSACTION READ ONLY;
The SET TRANSACTION statement must be the first statement of a new transaction; if
any DML statements (including queries) or other non-DDL statements (such as SET
ROLE) precede a SET TRANSACTION READ ONLY statement, an error is returned. Once
a SET TRANSACTION READ ONLY statement successfully executes, only SELECT
(without a FOR UPDATE clause), COMMIT, ROLLBACK, or non-DML statements (such
as SET ROLE, ALTER SYSTEM, LOCK TABLE) are allowed in the transaction.
Otherwise, an error is returned. A COMMIT, ROLLBACK, or DDL statement terminates
the read-only transaction; a DDL statement causes an implicit commit of the read-only
transaction and commits in its own transaction.
Using Cursors within Applications
PL/SQL implicitly declares a cursor for all SQL data manipulation statements,
including queries that return only one row. For queries that return more than one row,
you can explicitly declare a cursor to process the rows individually.
A cursor is a handle to a specific private SQL area. In other words, a cursor can be
thought of as a name for a specific private SQL area. A PL/SQL cursor variable
enables the retrieval of multiple rows from a stored procedure. Cursor variables allow
you to pass cursors as parameters in your 3GL application. Cursor variables are
described in Oracle Database PL/SQL User's Guide and Reference.
Although most Oracle Database users rely on the automatic cursor handling of the
database utilities, the programmatic interfaces offer application designers more control
over cursors. In application development, a cursor is a named resource available to a
program, which can be specifically used for parsing SQL statements embedded within
the application.
Declaring and Opening Cursors
There is no absolute limit to the total number of cursors one session can have open at
one time, subject to two constraints:
See Also: Oracle Database Administrator's Guide for information on
long-running queries and resumable space allocation
Using Cursors within Applications
SQL Processing for Application Developers 2-7
Each cursor requires virtual memory, so a session's total number of cursors is
limited by the memory available to that process.
A systemwide limit of cursors for each session is set by the initialization parameter
named OPEN_CURSORS found in the parameter file (such as INIT.ORA).
Explicitly creating cursors for precompiler programs can offer some advantages in
tuning those applications. For example, increasing the number of cursors can often
reduce the frequency of parsing and improve performance. If you know how many
cursors may be required at a given time, then you can make sure you can open that
many simultaneously.
Using a Cursor to Execute Statements Again
After each stage of execution, the cursor retains enough information about the SQL
statement to reexecute the statement without starting over, as long as no other SQL
statement has been associated with that cursor. The statement can be reexecuted
without including the parse stage.
By opening several cursors, the parsed representation of several SQL statements can
be saved. Repeated execution of the same SQL statements can thus begin at the
describe, define, bind, or execute step, saving the repeated cost of opening cursors and
parsing.
To understand the performance characteristics of a cursor, a DBA can retrieve the text
of the query represented by the cursor using the V$SQL catalog view. Because the
results of EXPLAIN PLAN on the original query might differ from the way the query is
actually processed, a DBA can get more precise information by examining the
V$SQL_PLAN, V$SQL_PLAN_STATISTICS, and V$SQL_PLAN_STATISTICS_ALL
catalog views.:
The V$SQL_PLAN view contains the execution plan information for each child
cursor loaded in the library cache.
The V$SQL_PLAN_STATISTICS view provides execution statistics at the row
source level for each child cursor.
The V$SQL_PLAN_STATISTICS_ALL view contains memory usage statistics for
row sources that use SQL memory (sort or hash-join). This view concatenates
information in V$SQL_PLAN with execution statistics from
V$SQL_PLAN_STATISTICS and V$SQL_WORKAREA.
Closing Cursors
Closing a cursor means that the information currently in the associated private area is
lost and its memory is deallocated. Once a cursor is opened, it is not closed until one of
the following events occurs:
The user program terminates its connection to the server.
If the user program is an OCI program or precompiler application, then it
explicitly closes any open cursor during the execution of that program. (However,
when this program terminates, any cursors remaining open are implicitly closed.)
See Also: Oracle Database Reference for descriptions of parameters
See Also: Oracle Database Reference for details on these views
Locking Data Explicitly
2-8 Oracle Database Application Developer’s Guide - Fundamentals
Cancelling Cursors
Cancelling a cursor frees resources from the current fetch.The information currently in
the associated private area is lost but the cursor remains open, parsed, and associated
with its bind variables.
Locking Data Explicitly
Oracle Database always performs necessary locking to ensure data concurrency,
integrity, and statement-level read consistency. You can override these default locking
mechanisms. For example, you might want to override the default locking of Oracle
Database if:
You want transaction-level read consistency or "repeatable reads"—where
transactions query a consistent set of data for the duration of the transaction,
knowing that the data has not been changed by any other transactions. This level
of consistency can be achieved by using explicit locking, read-only transactions,
serializable transactions, or overriding default locking for the system.
A transaction requires exclusive access to a resource. To proceed with its
statements, the transaction with exclusive access to a resource does not have to
wait for other transactions to complete.
The automatic locking mechanisms can be overridden at the transaction level.
Transactions including the following SQL commands override Oracle Database's
default locking:
LOCK TABLE
SELECT, including the FOR UPDATE clause
SET TRANSACTION with the READ ONLY or ISOLATION LEVEL SERIALIZABLE
options
Locks acquired by these statements are released after the transaction is committed or
rolled back.
The following sections describe each option available for overriding the default
locking of Oracle Database. The initialization parameter DML_LOCKS determines the
maximum number of DML locks allowed.
Although the default value is usually enough, you might need to increase it if you use
additional manual locks.
Note: You cannot cancel cursors using Pro*C/C++ or PL/SQL.
See Also: Oracle Call Interface Programmer's Guide for more
information about cancelling cursors
See Also: Oracle Database Reference for a discussion of parameters
Caution: If you override the default locking of Oracle Database at
any level, be sure that the overriding locking procedures operate
correctly: Ensure that data integrity is guaranteed, data concurrency is
acceptable, and deadlocks are not possible or are appropriately
handled.
Locking Data Explicitly
SQL Processing for Application Developers 2-9
Choosing a Locking Strategy
A transaction explicitly acquires the specified table locks when a LOCK TABLE
statement is executed. A LOCK TABLE statement manually overrides default locking.
When a LOCK TABLE statement is issued on a view, the underlying base tables are
locked. The following statement acquires exclusive table locks for the EMP_TAB and
DEPT_TAB tables on behalf of the containing transaction:
LOCK TABLE Emp_tab, Dept_tab
IN EXCLUSIVE MODE NOWAIT;
You can specify several tables or views to lock in the same mode; however, only a
single lock mode can be specified for each LOCK TABLE statement.
You can also indicate if you do or do not want to wait to acquire the lock. If you
specify the NOWAIT option, then you only acquire the table lock if it is immediately
available. Otherwise an error is returned to notify that the lock is not available at this
time. In this case, you can attempt to lock the resource at a later time. If NOWAIT is
omitted, then the transaction does not proceed until the requested table lock is
acquired. If the wait for a table lock is excessive, then you might want to cancel the
lock operation and retry at a later time; you can code this logic into your applications.
When to Lock with ROW SHARE and ROW EXCLUSIVE Mode
LOCK TABLE Emp_tab IN ROW SHARE MODE;
LOCK TABLE Emp_tab IN ROW EXCLUSIVE MODE;
ROW SHARE and ROW EXCLUSIVE table locks offer the highest degree of concurrency.
You might use these locks if:
Your transaction needs to prevent another transaction from acquiring an
intervening share, share row, or exclusive table lock for a table before the table can
be updated in your transaction. If another transaction acquires an intervening
share, share row, or exclusive table lock, no other transactions can update the table
until the locking transaction commits or rolls back.
Your transaction needs to prevent a table from being altered or dropped before the
table can be modified later in your transaction.
When to Lock with SHARE Mode
LOCK TABLE Emp_tab IN SHARE MODE;
SHARE table locks are rather restrictive data locks. You might use these locks if:
Your transaction only queries the table, and requires a consistent set of the table
data for the duration of the transaction.
You can hold up other transactions that try to update the locked table, until all
transactions that hold SHARE locks on the table either commit or roll back.
Other transactions may acquire concurrent SHARE table locks on the same table,
also allowing them the option of transaction-level read consistency.
Note: When a table is locked, all rows of the table are locked. No
other user can modify the table.
Locking Data Explicitly
2-10 Oracle Database Application Developer’s Guide - Fundamentals
For example, assume that two tables, EMP_TAB and BUDGET_TAB, require a consistent
set of data in a third table, DEPT_TAB. For a given department number, you want to
update the information in both of these tables, and ensure that no new members are
added to the department between these two transactions.
Although this scenario is quite rare, it can be accommodated by locking the DEPT_TAB
table in SHARE MODE, as shown in the following example. Because the DEPT_TAB table
is rarely updated, locking it probably does not cause many other transactions to wait
long.
LOCK TABLE Dept_tab IN SHARE MODE;
UPDATE Emp_tab
SET sal = sal * 1.1
WHERE deptno IN
(SELECT deptno FROM Dept_tab WHERE loc = 'DALLAS');
UPDATE Budget_tab
SET Totsal = Totsal * 1.1
WHERE Deptno IN
(SELECT Deptno FROM Dept_tab WHERE Loc = 'DALLAS');
COMMIT; /* This releases the lock */
When to Lock with SHARE ROW EXCLUSIVE Mode
LOCK TABLE Emp_tab IN SHARE ROW EXCLUSIVE MODE;
Caution: Your transaction may or may not update the table later in
the same transaction. However, if multiple transactions concurrently
hold share table locks for the same table, no transaction can update
the table (even if row locks are held as the result of a SELECT... FOR
UPDATE statement). Therefore, if concurrent share table locks on the
same table are common, updates cannot proceed and deadlocks are
common. In this case, use share row exclusive or exclusive table locks
instead.
Note: You may need to set up data structures similar to the following
for certain examples to work:
CREATE TABLE dept_tab(
deptno NUMBER(2) NOT NULL,
dname VARCHAR2(14),
loc VARCHAR2(13));
CREATE TABLE emp_tab (
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(2));
CREATE TABLE Budget_tab (
totsal NUMBER(7,2),
deptno NUMBER(2) NOT NULL);
Locking Data Explicitly
SQL Processing for Application Developers 2-11
You might use a SHARE ROW EXCLUSIVE table lock if:
Your transaction requires both transaction-level read consistency for the specified
table and the ability to update the locked table.
You do not care if other transactions acquire explicit row locks (using SELECT...
FOR UPDATE), which might make UPDATE and INSERT statements in the locking
transaction wait and might cause deadlocks.
You only want a single transaction to have this behavior.
When to Lock in EXCLUSIVE Mode
LOCK TABLE Emp_tab IN EXCLUSIVE MODE;
You might use an EXCLUSIVE table if:
Your transaction requires immediate update access to the locked table. When your
transaction holds an exclusive table lock, other transactions cannot lock specific
rows in the locked table.
Your transaction also ensures transaction-level read consistency for the locked
table until the transaction is committed or rolled back.
You are not concerned about low levels of data concurrency, making transactions
that request exclusive table locks wait in line to update the table sequentially.
Privileges Required
You can automatically acquire any type of table lock on tables in your schema. To
acquire a table lock on a table in another schema, you must have the LOCK ANY TABLE
system privilege or any object privilege (for example, SELECT or UPDATE) for the
table.
Letting Oracle Database Control Table Locking
Letting Oracle Database control table locking means your application needs less
programming logic, but also has less control, than if you manage the table locks
yourself.
Issuing the command SET TRANSACTION ISOLATION LEVEL SERIALIZABLE or
ALTER SESSION ISOLATION LEVEL SERIALIZABLE preserves ANSI serializability
without changing the underlying locking protocol. This technique allows concurrent
access to the table while providing ANSI serializability. Getting table locks greatly
reduces concurrency.
The settings for these parameters should be changed only when an instance is shut
down. If multiple instances are accessing a single database, then all instances should
use the same setting for these parameters.
See Also:
Oracle Database SQL Reference for information on the SET
TRANSACTION statement
Oracle Database SQL Reference for information on the ALTER
SESSION statements
Locking Data Explicitly
2-12 Oracle Database Application Developer’s Guide - Fundamentals
Explicitly Acquiring Row Locks
You can override default locking with a SELECT statement that includes the FOR
UPDATE clause. This statement acquires exclusive row locks for selected rows (as an
UPDATE statement does), in anticipation of updating the selected rows in a subsequent
statement.
You can use a SELECT... FOR UPDATE statement to lock a row without actually
changing it. For example, several triggers in Chapter 9, "Coding Triggers", show how
to implement referential integrity. In the EMP_DEPT_CHECK trigger (see "Foreign Key
Trigger for Child Table"), the row that contains the referenced parent key value is
locked to guarantee that it remains for the duration of the transaction; if the parent key
is updated or deleted, referential integrity would be violated.
SELECT... FOR UPDATE statements are often used by interactive programs that allow a
user to modify fields of one or more specific rows (which might take some time); row
locks are acquired so that only a single interactive program user is updating the rows
at any given time.
If a SELECT... FOR UPDATE statement is used when defining a cursor, the rows in the
return set are locked when the cursor is opened (before the first fetch) rather than
being locked as they are fetched from the cursor. Locks are only released when the
transaction that opened the cursor is committed or rolled back, not when the cursor is
closed.
Each row in the return set of a SELECT... FOR UPDATE statement is locked
individually; the SELECT... FOR UPDATE statement waits until the other transaction
releases the conflicting row lock. If a SELECT... FOR UPDATE statement locks many
rows in a table, and if the table experiences a lot of update activity, it might be faster to
acquire an EXCLUSIVE table lock instead.
When acquiring row locks with SELECT... FOR UPDATE, you can specify the NOWAIT
option to indicate that you are not willing to wait to acquire the lock. If you cannot
acquire then lock immediately, an error is returned to signal that the lock is not
possible at this time. You can try to lock the row again later.
By default, the transaction waits until the requested row lock is acquired. If the wait
for a row lock is too long, you can code logic into your application to cancel the lock
operation and try again later.
Note: The return set for a SELECT... FOR UPDATE may change
while the query is running; for example, if columns selected by the
query are updated or rows are deleted after the query started. When
this happens, SELECT... FOR UPDATE acquires locks on the rows
that did not change, gets a new read-consistent snapshot of the table
using these locks, and then restarts the query to acquire the remaining
locks.
This can cause a deadlock between sessions querying the table
concurrently with DML operations when rows are locked in a
non-sequential order. To prevent such deadlocks, design your
application so that any concurrent DML on the table does not affect
the return set of the query. If this is not feasible, you may want to
serialize queries in your application.
About User Locks
SQL Processing for Application Developers 2-13
About User Locks
You can use Oracle Lock Management services for your applications by making calls
to the DBMS_LOCK package. It is possible to request a lock of a specific mode, give it a
unique name recognizable in another procedure in the same or another instance,
change the lock mode, and release it. Because a reserved user lock is the same as an
Oracle Database lock, it has all the features of a database lock, such as deadlock
detection. Be certain that any user locks used in distributed transactions are released
upon COMMIT, or an undetected deadlock can occur.
When to Use User Locks
User locks can help to:
Provide exclusive access to a device, such as a terminal
Provide application-level enforcement of read locks
Detect when a lock is released and cleanup after the application
Synchronize applications and enforce sequential processing
Example of a User Lock
The following Pro*COBOL precompiler example shows how locks can be used to
ensure that there are no conflicts when multiple people need to access a single device.
*****************************************************************
* Print Check *
* Any cashier may issue a refund to a customer returning goods. *
* Refunds under $50 are given in cash, more than $50 by check. *
* This code prints the check. The one printer is opened by all *
* the cashiers to avoid the overhead of opening and closing it *
* for every check. This means that lines of output from multiple*
* cashiers could become interleaved if we don't ensure exclusive*
* access to the printer. The DBMS_LOCK package is used to *
* ensure exclusive access. *
*****************************************************************
CHECK-PRINT
* Get the lock "handle" for the printer lock.
MOVE "CHECKPRINT" TO LOCKNAME-ARR.
MOVE 10 TO LOCKNAME-LEN.
EXEC SQL EXECUTE
BEGIN DBMS_LOCK.ALLOCATE_UNIQUE ( :LOCKNAME, :LOCKHANDLE );
END; END-EXEC.
* Lock the printer in exclusive mode (default mode).
EXEC SQL EXECUTE
BEGIN DBMS_LOCK.REQUEST ( :LOCKHANDLE );
END; END-EXEC.
* We now have exclusive use of the printer, print the check.
...
* Unlock the printer so other people can use it
EXEC SQL EXECUTE
BEGIN DBMS_LOCK.RELEASE ( :LOCKHANDLE );
END; END-EXEC.
See Also: Oracle Database PL/SQL Packages and Types Reference for
detailed information on the DBMS_LOCK package
Using Serializable Transactions for Concurrency Control
2-14 Oracle Database Application Developer’s Guide - Fundamentals
Viewing and Monitoring Locks
Table 25 describes Oracle Database facilities to display locking information for
ongoing transactions within an instance.
Using Serializable Transactions for Concurrency Control
By default, Oracle Database permits concurrently executing transactions to modify,
add, or delete rows in the same table, and in the same data block. Changes made by
one transaction are not seen by another concurrent transaction until the transaction
that made the changes commits.
If a transaction A attempts to update or delete a row that has been locked by another
transaction B (by way of a DML or SELECT... FOR UPDATE statement), then A's DML
command blocks until B commits or rolls back. Once B commits, transaction A can see
changes that B has made to the database.
For most applications, this concurrency model is the appropriate one, because it
provides higher concurrency and thus better performance. But some rare cases require
transactions to be serializable. Serializable transactions must execute in such a way
that they appear to be executing one at a time (serially), rather than concurrently.
Concurrent transactions executing in serialized mode can only make database changes
that they could have made if the transactions ran one after the other.
Figure 2–1 shows a serializable transaction (B) interacting with another transaction
(A).
The ANSI/ISO SQL standard SQL92 defines three possible kinds of transaction
interaction, and four levels of isolation that provide increasing protection against these
interactions. These interactions and isolation levels are summarized in Table 24.
Table 2–3 Ways to Display Locking Information
Tool Description
Oracle Enterprise
Manager 10g Database
Control
From the Additional Monitoring Links section of the Database
Performance page, click Database Locks to display user blocks,
blocking locks, or the complete list of all database locks. Refer to
Oracle Database 2 Day DBA for more information.
UTLLOCKT.SQL The UTLLOCKT.SQL script displays a simple character lock wait-for
graph in tree structured fashion. Using any ad hoc SQL tool (such as
SQL*Plus) to execute the script, it prints the sessions in the system
that are waiting for locks and the corresponding blocking locks. The
location of this script file is operating system dependent. (You must
have run the CATBLOCK.SQL script before using UTLLOCKT.SQL.)
Table 2–4 Summary of ANSI Isolation Levels
Isolation Level Dirty Read1
1A transaction can read uncommitted data changed by another transaction.
Non-Repeatable Read2
2A transaction rereads data committed by another transaction and sees the new data.
Phantom Read3
3A transaction can execute a query again, and discover new rows inserted by another
committed transaction.
READ UNCOMMITTED Possible Possible Possible
READ COMMITTED Not possible Possible Possible
REPEATABLE READ Not possible Not possible Possible
SERIALIZABLE Not possible Not possible Not possible
Using Serializable Transactions for Concurrency Control
SQL Processing for Application Developers 2-15
The behavior of Oracle Database with respect to these isolation levels is summarized
in Table 25.
How Serializable Transactions Interact
Figure 2–1 on page 2-16 shows how a serializable transaction (Transaction B) interacts
with another transaction (A, which can be either SERIALIZABLE or READ
COMMITTED).
When a serializable transaction fails with an ORA-08177 error ("cannot serialize
access"), the application can take any of several actions:
Commit the work executed to that point
Execute additional, different, statements, perhaps after rolling back to a prior
savepoint in the transaction
Roll back the entire transaction and try it again
Oracle Database stores control information in each data block to manage access by
concurrent transactions. To use the SERIALIZABLE isolation level, you must use the
INITRANS clause of the CREATE TABLE or ALTER TABLE command to set aside
storage for this control information. To use serializable mode, INITRANS must be set
to at least 3.
Table 2–5 ANSI Isolation Levels and Oracle Database
Isolation Level Description
READ UNCOMMITTED Oracle Database never permits "dirty reads." Although some other
database products use this undesirable technique to improve
thoughput, it is not required for high throughput with Oracle Database.
READ COMMITTED Oracle Database meets the READ COMMITTED isolation standard. This is
the default mode for all Oracle Database applications. Because an
Oracle Database query only sees data that was committed at the
beginning of the query (the snapshot time), Oracle Database actually
offers more consistency than is required by the ANSI/ISO SQL92
standards for READ COMMITTED isolation.
REPEATABLE READ Oracle Database does not normally support this isolation level, except
as provided by SERIALIZABLE.
SERIALIZABLE Oracle Database does not normally support this isolation level, except
as provided by SERIALIZABLE.
Using Serializable Transactions for Concurrency Control
2-16 Oracle Database Application Developer’s Guide - Fundamentals
Figure 2–1 Time Line for Two Transactions
Setting the Isolation Level of a Transaction
You can change the isolation level of a transaction using the ISOLATION LEVEL clause
of the SET TRANSACTION command, which must be the first command issued in a
transaction.
Use the ALTER SESSION command to set the transaction isolation level on a
session-wide basis.
The INITRANS Parameter
Oracle Database stores control information in each data block to manage access by
concurrent transactions. Therefore, if you set the transaction isolation level to
See Also: Oracle Database Reference for the complete syntax of the
SET TRANSACTION and ALTER SESSION commands
TRANSACTION A
(arbitrary)
begin work
update row 2
in block 1
Issue update "too recent"
for B to see
TIME
TRANSACTION B
(serializable)
SET TRANSACTION
ISOLATION LEVEL
SERIALIZABLE
read row 1 in block 1
Change other row in
same block, see own
changes
update row 1 in block 1
read updated row 1 in
block 1
insert row 4 Create possible
"phantom" row
Uncommitted changes
invisible
read old row 2 in block 1
search for row 4
(notfound)
commit
Make changes visible
to transactions that
begin later
Make changes
after A commits update row 3 in block 1
B can see its own changes
but not the committed
changes of transaction A.
re-read updated row 1
in block 1
search for row 4 (not found)
read old row 2 in block 1
Failure on attempt to update
row updated and committed
since transaction B began
update row 2 in block 1
FAILS; rollback and retry
Using Serializable Transactions for Concurrency Control
SQL Processing for Application Developers 2-17
SERIALIZABLE, then you must use the ALTER TABLE command to set INITRANS to
at least 3. This parameter causes Oracle Database to allocate sufficient storage in each
block to record the history of recent transactions that accessed the block. Higher values
should be used for tables that will undergo many transactions updating the same
blocks.
Referential Integrity and Serializable Transactions
Because Oracle Database does not use read locks, even in SERIALIZABLE
transactions, data read by one transaction can be overwritten by another. Transactions
that perform database consistency checks at the application level should not assume
that the data they read will not change during the execution of the transaction (even
though such changes are not visible to the transaction). Database inconsistencies can
result unless such application-level consistency checks are coded carefully, even when
using SERIALIZABLE transactions. Note, however, that the examples shown in this
section are applicable for both READ COMMITTED and SERIALIZABLE transactions.
Figure 2–2 on page 2-17 shows two different transactions that perform
application-level checks to maintain the referential integrity parent/child relationship
between two tables. One transaction checks that a row with a specific primary key
value exists in the parent table before inserting corresponding child rows. The other
transaction checks to see that no corresponding detail rows exist before deleting a
parent row. In this case, both transactions assume (but do not ensure) that data they
read will not change before the transaction completes.
Figure 2–2 Referential Integrity Check
The read issued by transaction A does not prevent transaction B from deleting the
parent row, and transaction B's query for child rows does not prevent transaction A
from inserting child rows. This scenario leaves a child row in the database with no
corresponding parent row. This result occurs even if both A and B are SERIALIZABLE
transactions, because neither transaction prevents the other from making changes in
the data it reads to check consistency.
TRANSACTION A TRANSACTION B
read parent (it exists) read child rows (not found)
insert child row(s) delete parent
commit work commit work
A's query does
not prevent this
delete
B's query does
not prevent this
insert
Using Serializable Transactions for Concurrency Control
2-18 Oracle Database Application Developer’s Guide - Fundamentals
As this example shows, sometimes you must take steps to ensure that the data read by
one transaction is not concurrently written by another. This requires a greater degree
of transaction isolation than defined by SQL92 SERIALIZABLE mode.
Using SELECT FOR UPDATE
Fortunately, it is straightforward in Oracle Database to prevent the anomaly described:
Transaction A can use SELECT FOR UPDATE to query and lock the parent row and
thereby prevent transaction B from deleting the row.
Transaction B can prevent Transaction A from gaining access to the parent row by
reversing the order of its processing steps. Transaction B first deletes the parent
row, and then rolls back if its subsequent query detects the presence of
corresponding rows in the child table.
Referential integrity can also be enforced in Oracle Database using database triggers,
instead of a separate query as in Transaction A. For example, an INSERT into the child
table can fire a BEFORE INSERT row-level trigger to check for the corresponding
parent row. The trigger queries the parent table using SELECT FOR UPDATE, ensuring
that parent row (if it exists) remains in the database for the duration of the transaction
inserting the child row. If the corresponding parent row does not exist, the trigger
rejects the insert of the child row.
SQL statements issued by a database trigger execute in the context of the SQL
statement that caused the trigger to fire. All SQL statements executed within a trigger
see the database in the same state as the triggering statement. Thus, in a READ
COMMITTED transaction, the SQL statements in a trigger see the database as of the
beginning of the triggering statement execution, and in a transaction executing in
SERIALIZABLE mode, the SQL statements see the database as of the beginning of the
transaction. In either case, the use of SELECT FOR UPDATE by the trigger correctly
enforces referential integrity.
READ COMMITTED and SERIALIZABLE Isolation
Oracle Database gives you a choice of two transaction isolation levels with different
characteristics. Both the READ COMMITTED and SERIALIZABLE isolation levels
provide a high degree of consistency and concurrency. Both levels reduce contention,
and are designed for deploying real-world applications. The rest of this section
compares the two isolation modes and provides information helpful in choosing
between them.
Transaction Set Consistency
A useful way to describe the READ COMMITTED and SERIALIZABLE isolation levels in
Oracle Database is to consider:
A collection of database tables (or any set of data)
A sequence of reads of rows in those tables
The set of transactions committed at any moment
An operation (a query or a transaction) is transaction set consistent if its read
operations all return data written by the same set of committed transactions. When an
operation is not transaction set consistent, some reads reflect the changes of one set of
transactions, and other reads reflect changes made by other transactions. Such an
operation sees the database in a state that reflects no single set of committed
transactions.
Using Serializable Transactions for Concurrency Control
SQL Processing for Application Developers 2-19
Oracle Database transactions executing in READ COMMITTED mode are transaction-set
consistent on an individual-statement basis, because all rows read by a query must be
committed before the query begins.
Oracle Database transactions executing in SERIALIZABLE mode are transaction set
consistent on an individual-transaction basis, because all statements in a
SERIALIZABLE transaction execute on an image of the database as of the beginning of
the transaction.
In other database systems, a single query run in READ COMMITTED mode provides
results that are not transaction set consistent. The query is not transaction set
consistent, because it may see only a subset of the changes made by another
transaction. For example, a join of a master table with a detail table could see a master
record inserted by another transaction, but not the corresponding details inserted by
that transaction, or vice versa. The READ COMMITTED mode avoids this problem, and
so provides a greater degree of consistency than read-locking systems.
In read-locking systems, at the cost of preventing concurrent updates, SQL92
REPEATABLE READ isolation provides transaction set consistency at the statement
level, but not at the transaction level. The absence of phantom protection means two
queries issued by the same transaction can see data committed by different sets of
other transactions. Only the throughput-limiting and deadlock-susceptible
SERIALIZABLE mode in these systems provides transaction set consistency at the
transaction level.
Comparison of READ COMMITTED and SERIALIZABLE Transactions
Table 26 summarizes key similarities and differences between READ COMMITTED and
SERIALIZABLE transactions.
Table 2–6 Read Committed Versus Serializable Transaction
Operation Read Committed Serializable
Dirty write Not Possible Not Possible
Dirty read Not Possible Not Possible
Non-repeatable read Possible Not Possible
Phantoms Possible Not Possible
Compliant with ANSI/ISO SQL 92 Yes Yes
Read snapshot time Statement Transaction
Transaction set consistency Statement level Transaction level
Row-level locking Yes Yes
Readers block writers No No
Writers block readers No No
Different-row writers block writers No No
Same-row writers block writers Yes Yes
Waits for blocking transaction Yes Yes
Subject to "can't serialize access" error No Yes
Error after blocking transaction aborts No No
Error after blocking transaction commits No Yes
Autonomous Transactions
2-20 Oracle Database Application Developer’s Guide - Fundamentals
Choosing an Isolation Level for Transactions
Choose an isolation level that is appropriate to the specific application and workload.
You might choose different isolation levels for different transactions. The choice
depends on performance and consistency needs, and consideration of application
coding requirements.
For environments with many concurrent users rapidly submitting transactions, you
must assess transaction performance against the expected transaction arrival rate and
response time demands, and choose an isolation level that provides the required
degree of consistency while performing well. Frequently, for high performance
environments, you must trade-off between consistency and concurrency (transaction
throughput).
Both Oracle Database isolation modes provide high levels of consistency and
concurrency (and performance) through the combination of row-level loc