Contents

Title and Copyright Information

Send Us Your Comments

Preface

Related Documentation

Documentation Accessibility

What's New in Application Development?

New Application Development Features in Oracle Database 10g Release 1

New Application Development Features in Oracle9i Release 2

New Application Development Features in Oracle9i Release 1

Part I Introduction to Application Development Features of Oracle Database

1 Programmatic Environments

Overview of Developing an Oracle Database Application

Overview of PL/SQL

A Simple PL/SQL Example

Advantages of PL/SQL

Full Support for SQL

Tight Integration with Oracle Database

Better Performance

Higher Productivity

Maintainability

PL/SQL Support for Object-Oriented Programming

Built-In Packages for Application Development

Built-In Packages for Server Management

Built-In Packages for Distributed Database Access

Overview of Java Support Built Into the Database

Overview of Pro*C/C++

How You Implement a Pro*C/C++ Application

Highlights of Pro*C/C++ Features

Overview of Pro*COBOL

How You Implement a Pro*COBOL Application

Highlights of Pro*COBOL Features

Overview of OCI and OCCI

Advantages of OCI

Parts of the OCI

Procedural and Non-Procedural Elements

Building an OCI Application

Overview of Oracle Data Provider for .NET (ODP.NET)

Using ODP.NET in a Simple Application

Overview of Oracle Objects for OLE (OO4O)

Choosing a Programming Environment

Choosing Whether to Use OCI or a Precompiler

Using Built-In Packages and Libraries

Java Compared to PL/SQL

PL/SQL Is Optimized for Database Access

PL/SQL Is Integrated with the Database

Both Java and PL/SQL Have Object-Oriented Features

Java Is Used for Open Distributed Applications

Part II Designing the Database

2 Selecting a Datatype

Summary of Oracle Built-In Datatypes

Representing Character Data

Column Lengths for Single-Byte and Multibyte Character Sets

Implicit Conversion Between CHAR/VARCHAR2 and NCHAR/NVARCHAR2

Comparison Semantics

Representing Numeric Data with Number and Floating-Point Datatypes

Floating-Point Number System Concepts

About Floating-Point Formats

Representing Special Values with Native Floating-Point Formats

Behavior of Special Values for Native Floating-Point Datatypes

Rounding of Native Floating-Point Datatypes

Comparison Operators for Native Floating-Point Datatypes

Arithmetic Operators for Native Floating-Point Datatypes

Conversion Functions for Native Floating-Point Datatypes

Exceptions for Native Floating-Point Datatypes

Client Interfaces for Native Floating-Point Datatypes

SQL Native Floating-Point Datatypes

OCI Native Floating-Point Datatypes SQLT_BFLOAT and SQLT_BDOUBLE

Native Floating-Point Datatypes Supported in Oracle OBJECT Types

Pro*C/C++ Support for Native Floating-Point Datatypes

Storing Data Using the NUMBER Datatype

Representing Date and Time Data

Checking If Two DATE Values Refer to the Same Day

Displaying the Current Date and Time

Setting SYSDATE to a Constant Value

Printing a Date with BC/AD Notation

Performing Date Arithmetic

Converting Between Datetime Types

Handling Time Zones

Importing and Exporting Datetime Types

Establishing Year 2000 Compliance

Oracle Server Year 2000 Compliance

Centuries and the Year 2000

Examples of The RR Date Format

Examples of The CC Date Format

Storing Dates in Character Datatypes

Viewing Date Settings

Altering Date Settings

Troubleshooting Y2K Problems in Applications

Representing Conditional Expressions as Data

Representing Geographic Coordinate Data

Representing Image, Audio, and Video Data

Representing Searchable Text Data

Representing Large Amounts of Data

Using RAW and LONG RAW Datatypes

Addressing Rows Directly with the ROWID Datatype

Extended ROWID Format

Different Forms of the ROWID

ROWID Pseudocolumn

External Character ROWID

External Binary ROWID

ROWID Migration and Compatibility Issues

Accessing Oracle Database Version 7 from an Oracle9i Client

Accessing an Oracle9i Database from a Client of Oracle Database Version 7

Import and Export

ANSI/ISO, DB2, and SQL/DS Datatypes

How Oracle Database Converts Datatypes

Datatype Conversion During Assignments

Datatype Conversion During Expression Evaluation

Representing Dynamically Typed Data

Representing XML Data

3 Maintaining Data Integrity Through Constraints

Overview of Integrity Constraints

When to Enforce Business Rules with Integrity Constraints

Example of an Integrity Constraint for a Business Rule

When to Enforce Business Rules in Applications

Creating Indexes for Use with Constraints

When to Use NOT NULL Integrity Constraints

When to Use Default Column Values

Setting Default Column Values

Choosing a Table's Primary Key

When to Use UNIQUE Key Integrity Constraints

Constraints On Views: for Performance, Not Data Integrity

Enforcing Referential Integrity with Constraints

About Nulls and Foreign Keys

Defining Relationships Between Parent and Child Tables

No Constraints on the Foreign Key

NOT NULL Constraint on the Foreign Key

UNIQUE Constraint on the Foreign Key

UNIQUE and NOT NULL Constraints on the Foreign Key

Rules for Multiple FOREIGN KEY Constraints

Deferring Constraint Checks

Guidelines for Deferring Constraint Checks

Select Appropriate Data

Ensure Constraints Are Created Deferrable

Set All Constraints Deferred

Check the Commit (Optional)

Managing Constraints That Have Associated Indexes

Minimizing Space and Time Overhead for Indexes Associated with Constraints

Guidelines for Indexing Foreign Keys

About Referential Integrity in a Distributed Database

When to Use CHECK Integrity Constraints

Restrictions on CHECK Constraints

Designing CHECK Constraints

Rules for Multiple CHECK Constraints

Choosing Between CHECK and NOT NULL Integrity Constraints

Examples of Defining Integrity Constraints

Example: Defining Integrity Constraints with the CREATE TABLE Command

Example: Defining Constraints with the ALTER TABLE Command

Privileges Required to Create Constraints

Naming Integrity Constraints

Enabling and Disabling Integrity Constraints

Why Disable Constraints?

About Exceptions to Integrity Constraints

Enabling Constraints

Creating Disabled Constraints

Enabling and Disabling Existing Integrity Constraints

Enabling Existing Constraints

Disabling Existing Constraints

Tip: Using the Data Dictionary to Find Constraints

Guidelines for Enabling and Disabling Key Integrity Constraints

Fixing Constraint Exceptions

Altering Integrity Constraints

Renaming Integrity Constraints

Dropping Integrity Constraints

Managing FOREIGN KEY Integrity Constraints

Datatypes and Names for Foreign Key Columns

Limit on Columns in Composite Foreign Keys

Foreign Key References Primary Key by Default

Privileges Required to Create FOREIGN KEY Integrity Constraints

Choosing How Foreign Keys Enforce Referential Integrity

Viewing Definitions of Integrity Constraints

Examples of Defining Integrity Constraints

Example 1: Listing All of Your Accessible Constraints

Example 2: Distinguishing NOT NULL Constraints from CHECK Constraints

Example 3: Listing Column Names that Constitute an Integrity Constraint

4 Selecting an Index Strategy

Guidelines for Application-Specific Indexes

Create Indexes After Inserting Table Data

Switch Your Temporary Tablespace to Avoid Space Problems Creating Indexes

Index the Correct Tables and Columns

Limit the Number of Indexes for Each Table

Choose the Order of Columns in Composite Indexes

Gather Statistics to Make Index Usage More Accurate

Drop Indexes That Are No Longer Required

Privileges Required to Create an Index

Creating Indexes: Basic Examples

When to Use Domain Indexes

When to Use Function-Based Indexes

Advantages of Function-Based Indexes

Examples of Function-Based Indexes

Example: Function-Based Index for Case-Insensitive Searches

Example: Precomputing Arithmetic Expressions with a Function-Based Index

Example: Function-Based Index for Language-Dependent Sorting

Restrictions for Function-Based Indexes

5 How Oracle Database Processes SQL Statements

Overview of SQL Statement Execution

Identifying Extensions to SQL92 (FIPS Flagging)

Grouping Operations into Transactions

Improving Transaction Performance

Committing Transactions

Rolling Back Transactions

Defining Transaction Savepoints

An Example of COMMIT, SAVEPOINT, and ROLLBACK

Privileges Required for Transaction Management

Ensuring Repeatable Reads with Read-Only Transactions

Using Cursors within Applications

Declaring and Opening Cursors

Using a Cursor to Execute Statements Again

Closing Cursors

Cancelling Cursors

Locking Data Explicitly

Choosing a Locking Strategy

When to Lock with ROW SHARE and ROW EXCLUSIVE Mode

When to Lock with SHARE Mode

When to Lock with SHARE ROW EXCLUSIVE Mode

When to Lock in EXCLUSIVE Mode

Privileges Required

Letting Oracle Database Control Table Locking

Explicitly Acquiring Row Locks

About User Locks

When to Use User Locks

Example of a User Lock

Viewing and Monitoring Locks

Using Serializable Transactions for Concurrency Control

How Serializable Transactions Interact

Setting the Isolation Level of a Transaction

The INITRANS Parameter

Referential Integrity and Serializable Transactions

Using SELECT FOR UPDATE

READ COMMITTED and SERIALIZABLE Isolation

Transaction Set Consistency

Comparison of READ COMMITTED and SERIALIZABLE Transactions

Choosing an Isolation Level for Transactions

Application Tips for Transactions

Autonomous Transactions

Examples of Autonomous Transactions

Entering a Buy Order

Example: Making a Bank Withdrawal

Defining Autonomous Transactions

Restrictions on Autonomous Transactions

Resuming Execution After a Storage Error Condition

What Operations Can Be Resumed After an Error Condition?

Limitations on Resuming Operations After an Error Condition

Writing an Application to Handle Suspended Storage Allocation

Example of Resumable Storage Allocation

6 Coding Dynamic SQL Statements

What Is Dynamic SQL?

Why Use Dynamic SQL?

Executing DDL and SCL Statements in PL/SQL

Executing Dynamic Queries

Referencing Database Objects that Do Not Exist at Compilation

Optimizing Execution Dynamically

Executing Dynamic PL/SQL Blocks

Performing Dynamic Operations Using Invoker's Rights

A Dynamic SQL Scenario Using Native Dynamic SQL

Sample DML Operation Using Native Dynamic SQL

Sample DDL Operation Using Native Dynamic SQL

Sample Single-Row Query Using Native Dynamic SQL

Sample Multiple-Row Query Using Native Dynamic SQL

Choosing Between Native Dynamic SQL and the DBMS_SQL Package

Advantages of Native Dynamic SQL

Native Dynamic SQL is Easy to Use

Native Dynamic SQL is Faster than DBMS_SQL

Performance Tip: Using Bind Variables

Native Dynamic SQL Supports User-Defined Types

Native Dynamic SQL Supports Fetching Into Records

Advantages of the DBMS_SQL Package

DBMS_SQL is Supported in Client-Side Programs

DBMS_SQL Supports DESCRIBE

DBMS_SQL Supports SQL Statements Larger than 32KB

DBMS_SQL Lets You Reuse SQL Statements

Examples of DBMS_SQL Package Code and Native Dynamic SQL Code

Querying Using Dynamic SQL: Example

Performing DML Using Dynamic SQL: Example

Performing DML with RETURNING Clause Using Dynamic SQL: Example

Using Dynamic SQL in Languages Other Than PL/SQL

7 Using Procedures and Packages

Overview of PL/SQL Program Units

Anonymous Blocks

Stored Program Units (Procedures, Functions, and Packages)

Naming Procedures and Functions

Parameters for Procedures and Functions

Parameter Modes

Parameter Datatypes

%TYPE and %ROWTYPE Attributes

Tables and Records

Default Parameter Values

Creating Stored Procedures and Functions

Privileges to Create Procedures and Functions

Altering Stored Procedures and Functions

Dropping Procedures and Functions

Privileges to Drop Procedures and Functions

External Procedures

PL/SQL Packages

Example of a PL/SQL Package Specification and Body

PL/SQL Object Size Limitation

Size Limitation by Version

Creating Packages

Creating Packaged Objects

Privileges to Create or Drop Packages

Naming Packages and Package Objects

Package Invalidations and Session State

Packages Supplied With Oracle Database

Overview of Bulk Binds

When to Use Bulk Binds

DML Statements that Reference Collections

SELECT Statements that Reference Collections

FOR Loops that Reference Collections and the Returning Into Clause

Hiding PL/SQL Code with the PL/SQL Wrapper

Compiling PL/SQL Procedures for Native Execution

Remote Dependencies

Disadvantages of the Timestamp Model

When Does a Signature Change?

Default Parameter Values

Examples of Changing Procedure Signatures

Controlling Remote Dependencies

Dependency Resolution

Suggestions for Managing Dependencies

Cursor Variables

Declaring and Opening Cursor Variables

Examples of Cursor Variables

Implementing Variant Records

Handling PL/SQL Compile-Time Errors

Handling Run-Time PL/SQL Errors

Declaring Exceptions and Exception Handling Routines

Unhandled Exceptions

Handling Errors in Distributed Queries

Handling Errors in Remote Procedures

Debugging Stored Procedures

Calling Stored Procedures

A Procedure or Trigger Calling Another Procedure

Interactively Calling Procedures From Oracle Database Tools

Calling Procedures within 3GL Applications

Name Resolution When Calling Procedures

Privileges Required to Execute a Procedure

Specifying Values for Procedure Arguments

Calling Remote Procedures

Remote Procedure Calls and Parameter Values

Referencing Remote Objects

Synonyms for Procedures and Packages

Calling Stored Functions from SQL Expressions

Using PL/SQL Functions

Syntax for SQL Calling a PL/SQL Function

Naming Conventions

Name Precedence

Example of Calling a PL/SQL Function from SQL

Using Default Values

Requirements for Calling PL/SQL Functions from SQL Expressions

Controlling Side Effects

Declaring a Function

Parallel Query and Parallel DML

PRAGMA RESTRICT_REFERENCES - for Backward Compatibility

Using the Keyword TRUST

Differences between Static and Dynamic SQL Statements.

Overloading Packaged PL/SQL Functions

Serially Reusable PL/SQL Packages

Why Serially Reusable Packages?

Syntax of Serially Reusable Packages

Semantics of Serially Reusable Packages

Examples of Serially Reusable Packages

Example 1: How Package Variables Act Across Call Boundaries

Example 2: How Package Variables Act Across Call Boundaries

Example 3: Open Cursors in Serially Reusable Packages at Call Boundaries

Returning Large Amounts of Data from a Function

Coding Your Own Aggregate Functions

8 Calling External Procedures

Overview of Multi-Language Programs

What Is an External Procedure?

Overview of The Call Specification for External Procedures

Loading External Procedures

Loading Java Class Methods

Loading External C Procedures

Publishing External Procedures

The AS LANGUAGE Clause for Java Class Methods

The AS LANGUAGE Clause for External C Procedures

CALLING STANDARD

Publishing Java Class Methods

Publishing External C Procedures

Locations of Call Specifications

Example: Locating a Call Specification in a PL/SQL Package Body

Example: Locating a Call Specification in an Object Type Specification

Example: Locating a Call Specification in an Object Type Body

Passing Parameters to External C Procedures with Call Specifications

Specifying Datatypes

External Datatype Mappings

BY VALUE/REFERENCE for IN and IN OUT Parameter Modes

The PARAMETERS Clause

Overriding Default Datatype Mapping

Specifying Properties

LENGTH and MAXLEN

CHARSETID and CHARSETFORM

Repositioning Parameters

Passing Parameters by Reference

Inter-Language Parameter Mode Mappings

Executing External Procedures with the CALL Statement

Preconditions for External Procedures

Privileges of External Procedures

Managing Permissions

Creating Synonyms for External Procedures

CALL Statement Syntax

Calling Java Class Methods

How the Database Server Calls External C Procedures

Handling Errors and Exceptions in Multi-Language Programs

Generic Compile Time Call specification Errors

C Exception Handling

Using Service Procedures with External C Procedures

OCIExtProcAllocCallMemory

OCIExtProcRaiseExcp

OCIExtProcRaiseExcpWithMsg

Doing Callbacks with External C Procedures

OCIExtProcGetEnv

Object Support for OCI Callbacks

Restrictions on Callbacks

Debugging External Procedures

Using Package DEBUG_EXTPROC

Guidelines for External C Procedures

Restrictions on External C Procedures

Part III The Active Database

9 Using Triggers

Designing Triggers

Creating Triggers

Types of Triggers

Overview of System Events

Getting the Attributes of System Events

Naming Triggers

When Is the Trigger Fired?

Do Import and SQL*Loader Fire Triggers?

How Column Lists Affect UPDATE Triggers

Controlling When a Trigger Is Fired (BEFORE and AFTER Options)

Ordering of Triggers

Modifying Complex Views (INSTEAD OF Triggers)

Views that Require INSTEAD OF Triggers

INSTEAD OF Trigger Example

Object Views and INSTEAD OF Triggers

Triggers on Nested Table View Columns

Firing Triggers One or Many Times (FOR EACH ROW Option)

Firing Triggers Based on Conditions (WHEN Clause)

Coding the Trigger Body

Example: Monitoring Logons with a Trigger

Example: Calling a Java Procedure from a Trigger

Accessing Column Values in Row Triggers

Example: Modifying LOB Columns with a Trigger

INSTEAD OF Triggers on Nested Table View Columns

Avoiding Name Conflicts with Triggers (REFERENCING Option)

Detecting the DML Operation That Fired a Trigger

Error Conditions and Exceptions in the Trigger Body

Triggers and Handling Remote Exceptions

Restrictions on Creating Triggers

Who Is the Trigger User?

Privileges Needed to Work with Triggers

Compiling Triggers

Dependencies for Triggers

Recompiling Triggers

Modifying Triggers

Debugging Triggers

Enabling and Disabling Triggers

Enabling Triggers

Disabling Triggers

Viewing Information About Triggers

Examples of Trigger Applications

Auditing with Triggers: Example

Integrity Constraints and Triggers: Examples

Referential Integrity Using Triggers

Foreign Key Trigger for Child Table

UPDATE and DELETE RESTRICT Trigger for Parent Table

UPDATE and DELETE SET NULL Triggers for Parent Table: Example

DELETE Cascade Trigger for Parent Table: Example

UPDATE Cascade Trigger for Parent Table: Example

Trigger for Complex Check Constraints: Example

Complex Security Authorizations and Triggers: Example

Transparent Event Logging and Triggers

Derived Column Values and Triggers: Example

Building Complex Updatable Views Using Triggers: Example

Tracking System Events Using Triggers

Fine-Grained Access Control Using Triggers: Example

Responding to System Events through Triggers

10 Working With System Events

Event Attribute Functions

List of Database Events

11 Using the Publish-Subscribe Model for Applications

Introduction to Publish-Subscribe

Publish-Subscribe Architecture

Publish-Subscribe Concepts

Examples of a Publish-Subscribe Mechanism

Part IV Developing Specialized Applications

12 Using Regular Expressions With Oracle Database

What are Regular Expressions?

Oracle Database Regular Expression Support

Oracle Database SQL Functions for Regular Expressions

Metacharacters Supported in Regular Expressions

Constructing Regular Expressions

Basic String Matching with Regular Expressions

Regular Expression Operations on Subexpressions

Regular Expression Operator and Metacharacter Usage

13 Developing Web Applications with PL/SQL

PL/SQL Web Applications

PL/SQL Gateway

Configuring mod_plsql

Uploading and Downloading Files With PL/SQL Gateway

Uploading Files to the Database

Downloading Files From the Database

Custom Authentication With PL/SQL Gateway

PL/SQL Web Toolkit

Generating HTML Output from PL/SQL

Passing Parameters to a PL/SQL Web Application

Passing List and Dropdown List Parameters from an HTML Form

Passing Radio Button and Checkbox Parameters from an HTML Form

Passing Entry Field Parameters from an HTML Form

Passing Hidden Parameters from an HTML Form

Uploading a File from an HTML Form

Submitting a Completed HTML Form

Handling Missing Input from an HTML Form

Maintaining State Information Between Web Pages

Performing Network Operations within PL/SQL Stored Procedures

Sending E-Mail from PL/SQL

Getting a Host Name or Address from PL/SQL

Working with TCP/IP Connections from PL/SQL

Retrieving the Contents of an HTTP URL from PL/SQL

Working with Tables, Image Maps, Cookies, and CGI Variables from PL/SQL

Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)

Enabling PL/SQL Web Applications for XML

14 Porting Non-Oracle Applications to Oracle Database 10g

Performing Natural Joins and Inner Joins

Migrating a Schema and Data from Another Database System

Performing Several Comparisons within a Query

15 Using Flashback Features

Overview of Flashback Features

Application Development Features

Database Administration Features

Database Administration Tasks Before Using Flashback Features

Using Flashback Query (SELECT ... AS OF)

Examining Past Data: Example

Tips for Using Flashback Query

Using the DBMS_FLASHBACK Package

Using ORA_ROWSCN

Using Flashback Version Query

Using Flashback Transaction Query

Flashback Transaction Query and Flashback Version Query: Example

Flashback Tips

Flashback Tips - Performance

Flashback Tips - General

16 Using Oracle XA with Transaction Monitors

X/Open Distributed Transaction Processing (DTP)

Required Public Information

XA and the Two-Phase Commit Protocol

Transaction Processing Monitors (TPMs)

Support for Dynamic and Static Registration

Oracle XA Library Interface Subroutines

XA Library Subroutines

Extensions to the XA Interface

Developing and Installing Applications That Use the XA Libraries

Responsibilities of the DBA or System Administrator

Responsibilities of the Application Developer

Defining the xa_open String

Syntax of the xa_open String

Required Fields

Optional Fields

Interfacing XA with Precompilers and OCIs

Using Precompilers with the Oracle XA Library

Using Precompilers with the Default Database

Using Precompilers with a Named Database

Using OCI with the Oracle XA Library

Transaction Control using XA

Examples of Precompiler Applications

Migrating Precompiler or OCI Applications to TPM Applications

XA Library Thread Safety

Specifying Threading in the Open String

Restrictions on Threading in XA

Troubleshooting XA Applications

The xa_open string DbgFl

Trace File Locations

Trace File Examples

In-Doubt or Pending Transactions

Oracle Database SYS Account Tables

XA Issues and Restrictions

Changes to Oracle XA Support

XA Changes from Release 8.0 to Release 8.1

XA Changes from Release 7.3 to Release 8.0

Session Caching Is No Longer Needed

Dynamic Registration Is Supported

Loosely Coupled Transaction Branches Are Supported

SQLLIB Is Not Needed for OCI Applications

No Installation Script Is Needed to Run XA

XA Library Use with Oracle Real Application Clusters Option on All Platforms

Transaction Recovery for Oracle Real Application Clusters Has Been Improved

Both Global and Local Transactions Are Possible

The xa_open String Has Been Modified

Index