Oracle8 Application Developer's Guide
Release 8.0
A58241-01

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Information in This Guide

Feature Coverage and Availability

How This Book Is Organized

Conventions Used in this Guide

Your Comments Are Welcome

1 Information Sources for Application Developers

Sources of Information

Specific Topics

Client-Side Tools

Communicating with 3GL Programs

Database Constraints

Database Design

Oracle-Supplied Packages

2 The Application Developer

Assessing Needs

Designing the Database

Designing the Application

Using Available Features

Using the Oracle Call Interface

Enforcing Security in Your Application

Tuning an Application

Maintaining and Updating an Application

3 Processing SQL Statements

SQL Statement Execution

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

Creating the DBMS_LOCK Package

ALLOCATE_UNIQUE Procedure

REQUEST Function

CONVERT Function

RELEASE Function

SLEEP Procedure

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

4 Managing Schema Objects

Managing Tables

Designing Tables

Creating Tables

Altering Tables

Dropping Tables

Replacing Views

Modifying a Join View

Key-Preserved Tables

Rule for DML Statements on Join Views

Using the UPDATABLE_COLUMNS Views

Managing Sequences

Creating Sequences

Altering Sequences

Using Sequences

Dropping Sequences

Managing Synonyms

Creating Synonyms

Dropping Synonyms

Managing Indexes

Creating Indexes

Dropping 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

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

5 Selecting a Datatype

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

6 Large Objects (LOBs)

Introduction to LOBs

Internal LOBs and External LOBs (BFILEs)

LOBs in Comparison to LONG and LONG RAW Types

Packages for Working with LOBs

Defining Internal and External LOBs for Tables

Stipulating Tablespace and Storage Characteristics for Internal Lobs

Initializing Internal LOBs (SQL DML)

Accessing External LOBs (SQL DML)

Catalog Views on Directories

Guidelines for DIRECTORY Usage

Maximum Number of Open BFILEs

BFILEs in MTS Mode

Closing BFILEs after Program Termination

LOB Value and Locators

LOB Locator Operations

Efficient Reads and Writes of Large Amounts of LOB Data

LOBs in the Object Cache

LOB Buffering Subsystem

User Guidelines for Best Performance Practices

Working with Varying-Width Character Data

Reference Overview

EMPTY_BLOB() and EMPTY_CLOB() Functions

BFILENAME() Function

Using the OCI to Manipulate LOBs

DBMS_LOB Package

Package Routines

Type Definitions

DBMS_LOB Exceptions

DBMS_LOB Security

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

7 User-Defined Datatypes - An Extended Example

A Purchase Order Example

Entities and Relationships

Part 1: Relational Approach

Part 2: Object-Relational Approach with Object Tables

8 Object Views-An Extended Example

Purchase Order Example

Defining Object Views

Updating the Object Views

9 Maintaining Data Integrity

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

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

10 Using Procedures and Packages

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

Controlling Remote Dependencies

Suggestions for Managing Dependencies

Cursor Variables

Declaring and Opening Cursor Variables

Examples of Cursor Variables

Hiding PL/SQL Code

Declaring Exceptions and Exception Handling Routines

Unhandled Exceptions

Handling Errors in Distributed Queries

Handling Errors in Remote Procedures

Compile Time Errors

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

Naming Conventions

Meeting Basic Requirements

Controlling Side Effects

Serially Reusable PL/SQL Packages

Privileges Required

Supplied Packages

Packages Supporting SQL Features

Packages Supporting Additional Functionality

Describing Stored Procedures

DBMS_DESCRIBE Package

DESCRIBE_PROCEDURE Procedure

Listing Information about Procedures and Packages

The DBMS_ROWID Package

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

11 Advanced Queuing

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

Modeling Queue Entities

Illustrating Basic Queuing

Illustrating Client-Server Communication Using AQ

Multiple-Consumer Dequeuing of the Same Message

Illustrating Multiple-Consumer Dequeuing of the Same Message

Illustrating Dequeuing of Specified Messages by Specified Recipients

Illustrating the Implementation of Workflows using AQ

Message Propagation

llustration of Message Propagation

Oracle Advanced Queuing by Example

Overview Summary

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 Object Type Messages Using OCI

Enqueue and Dequeue of RAW Type Messages

Enqueue and Dequeue of RAW Type Messages using Pro*C/C++

Enqueue and Dequeue of RAW Type Messages using OCI

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 by Correlation and Message ID using OCI

Enqueue and Dequeue of Messages to/from a Multiconsumer Queue using PL/SQL

Enqueue and Dequeue of Messages to/from a Multiconsumer Queue using OCI

Enqueue of Messages for remote subscribers/recipients to a Multiconsumer Queue and Propagation Scheduling

Unscheduling Propagation

Enqueue and Dequeue using Message Grouping

Drop AQ Objects

Revoke Roles and Privileges

Oracle Advanced Queuing Reference

Reference Overview

INIT.ORA Parameter

Data Structures

Message Properties

Operational Interface

Enumerated Constants in the Operational Interface

Administrative Interface

Enumerated Constants in the Administrative Interface

Database Objects

Administration Topics

Optimizing Propagation

Reliability and Recoverability

Enterprise Manager Support

Importing and Exporting Queue Data

Troubleshooting

Dynamic Statistics Views

Reference to Demos

Compatibility & Upgrade

12 PL/SQL Input/Output

Creating the DBMS_PIPE Package

PACK_MESSAGE Procedures

RECEIVE_MESSAGE

UNPACK_MESSAGE Procedures

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

Creating the DBMS_OUTPUT Package

ENABLE Procedure

DISABLE Procedure

PUT and PUT_LINE Procedures

GET_LINE and GET_LINES Procedures

Examples Using the DBMS_OUTPUT Package

PL/SQL File I/O

13 Using Database Triggers

Designing Triggers

Creating Triggers

Prerequisites for Creating Triggers

Naming Triggers

The BEFORE and AFTER Options

The INSTEAD OF Option

Triggering Statement

FOR EACH ROW Option

The WHEN Clause

The Trigger Body

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

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

14 Using Dynamic SQL

Overview of Dynamic SQL

Creating the DBMS_SQL Package

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

15 Dependencies Among Schema Objects

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

16 Signalling Database Events with Alerters

Creating the DBMS_ALERT Package

REGISTER Procedure

REMOVE Procedure

SIGNAL Procedure

WAITANY Procedure

WAITONE Procedure

Checking for Alerts

SET_DEFAULTS Procedure

Example of Using Alerts

17 Establishing a Security Policy

Application Security Policy

Application Administrators

Roles and Application Privilege Management

Enabling Application Roles

Restricting Application Roles from Tool Users

Managing Privileges and Roles

Creating a Role

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

18 Oracle XA

XA Library-Related Information

General Information about the Oracle 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

The XA Library Can Be Used with the Oracle Parallel Server Option on All Platforms

Transaction Recovery for Oracle Parallel Server Has Been Improved

Both Global and Local Transactions Are Possible

The xa_open String Has Been Modified

General Issues and Restrictions

Oracle Parallel Server Option

SQL-based Restrictions

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

Troubleshooting

Trace File Examples

In-doubt or Pending Transactions

Oracle Server SYS Account Tables

Copyright © 1997 Oracle Corporation.
All Rights Reserved.