Oracle8 Server Application Developer's Guide

Sources of Information

Specific Topics

Business Rules

Client-Side Tools

Communicating with 3GL Programs

Oracle-Supplied Packages

Designing the Database

Designing the Application

Using Available Features

Using the Oracle Call Interface

Writing SQL

Enforcing Security in Your Application

Tuning an Application

Maintaining and Updating an Application

SQL Statement Execution

FIPS Flagging

Controlling Transactions

Improving Performance

Committing a Transaction

Rolling Back a Transaction

Defining a Transaction Savepoint

Privileges Required for Transaction Management

Read-Only Transactions

The Use of Cursors

Declaring and Opening Cursors

Using a Cursor to Re-Execute Statements

Closing Cursors

Cancelling Cursors

Explicit Data Locking

Explicitly Acquiring Table Locks

Privileges Required

Explicitly Acquiring Row Locks

SERIALIZABLE and ROW_LOCKING Parameters

Summary of Non-Default Locking Options

Creating User Locks

The DBMS_LOCK Package

Security

Creating the DBMS_LOCK Package

ALLOCATE_UNIQUE Procedure

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

Modifying a Join View

Key-Preserved Tables

Rule for DML Statements on Join Views

Using the UPDATABLE_COLUMNS Views

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

Miscellaneous Management Topics for Schema Objects

Creating Multiple Tables and Views in One Operation

Naming Schema Objects

Name Resolution in SQL Statements

Renaming Schema Objects

Listing Information about Schema Objects

Oracle Built-In Datatypes

Using Character Datatypes

Using the NUMBER Datatype

Using the DATE Datatype

Using the LONG Datatype

Using RAW and LONG RAW Datatypes

ROWIDs and the ROWID Datatype

Trusted Oracle MLSLABEL Datatype

ANSI/ISO, DB2, and SQL/DS Datatypes

Data Conversion

Rule 1: Assignments

Rule 2: Expression Evaluation

Data Conversion for Trusted Oracle

Introduction to LOBs

LOB Reference

Reference Overview

EMPTY_BLOB() and EMPTY_CLOB() Functions

BFILENAME() Function

Using the OCI to Manipulate LOBs

DBMS_LOB General Usage Notes

BFILE-Specific Usage Notes

DBMS_LOB.APPEND() Procedure

DBMS_LOB.COMPARE() Function

DBMS_LOB.COPY() Procedure

DBMS_LOB.ERASE() Procedure

DBMS_LOB.FILECLOSE() Procedure

DBMS_LOB.FILECLOSEALL() Procedure

DBMS_LOB.FILEEXISTS() Function

DBMS_LOB.FILEGETNAME() Procedure

DBMS_LOB.FILEISOPEN() Function

DBMS_LOB.FILEOPEN() Procedure

DBMS_LOB.GETLENGTH() Function

DBMS_LOB.INSTR() Function

DBMS_LOB.LOADFROMFILE() Procedure

DBMS_LOB.READ() Procedure

DBMS_LOB.SUBSTR() Function

DBMS_LOB.TRIM() Procedure

DBMS_LOB.WRITE() Procedure

LOB Restrictions

Introduction

Purchase Order Example

Entities and Relationships

Part 1: Relational Approach

Part 2: Object-Relational Approach with Object Tables

Introduction

Purchase Order Example

Defining Object Views

Updating the Object Views

Sample Updates

Selecting

Using Integrity Constraints

When to Use Integrity Constraints

Taking Advantage of Integrity 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

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

Enabling and Disabling Constraints Upon Definition

UNIQUE Key, PRIMARY KEY, and FOREIGN KEY

Enabling and Disabling Integrity Constraints

Why Enable or Disable Constraints?

Integrity Constraint Violations

On Definition

Enabling and Disabling Defined Integrity Constraints

Enabling and Disabling Key Integrity Constraints

Enabling Constraints after a Parallel Direct Path Load

Exception Reporting

Altering Integrity Constraints

Dropping Integrity Constraints

Managing FOREIGN KEY Integrity Constraints

Defining FOREIGN KEY Integrity Constraints

Enabling FOREIGN KEY Integrity Constraints

Listing Integrity Constraint Definitions

Examples

PL/SQL Procedures and Packages

Anonymous Blocks

Database Triggers

Stored Procedures and Functions

Creating Stored Procedures and Functions

Altering Stored Procedures and Functions

External Procedures

PL/SQL Packages

Creating Packages

Creating Packaged Objects

Naming Packages and Package Objects

Dropping Packages and Procedures

Package Invalidations and Session State

Remote Dependencies

Timestamps

Signatures

Controlling Remote Dependencies

Suggestions for Managing Dependencies

Cursor Variables

Declaring and Opening Cursor Variables

Examples of Cursor Variables

Hiding PL/SQL Code

Error Handling

Declaring Exceptions and Exception Handling Routines

Unhandled Exceptions

Handling Errors in Distributed Queries

Handling Errors in Remote Procedures

Compile Time Errors

Debugging

Invoking Stored Procedures

A Procedure or Trigger Calling Another Procedure

Interactively Invoking Procedures From Oracle Tools

Calling Procedures within 3GL Applications

Name Resolution When Invoking Procedures

Privileges Required to Execute a Procedure

Specifying Values for Procedure Arguments

Invoking Remote Procedures

Referencing Remote Objects

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

Privileges Required

Supplied Packages

Packages Supporting SQL Features

Packages Supporting Additional Functionality

Describing Stored Procedures

DBMS_DESCRIBE Package

Security

Types

Errors

DESCRIBE_PROCEDURE Procedure

Listing Information about Procedures and Packages

The DBMS_ROWID Package

Summary

Exceptions

ROWID_CREATE Function

ROWID_INFO Procedure

ROWID_TYPE Function

ROWID_OBJECT Function

ROWID_RELATIVE_FNO Function

ROWID_BLOCK_NUMBER Function

ROWID_ROW_NUMBER Function

ROWID_TO_ABSOLUTE_FNO Function

ROWID_TO_EXTENDED Function

ROWID_TO_RESTRICTED Function

ROWID_VERIFY Function

The UTL_HTTP Package

Introduction to Oracle Advanced Queuing

Introduction Overview

Complex Systems

Possible Solutions: Synchronous versus Disconnected/Deferred
Communication

Oracle Advanced Queuing - Features

Oracle Advanced Queuing - Primary Components

Basic Queuing

Multiple-Consumer Dequeuing of the Same Message

Oracle Advanced Queuing by Example

Example Overview

Assign Roles and Privileges

Create Queue Tables and Queues

Enqueue and Dequeue of Object Type Messages

Enqueue and Dequeue of Object Type Messages Using Pro*C/C++

Enqueue and Dequeue of Messages by Priority

Dequeue of Messages after Preview by Criterion

Enqueue and Dequeue of Messages with Time Delay and
Expiration

Enqueue and Dequeue of Messages by Correlation and Message
Id Using Pro*C/C++

Enqueue and Dequeue of Messages to/from a Multiconsumer
Queue

Drop AQ Objects

Revoke Roles and Privileges

Oracle Advanced Queuing Reference

Operational Interface

Enumerated Constants in the Operational Interface

Administrative Interface

Enumerated Constants in the Administrative Interface

Administration Topics

Creating the DBMS_PIPE Package

PACK_MESSAGE Procedures

SEND_MESSAGE

RECEIVE_MESSAGE

NEXT_ITEM_TYPE

UNPACK_MESSAGE Procedures

REMOVE_PIPE

Managing Pipes

Purging the Contents of a Pipe

Resetting the Message Buffer

Getting a Unique Session Name

Example 1: Debugging

Example 2: Execute System Commands

Output from Stored Procedures and Triggers

Summary

Creating the DBMS_OUTPUT Package

Errors

ENABLE Procedure

DISABLE Procedure

PUT and PUT_LINE Procedures

GET_LINE and GET_LINES Procedures

Examples Using the DBMS_OUTPUT Package

Prerequisites for Creating Triggers

Naming Triggers

The BEFORE and AFTER Options

The INSTEAD OF Option

Triggers and Handling Remote Exceptions

Restrictions on Creating Triggers

Who Is the Trigger User?

Privileges Required to Create Triggers

Privileges for Referenced Schema Objects

When Triggers Are Compiled

Dependencies

Recompiling a Trigger

Migration Issues

Debugging a Trigger

Modifying a Trigger

Enabling and Disabling Triggers

Disabling Triggers

Enabling Triggers

Privileges Required to Enable and Disable Triggers

Listing Information About Triggers

Examples of Trigger Applications

Auditing with Triggers

Integrity Constraints and Triggers

Complex Security Authorizations and Triggers

Transparent Event Logging and Triggers

Derived Column Values and Triggers

Overview of Dynamic SQL

Creating the DBMS_SQL Package

Using DBMS_SQL

Execution Flow

Security for Dynamic SQL

For Oracle Server Users

For Trusted Oracle Server Users

Procedures and Functions

OPEN_CURSOR Function

PARSE Procedure

BIND_VARIABLE and BIND_ARRAY Procedures

Processing Queries

DEFINE_COLUMN Procedure

DEFINE_ARRAY Procedure

DEFINE_COLUMN_LONG Procedure

EXECUTE Function

EXECUTE_AND_FETCH Function

FETCH_ROWS Function

COLUMN_VALUE Procedure

COLUMN_VALUE_LONG Procedure

VARIABLE_VALUE Procedure

Processing Updates, Inserts and Deletes

IS_OPEN Function

DESCRIBE_COLUMNS Procedure

CLOSE_CURSOR Procedure

Locating Errors

LAST_ERROR_POSITION Function

LAST_ROW_COUNT Function

LAST_ROW_ID Function

LAST_SQL_FUNCTION_CODE Function

Examples of Using DBMS_SQL

Dependency Issues

Avoiding Runtime Recompilation

Remote Dependencies

Manually Recompiling

Manually Recompiling Views

Manually Recompiling Procedures and Functions

Manually Recompiling Packages

Manually Recompiling Triggers

Listing Dependency Management Information

The Dependency Tracking Utility

Overview

Creating the DBMS_ALERT Package

SET_DEFAULTS Procedure

Example of Using Alerts

Application Security Policy

Application Administrators

Roles and Application Privilege Management

Enabling Application Roles

Restricting Application Roles from Tool Users

Schemas

Managing Privileges and Roles

Creating a Role

Enabling and Disabling Roles

Dropping Roles

Granting and Revoking Privileges and Roles

Granting to, and Revoking from, the User Group PUBLIC

When Do Grants and Revokes Take Effect?

How Do Grants Affect Dependent Objects?

XA Library-Related Information

General Information about the Oracle XA

README.doc

Changes from Release 7.3 to Release 8.0.3

General Issues and Restrictions

Database Links

Oracle Parallel Server Option

Miscellaneous XA Issues

Basic Architecture

X/Open Distributed Transaction Processing(DTP)

Transaction Recovery Management

Oracle XA Library Interface Subroutines

XA Library Subroutines

Extensions to the XA Interface

Transaction Processing Monitors (TPMs)

Required Public Information

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 to Precompilers and OCIs

Using Precompilers with the Oracle XA Library

Using OCI with the Oracle XA Library

Transaction Control

Examples of Precompiler Applications

Migrating Precompiler or OCI Applications to TPM Applications

XA Library Thread Safety

The Open String Specification

In-doubt or Pending Transactions

Oracle Server SYS Account Tables

Contents

Title and Copyright

Preface

Chapter 1 Information Sources for Application Developers

Chapter 2 The Application Developer

Chapter 3 Processing SQL Statements

Chapter 4 Managing Schema Objects

Chapter 5 Selecting a Datatype

Chapter 6 Large Objects (LOBs)

Chapter 7 User-Defined Datatypes-An Extended Example

Chapter 8 Object Views-An Extended Example

Chapter 9 Maintaining Data Integrity

Chapter 10 Using Procedures and Packages

Chapter 11 Advanced Queuing

Chapter 12 PL/SQL Input/Output

Chapter 13 Using Database Triggers

Chapter 14 Using Dynamic SQL

Chapter 15 Dependencies Among Schema Objects

Chapter 16 Signalling Database Events with Alerters

Chapter 17 Establishing a Security Policy

Chapter 18 Oracle XA

Index