Skip Headers

Oracle® Database Application Developer's Guide - Fundamentals
10g Release 1 (10.1)

Part Number B10795-01
Go to Documentation Home
Go to Book List
Book List
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to next page
View PDF


Title and Copyright Information

Send Us Your Comments


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
PL/SQL Support for Object-Oriented Programming
Object Types
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 Oracle JVM
Overview of Oracle Extensions to JDBC
JDBC Thin 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 Database Web Services
Database as a Web Service Provider
Database as a Web Service Consumer
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++
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)
OO4O Automation Server
OO4O Object Model
OraMetaData and OraMDAttribute
OraParameters and OraParameter
Support for Oracle LOB and Object Datatypes
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 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
Date Format
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
Time Format
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
Internal ROWID
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
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
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 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
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
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
Package States
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
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
Overriding Default Datatype Mapping
Specifying Properties
Repositioning Parameters
Using SELF
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
Doing Callbacks with External C Procedures
Object Support for OCI Callbacks
Restrictions on Callbacks
Debugging External Procedures
Demo Program
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
CALL Syntax
Responding to System Events through Triggers

10 Working With System Events

Event Attribute Functions
List of Database Events
System Events
Client 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)
Choosing a Software Configuration
Choosing Between PSP and the PL/SQL Web Toolkit
How PSP Relates to Other Scripting Solutions
Writing the Code and Content for the PL/SQL Server Page
The Format of the PSP File
Syntax of PL/SQL Server Page Elements
Page Directive
Procedure Directive
Parameter Directive
Include Directive
Declaration Block
Code Block (Scriptlet)
Expression Block
Loading the PL/SQL Server Page into the Database as a Stored Procedure
Running a PL/SQL Server Page Through a URL
Sample PSP URLs
Examples of PL/SQL Server Pages
Sample Table
Dumping the Sample Table
Printing the Sample Table using a Loop
Allowing a User Selection
Sample HTML Form to Call a PL/SQL Server Page
Debugging PL/SQL Server Page Problems
Putting an Application using PL/SQL Server Pages into Production
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 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
XA Trace Files
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