Oracle Database Application Developer’s Guide Fundamentals Developer 10g Release 2

• Contents
• Preface
  • Audience
  • Documentation Accessibility
  • Related Documents
  • Conventions
• 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
      • PL/SQL Web Development Tools
    • Overview of Java Support Built Into the Database
      • Overview of Oracle JVM
      • Overview of Oracle Extensions to JDBC
      • Overview of Oracle SQLJ
      • Overview of Oracle JPublisher
      • Overview of Java Stored Procedures
      • Overview of Oracle Database Web Services
      • Overview of Writing Procedures and Functions in Java
    • 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
      • 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
• 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
      • Rolling Back Transactions
      • Defining Transaction Savepoints
    • 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
      • 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
      • Referential Integrity and Serializable Transactions
      • READ COMMITTED and SERIALIZABLE Isolation
      • Application Tips for Transactions
    • Autonomous Transactions
      • Examples of Autonomous Transactions
      • 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
    • Representing Numeric Data
      • What Are the Numeric Datatypes?
      • Using Floating-Point Number 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
    • Representing Datetime Data
      • Representing Datetime Data: Overview
      • Manipulating the Date Format
      • 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
      • 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
      • 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
      • Restrictions for Function-Based Indexes
  • 6 Maintaining Data Integrity in Application Development
    • Overview of Integrity Constraints
      • When to Enforce Business Rules with Integrity Constraints
      • 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
    • 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
      • 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)
    • Compiling PL/SQL Procedures for Native Execution
    • Remote Dependencies
      • Timestamps
      • Signatures
      • Controlling Remote Dependencies
    • Cursor Variables
      • Declaring and Opening Cursor Variables
      • Examples of Cursor Variables
    • 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
      • Requirements for Calling PL/SQL Functions from SQL Expressions
      • Controlling Side Effects
      • Serially Reusable PL/SQL 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
      • Advantages of the DBMS_SQL Package
      • Examples of DBMS_SQL Package Code and Native Dynamic SQL Code
    • Avoiding SQL Injection in PL/SQL
      • Overview of SQL Injection Techniques
      • Guarding Against SQL Injection
  • 9 Coding Triggers
    • Designing Triggers
    • Creating Triggers
      • Types of Triggers
      • Naming Triggers
      • When Is the Trigger Fired?
      • Controlling When a Trigger Is Fired (BEFORE and AFTER Options)
      • Ordering of Triggers
      • Modifying Complex Views (INSTEAD OF Triggers)
      • 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
      • 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
  • 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
    • 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
      • 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
    • 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
    • Querying Change Notification Registrations
    • Interpreting a Database Change Notification
      • Interpreting a CHNF$_DESC 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
    • 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
    • Executing External Procedures with the CALL Statement
      • Preconditions 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
      • 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
      • Interfacing XA with Precompilers and OCI
      • Managing Transaction Control with XA
      • Migrating Precompiler or OCI Applications to TPM Applications
      • Managing XA Library Thread Safety
    • Troubleshooting XA Applications
      • Accessing XA Trace Files
      • 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
      • SQL-Based XA Restrictions
      • 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
  • Symbols
  • A
  • B
  • C
  • D
  • E
  • F
  • G
  • H
  • I
  • J
  • K
  • L
  • M
  • N
  • O
  • P
  • Q
  • R
  • S
  • T
  • U
  • V
  • W
  • X