Skip Headers
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
What's New in Application Development?
Oracle Database 10
g
Release 2 (10.2) New Features
Oracle Database 10
g
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
Scalability
Manageability
Object-Oriented Programming Support
Portability
Security
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
OraSession
OraServer
OraDatabase
OraDynaset
OraField
OraMetaData and OraMDAttribute
OraParameters and OraParameter
OraParamArray
OraSQLStmt
OraAQ
OraAQMsg
OraAQAgent
Support for Oracle LOB and Object Datatypes
OraBLOB and OraCLOB
OraBFILE
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
Extended 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
Triggers
Compiling PL/SQL Procedures for Native Execution
Remote Dependencies
Timestamps
Disadvantages of the Timestamp Model
Signatures
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
Fetching Data
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
Arguments
Using Default Values
Privileges
Requirements for Calling PL/SQL Functions from SQL Expressions
Controlling Side Effects
Restrictions
Declaring a Function
Parallel Query and Parallel DML
PRAGMA RESTRICT_REFERENCES – for Backward Compatibility
Serially Reusable PL/SQL Packages
Package States
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
Error Handling
Execution Model
Event Attribute Functions
List of Database Events
System Events
Client 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
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
Privileges
What Is a Database Change Registration?
Supported Query Types
Registration Properties
Drop Table
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
Best Practices
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
LIBRARY
NAME
LANGUAGE
CALLING STANDARD
WITH CONTEXT
PARAMETERS
AGENT IN
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
INDICATOR
LENGTH and MAXLEN
CHARSETID and CHARSETFORM
Repositioning Parameters
Using SELF
Passing Parameters by Reference
WITH CONTEXT
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
Demo Program
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
DDL Statements
Session State
EXEC SQL
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