ewtoc

High Performance Oracle Database Automation
(Imprint: Coriolis)
(Publisher: The Coriolis Group)
Author: Jonathan Ingram
ISBN: 1576101525

---

Introduction

A Note From Donald Burleson

Chapter 1Database Automation In The Real World

Scripting

Backup And Restoration

Creating And Maintaining Users

Dynamic Code Generation

Testing

Other Uses

Stored PL/SQL Objects

Procedures

Functions

Packages

Database Triggers

The Future Of Database Automation

The Business Rules Engine

Summary

Chapter 2The Architecture Of The Oracle Database, SQL, And PL/SQL

The Architecture Of The Oracle Database

Constraints

Indexing

Privileges And Roles

Schemas

Sequences

Snapshots

Synonyms

Tables And Columns

Views

The System Global Area

What Is SQL?

Data Definition Language

Data Manipulation Language

COMMIT And ROLLBACK

Datatypes

Joins

Table Aliases

Locks

NULL Values

Operators

Subqueries

An Overview Of PL/SQL

Blocks

Cursors

Datatypes

Exceptions And Exception Handling

IF-THEN-ELSE Logic

The NULL Statement

Looping Constructs

Stored Objects

A PL/SQL Wish List

How Does The Database Parse SQL And PL/SQL?

Summary

Chapter 3SQL And PL/SQL Scripting

The Many Faces Of Scripting

Backup And Restore

Dynamic Code Generation

Security And User Administration

Reporting

Testing

Other Tasks

Documenting Scripts

Scripting In SQL*Plus

SQL*Plus Commands

Connecting To SQL*Plus

Logging Out Of SQL*Plus

Script Parameters

Spooling Output To Files

Substitution Variables

PL/SQL In Scripts

Anonymous PL/SQL Blocks

Using DBMS_Output And UTL_File

Step-By-Step: Building A Report In SQL*Plus

Requirements

Pseudocode

Code

Summary

Chapter 4Procedures

Advantages Of Procedures

Embedded SQL

Maintainability

Modularity

Performance Improvement

Reusability

Creating And Dropping Procedures

Recompiling Procedures

Local Procedures

Dependencies

Parameters

Datatypes

Parameter Types

Default Values

Calling Procedures

Anonymous PL/SQL Blocks

Stored PL/SQL Objects

Notation

Procedure Structure

The Procedure Declaration

Variable Declarations

Executable Declarations

The Procedure Body

Exception Handlers

Documenting Procedures

The Header

Pseudocode

Comments

Identifiers

Step-By-Step: Design A Procedure

Procedure Requirements

Design

Pseudocode

Code

Testing The Procedure

Summary

Chapter 5Functions

Advantages Of Functions

Embedded SQL

Maintainability

Modularity

Performance Improvement

Reliability

Creating And Dropping Functions

Local Functions

Dependencies

Parameters

Datatypes

Return Values

Datatypes

Calling Functions

DML Statements

Anonymous PL/SQL Blocks

Stored PL/SQL Objects

The Structure Of A Function

The Function Declaration

Variable Declarations

Executable Declarations

The Function Body

Exception Handling

Documenting Functions

The Header

Pseudocode

Comments

Identifiers

Step-By-Step: Creating A Function

Function Requirements

Designing The Function

Pseudocode

Code

Testing

Summary

Chapter 6Packages

The Package Spec

Global Variables

Procedures

Functions

Overloading Procedures And Functions

The Package Body

Procedures

Functions

Initializing A Package

Step-By-Step: Building A Package

Requirements

Pseudocode

Code

Testing

Summary

Chapter 7Database Triggers

Features Of Database Triggers

Embedded DML Statements

Event-Driven Processing

Maintainability

Performance Improvement

Referencing Column Values

Reusability

The :old And :new Specifications

Boolean Functions

Creating And Dropping Triggers

Recompiling Database Triggers

Disabling And Enabling Triggers

Mutating Table Errors

Referencing A Triggers Associated Table

Foreign Key References

Cascading Deletes

Working Around The Rules

The Data Dictionary And Trigger Source Code

Typical Uses For Triggers

Enforcing Complex Business Rules

Updating Relevant Data

Marking Rows For Processing

Signaling An Event

Trigger Structure

Trigger Declaration

Triggering Event

Associated Table

Trigger Level

WHEN Clause

Trigger Body

Documenting Triggers

Trigger Header

Step-By-Step: Creating A Trigger

Trigger Requirements

Determining The Triggers Level

Pseudocode

Code

Testing The Trigger

Summary

Chapter 8Debugging

Compile Errors

Listing Compile Errors

Fixing Compile Errors

Using Data Dictionary Tables

Line Numbers

When The Line Number Is Wrong

Runtime Errors

Using The DBMS_Output Package

Locating Runtime Errors

Handling Exceptions Cleanly During Execution

Useful Functions

Summary

Chapter 9Special Packages

DBMS_ALERT

The Register() Procedure

The Remove() Procedure

The RemoveAll() Procedure

The Set_Defaults() Procedure

The Signal() Procedure

The WaitAny() Procedure

The WaitOne() Procedure

Using Signals

DBMS_DDL

The Alter_Compile() Procedure

The Analyze_Object() Procedure

DBMS_Describe

DBMS_Job

The Broken() Procedure

The Change() Procedure

The Interval() Procedure

The ISubmit() Procedure

The Next_Date() Procedure

The Remove() Procedure

The Run() Procedure

The Submit() Procedure

The User_Export() Procedure

The What() Procedure

Scheduling A Job

DBMS_Output

DBMS_Pipe

The Create_Pipe() Function

The Next_Item_Type() Function

The Pack_Message() Procedure

The Purge() Procedure

The Receive_Message() Function

The Remove_Pipe() Function

The Send_Message() Function

The Unique_Session_Name() Function

The Unpack_Message() Procedure

Using Pipes

DBMS_SQL

The Bind_Variable() Procedures

The Close_Cursor() Procedure

The Column_Value() Procedures

The Define_Column() Procedures

The Execute() Function

The Execute_And_Fetch() Function

The Fetch_Rows() Function

The Open_Cursor() Function

The Parse() Procedure

The Variable_Value() Procedures

Using The DBMS_SQL Package

DBMS_Utility

UTL_File

The FClose() Procedure

The FClose_All() Procedure

The FFlush() Procedure

The FOpen() Function

The Get_Line() Function

The Is_Open() Function

The New_Line() Procedure

The Put() Procedure

The PutF() Procedure

The Put_Line() Procedure

Using The UTL_File Package

Summary

Chapter 10Performance Tuning

Tuning SQL

Using The EXPLAIN PLAN Statement

Using TKPROF

Other Tuning Tips

Tuning PL/SQL

Using Cursors

Exception Handling

Summary

Appendix A

Appendix B

Appendix C

Appendix D

Appendix E

Index