Professional Oracle Programming

by Greenwald, Rick; Stackowiak, Robert; Dodge, Gary; Klein, David; Shapiro, Ben; Chelliah, Christopher G.

• ISBN13: 9780764574825
• ISBN10: 0764574825
• Format: Paperback
• Copyright: 2005-06-17
• Publisher: Wrox

Summary

One of the only Oracle books to focus exclusively on database programming rather than administration Oracle owns sixty percent of the commercial database market Provides full coverage of the latest Oracle version, 10g-including new features such as regular expressions and the MODEL SQL clause-as well as versions 8, 8i, and 9i The authors are well-known as Oracle gurus-Greenwald is the author of Oracle in a Nutshell and the coauthor, with Stackowiak, of Oracle 9 Essentials Shows how to use Oracle data and data structures to build robust, scalable database applications using Java, SQL, and PL/SQL

Author Biography

Rick Greenwald has been in the technology industry for over 20 years and is the author of 12 previous books, most of them on Oracle. He has been involved with development and databases for his entire career, including stops at Data General, Cognos, Gupta Technologies, and his current employer, Oracle. Computers and computing are a sideline for Rick — his real job is father to his three wonderful girls, with a primary hobby of music appreciation.

Robert Stackowiak is Senior Director of Business Intelligence (BI) in Oracle’s Technology Business Unit. He is recognized worldwide for his expertise in business intelligence and data warehousing in leading the North American BI team at Oracle. His background includes over 20 years in IT related roles at Oracle, IBM, Harris Computer Systems, and the U.S. Army Corps of Engineers including management of technical teams, software development, sales and sales consulting, systems engineering, and business development.

Gary Dodge has been focused on database technology since his first COBOL programming job with IMS DB/DC in 1976. He joined Oracle Corporation in 1987 and has served in various management and technical positions within both the sales and consulting divisions. He has been a frequent speaker on database topics at many local and national information technology conferences. In addition to several magazine articles, he is co-author (with Tim Gorman) of Oracle8 Data Warehousing and Essential Oracle8i Data Warehousing, both published by John Wiley & Sons.

David Klein has been in the technology industry for over 20 years with a variety of companies, including Data General, Wang Laboratories, Gupta Technologies, Oracle, and a few consulting services companies. He has had many roles, including management of application development and database design teams, sales and sales consulting, systems engineering and marketing. Recently, he has focused on developing classroom and online training courses. An active wife and two boys and a 200-year-old house take up any free time.

Ben Shapiro is the president of ObjectArts Inc., a New York City-based technology consulting company, and has been designing database systems with Oracle since 1997. ObjectArts has worked with many large corporate clients developing XML-based publishing tools and web-based applications. Before ObjectArts, Ben worked with several NYC-based startup companies as a technical lead building content management software.

Christopher G. Chelliah joined Oracle as a Consultant in 1995. He brings with him project management, architecture, and development experience from a number of large, multinational sites in the mining, oil and gas, and telecom industries. Chris has been involved with emerging and database technologies for his entire career and is an accomplished software architect with a flair for business development. His expertise has been actively sought by a number of major Oracle clients in Australia, Europe, and the United States. Chris, his wife and two kids are currently in Singapore, where he leads a team defining and executing on innovative E-Government strategies for Oracle’s Public Sector industry in Asia Pacific.

Table of Contents

About the Authors	v
Acknowledgments	ix
Introduction	xxvii
Introduction	xxvii
What Does This Book Cover?	xxvii
Who Is This Book For?	xxvii
What You Need to Use This Book	xxviii
How Is This Book Structured?	xxviii
Part I: Oracle Essentials	xxviii
Part II: Data Topics	xxix
Part III: Database Programming Languages	xxix
Part IV: Programming Techniques	xxix
Part V: Business Intelligence Techniques	xxx
Part VI: Optimization	xxx
The Bigger Picture	xxx
Conventions	xxx
Source Code	xxxi
Errata	xxxi
p2p.wrox.com	xxxii
Chapter 1: Oracle Architecture and Storage	1	(14)
Architecture	2	(7)
The Instance	2	(4)
Data Organization	6	(3)
Data Types	9	(4)
Character Data Types	9	(1)
BFILE	10	(1)
Numeric Data Types	11	(1)
Date Data Types	12	(1)
RAW and LONG RAW	12	(1)
Other Data Types	13	(1)
Summary	13	(2)
Chapter 2: Using SQL	15	(12)
The Processing Cycle for SQL Statements	15	(6)
Connecting to a Database	16	(2)
Establishing a Cursor	18	(1)
Submitting SQL Statements	18	(3)
Receiving Data	21	(1)
Performance Considerations	21	(5)
Retrieval Performance	22	(1)
Using Bind Variables	23	(2)
Parallel Operations	25	(1)
Summary	26	(1)
Chapter 3: Handling Multiple Users	27	(16)
Goals	28	(1)
Data Integrity	28	(1)
Isolation	29	(1)
Serialization	29	(1)
Transactions	29	(1)
Concurrent User Integrity Problems	30	(3)
Locks	33	(1)
Contention	34	(1)
The Oracle Solution	35	(4)
Multiversion Read Consistency	35	(2)
Integrity Implications	37	(1)
Performance Implications	37	(1)
Isolation Levels	38	(1)
Implementation Issues	39	(2)
Write Contention	39	(1)
Avoiding Changed Data	40	(1)
Summary	41	(2)
Chapter 4: Database Design Basics	43	(24)
Database Design Phases	45	(8)
Conceptual Database Design	45	(1)
Logical Database Design	45	(1)
Physical Design	46	(1)
Practical Design	47	(1)
Database Design Tools	48	(1)
Database Design Techniques	49	(4)
Database Design Case Study Example	53	(1)
Normalization	54	(8)
First Normal Form	56	(3)
Second Normal Form	59	(1)
Third Normal Form	60	(1)
Other Keys	60	(2)
Normalization Summary	62	(1)
Defining Additional Entities (Tables)	62	(1)
Denormalization	63	(1)
Other Physical Design Options	64	(2)
Object-Oriented Design Options of Oracle	66	(1)
Summary	66	(1)
Chapter 5: Oracle Security	67	(26)
Security Concepts	67	(2)
Confidentiality and Privacy	67	(1)
Integrity	68	(1)
Why Database-Level Security?	68	(1)
Authentication	69	(4)
Users and Passwords	69	(1)
Smart Cards and Biometric Identification	70	(1)
Oracle Syntax	70	(3)
Authorization	73	(5)
System Privileges	74	(1)
Object Privileges	74	(1)
Roles	75	(3)
Stored Procedures	78	(1)
Access Control	78	(7)
Views	79	(1)
Encryption	80	(1)
Virtual Private Database	81	(3)
Oracle Label Security Option	84	(1)
Accountability	85	(3)
Auditing	85	(3)
Triggers	88	(1)
Enterprise Security	88	(4)
LDAP	89	(1)
Enterprise Users	89	(1)
Shared Schemas	89	(1)
Database Security	90	(1)
Network Security	90	(2)
Security Practices	92	(1)
Summary	92	(1)
Chapter 6: The Oracle Data Dictionary	93	(20)
What Is the Data Dictionary?	93	(1)
Structure of the Oracle Data Dictionary	94	(7)
USER_TABLES	95	(1)
ALL_TABLES	96	(1)
DBA_TABLES	97	(1)
TAB	98	(1)
V_$FIXED_TABLE	99	(2)
The Oracle Data Dictionary During Development	101	(7)
Locating and Describing Data Dictionary Views	101	(2)
Which Set of Views?	103	(4)
Updating the Data Dictionary	107	(1)
The Oracle Data Dictionary at Execution Time	108	(4)
SQL to Generate SQL	108	(2)
Dynamic SQL	110	(2)
Summary	112	(1)
Chapter 7: Installing Oracle	113	(16)
Getting Oracle Software	113	(3)
Installing Oracle Server Software	116	(6)
System Requirements	116	(1)
Installing the Software	117	(5)
Accessing Your Oracle Database	122	(1)
Installing Oracle Client Software	123	(4)
Java Drivers	124	(1)
Oracle Client Software	124	(1)
Installing the Instant Client	125	(1)
Which Driver?	126	(1)
Summary	127	(2)
Chapter 8: Introduction to SQL	129	(30)
A Bit of History	129	(1)
Standards	130	(1)
SQL Statement Groupings	130	(1)
Data Definition Language (DDL)	131	(4)
CREATE	131	(2)
ALTER	133	(1)
DROP	134	(1)
Caveat!	134	(1)
Data Manipulation Language (DML)	135	(1)
SQL: Set-Orientated and Nonprocedural	135	(21)
SELECT Statement	135	(12)
Multi-Table Access	147	(3)
Subqueries	150	(3)
Insert	153	(2)
UPDATE	155	(1)
DELETE	155	(1)
COMMIT/ROLLBACK/SAVEPOINT	156	(1)
Summary	156	(3)
Chapter 9: Extended SQL	159	(26)
Extended Conditions	159	(7)
LIKE and REGEXP_LIKE	159	(1)
IN and EXISTS	160	(4)
ALL, SOME, and ANY	164	(2)
Extended Set Operations	166	(5)
UNION	166	(2)
INTERSECT	168	(1)
MINUS	169	(2)
MERGE	171	(3)
An Example	172	(1)
Caveats	173	(1)
TRUNCATE	174	(1)
ROWNUM and Ranking	174	(5)
Using ROWNUM	174	(3)
Using Analytics	177	(2)
Flashback Query	179	(4)
How It Works	180	(1)
Syntax	180	(1)
An Example of Flashback Query	180	(3)
Caveats	183	(1)
Extended Flashback Capabilities	183	(1)
Returning Data from Write Operations	183	(1)
Additional Extensions	184	(1)
Summary	184	(1)
Chapter 10: Indexes	185	(28)
Index Basics	186	(7)
How an Index Works	186	(4)
Keys	190	(1)
Index Attributes	190	(3)
B-Tree Indexes	193	(3)
How It Works	193	(1)
B-Tree Index Myths	194	(2)
Reverse Key Indexes	196	(1)
Function-Based Indexes	197	(5)
Function-Based Indexes at Work	198	(3)
Caveats	201	(1)
Domain Indexes	202	(1)
Bitmap Indexes	202	(4)
The Structure of a Bitmap Index	203	(1)
The Impact of a Bitmap Index	204	(2)
Bitmap Join Index	206	(1)
Index-Organized Tables	207	(1)
Index Clusters	207	(2)
Hash Cluster	209	(1)
Design and Performance Tips	209	(2)
Start Small	209	(1)
Key Compression	210	(1)
SQL Access Advisor	211	(1)
Summary	211	(2)
Chapter 11: Constraints	213	(28)
Database Integrity Features	213	(3)
Data Typing	214	(1)
Constraints	215	(1)
Triggers and Stored Procedures	215	(1)
Types of Constraints	216	(16)
NOT NULL	217	(2)
PRIMARY KEY	219	(2)
UNIQUE	221	(1)
FOREIGN KEY	222	(4)
CHECK	226	(4)
DEFAULT	230	(1)
REF Constraints	231	(1)
Working with Constraints	232	(8)
Alter Table	233	(1)
Constraint States	234	(2)
Constraints and Indexes	236	(2)
Deferrable Constraints	238	(1)
Direct Path Operations	238	(1)
Constraints and Export/Import	239	(1)
Effect on Mutating Table Triggers	239	(1)
Constraints on Views	239	(1)
Using Database Constraints for Application-Side Rule Checking	240	(1)
Summary	240	(1)
Chapter 12: Other Database Structures	241	(24)
Tablespaces	241	(2)
Temporary Tablespaces	242	(1)
Undo Tablespaces	243	(1)
Compression	243	(2)
Partitioning	245	(1)
Views	246	(2)
Materialized Views	248	(1)
Sequences	248	(2)
Global Temporary Tables	250	(1)
Recycle Bin	250	(3)
Synonyms	253	(2)
Why Use Synonyms?	253	(1)
Working with Synonyms	253	(2)
Database Links	255	(1)
Advanced Queuing and Streams	255	(3)
Streams AQ	255	(1)
Streams	256	(2)
Streams Summary	258	(1)
Objects	258	(1)
Stored Procedures, Functions, and Triggers	259	(1)
Accessing Flat Files	260	(1)
External Tables	260	(1)
UTL_FILE	260	(1)
SGA Buffer Pool Assignments	260	(1)
Workflows	261	(1)
Workspace Manager	262	(1)
Summary	263	(2)
Chapter 13: Functions	265	(50)
What Is a Function?	265	(1)
Defining Your Own Functions	265	(3)
Permissions to Create Functions	266	(1)
Where Can Functions Be Used?	266	(1)
Defining a Java Function	267	(1)
Viewing Information about Your Functions	267	(1)
Deleting a Function	268	(1)
Oracle's Built-In SQL Functions	268	(45)
Aggregate Functions	268	(7)
Numeric Functions	275	(6)
Character Functions	281	(7)
Date and Time Functions	288	(7)
Conversion Functions	295	(8)
Collection Functions	303	(1)
Other Oracle Functions	303	(6)
XML Functions	309	(4)
Summary	313	(2)
Chapter 14: Distributed Queries, Transactions, and Databases	315	(10)
Linking Distributed Oracle Databases	316	(3)
Distributed Transactions and Two-Phase Commit	319	(1)
Heterogeneous Services	320	(3)
ODBC	321	(1)
Transparent Gateways	322	(1)
Summary	323	(2)
Chapter 15: PL/SQL Basics	325	(26)
Code Architecture	325	(6)
The Block Header	326	(2)
The Declaration Section	328	(1)
The Execution Section	328	(1)
The Exception Section	329	(1)
How Exceptions Are Handled	329	(1)
Error-Related Functions	330	(1)
Scoping and Notation	330	(1)
PL/SQL language	331	(4)
Implementing Logic	335	(6)
Conditional Logic	335	(3)
Branching	338	(1)
Iterative	339	(1)
Doing Nothing	340	(1)
Creating a PL/SQL Program	341	(3)
The Purpose of the Sample Procedure	341	(1)
Starting the Code	341	(1)
Adding Variables	342	(1)
Adding Logic	342	(1)
Adding Exception Handling	343	(1)
Using PL/SQL Code	344	(4)
Preparing for Compilation	344	(1)
Compiling PL/SQL Code	344	(1)
Running PL/SQL Code	345	(1)
Compilation, Source Code, and Dependencies	346	(2)
Security	348	(1)
Granting Access to PL/SQL units	348	(1)
Program Unit Rights	348	(1)
Native Compilation	349	(1)
Uses of PL/SQL Units	350	(1)
Summary	350	(1)
Chapter 16: PL/SQL and SQL	351	(28)
Basic SQL in PL/SQL	351	(1)
Cursors	352	(1)
Creating a Cursor	352	(1)
Opening a Cursor	352	(1)
Fetching Data	352	(1)
Closing the Cursor	353	(1)
Cursor Attributes	353	(1)
Using a Single Cursor	353	(4)
Declare Variables	354	(1)
Declare Cursor	354	(1)
Open a Cursor	354	(1)
Fetch	355	(1)
Clean Up	356	(1)
Running the Procedure	356	(1)
Special Data Types	357	(2)
%TYPE	357	(1)
%ROWTYPE	358	(1)
FOR Cursor	359	(1)
Syntax	359	(1)
Modifying the Example	359	(1)
Implicit Cursors	360	(2)
REF CURSORs	362	(1)
PL/SQL Records and Collections	363	(6)
PL/SQL Records	363	(1)
PL/SQL Records and SQL	364	(1)
Associative Arrays	364	(1)
Nested Tables	365	(1)
Variable Arrays	366	(1)
Working with Collections	366	(1)
Collection Operations	367	(2)
Which Collection Type?	369	(1)
BULK COLLECT	369	(4)
Using BULK COLLECT	370	(3)
FORALL	373	(2)
Exception Cases	373	(1)
FORALL Enhancements	374	(1)
Dynamic SQL	375	(1)
EXECUTE IMMEDIATE	376	(1)
Bulk Operations with Dynamic SQL	376	(1)
More on Dynamic SQL	376	(1)
Summary	376	(3)
Chapter 17: PL/SQL Packages	379	(18)
What Is a Package?	379	(1)
Impact of Packages	380	(3)
Organization	381	(1)
Visibility	381	(1)
Scope	381	(1)
Dependencies	382	(1)
Runtime Memory Usage	382	(1)
Oracle's Endorsement	383	(1)
An Example	383	(8)
The Package Specification	383	(1)
The Package Body	384	(6)
Running the Example	390	(1)
Using Packages Together	391	(5)
Your Second Package	392	(1)
Package Code	392	(1)
Changing the First Package	393	(1)
Impact on the Second Package	394	(2)
Summary	396	(1)
Chapter 18: Introduction to Java Database Programming	397	(40)
Java Architecture	398	(8)
Java: The Language	398	(1)
Java Virtual Machine and Bytecode	398	(1)
What about Performance?	399	(1)
J2SE and J2EE	400	(6)
Oracle's Java Architecture	406	(7)
JDBC and Persistence Frameworks	408	(3)
Java outside the Database	411	(1)
Java inside the Database	412	(1)
Building Sample Applications	413	(21)
Setting the CLASSPATH	413	(1)
Java Application Using JDBC-Basic Ingredients	414	(5)
Java Application Using OracleAS TopLink	419	(8)
Java Stored Procedures	427	(7)
Summary	434	(3)
Chapter 19: Triggers	437	(18)
What Are Triggers?	437	(1)
Types of Triggers	437	(1)
Order of Execution	438	(1)
Working with Triggers	438	(5)
Naming Triggers	438	(1)
Permissions for Creating Triggers	439	(1)
Creating Triggers	439	(1)
:new and :old	440	(1)
Update Triggers	441	(1)
Delete Triggers	441	(1)
Controlling When the Trigger Fires	442	(1)
Inserting or Updating?	443	(1)
Special Circumstances	443	(5)
INSTEAD OF Triggers	444	(4)
Triggers for Referential Integrity	448	(1)
Triggers in Other Languages	448	(1)
Triggers and Mutating Tables	449	(2)
Removing Triggers	451	(1)
Deleting/Dropping Triggers	451	(1)
Disabling Triggers	451	(1)
Recompiling Triggers	452	(1)
Viewing Triggers in the Data Dictionary	452	(1)
Limitations of Triggers	453	(1)
Summary	453	(2)
Chapter 20: Regular Expressions and Expression Filter	455	(32)
Regular Expressions	456	(20)
Regular Expression Concepts	457	(4)
Relevance to Database Applications	461	(1)
Oracle Regular Expressions	462	(8)
Usage Scenarios	470	(5)
Architecture and Performance Considerations	475	(1)
Expression Filter	476	(9)
Expression Filter Concepts	478	(6)
Relevance to Database Applications	484	(1)
Summary	485	(2)
Chapter 21: Object Relational Interactions with Oracle	487	(34)
Object Relational	488	(1)
ODBMS and RDBMS and ORDBMS	488	(1)
Introducing Oracle Objects	489	(12)
Object Types	490	(3)
Object Type Methods	493	(4)
Inheritance Support	497	(2)
Schema Evolution	499	(1)
Mapping to UML Concepts	500	(1)
Using Oracle Object Types	501	(10)
Existing Java Class as a Persistent Object Type	501	(3)
Creating Instances of Object Types in the Database	504	(2)
Using Java to Interact with Database Objects	506	(5)
Object Views on Relational Data	511	(4)
Oracle JPublisher (JPub)	515	(5)
Configuration and Run Time	516	(1)
Publishing SQL and DML Statements	517	(1)
Publishing Java Stored Procedures	518	(1)
Publishing Object Types	519	(1)
Support for Web Services	520	(1)
Summary	520	(1)
Chapter 22: Oracle XML DB	521	(58)
Overview of the XML DB Repository	521	(15)
Protocol-Based Access to XML in the Oracle Repository	522	(7)
SQL-Based Access to XML Data in the Oracle Repository	529	(4)
Enabling a New Schema for Use with XDB Repositories	533	(1)
ACL-Based Security on Oracle XML Repositories	533	(3)
XMLType	536	(4)
Creating XMLType Columns and Tables	537	(1)
XML Views	538	(2)
XML Schema	540
SQL/XML and Query Rewrite	533	(21)
SQL to XML	554	(3)
Transforming XML	557	(1)
A Content Management Example	558	(19)
Creating the Content Management Application Front End	558	(16)
Oracle XML Outside the Database-Oracle XDK	574	(3)
Summary	577	(2)
Chapter 23: HTML-DB	579	(50)
What Is HTML-DB?	579	(1)
The HTML-DB Environment	580	(1)
Installing HTML-DB	581	(7)
Preparing a Development Environment	588	(5)
Creating a Workspace	588	(3)
Creating an Application Developer	591	(2)
Importing Data	593	(6)
Creating an Application	599	(11)
Functionality in Your HTML-DB Application	610	(8)
Adding a New Page	618	(4)
Modifying Page Attributes	622	(3)
Running the Application	625	(1)
Is That All There Is?	626	(1)
The Next Step	627	(1)
Summary	627	(2)
Chapter 24: High-Speed Data Movement	629	(10)
Export/Import and the "Data Pump"	630	(2)
Transportable Tablespaces	632	(2)
Usage Considerations	632	(1)
Transportable Tablespace Examples	632	(2)
Streams	634	(4)
Streams Phases and Processes	634	(1)
Configuring and Monitoring Advanced Queues	635	(3)
Summary	638	(1)
Chapter 25: Data Loading and Management	639	(14)
SQL* Loader	640	(1)
External Table Database Feature	641	(2)
Defining External Tables	641	(1)
Loading from External Tables	642	(1)
Transformations	643	(1)
Change Data Capture	644	(1)
Partitioning and the Speed of ETL	645	(2)
Oracle Warehouse Builder	647	(5)
Typical Build and Deploy Steps Using OWB	647	(1)
An Example Using OWB	648	(4)
Nonrelational Targets	652	(1)
Summary	652	(1)
Chapter 26: Business Intelligence Query	653	(18)
Schema for Business Intelligence	654	(7)
Dimensions and Hierarchies	658	(2)
Creating Dimensions	660	(1)
Summary Tables and Materialized Views	661	(4)
Using the SQL Access Advisor (Summary Advisor)	663	(1)
Creating and Editing Materialized Views	664	(1)
Aggregation through SQL: Rollup and Cube	665	(2)
Proactive Management of Complex Query Workloads	667	(2)
Summary	669	(2)
Chapter 27: Business Intelligence Analysis	671	(18)
SQL Analytics and Statistics	671	(11)
Ranking and Percentiles	672	(3)
Windows Functions for Cumulative and Moving Aggregates	675	(3)
Lag/Lead Analysis	678	(1)
First/Last Analysis	679	(1)
Linear Regression and Other Advanced Functions	680	(2)
OLAP	682	(4)
Data Mining	686	(2)
Summary	688	(1)
Chapter 28: Optimization	689	(36)
What Is Optimization?	689	(1)
The Oracle Optimizer	690	(1)
Optimizer Modes	691	(1)
Desupported Optimizer Modes	691	(1)
Setting Optimizer Modes	692	(1)
Statistics	692	(7)
Statistics and Cost	692	(1)
Types of Statistics	693	(1)
Collecting Statistics	694	(1)
Statistics and Change	694	(1)
Production and Test Environments	695	(1)
Histograms	696	(1)
Working with Histograms	696	(3)
Execution Plans	699	(3)
The Execution Plan	699	(1)
Data Operations	700	(2)
Execution Statistics	702	(7)
Overall Execution Statistics	703	(1)
Detailed Statistics	704	(1)
Execution Plan and Statistics-An Example	704	(5)
Viewing Execution Plans and Statistics	709	(7)
AUTOTRACE	709	(1)
EXPLAIN PLAN	710	(2)
SQL Trace and TKPROF	712	(3)
Enterprise Manager	715	(1)
V$SQL_PLAN and V$SQL_PLAN_STATISTICS	715	(1)
Hints	716	(6)
Syntax of a Hint	716	(1)
Available Hints	716	(4)
Parallel Operations Hints	720	(1)
Now Don't Use Them	721	(1)
Stored Outlines	722	(1)
Creating Stored Outlines	722	(1)
Using Stored Outlines	723	(1)
Editing Stored Outlines	723	(1)
Summary	723	(2)
Index	725