Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Introduction To Working With The Server

1 Understanding the Oracle Programmatic Environments

Overview of PL/SQL

How Does PL/SQL Work?

Advantages of PL/SQL

Overview of OCI

Advantages of OCI

Parts of the OCI

Procedural and Non-Procedural Elements

Building an OCI Application

Overview of Oracle Objects for OLE

The 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

Overview of Pro*C/C++

How You Implement a Pro*C/C++ Application

Highlights of Pro*C/C++ Features

New Oracle8i Features Supported

Overview of Pro*COBOL

How You Implement a Pro*COBOL Application

Highlights of Pro*COBOL Features

New Oracle8i Features Supported

Overview of Oracle JDBC

JDBC Thin Driver

JDBC OCI Driver

The JDBC Server Driver

Extensions of JDBC

Sample Program for the JDBC Thin Driver

Java in the RDBMS

Why Use Stored Procedures?

JDBC in SQLJ Applications

Overview of Oracle SQLJ

SQLJ Tool

SQLJ Design Goals

Strengths of Oracle's SQLJ Implementation

Comparison of SQLJ with JDBC

SQLJ Example for Object Types

SQLJ Stored Procedures in the Server

Choosing a Programming Environment

Use OCI or a Precompiler?

Built-in Packages and Libraries

Java vs. PL/SQL

Part II Designing the Database

2 Managing Schema Objects

Managing Tables

Designing Tables

Creating Tables

Altering Tables

Dropping Tables

Managing Temporary Tables

Creating Temporary Tables

Using Temporary Tables

Examples: Using Temporary Tables

Managing Views

Creating Views

Replacing Views

Using Views

Dropping Views

Modifying a Join View

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

Dropping Synonyms

Creating Multiple Tables and Views in One Operation

Naming Schema Objects

Name Resolution in SQL Statements

Renaming Schema Objects

Renaming the Schema

Listing Information about Schema Objects

3 Selecting a Datatype

Oracle Built-In Datatypes

Using Character Datatypes

Using the NUMBER Datatype

Using the DATE Datatype

Establishing Year 2000 Compliance

Using the LONG Datatype

Using RAW and LONG RAW Datatypes

ROWIDs and the ROWID Datatype

ANSI/ISO, DB2, and SQL/DS Datatypes

Data Conversion

Rule 1: Assignments

Rule 2: Expression Evaluation

4 Maintaining Data Integrity

Using Integrity Constraints

When to Enforce Business Rules with Integrity Constraints

When to Enforce Business Rules in Applications

Creating Indexes for Use with Constraints

Using NOT NULL Integrity Constraints

Setting Default Column Values

Choosing a Table's Primary Key

Using UNIQUE Key Integrity Constraints

Using Referential Integrity Constraints

Nulls and Foreign Keys

Relationships Between Parent and Child Tables

Multiple FOREIGN KEY Constraints

Deferring Constraint Checks

Managing Constraints That Have Associated Indexes

Concurrency Control, Indexes, and Foreign Keys

Referential Integrity in a Distributed Database

Using CHECK Integrity Constraints

Restrictions on CHECK Constraints

Designing CHECK Constraints

Multiple CHECK Constraints

CHECK and NOT NULL Integrity Constraints

Defining Integrity Constraints

The CREATE TABLE Command

The ALTER TABLE Command

Required Privileges

Naming Integrity Constraints

Why Disable Constraints?

Integrity Constraint Violations

On Definition

Enabling and Disabling Existing Integrity Constraints

Enabling and Disabling Key Integrity Constraints

Exception Reporting

Altering Integrity Constraints

Examples of MODIFY CONSTRAINT

Dropping Integrity Constraints

Managing FOREIGN KEY Integrity Constraints

Defining FOREIGN KEY Integrity Constraints

Enabling FOREIGN KEY Integrity Constraints

Listing Integrity Constraint Definitions

Examples

5 Selecting an Index Strategy

Managing Indexes

Creating Indexes

Dropping Indexes

Function-Based Indexes

Using Function-Based Indexes

Example Function-Based Indexes

Requirements and Restrictions for Function-Based Indexes

Managing Clusters, Clustered Tables, and Cluster Indexes

Guidelines for Creating Clusters

Performance Considerations

Creating Clusters, Clustered Tables, and Cluster Indexes

Manually Allocating Storage for a Cluster

Dropping Clusters, Clustered Tables, and Cluster Indexes

Managing Hash Clusters and Clustered Tables

Creating Hash Clusters and Clustered Tables

Controlling Space Usage Within a Hash Cluster

Dropping Hash Clusters

When to Use Hashing

6 Speeding Up Index Access with Index-Organized Tables

Overview of Index-Organized Tables

Index-Organized Tables versus Ordinary Tables

Advantages of Index-Organized Tables

Features of Index-Organized Tables

When to Use Index-Organized Tables

Example

7 Processing SQL Statements

SQL Statement Execution

Identifying Extensions to SQL92 (FIPS Flagging)

Controlling Transactions

Improving Performance

Committing a Transaction

Rolling Back a Transaction

Defining a Transaction Savepoint

Privileges Required for Transaction Management

Ensuring Repeatable Reads with Read-Only Transactions

Using Cursors

Declaring and Opening Cursors

Using a Cursor to Re-Execute Statements

Closing Cursors

Cancelling Cursors

Explicit Data Locking

Choosing a Locking Strategy

Letting Oracle Control Table Locking

Summary of Non-Default Locking Options

Explicitly Acquiring Row Locks

User Locks

Creating User Locks

Sample User Locks

Viewing and Monitoring Locks

Concurrency Control Using Serializable Transactions

Serializable Transaction Interaction

Setting the Isolation Level

Referential Integrity and Serializable Transactions

READ COMMITTED and SERIALIZABLE Isolation

Application Tips

Autonomous Transactions

Examples

Defining Autonomous Transactions

8 Dynamic SQL

What Is Dynamic SQL?

When to Use Dynamic SQL

To Execute Dynamic DML Statements

To Execute Statements Not Supported by Static SQL in PL/SQL

To Execute Dynamic Queries

To Reference Database Objects that Do Not Exist at Compilation

To Optimize Execution Dynamically

To Invoke Dynamic PL/SQL Blocks

To Perform Dynamic Operations Using Invoker-Rights

A Dynamic SQL Scenario Using Native Dynamic SQL

Data Model

Sample DML Operation

Sample DDL Operation

Sample Dynamic Single-Row Query

Sample Dynamic Multiple-Row Query

Native Dynamic SQL vs. 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

Application Development Languages Other Than PL/SQL

9 Using Procedures and Packages

PL/SQL Program Units

Anonymous Blocks

Stored Program Units (Procedures, Functions, and Packages)

Wrapping PL/SQL Code

Remote Dependencies

Timestamps

Signatures

Controlling Remote Dependencies

Cursor Variables

Declaring and Opening Cursor Variables

Examples of Cursor Variables

Compile-Time Errors

Run-Time Error Handling

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

Naming Conventions

Meeting Basic Requirements

Controlling Side Effects

Overloading

Serially Reusable PL/SQL Packages

10 External Routines

What is an External Routine?

Loading External Routines

Loading Java Class Methods

Loading External C Routines

Publishing an External Routine

The AS LANGUAGE Clause for Java Class Methods

The AS LANGUAGE Clause for External C Routines

Publishing Java Class Methods

Publishing External C Routines

Passing Parameters to Java Class Methods with Call Specifications

Passing Parameters to External C Routines 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

Preliminaries

CALL Statement Syntax

Calling Java Class Methods

Calling External C Routines

Generic Compile Time Call specification Errors

Java Exception Handling

C Exception Handling

Using Service Routines with External C Routines

Doing Callbacks with External C Routines

Object Support for OCI Callbacks

Restrictions on Callbacks

Debugging External Routines

Demo Program

Guidelines for External C Routines

Restrictions on External C Routines

11 Establishing Security Policies

Introduction to Security Policies

Security Threats and Countermeasures

What Discretionary Security Policies Can Cover

Features to Use in Establishing Security Policies

Application Security

Considerations for Using Application-Based Security

Tasks of Application Administrators

Overview of Roles and Application Privilege Management

Associating Privileges with the User's Current Application Role

Restricting Application Roles from Tool Users

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

Fine-Grained Access Control

Features of Fine-Grained Access Control

How to Add a Policy to a Table or View

Example of a Dynamically Modified Statement

Application Context

Features of Application Context

Feature Design Principles for Application Context

Ways to Use Application Context with Fine Grained Access Control

How to Use Application Context

Examples

Authentication Through a Middle Tier

Advantages of n-Tier Authentication

Security Challenges of Three-tier Computing

Oracle8i n-Tier Authentication Solutions

Data Encryption

The DBMS_OBFUSCATION_TOOLKIT Package

Development Considerations

Part III The Active Database

12 Using Triggers

Designing Triggers

Creating Triggers

Prerequisites for Creating Triggers

Types of Triggers

Naming Triggers

Triggering Statement

BEFORE and AFTER Options

INSTEAD OF Triggers

FOR EACH ROW Option

WHEN Clause

The Trigger Body

Triggers and Handling Remote Exceptions

Restrictions on Creating Triggers

Who Is the Trigger User?

Privileges

Compiling Triggers

Dependencies

Recompiling Triggers

Migration Issues

Modifying Triggers

Debugging Triggers

Enabling and Disabling Triggers

Enabling Triggers

Disabling Triggers

Listing Information About Triggers

Examples of Trigger Applications

Triggering Event Publication

Publication Framework

13 Working With System Events

Event Attribute Functions

List of Events

Resource Manager Events

Client Events

14 Using Publish-Subscribe

Introduction to Publish-Subscribe

Publish-Subscribe Infrastructure

Publish-Subscribe Concepts

Examples

Part IV Developing Specialized Applications

15 Developing Web Applications with PL/SQL

Performing Network Operations with PL/SQL

Sending Mail

Getting a Host Name or Address

Working with TCP/IP Connections

Retrieving the Contents of an HTTP URL

Working with Tables, Image Maps, Cookies, CGI Variables, and More

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 via a URL

Examples of PL/SQL Server Pages

Debugging PL/SQL Server Page Problems

Putting an Application using PL/SQL Server Pages into Production

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

General XA Issues and Restrictions

Database Links

Oracle Parallel Server Option

SQL-based Restrictions

Miscellaneous XA Issues

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