Oracle8i Application Developer's Guide

What Can PL/SQL Do?

How Does PL/SQL Work?

What Advantages Does PL/SQL Offer?

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

Pro*C/C++

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

Highlights of Pro*C/C++ Features

New Oracle8i Features Supported

Pro*COBOL

How You Implement a Pro*COBOL Application

Highlights of Pro*COBOL Features

New Oracle8i Features Supported

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

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

Why Employ Visual Modelling?

Unified Modelling Language

Illustrations and Diagrams

Use Cases

Managing Tables

Managing Temporary Tables

Creating Temporary Tables

Using Temporary Tables

Examples: Using Temporary Tables

Managing Views

Modifying a Join View

Key-Preserved Tables

Rule for DML Statements on Join Views

Using the UPDATABLE_COLUMNS Views

Outer Joins

Managing Sequences

Managing Synonyms

Creating Synonyms

Using Synonyms

Dropping Synonyms

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

Renaming the Schema

Listing Information about Schema Objects

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

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

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

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

Managing Indexes

Creating Indexes

Dropping Indexes

Function-Based Indexes

Using Function-Based Indexes

Example Function-Based Indexes

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

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

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

Using 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

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

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

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

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

The Need to Work with Multiple Languages

What is an External Routine?

The Call Specification

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

Locations of Call Specifications

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

Executing External Routines: the CALL Statement

Preliminaries

CALL Statement Syntax

Calling Java Class Methods

Calling External C Routines

Errors and Exceptions

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

About Security Policies

Application Security

Application Administrators

Roles and Application Privilege Management

Enabling Application Roles

Restricting Application Roles from Tool Users

Schemas

Managing Privileges and Roles

Application Context

Features of Application Context

Using Application Context

Fine-Grained Access Control

Features of Fine-Grained Access Control

Example of a Dynamically Modified Statement

Using Application Context within a Fine-Grained Access Control Package

Examples

Example 1: Order Entry Application

Example 2: Human Resources Application #1

Example 3: Human Resources Application #2

Designing Triggers

Creating Triggers

Prerequisites for Creating Triggers

Types of Triggers

Naming Triggers

Triggering Statement

BEFORE and AFTER Options

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

Event Attribute Functions

List of Events

Resource Manager Events

Client Events

Introduction to Publish-Subscribe

Publish-Subscribe Infrastructure

Publish-Subscribe Concepts

Examples

Introduction

A Purchase Order Example

Implementing the Application Under The Relational Model

Entities and Relationships

Creating Tables Under the Relational Model

Schema Plan Under the Relational Model

Inserting Values Under the Relational Model

Querying Data Under The Relational Model

Updating Data Under The Relational Model

Deleting Data Under The Relational Model

Limitations of a Purely Relational Model

The Evolution of the Object-Relational Database System

Implementing the Application Under The Object-Relational Model

Defining Types

Method Definitions

Creating Object Tables

Object Datatypes as a Template for Object Tables

Object Identifiers and References

Object Tables with Embedded Objects

Partitioning Tables with Oracle Objects

Introduction

Advantages of Using Views to Synthesize Objects

Fundamental Elements of Using Objects in Views

Objects in Columns

Collection Objects

Row Objects and Object Identifiers

Object References

Inverse Relationships

Mutating Objects and Validation

Extending the Purchase Order Example

Inserting into the Nested Table

INSTEAD-OF Trigger for Customer_objview

INSTEAD-OF Trigger for Stock_objview

Inserting Values

Deleting

Using the OCI Object Cache

Views on Remote Tables

Partitioning Tables with Objects

Parallel Query with Objects

Circular View References

Creation of Tables and Types

View Creation

Object Types

Column Objects vs. Row Objects

Comparing Objects

REFs

Object Identifiers (OIDs)

Collections

Methods

Invoker and Definer Rights

Function-Based Indexes on the Return Values of Type Methods

Other Considerations

New Object Format in Release 8.1

Replication

Inheritance

Constraints on Objects

Type Evolution

Performance Tuning

Parallel Query with Oracle Objects

Support for Exporting, Importing, and Loading Oracle Objects

Oracle Call Interface (OCI)

Associative Access

Navigational Access

Building an OCI Program that Manipulates Objects

OCI Tips and Techniques

Demonstration of OCI and Oracle Objects

Pro*C/C++

Associative Access in Pro*C/C++

Navigational Access in Pro*C/C++

Converting Between Oracle Types and C Types

Oracle Objects For OLE

OraObject

OraRef

OraCollection

Java: JDBC and Oracle SQLJ

JDBC Access to Oracle Object Data

Support for Objects in Oracle SQLJ

Overview of CUBE, ROLLUP, and Top-N Queries

Analyzing across Multiple Dimensions

Optimized Performance

Interpreting "[NULL]" Values in Results

Calculating Subtotals without ROLLUP

CUBE

Calculating subtotals without CUBE

When to Use CUBE

Using Other Aggregate Functions with ROLLUP and CUBE

Other Considerations when Using ROLLUP and CUBE

Hierarchy Handling in ROLLUP and CUBE

Column Capacity in ROLLUP and CUBE

HAVING Clause Used with ROLLUP and CUBE

Optimized "Top-N" Analysis

Details

Examples

Reference

XA Library-Related Information

General Information about the Oracle XA

README.doc

Changes from Release 8.0 to Release 8.1

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

Database Links

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

Registration

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

Restrictions

Troubleshooting

Trace Files

Trace File Examples

In-doubt or Pending Transactions

Oracle Server SYS Account Tables

Contents

Title and Copyright Information

Send Us Your Comments

Preface

Part I Introduction To Working With The Server

1 Programmatic Environments

2 Visual Modelling for Software Development

Part II Designing the Database

3 Managing Schema Objects

4 Selecting a Datatype

5 Maintaining Data Integrity

6 Selecting an Index Strategy

7 Managing Index-Organized Tables

8 Processing SQL Statements

9 Dynamic SQL

10 Using Procedures and Packages

11 External Routines

12 Establishing Security Policies

Part III The Active Database

13 Using Triggers

14 Working With System Events

15 Using Publish-Subscribe

Part IV The Object-Relational Database Management System

16 User-Defined Datatypes

17 Objects in Views

18 Design Considerations for Oracle Objects

19 Programmatic Environments for Oracle Objects

Part V CUBE and ROLLUP Extensions to SQL

20 Analyzing Data with ROLLUP, CUBE, AND TOP-N QUERIES

A Oracle XA

Index