Oracle8i Application Developer's Guide - Fundamentals
Release 2 (8.1.6)







Title and Copyright Information

Send Us Your Comments


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
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 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
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
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
Dropping Integrity Constraints
Managing FOREIGN KEY Integrity Constraints
Defining FOREIGN KEY Integrity Constraints
Enabling FOREIGN KEY Integrity Constraints
Listing Integrity Constraint Definitions

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

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
Application Tips
Autonomous Transactions
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
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
Naming Conventions
Meeting Basic Requirements
Controlling Side Effects
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
Overriding Default Datatype Mapping
Specifying Properties
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
Authentication Through a Middle Tier
Advantages of n-Tier Authentication
Security Challenges of Three-tier Computing
Oracle8i n-Tier Authentication Solutions
Data Encryption
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
WHEN Clause
The Trigger Body
Triggers and Handling Remote Exceptions
Restrictions on Creating Triggers
Who Is the Trigger User?
Compiling Triggers
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

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


Copyright © 1999 Oracle Corporation.

All Rights Reserved.