Oracle® Database
Application Developer's Guide - Fundamentals
10g Release 1 (10.1)
Part No. B10795-01
December 2003
Oracle Database Application Developer's Guide - Fundamentals, 10g Release 1 (10.1)
Part No. B10795-01
Copyright © 1996, 2003 Oracle Corporation. All rights reserved.
Primary Authors: Drew Adams, Eric Paapanen
Contributing Authors: M. Cowan, R. Moran, 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, R. Murthy, R. Pang, B. Sinha, S. Vemuri, W. Wang, D. Wong, A. Yalamanchi, Q. Yu
Graphic Designer: V. Moore
The Programs (which include both the software and documentation) contain proprietary information of
Oracle Corporation; they are provided under a license agreement containing restrictions on use and
disclosure and are also protected by copyright, patent and other intellectual and industrial property
laws. Reverse engineering, disassembly or decompilation of the Programs, 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. Oracle Corporation does not warrant that this
document is error-free. Except as may be expressly permitted in your license agreement for these
Programs, no part of these Programs may be reproduced or transmitted in any form or by any means,
electronic or mechanical, for any purpose, without the express written permission of Oracle Corporation.
If the Programs are delivered to the U.S. Government or anyone licensing or using the programs on
behalf of the U.S. Government, the following notice is applicable:
Restricted Rights Notice Programs delivered subject to the DOD FAR Supplement are "commercial
computer software" and use, duplication, and disclosure of the Programs, including documentation,
shall be subject to the licensing restrictions set forth in the applicable Oracle license agreement.
Otherwise, Programs delivered subject to the Federal Acquisition Regulations are "restricted computer
software" and use, duplication, and disclosure of the Programs shall be subject to the restrictions in FAR

A Simple PL/SQL Example ........................................................................................................ 1-4
Advantages of PL/SQL ............................................................................................................... 1-5
Full Support for SQL............................................................................................................. 1-5
Tight Integration with Oracle Database............................................................................. 1-5
Better Performance................................................................................................................ 1-5
iv
Higher Productivity .............................................................................................................. 1-6
Scalability................................................................................................................................ 1-6
Maintainability....................................................................................................................... 1-6
PL/SQL Support for Object-Oriented Programming ...................................................... 1-6
Object Types.................................................................................................................... 1-6
Collections ....................................................................................................................... 1-7
Portability ............................................................................................................................... 1-7
Security.................................................................................................................................... 1-7
Built-In Packages for Application Development .............................................................. 1-7
Built-In Packages for Server Management......................................................................... 1-8
Built-In Packages for Distributed Database Access.......................................................... 1-8
Overview of Java Support Built Into the Database...................................................................... 1-8
Overview of Oracle JVM.............................................................................................................. 1-8
Overview of Oracle Extensions to JDBC ................................................................................... 1-9
JDBC Thin Driver................................................................................................................. 1-10
JDBC OCI Driver ................................................................................................................. 1-10
JDBC Server-Side Internal Driver ..................................................................................... 1-11
Oracle Database Extensions to JDBC Standards ............................................................. 1-11
Sample JDBC 2.0 Program.................................................................................................. 1-12
Sample Pre-2.0 JDBC Program .......................................................................................... 1-12
JDBC in SQLJ Applications ................................................................................................ 1-13
Overview of Oracle SQLJ........................................................................................................... 1-13
Benefits of SQLJ ................................................................................................................... 1-15
Comparing SQLJ with JDBC.............................................................................................. 1-15

OraField ................................................................................................................................ 1-35
OraMetaData and OraMDAttribute ................................................................................. 1-35
OraParameters and OraParameter ................................................................................... 1-35
OraParamArray ................................................................................................................... 1-36
OraSQLStmt ......................................................................................................................... 1-36
OraAQ................................................................................................................................... 1-36
OraAQMsg ........................................................................................................................... 1-37
OraAQAgent ....................................................................................................................... 1-37
Support for Oracle LOB and Object Datatypes...................................................................... 1-37
OraBLOB and OraCLOB .................................................................................................... 1-38
OraBFILE .............................................................................................................................. 1-38
Oracle Data Control.................................................................................................................... 1-39
Oracle Objects for OLE C++ Class Library ............................................................................. 1-39
Additional Sources of Information .......................................................................................... 1-39
Choosing a Programming Environment....................................................................................... 1-40
Choosing Whether to Use OCI or a Precompiler................................................................... 1-40
Using Built-In Packages and Libraries .................................................................................... 1-41
vi
Java Compared to PL/SQL ....................................................................................................... 1-41
PL/SQL Is Optimized for Database Access..................................................................... 1-42
PL/SQL Is Integrated with the Database......................................................................... 1-42
Both Java and PL/SQL Have Object-Oriented Features................................................ 1-42
Java Is Used for Open Distributed Applications ............................................................ 1-42
Part II Designing the Database
2 Selecting a Datatype
Summary of Oracle Built-In Datatypes .......................................................................................... 2-2
Representing Character Data............................................................................................................ 2-8
Column Lengths for Single-Byte and Multibyte Character Sets..................................... 2-9
Implicit Conversion Between CHAR/VARCHAR2 and NCHAR/NVARCHAR2 .. 2-10
Comparison Semantics ....................................................................................................... 2-10

Centuries and the Year 2000 .............................................................................................. 2-25
Examples of The RR Date Format..................................................................................... 2-26
Examples of The CC Date Format..................................................................................... 2-27
Storing Dates in Character Datatypes .............................................................................. 2-27
Viewing Date Settings......................................................................................................... 2-28
Altering Date Settings......................................................................................................... 2-29
Troubleshooting Y2K Problems in Applications ............................................................ 2-29
Representing Conditional Expressions as Data.......................................................................... 2-32
Representing Geographic Coordinate Data ................................................................................ 2-33
Representing Image, Audio, and Video Data.............................................................................. 2-33
Representing Searchable Text Data............................................................................................... 2-34
Representing Large Amounts of Data .......................................................................................... 2-34
Using RAW and LONG RAW Datatypes ............................................................................... 2-35
Addressing Rows Directly with the ROWID Datatype ............................................................ 2-36
Extended ROWID Format.................................................................................................. 2-36
Different Forms of the ROWID ......................................................................................... 2-37
ROWID Pseudocolumn............................................................................................... 2-37
Internal ROWID ........................................................................................................... 2-37
External Character ROWID ........................................................................................ 2-37
External Binary ROWID.............................................................................................. 2-38
ROWID Migration and Compatibility Issues.................................................................. 2-38
Accessing Oracle Database Version 7 from an Oracle9i Client ............................. 2-39
Accessing an Oracle9i Database from a Client of Oracle Database Version 7 .... 2-39
Import and Export........................................................................................................ 2-39
ANSI/ISO, DB2, and SQL/DS Datatypes .................................................................................... 2-39
How Oracle Database Converts Datatypes ................................................................................. 2-40
Datatype Conversion During Assignments............................................................................ 2-41
viii
Datatype Conversion During Expression Evaluation ........................................................... 2-43
Representing Dynamically Typed Data ....................................................................................... 2-44

About Referential Integrity in a Distributed Database............................................................. 3-15
When to Use CHECK Integrity Constraints ................................................................................ 3-15
ix
Restrictions on CHECK Constraints........................................................................................ 3-16
Designing CHECK Constraints ................................................................................................ 3-16
Rules for Multiple CHECK Constraints ................................................................................. 3-17
Choosing Between CHECK and NOT NULL Integrity Constraints .................................. 3-17
Examples of Defining Integrity Constraints .............................................................................. 3-17
Example: Defining Integrity Constraints with the CREATE TABLE Command.............. 3-18
Example: Defining Constraints with the ALTER TABLE Command................................. 3-18
Privileges Required to Create Constraints ............................................................................. 3-19
Naming Integrity Constraints .................................................................................................. 3-19
Enabling and Disabling Integrity Constraints ........................................................................... 3-19
Why Disable Constraints? ................................................................................................. 3-20
About Exceptions to Integrity Constraints ...................................................................... 3-20
Enabling Constraints .......................................................................................................... 3-20
Creating Disabled Constraints ......................................................................................... 3-21
Enabling and Disabling Existing Integrity Constraints ........................................................ 3-21
Enabling Existing Constraints .......................................................................................... 3-21
Disabling Existing Constraints ......................................................................................... 3-22
Tip: Using the Data Dictionary to Find Constraints ...................................................... 3-22
Guidelines for Enabling and Disabling Key Integrity Constraints ..................................... 3-23
Fixing Constraint Exceptions ................................................................................................... 3-23
Altering Integrity Constraints........................................................................................................ 3-23
Renaming Integrity Constraints ............................................................................................... 3-24
Dropping Integrity Constraints ..................................................................................................... 3-25
Managing FOREIGN KEY Integrity Constraints ...................................................................... 3-26
Datatypes and Names for Foreign Key Columns.................................................................. 3-26
Limit on Columns in Composite Foreign Keys...................................................................... 3-26
Foreign Key References Primary Key by Default .................................................................. 3-26

Grouping Operations into Transactions......................................................................................... 5-4
Improving Transaction Performance......................................................................................... 5-4
Committing Transactions ........................................................................................................... 5-5
Rolling Back Transactions .......................................................................................................... 5-5
Defining Transaction Savepoints ............................................................................................... 5-6
An Example of COMMIT, SAVEPOINT, and ROLLBACK ............................................ 5-6
Privileges Required for Transaction Management .................................................................. 5-7
Ensuring Repeatable Reads with Read-Only Transactions ....................................................... 5-7
Using Cursors within Applications ................................................................................................ 5-8
Declaring and Opening Cursors ................................................................................................ 5-9
Using a Cursor to Execute Statements Again........................................................................... 5-9
Closing Cursors .......................................................................................................................... 5-10
xi
Cancelling Cursors .................................................................................................................... 5-10
Locking Data Explicitly .................................................................................................................. 5-10
Choosing a Locking Strategy ................................................................................................... 5-11
When to Lock with ROW SHARE and ROW EXCLUSIVE Mode ............................... 5-12
When to Lock with SHARE Mode .................................................................................... 5-12
When to Lock with SHARE ROW EXCLUSIVE Mode.................................................. 5-14
When to Lock in EXCLUSIVE Mode................................................................................ 5-15
Privileges Required ............................................................................................................ 5-15
Letting Oracle Database Control Table Locking.................................................................... 5-15
Explicitly Acquiring Row Locks .............................................................................................. 5-16
About User Locks.............................................................................................................................. 5-17
When to Use User Locks............................................................................................................ 5-18
Example of a User Lock ............................................................................................................. 5-18
Viewing and Monitoring Locks................................................................................................ 5-19
Using Serializable Transactions for Concurrency Control ...................................................... 5-19
How Serializable Transactions Interact................................................................................... 5-21
Setting the Isolation Level of a Transaction............................................................................ 5-23

Sample DML Operation Using Native Dynamic SQL............................................................. 6-8
Sample DDL Operation Using Native Dynamic SQL ............................................................. 6-9
Sample Single-Row Query Using Native Dynamic SQL ........................................................ 6-9
Sample Multiple-Row Query Using Native Dynamic SQL.................................................. 6-10
Choosing Between Native Dynamic SQL and the DBMS_SQL Package.............................. 6-11
Advantages of Native Dynamic SQL....................................................................................... 6-11
Native Dynamic SQL is Easy to Use................................................................................. 6-12
Native Dynamic SQL is Faster than DBMS_SQL............................................................ 6-14
Performance Tip: Using Bind Variables.................................................................... 6-14
Native Dynamic SQL Supports User-Defined Types..................................................... 6-15
Native Dynamic SQL Supports Fetching Into Records.................................................. 6-15
Advantages of the DBMS_SQL Package ................................................................................. 6-16
DBMS_SQL is Supported in Client-Side Programs........................................................ 6-16
DBMS_SQL Supports DESCRIBE ..................................................................................... 6-16
DBMS_SQL Supports SQL Statements Larger than 32KB............................................. 6-16
DBMS_SQL Lets You Reuse SQL Statements.................................................................. 6-16
Examples of DBMS_SQL Package Code and Native Dynamic SQL Code......................... 6-17
Querying Using Dynamic SQL: Example ........................................................................ 6-17
Performing DML Using Dynamic SQL: Example........................................................... 6-19
Performing DML with RETURNING Clause Using Dynamic SQL: Example ........... 6-19
Using Dynamic SQL in Languages Other Than PL/SQL.......................................................... 6-20
xiii
7 Using Procedures and Packages
Overview of PL/SQL Program Units .............................................................................................. 7-2
Anonymous Blocks ...................................................................................................................... 7-2
Stored Program Units (Procedures, Functions, and Packages) ............................................ 7-4
Naming Procedures and Functions ................................................................................... 7-5
Parameters for Procedures and Functions......................................................................... 7-5
Parameter Modes ........................................................................................................... 7-6
Parameter Datatypes ..................................................................................................... 7-7

Timestamps.................................................................................................................................. 7-21
Disadvantages of the Timestamp Model ........................................................................ 7-22
Signatures .................................................................................................................................... 7-23
When Does a Signature Change? ...................................................................................... 7-25
Modes............................................................................................................................. 7-25
Default Parameter Values ........................................................................................... 7-26
Examples of Changing Procedure Signatures................................................................. 7-26
Controlling Remote Dependencies ......................................................................................... 7-28
Dependency Resolution...................................................................................................... 7-29
Suggestions for Managing Dependencies........................................................................ 7-29
Cursor Variables ............................................................................................................................... 7-30
Declaring and Opening Cursor Variables .............................................................................. 7-31
Examples of Cursor Variables................................................................................................... 7-31
Fetching Data ....................................................................................................................... 7-31
Implementing Variant Records ......................................................................................... 7-32
Handling PL/SQL Compile-Time Errors ...................................................................................... 7-33
Handling Run-Time PL/SQL Errors .............................................................................................. 7-35
Declaring Exceptions and Exception Handling Routines .................................................... 7-36
Unhandled Exceptions .............................................................................................................. 7-38
Handling Errors in Distributed Queries ................................................................................. 7-38
Handling Errors in Remote Procedures ................................................................................. 7-38
Debugging Stored Procedures........................................................................................................ 7-40
Calling Stored Procedures............................................................................................................... 7-43
A Procedure or Trigger Calling Another Procedure ...................................................... 7-43
Interactively Calling Procedures From Oracle Database Tools ................................... 7-44
Calling Procedures within 3GL Applications ................................................................ 7-45
Name Resolution When Calling Procedures................................................................... 7-45
Privileges Required to Execute a Procedure ................................................................... 7-45
Specifying Values for Procedure Arguments ................................................................. 7-46
Calling Remote Procedures ............................................................................................................ 7-47

Example 3: Open Cursors in Serially Reusable Packages at Call Boundaries..... 7-68
Returning Large Amounts of Data from a Function.................................................................. 7-69
Coding Your Own Aggregate Functions ...................................................................................... 7-71
8 Calling External Procedures
Overview of Multi-Language Programs......................................................................................... 8-2
What Is an External Procedure? ....................................................................................................... 8-3
Overview of The Call Specification for External Procedures .................................................... 8-4
Loading External Procedures............................................................................................................ 8-4
Loading Java Class Methods....................................................................................................... 8-5
xvi
Loading External C Procedures.................................................................................................. 8-5
Publishing External Procedures..................................................................................................... 8-10
The AS LANGUAGE Clause for Java Class Methods........................................................... 8-12
The AS LANGUAGE Clause for External C Procedures ...................................................... 8-12
LIBRARY............................................................................................................................... 8-12
NAME ................................................................................................................................... 8-12
LANGUAGE ........................................................................................................................ 8-12
CALLING STANDARD...................................................................................................... 8-12
WITH CONTEXT................................................................................................................. 8-13
PARAMETERS..................................................................................................................... 8-13
AGENT IN............................................................................................................................ 8-13
Publishing Java Class Methods ..................................................................................................... 8-13
Publishing External C Procedures ................................................................................................. 8-14
Locations of Call Specifications..................................................................................................... 8-14
Example: Locating a Call Specification in a PL/SQL Package Body ........................... 8-15
Example: Locating a Call Specification in an Object Type Specification..................... 8-16
Example: Locating a Call Specification in an Object Type Body.................................. 8-16
Passing Parameters to External C Procedures with Call Specifications................................. 8-18
Specifying Datatypes.................................................................................................................. 8-19
External Datatype Mappings .................................................................................................... 8-21

Object Support for OCI Callbacks............................................................................................ 8-48
Restrictions on Callbacks........................................................................................................... 8-49
Debugging External Procedures............................................................................................... 8-50
Using Package DEBUG_EXTPROC.................................................................................. 8-51
Demo Program............................................................................................................................ 8-51
Guidelines for External C Procedures ..................................................................................... 8-51
Restrictions on External C Procedures .................................................................................... 8-53
Part III The Active Database
9 Using Triggers
Designing Triggers ............................................................................................................................. 9-2
Creating Triggers ................................................................................................................................ 9-2
Types of Triggers.......................................................................................................................... 9-3
Overview of System Events................................................................................................. 9-4
Getting the Attributes of System Events............................................................................ 9-4
Naming Triggers .......................................................................................................................... 9-4
When Is the Trigger Fired? ......................................................................................................... 9-5
Do Import and SQL*Loader Fire Triggers? ....................................................................... 9-5
How Column Lists Affect UPDATE Triggers .................................................................. 9-6
Controlling When a Trigger Is Fired (BEFORE and AFTER Options) ................................. 9-6
xviii
Ordering of Triggers .................................................................................................................... 9-7
Modifying Complex Views (INSTEAD OF Triggers).............................................................. 9-8
Views that Require INSTEAD OF Triggers ....................................................................... 9-9
INSTEAD OF Trigger Example ......................................................................................... 9-10
Object Views and INSTEAD OF Triggers........................................................................ 9-11
Triggers on Nested Table View Columns........................................................................ 9-12
Firing Triggers One or Many Times (FOR EACH ROW Option) ....................................... 9-13
Firing Triggers Based on Conditions (WHEN Clause) ........................................................ 9-14
Coding the Trigger Body ................................................................................................................ 9-15
Example: Monitoring Logons with a Trigger........................................................... 9-15

Trigger for Complex Check Constraints: Example ........................................................ 9-43
Complex Security Authorizations and Triggers: Example ........................................... 9-45
Transparent Event Logging and Triggers........................................................................ 9-46
Derived Column Values and Triggers: Example ........................................................... 9-46
Building Complex Updatable Views Using Triggers: Example................................... 9-47
Tracking System Events Using Triggers.......................................................................... 9-49
Fine-Grained Access Control Using Triggers: Example......................................... 9-49
CALL Syntax................................................................................................................. 9-50
Responding to System Events through Triggers ....................................................................... 9-50
10 Working With System Events
Event Attribute Functions............................................................................................................... 10-2
List of Database Events.................................................................................................................... 10-7
System Events ............................................................................................................................. 10-7
Client Events ............................................................................................................................... 10-8
11 Using the Publish-Subscribe Model for Applications
Introduction to Publish-Subscribe................................................................................................ 11-2
Publish-Subscribe Architecture..................................................................................................... 11-3
Publish-Subscribe Concepts........................................................................................................... 11-3
Examples of a Publish-Subscribe Mechanism............................................................................ 11-6
Part IV Developing Specialized Applications
12 Using Regular Expressions With Oracle Database
What are Regular Expressions?...................................................................................................... 12-2
Oracle Database Regular Expression Support ............................................................................ 12-2
Oracle Database SQL Functions for Regular Expressions........................................................ 12-2
Metacharacters Supported in Regular Expressions ................................................................... 12-4
Constructing Regular Expressions ................................................................................................ 12-5
xx
Basic String Matching with Regular Expressions .................................................................. 12-5
Regular Expression Operations on Subexpressions .............................................................. 12-5
Regular Expression Operator and Metacharacter Usage...................................................... 12-5

The Format of the PSP File ............................................................................................... 13-21
xxi
Syntax of PL/SQL Server Page Elements ............................................................................. 13-27
Page Directive .................................................................................................................... 13-27
Procedure Directive .......................................................................................................... 13-27
Parameter Directive .......................................................................................................... 13-28
Include Directive ............................................................................................................... 13-28
Declaration Block............................................................................................................... 13-28
Code Block (Scriptlet) ....................................................................................................... 13-28
Expression Block................................................................................................................ 13-29
Loading the PL/SQL Server Page into the Database as a Stored Procedure................... 13-29
Running a PL/SQL Server Page Through a URL ................................................................ 13-30
Sample PSP URLs.............................................................................................................. 13-30
Examples of PL/SQL Server Pages........................................................................................ 13-31
Sample Table...................................................................................................................... 13-31
Dumping the Sample Table ............................................................................................. 13-32
Printing the Sample Table using a Loop........................................................................ 13-32
Allowing a User Selection................................................................................................ 13-33
Sample HTML Form to Call a PL/SQL Server Page.................................................... 13-35
Debugging PL/SQL Server Page Problems.......................................................................... 13-38
Putting an Application using PL/SQL Server Pages into Production ............................. 13-39
Enabling PL/SQL Web Applications for XML .......................................................................... 13-41
14 Porting Non-Oracle Applications to Oracle Database 10g
Performing Natural Joins and Inner Joins................................................................................... 14-2
Migrating a Schema and Data from Another Database System.............................................. 14-2
Performing Several Comparisons within a Query..................................................................... 14-2
15 Using Flashback Features
Overview of Flashback Features.................................................................................................... 15-2
Application Development Features......................................................................................... 15-2
Database Administration Features........................................................................................... 15-3

Using Precompilers with the Oracle XA Library.......................................................... 16-16
Using Precompilers with the Default Database ............................................................ 16-16
Using Precompilers with a Named Database................................................................ 16-17
Using OCI with the Oracle XA Library .......................................................................... 16-18
Transaction Control using XA ................................................................................................ 16-19
Examples of Precompiler Applications.......................................................................... 16-20
Migrating Precompiler or OCI Applications to TPM Applications .................................. 16-21
XA Library Thread Safety........................................................................................................ 16-23
Specifying Threading in the Open String ...................................................................... 16-23
Restrictions on Threading in XA ..................................................................................... 16-23
xxiii
Troubleshooting XA Applications............................................................................................... 16-24
XA Trace Files ........................................................................................................................... 16-24
The xa_open string DbgFl ................................................................................................ 16-24
Trace File Locations........................................................................................................... 16-25
Trace File Examples.................................................................................................................. 16-25
In-Doubt or Pending Transactions......................................................................................... 16-26
Oracle Database SYS Account Tables .................................................................................... 16-26
XA Issues and Restrictions............................................................................................................ 16-27
Changes to Oracle XA Support .................................................................................................... 16-32
XA Changes from Release 8.0 to Release 8.1 ........................................................................ 16-32
XA Changes from Release 7.3 to Release 8.0 ........................................................................ 16-32
Session Caching Is No Longer Needed.......................................................................... 16-33
Dynamic Registration Is Supported ............................................................................... 16-33
Loosely Coupled Transaction Branches Are Supported ............................................. 16-33
SQLLIB Is Not Needed for OCI Applications............................................................... 16-34
No Installation Script Is Needed to Run XA ................................................................. 16-34
XA Library Use with Oracle Real Application Clusters Option on All Platforms .. 16-34
Transaction Recovery for Oracle Real Application Clusters Has Been Improved .. 16-34
Both Global and Local Transactions Are Possible........................................................ 16-34

500 Oracle Parkway, Mailstop 4op11
Redwood Shores, CA 94065
USA
If you would like a reply, please give your name, address, telephone number, and (optionally) elec-
tronic mail address.

If you have problems with the software, please contact your local Oracle Support Services.