Contents

Title and Copyright Information

Send Us Your Comments

Preface

What's New in Application Development?

1 Understanding the Oracle Programmatic Environments

Overview of Developing an Oracle Application

Overview of PL/SQL

A Simple PL/SQL Example

Advantages of PL/SQL

Overview of Java Stored Procedures, JDBC, and SQLJ

Overview of Writing Procedures and Functions in Java

Overview of Oracle JDBC

Overview of Oracle SQLJ

SQLJ Tool

Benefits of SQLJ

Comparing SQLJ with JDBC

SQLJ Example for Object Types

SQLJ Stored Procedures in the Server

Programming with J2EE, OC4J, SOAP, JAAS, Servlets, JSPs, EJBs, CORBA, and UDDI

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 Objects for OLE (OO4O)

OO4O Automation Server

OO4O Object Model

Support for Oracle LOB and Object Datatypes

The Oracle Data Control

The 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 versus PL/SQL

2 Managing Schema Objects

Managing Tables

Designing Tables

Creating Tables

Managing Temporary Tables

Creating Temporary Tables

Using Temporary Tables

Examples: Using Temporary Tables

Tip: Referencing the Same Subquery Multiple Times

Managing Views

Creating Views

Replacing Views

Using Views in Queries

Dropping Views

Modifying a Join View

About Key-Preserved Tables

Rule for DML Statements on Join Views

Using the UPDATABLE_COLUMNS Views

Outer Joins

Managing Sequences

Creating Sequences

Altering Sequences

Using Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Using Synonyms in DML Statements

Dropping Synonyms

Creating Multiple Tables and Views in One Operation

Naming Schema Objects

Rules for Name Resolution in SQL Statements

Renaming Schema Objects

Switching to a Different Schema

Listing Information about Schema Objects

3 Selecting a Datatype

Summary of Oracle Built-In Datatypes

Representing Character Data

Representing Numeric Data

Representing Date and Time Data

Date Format

Time Format

Establishing Year 2000 Compliance

Representing Geographic Coordinate Data

Representing Image, Audio, and Video Data

Representing Searchable Text Data

Representing Large Data Types

Migrating LONG Datatypes to LOB Datatypes

Using RAW and LONG RAW Datatypes

Addressing Rows Directly with the ROWID Datatype

ANSI/ISO, DB2, and SQL/DS Datatypes

How Oracle Converts Datatypes

Datatype Conversion During Assignments

Datatype Conversion During Expression Evaluation

Representing Dynamically Typed Data

Representing XML Data

4 Maintaining Data Integrity Through Constraints

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

Defining Integrity Constraints with the CREATE TABLE Command: Example

Defining Constraints with the ALTER TABLE Command: Example

Privileges Required to Create Constraints

Naming Integrity Constraints

Enabling and Disabling Integrity 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

Rules for FOREIGN KEY Integrity Constraints

Restriction on Enabling FOREIGN KEY Integrity Constraints

Viewing Definitions of Integrity Constraints

Examples of Defining Integrity Constraints

5 Selecting an Index Strategy

Guidelines for Application-Specific Indexes

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 Speeding Up Index Access with Index-Organized Tables

What Are Index-Organized Tables?

Index-Organized Tables Versus Ordinary Tables

Advantages of Index-Organized Tables

Features of Index-Organized Tables

Why Use Index-Organized Tables?

Example of an Index-Organized Table

7 How Oracle 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

Letting Oracle Control Table Locking

Summary of Nondefault Locking Options

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

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

Querying Data at a Point in Time (Flashback Query)

Setting Up the Database for Flashback Query

Writing an Application that Uses Flashback Query

Flashback Query Restrictions

Tips for Using Flashback Query

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

Advantages of the DBMS_SQL Package

Examples of DBMS_SQL Package Code and Native Dynamic SQL Code

Using Dynamic SQL in Languages Other Than PL/SQL

Using PL/SQL Records in SQL INSERT and UPDATE Statements

9 Using Procedures and Packages

Overview of PL/SQL Program Units

Anonymous Blocks

Stored Program Units (Procedures, Functions, and Packages)

Hiding PL/SQL Code with the PL/SQL Wrapper

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

Calling Remote Procedures

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

Overloading Packaged PL/SQL Functions

Serially Reusable PL/SQL Packages

Returning Large Amounts of Data from a Function

Coding Your Own Aggregate Functions

10 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 Java Class Methods with 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

Preliminaries

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

Java Exception Handling

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

11 Database Security Overview for Application Developers

Introduction to Database Security Policies

Security Threats and Countermeasures

What Information Security Policies Can Cover

Features to Use in Establishing Security Policies

Recommended Application Design Practices to Reduce Risk

Introduction to Application Security Policies

Considerations for Using Application-Based Security

Security-Related Tasks of Application Administrators

Managing Application Privileges

Creating Secure Application Roles

Associating Privileges with the User's Database Role

Protecting Database Objects Through Use of Schemas

Managing Object Privileges

Creating a Role and Protecting Its Use

Enabling and Disabling Roles

Granting and Revoking System Privileges and Roles

Granting and Revoking Schema Object Privileges and Roles

Granting to, and Revoking from, the User Group PUBLIC

12 Implementing Application Security Policies

Introduction to Application Context

Features of Application Context

Ways to Use Application Context with Fine-Grained Access Control

User Models and Virtual Private Database

Creating a Virtual Private Database Policy with Oracle Policy Manager

How to Use Application Context

Examples: Application Context Within a Fine-Grained Access Control Function

Introduction to Application Context Accessed Globally

Initializing Application Context Externally

Initializing Application Context Globally

Introduction to Fine-Grained Access Control

Features of Fine-Grained Access Control

How Fine-Grained Access Control Works

How to Establish Policy Groups

How to Add a Policy to a Table, View, or Synonym

How to Check for Policies Applied to Statement

EXEMPT ACCESS POLICY System Privilege

Automatic Reparse

Fine-Grained Auditing

Introduction to Standard Auditing and Fine-Grained auditing

Standard Oracle9i Auditing Techniques

Fine-Grained Auditing Techniques

Enforcing Application Security

Use of Ad Hoc Tools a Potential Security Problem

Restricting Database Roles from SQL*Plus Users

13 Proxy Authentication

Advantages of Proxy Authentication

Security Challenges of Three-tier Computing

Who Is the Real User?

Does the Middle Tier Have Too Much Privilege?

How to Audit? Whom to Audit?

Can the User Be Re-Authenticated to the Database?

Oracle9i Proxy Authentication Solutions

Passing Through the Identity of the Real User

Limiting the Privilege of the Middle Tier

Re-authenticating the Real User

Auditing Actions Taken on Behalf of the Real User

Support for Application User Models

14 Data Encryption Using DBMS_OBFUSCATION_TOOLKIT

Securing Sensitive Information

Principles of Data Encryption

Principle 1: Encryption Does Not Solve Access Control Problems

Principle 2: Encryption Does Not Protect Against a Malicious DBA

Principle 3: Encrypting Everything Does Not Make Data Secure

Solutions For Stored Data Encryption in Oracle9i

Oracle9i Data Encryption Capabilities

Data Encryption Challenges

Encrypting Indexed Data

Key Management

Key Transmission

Key Storage

Changing Encryption Keys

Binary Large Objects (BLOBS)

Example of Data Encryption PL/SQL Program

15 Using 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)

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

Migration Issues for 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

16 Working With System Events

Event Attribute Functions

List of Database Events

System Events

Client Events

17 Using the Publish-Subscribe Model for Applications

Introduction to Publish-Subscribe

Publish-Subscribe Architecture

Publish-Subscribe Concepts

Examples of a Publish-Subscribe Mechanism

18 Developing Web Applications with PL/SQL

What Is a PL/SQL Web Application?

How Do I Generate HTML Output from PL/SQL?

How Do I Pass Parameters to a PL/SQL Web Application?

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, CGI Variables, and More from PL/SQL

Embedding PL/SQL Code in Web Pages (PL/SQL Server Pages)

Choosing a Software Configuration

Writing the Code and Content for the PL/SQL Server Page

Syntax of PL/SQL Server Page Elements

Loading the PL/SQL Server Page into the Database as a Stored Procedure

Running a PL/SQL Server Page Through a URL

Examples of PL/SQL Server Pages

Debugging PL/SQL Server Page Problems

Putting an Application using PL/SQL Server Pages into Production

Enabling PL/SQL Web Applications for XML

19 Porting Non-Oracle Applications to Oracle9i

Frequently Asked Questions About Porting

How Do I Perform Natural Joins and Inner Joins?

Is There an Automated Way to Migrate a Schema and Associated Data from Another Database System?

How Do I Perform Large Numbers of Comparisons within a Query?

Does Oracle Support Scalar Subqueries?

20 Working with Transaction Monitors with Oracle XA

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

Interfacing XA with Precompilers and OCIs

Transaction Control using XA

Migrating Precompiler or OCI Applications to TPM Applications

XA Library Thread Safety

Troubleshooting XA Applications

XA Trace Files

Trace File Examples

In-Doubt or Pending Transactions

Oracle Server 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

Index