Contents

List of Examples

List of Figures

List of Tables

Title and Copyright Information

Send Us Your Comments

Preface

Documentation Accessibility

Related Documents

What's New in Application Development?

Oracle Database 10g Release 2 (10.2) New Features

Oracle Database 10g Release 1 (10.1) New Features

1 Orientation to Oracle Programmatic Environments

Overview of Oracle Application Development

Overview of PL/SQL

What Is PL/SQL?

Advantages of PL/SQL

Integration with Oracle Database

High Performance

High Productivity

Object-Oriented Programming Support

Built-In Packages

PL/SQL Web Development Tools

Overview of Java Support Built Into the Database

Overview of Oracle JVM

Overview of Oracle Extensions to JDBC

JDBC Thin Driver

JDBC OCI Driver

JDBC Server-Side Internal Driver

Oracle Database Extensions to JDBC Standards

Sample JDBC 2.0 Program

Sample Pre-2.0 JDBC Program

JDBC in SQLJ Applications

Overview of Oracle SQLJ

Benefits of SQLJ

Comparing SQLJ with JDBC

SQLJ Stored Procedures in the Server

Overview of Oracle JPublisher

Overview of Java Stored Procedures

Overview of Oracle Database Web Services

Oracle Database as a Web Service Provider

Overview of Writing Procedures and Functions in Java

Overview of Writing Database Triggers in Java

Why Use Java for Stored Procedures and Triggers?

Overview of Pro*C/C++

Implementing 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)

OO4O Automation Server

OO4O Object Model

OraMetaData and OraMDAttribute

OraParameters and OraParameter

Support for Oracle LOB and Object Datatypes

OraBLOB and OraCLOB

Oracle Data Control

Oracle Objects for OLE C++ Class Library

Additional Sources of Information

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 I SQL for Application Developers

2 SQL Processing for Application Developers

Grouping Operations into Transactions

Deciding How to Group Operations in Transactions

Improving Transaction Performance

Committing Transactions

Managing Commit Redo Behavior

Rolling Back Transactions

Defining Transaction Savepoints

An Example of COMMIT, SAVEPOINT, and ROLLBACK

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

3 Using SQL Datatypes in Application Development

Representing Data with SQL Datatypes: Overview

Representing Character Data

Representing Character Data: Overview

Specifying Column Lengths as Bytes or Characters

Choosing Between the CHAR and VARCHAR2 Datatypes

Using Character Literals in SQL Statements

Quoting Character Literals

Representing Numeric Data

What Are the Numeric Datatypes?

Using Floating-Point Number Formats

Using a Floating-Point Binary Format

Representing Special Values with Native Floating-Point Formats

Using Comparison Operators for Native Floating-Point Datatypes

Performing Arithmetic Operations with Native Floating-Point Datatypes

Using Conversion Functions with Native Floating-Point Datatypes

Client Interfaces for 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

Representing Datetime Data

Representing Datetime Data: Overview

Using the DATE Datatype

Using the TIMESTAMP Datatype

Using the TIMESTAMP WITH TIME ZONE Datatype

Using the TIMESTAMP WITH LOCAL TIME ZONE Datatype

Representing the Difference Between Datetime Values

Manipulating the Date Format

Changing the Default Date Format

Displaying the Current Date and Time

Manipulating the Time Format

Performing Date Arithmetic

Converting Between Datetime Types

Importing and Exporting Datetime Types

Representing Specialized Data

Representing Geographic Data

Representing Multimedia Data

Representing Large Amounts of Data

Using RAW and LONG RAW Datatypes

Representing Searchable Text

Representing XML

Representing Dynamically Typed Data

Representing Data with ANSI/ISO, DB2, and SQL/DS Datatypes

Representing Conditional Expressions as Data

Identifying Rows by Address

Querying the ROWID Pseudocolumn

Accessing the ROWID Datatype

Restricted ROWID

External Binary ROWID

Accessing the UROWID Datatype

How Oracle Database Converts Datatypes

Datatype Conversion During Assignments

Datatype Conversion During Expression Evaluation

4 Using Regular Expressions in Oracle Database

Using Regular Expressions with Oracle Database: Overview

What Are Regular Expressions?

How Are Oracle Database Regular Expressions Useful?

Oracle Database Implementation of Regular Expressions

Oracle Database Support for the POSIX Regular Expression Standard

Regular Expression Metacharacters in Oracle Database

POSIX Metacharacters in Oracle Database Regular Expressions

Regular Expression Operator Multilingual Enhancements

Perl-Influenced Extensions in Oracle Regular Expressions

Using Regular Expressions in SQL Statements: Scenarios

Using an Integrity Constraint to Enforce a Phone Number Format

Using Back References to Reposition Characters

5 Using Indexes in Application Development

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

6 Maintaining Data Integrity in Application Development

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

Rules for Multiple FOREIGN KEY Constraints

Deferring Constraint Checks

Guidelines for Deferring Constraint Checks

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

Part II PL/SQL for Application Developers

7 Coding PL/SQL 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

Creating Stored Procedures and Functions

Altering Stored Procedures and Functions

Dropping Procedures and Functions

External Procedures

PL/SQL Packages

PL/SQL Object Size Limitation

Creating 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

Compiling PL/SQL Procedures for Native Execution

Remote Dependencies

Disadvantages of the Timestamp Model

When Does a Signature Change?

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

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

Serially Reusable PL/SQL Packages

Why Serially Reusable Packages?

Syntax of Serially Reusable Packages

Semantics of Serially Reusable Packages

Examples of Serially Reusable Packages

Returning Large Amounts of Data from a Function

Coding Your Own Aggregate Functions

8 Coding Dynamic SQL

What Is Dynamic SQL?

Programming with 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

Developing with Native Dynamic SQL: Scenario

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 with 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

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 Statements with Unknown Number of Inputs or Outputs

DBMS_SQL Supports SQL Statements Larger than 32 KB

DBMS_SQL Lets You Reuse SQL Statements

Examples of DBMS_SQL Package Code and Native Dynamic SQL Code

Querying with Dynamic SQL: Example

Performing DML with Dynamic SQL: Example

Performing DML with RETURNING Clause Using Dynamic SQL: Example

Avoiding SQL Injection in PL/SQL

Overview of SQL Injection Techniques

Statement Modification

Statement Injection

Guarding Against SQL Injection

Using Bind Variables to Guard Against SQL Injection

Using Validation Checks to Guard Against SQL Injection

9 Coding 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

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 on Object Tables

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

Responding to System Events through Triggers

How Events Are Published Through Triggers

Publication Context

Execution Model

Event Attribute Functions

List of Database Events

10 Developing Flashback Applications

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 – Performance

Flashback Tips – General

11 Developing Applications with the PL/SQL Web Toolkit

Developing PL/SQL Web Applications: Overview

Invoking a PL/SQL Web Application

Implementing a PL/SQL Web Application

PL/SQL Web Toolkit

Using the mod_plsql Gateway

Generating HTML Output with 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

12 Developing PL/SQL Server Pages

PL/SQL Server Pages: Overview

What Are PL/SQL Server Pages and Why Use Them?

Prerequisites for Developing and Deploying PL/SQL Server Pages

PSP and the HTP Package

PSP and Other Scripting Solutions

Writing a PL/SQL Server Page

Specifying Basic Server Page Characteristics

Specifying the Scripting Language

Returning Data to the Client

Handling Script Errors

Accepting User Input

Naming the PL/SQL Stored Procedure

Including the Contents of Other Files

Declaring Global Variables in a PSP Script

Specifying Executable Statements in a PSP Script

Substituting an Expression Result in a PSP Script

Quoting and Escaping Strings in a PSP Script

Including Comments in a PSP Script

Loading a PL/SQL Server Page into the Database

Querying PSP Source Code

Executing a PL/SQL Server Page Through a URL

Examples of PL/SQL Server Pages

Setup for PL/SQL Server Pages Examples

Printing the Sample Table with a Loop

Allowing a User Selection

Using an HTML Form to Call a PL/SQL Server Page

Including JavaScript in a PSP File

Debugging PL/SQL Server Page Problems

Putting PL/SQL Server Pages into Production

13 Developing Applications with Database Change Notification

What Is Database Change Notification?

Using Database Change Notification in the Middle Tier

Registering Queries for Database Change Notification

What Is a Database Change Registration?

Supported Query Types

Registration Properties

Interfaces for Database Change Registration

Creating a PL/SQL Stored Procedure as the Change Notification Recipient

Registering Queries for Change Notification Through PL/SQL

Creating a CHNF$_REG_INFO Object

Creating a Registration with DBMS_CHANGE_NOTIFICATION

Adding Objects to an Existing Registration

Querying Change Notification Registrations

Interpreting a Database Change Notification

Interpreting a CHNF$_DESC Object

Interpreting a CHNF$_TDESC Object

Interpreting a CHNF$_RDESC Object

Configuring Database Change Notification: Scenario

Creating a PL/SQL Callback Procedure

Registering the Query

Troubleshooting

Part III Advanced Topics for Application Developers

14 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

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

Using Service Procedures with External C Procedures

Doing Callbacks with External C Procedures

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

15 Developing Applications with Oracle XA

X/Open Distributed Transaction Processing (DTP)

DTP Terminology

Required Public Information

Oracle XA Library Interface Subroutines

XA Library Subroutines

Extensions to the XA Interface

Developing and Installing XA Applications

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 for the xa_open() String

Optional Fields for the xa_open() String

Interfacing XA with Precompilers and OCI

Using Precompilers with the Oracle XA Library

Using OCI with the Oracle XA Library

Managing Transaction Control with XA

Examples of Precompiler Applications

Migrating Precompiler or OCI Applications to TPM Applications

Managing XA Library Thread Safety

Specifying Threading in the Open String

Restrictions on Threading in XA

Troubleshooting XA Applications

Accessing XA Trace Files

The xa_open() String DbgFl

Trace File Locations

Managing In-Doubt or Pending Transactions

Using SYS Account Tables to Monitor XA Transactions

XA Issues and Restrictions

Using Database Links in XA Applications

Managing Transaction Branches in XA Applications

Using XA with Oracle Real Application Clusters

Managing Transaction Branches on Oracle Real Application Clusters (RAC)

Managing Instance Recovery in Real Application Clusters

Global Uniqueness of XIDs in Real Application Clusters

SQL-Based XA Restrictions

Rollbacks and Commits

Miscellaneous Restrictions

16 Developing Applications on the Publish-Subscribe Model

Introduction to Publish-Subscribe

Publish-Subscribe Architecture

Publish-Subscribe Concepts

Examples of a Publish-Subscribe Mechanism

Index