Oracle9i Application Developer's Guide - Fundamentals
Release 1 (9.0.1)

Part Number A88876-02
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index


Go to next page


Title and Copyright Information

Send Us Your Comments


What's New in Application Development?

1 Understanding the Oracle Programmatic Environments

Overview of Developing an Oracle Application
Overview of PL/SQL
A Simple PL/SQL Example
Advantages of PL/SQL
Overview of Java, JDBC, and SQLJ
Built-In Java Libraries
Overview of Writing Procedures and Functions in Java
Overview of Writing Database Triggers in Java
Why Use Stored Java for Stored Procedures and Triggers?
Overview of Oracle JDBC
JDBC Thin Driver
JDBC Server Driver
Extensions of JDBC
Sample Program for the JDBC Thin Driver
JDBC in SQLJ Applications
Overview of Oracle SQLJ
Benefits of SQLJ
Comparison of SQLJ with JDBC
SQLJ Example for Object Types
SQLJ Stored Procedures in the Server
Overview of Pro*C/C++
How You Implement a Pro*C/C++ Application
Highlights of Pro*C/C++ Features
Overview of Pro*COBOL
How You Implement a Pro*COBOL Application
Highlights of Pro*COBOL Features
Overview of OCI and OCCI
Advantages of OCI
Parts of the OCI
Procedural and Non-Procedural Elements
Building an OCI Application
Overview of Oracle Objects for OLE (OO4O)
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
Choosing a Programming Environment
Choosing Whether to Use OCI or a Precompiler
Using Built-In Packages and Libraries
Java versus PL/SQL

2 Managing Schema Objects

Managing Tables
Designing Tables
Creating Tables
Managing Temporary Tables
Creating Temporary Tables
Using Temporary Tables
Examples: Using Temporary Tables
Tip: Referencing the Same Subquery Multiple Times
Managing Views
Creating Views
Replacing Views
Using Views
Dropping Views
Modifying a Join View
About 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
Rules for Name Resolution in SQL Statements
Renaming Schema Objects
Switching to a Different Schema
Listing Information about Schema Objects

3 Selecting a Datatype

Summary of Oracle Built-In Datatypes
Representing Character Data
Representing Numeric Data
Representing Date and Time Data
Establishing Year 2000 Compliance
Representing Geographic Coordinate Data
Representing Image, Audio, and Video Data
Representing Searchable Text Data
Representing Large Data Types
Migrating LONG Datatypes to LOB Datatypes
Using RAW and LONG RAW Datatypes
Addressing Rows Directly with the ROWID Datatype
ANSI/ISO, DB2, and SQL/DS Datatypes
How Oracle Converts Datatypes
Datatype Conversion During Assignments
Datatype Conversion During Expression Evaluation
Representing Dynamically Typed Data
Representing XML Data

4 Maintaining Data Integrity Through Constraints

Overview of Integrity Constraints
When to Enforce Business Rules with Integrity Constraints
When to Enforce Business Rules in Applications
Creating Indexes for Use with Constraints
When to Use NOT NULL Integrity Constraints
When to Use Default Column Values
Setting Default Column Values
Choosing a Table's Primary Key
When to Use UNIQUE Key Integrity Constraints
Constraints On Views for Performance, Not Data Integrity
Enforcing Referential Integrity with Constraints
About Nulls and Foreign Keys
Defining Relationships Between Parent and Child Tables
Rules for Multiple FOREIGN KEY Constraints
Deferring Constraint Checks
Managing Constraints That Have Associated Indexes
Minimizing Space and Time Overhead for Indexes Associated with Constraints
Guidelines for Indexing Foreign Keys
About Referential Integrity in a Distributed Database
When to Use CHECK Integrity Constraints
Restrictions on CHECK Constraints
Designing CHECK Constraints
Rules for Multiple CHECK Constraints
Choosing Between CHECK and NOT NULL Integrity Constraints
Examples of Defining Integrity Constraints
Defining Integrity Constraints with the CREATE TABLE Command: Example
Defining Constraints with the ALTER TABLE Command: Example
Privileges Required to Create Constraints
Naming Integrity Constraints
Enabling and Disabling Integrity Constraints
Enabling and Disabling Existing Integrity Constraints
Guidelines for Enabling and Disabling Key Integrity Constraints
Fixing Constraint Exceptions
Altering Integrity Constraints
Dropping Integrity Constraints
Managing FOREIGN KEY Integrity Constraints
Rules for FOREIGN KEY Integrity Constraints
Restriction on Enabling FOREIGN KEY Integrity Constraints
Viewing Definitions of Integrity Constraints
Examples of Defining Integrity Constraints

5 Selecting an Index Strategy

Guidelines for Application-Specific Indexes
Creating Indexes: Basic Examples
When to Use Domain Indexes
When to Use Function-Based Indexes
Advantages of Function-Based Indexes
Examples of Function-Based Indexes
Restrictions for Function-Based Indexes

6 Speeding Up Index Access with Index-Organized Tables

What Are Index-Organized Tables?
Index-Organized Tables Versus Ordinary Tables
Advantages of Index-Organized Tables
Features of Index-Organized Tables
Why Use Index-Organized Tables?
Example of an Index-Organized Table

7 How Oracle Processes SQL Statements

Overview of SQL Statement Execution
Identifying Extensions to SQL92 (FIPS Flagging)
Grouping Operations into Transactions
Improving Transaction Performance
Committing Transactions
Rolling Back Transactions
Defining Transaction Savepoints
Privileges Required for Transaction Management
Ensuring Repeatable Reads with Read-Only Transactions
Using Cursors within Applications
Declaring and Opening Cursors
Using a Cursor to Execute Statements Again
Closing Cursors
Cancelling Cursors
Locking Data Explicitly
Choosing a Locking Strategy
Letting Oracle Control Table Locking
Summary of Nondefault Locking Options
Explicitly Acquiring Row Locks
About User Locks
When to Use User Locks
Example of a User Lock
Viewing and Monitoring Locks
Using Serializable Transactions for Concurrency Control
How Serializable Transactions Interact
Setting the Isolation Level of a Transaction
Referential Integrity and Serializable Transactions
Application Tips for Transactions
Autonomous Transactions
Examples of Autonomous Transactions
Defining Autonomous Transactions
Resuming Execution After a Storage Error Condition
What Operations Can Be Resumed After an Error Condition?
Limitations on Resuming Operations After an Error Condition
Writing an Application to Handle Suspended Storage Allocation
Example of Resumable Storage Allocation
Querying Data at a Point in Time (Flashback Query)
Setting Up the Database for Flashback Query
Writing an Application that Uses Flashback Query
Limitations of Flashback Query

8 Coding Dynamic SQL Statements

What Is Dynamic SQL?
Why Use Dynamic SQL?
Executing DDL and SCL Statements in PL/SQL
Executing Dynamic Queries
Referencing Database Objects that Do Not Exist at Compilation
Optimizing Execution Dynamically
Executing Dynamic PL/SQL Blocks
Performing Dynamic Operations Using Invoker-Rights
A Dynamic SQL Scenario Using Native Dynamic SQL
Sample DML Operation Using Native Dynamic SQL
Sample DDL Operation Using Native Dynamic SQL
Sample Single-Row Query Using Native Dynamic SQL
Sample Multiple-Row Query Using Native Dynamic SQL
Choosing Between Native Dynamic SQL and 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
Using Dynamic SQL in Languages Other Than PL/SQL

9 Using Procedures and Packages

Overview of PL/SQL Program Units
Anonymous Blocks
Stored Program Units (Procedures, Functions, and Packages)
Hiding PL/SQL Code with the PL/SQL Wrapper
Compiling PL/SQL Procedures for Native Execution
Remote Dependencies
Controlling Remote Dependencies
Cursor Variables
Declaring and Opening Cursor Variables
Examples of Cursor Variables
Handling PL/SQL Compile-Time Errors
Handling Run-Time PL/SQL Errors
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 for SQL Calling a PL/SQL Function
Naming Conventions
Meeting Basic Requirements
Controlling Side Effects
Overloading Packaged PL/SQL Functions
Serially Reusable PL/SQL Packages
Returning Large Amounts of Data from a Function
Coding Your Own Aggregate Functions

10 Calling External Procedures

Overview of Multi-Language Programs
What Is an External Procedure?
Overview of The Call Specification for External Procedures
Loading External Procedures
Loading Java Class Methods
Loading External C Procedures
Publishing External Procedures
The AS LANGUAGE Clause for Java Class Methods
The AS LANGUAGE Clause for External C Procedures
Publishing Java Class Methods
Publishing External C Procedures
Locations of Call Specifications
Passing Parameters to Java Class Methods with Call Specifications
Passing Parameters to External C Procedures with Call Specifications
Specifying Datatypes
External Datatype Mappings
BY VALUE/REFERENCE for IN and IN OUT Parameter Modes
Overriding Default Datatype Mapping
Specifying Properties
Executing External Procedures with the CALL Statement
CALL Statement Syntax
Calling Java Class Methods
How the Database Server Calls External C Procedures
Handling Errors and Exceptions in Multi-Language Programs
Generic Compile Time Call specification Errors
Java Exception Handling
C Exception Handling
Using Service Procedures with External C Procedures
Doing Callbacks with External C Procedures
Object Support for OCI Callbacks
Restrictions on Callbacks
Debugging External Procedures
Demo Program
Guidelines for External C Procedures
Restrictions on External C Procedures

11 Database Security Overview for Application Developers

Introduction to Database Security Policies
Security Threats and Countermeasures
What Information Security Policies Can Cover
Features to Use in Establishing Security Policies
Recommended Application Design Practices to Reduce Risk
Introduction to Application Security Policies
Considerations for Using Application-Based Security
Security-Related Tasks of Application Administrators
Managing Application Privileges
Creating Secure Application Roles
Associating Privileges with the User's Database Role
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

12 Implementing Application Security Policies

Introduction to Application Context
Features of Application Context
Ways to Use Application Context with Fine-Grained Access Control
User Models and Virtual Private Database
Creating a Virtual Private Database Policy with Oracle Policy Manager
How to Use Application Context
Examples: Application Context Within a Fine-Grained Access Control Function
Automatic Reparse
Introduction to Application Context Accessed Globally
Initializing Application Context Externally
Initializing Application Context Globally
Introduction to Fine-Grained Access Control
Features of Fine-Grained Access Control
How Fine-Grained Access Control Works
How to Establish Policy Groups
How to Add a Policy to a Table or View
How to Check for Policies Applied to Statement
Fine-Grained Auditing
Introduction to Standard Auditing and Fine-Grained auditing
Standard Oracle9i Auditing Techniques
Fine-Grained Auditing Techniques
Enforcing Application Security
Use of Ad Hoc Tools a Potential Security Problem
Restricting Database Roles from SQL*Plus Users

13 Proxy Authentication

Advantages of Proxy Authentication
Security Challenges of Three-tier Computing
Who Is the Real User?
Does the Middle Tier Have Too Much Privilege?
How to Audit? Whom to Audit?
Can the User Be Re-Authenticated to the Database?
Oracle9i Proxy Authentication Solutions
Passing Through the Identity of the Real User
Limiting the Privilege of the Middle Tier
Re-authenticating the Real User
Auditing Actions Taken on Behalf of the Real User
Support for Application User Models


Securing Sensitive Information
Principles of Data Encryption
Principle 1: Encryption Does Not Solve Access Control Problems
Principle 2: Encryption Does Not Protect Against a Malicious DBA
Principle 3: Encrypting Everything Does Not Make Data Secure
Solutions For Stored Data Encryption in Oracle9i
Oracle9i Data Encryption Capabilities
Data Encryption Challenges
Encrypting Indexed Data
Key Management
Key Transmission
Key Storage
Changing Encryption Keys
Binary Large Objects (BLOBS)
Example of Data Encryption PL/SQL Program

15 Using Triggers

Designing Triggers
Creating Triggers
Prerequisites for Creating Triggers
Types of Triggers
Naming Triggers
Triggering Statement
Controlling When a Trigger Is Fired (BEFORE and AFTER Options)
Modifying Complex Views (INSTEAD OF Triggers)
Firing Triggers One or Many Times (FOR EACH ROW Option)
Firing Triggers Based on Conditions (WHEN Clause)
Coding the Trigger Body
Triggers and Handling Remote Exceptions
Restrictions on Creating Triggers
Who Is the Trigger User?
Compiling Triggers
Dependencies for Triggers
Recompiling Triggers
Migration Issues for Triggers
Modifying Triggers
Debugging Triggers
Enabling and Disabling Triggers
Enabling Triggers
Disabling Triggers
Viewing Information About Triggers
Examples of Trigger Applications
Responding to System Events through Triggers
Publication Framework

16 Working With System Events

Event Attribute Functions
List of Database Events
System Events
Client Events

17 Using the Publish-Subscribe Model for Applications

Introduction to Publish-Subscribe
Publish-Subscribe Architecture
Publish-Subscribe Concepts
Examples of a Publish-Subscribe Mechanism

18 Developing Web Applications with PL/SQL

What Is a PL/SQL Web Application?
How Do I Generate HTML Output from PL/SQL?
How Do I Pass Parameters to a PL/SQL Web Application?
Performing Network Operations within PL/SQL Stored Procedures
Sending Mail from PL/SQL
Getting a Host Name or Address from PL/SQL
Working with TCP/IP Connections from PL/SQL
Retrieving the Contents of an HTTP URL from PL/SQL
Working with Tables, Image Maps, Cookies, CGI Variables, and More from PL/SQL
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 Through a URL
Examples of PL/SQL Server Pages
Debugging PL/SQL Server Page Problems
Putting an Application using PL/SQL Server Pages into Production
Enabling PL/SQL Web Applications for XML

19 Porting Non-Oracle Applications to Oracle9i

Frequently Asked Questions About Porting
How Do I Perform Natural Joins and Inner Joins?
Is There an Automated Way to Migrate a Schema and Associated Data from Another Database System?
How Do I Perform Large Numbers of Comparisons within a Query?
Does Oracle Support Scalar Subqueries?

20 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 XA with 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 Real Application Clusters 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


Go to next page
Copyright © 1996-2001, Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Go To Product List
Book List
Go To Index

Master Index