Cover image for Beginning Oracle programming
Beginning Oracle programming
Dillon, Sean.
Publication Information:
Berkeley, CA: Apress, [2002]

Physical Description:
xvii, 1099 pages : illustrations ; 23 cm
General Note:
"The expert's voice"--Cover.

Includes index.

"Books for professional by professionals"--Back cover.
Title Subject:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.D3 B445 2002 Adult Non-Fiction Central Closed Stacks

On Order



* A detailed tutorial that takes you from no knowledge of Oracle programming to mastery, teaching you how to write correct, production quality code right from the start.

* A clear, step-by-step guide to every aspect of programming the Oracle database, with practical programming techniques and troubleshooting advice from Oracle experts.

* Provides an extensive SQL toolkit to tackle common day-to-day database tasks

Author Notes

Sean Dillon: Sean Dillon started programming a Commodore Vic 20 back in 1980 at the age of nine. Sean grew and so did his love for programming. He began his professional programming career in the United States Marine Corps, shortly after returning from the Gulf War. Sean is now a Principal Technologist, working for Oracle Corporation based in Reston, Virginia. He has ten years of experience specializing in database architecture, database tuning, web application development, XML, and Web Services technologies.

Christopher Beck: Christopher Beck has a bachelor's degree in Computer Science from Rutgers University. He has worked in the industry for 11 years. Starting off as a junior Ada software developer for a government systems integrator, he has spent the last seven years with Oracle Corporation and is now a Principal Technologist there. He specializes in core database technologies and web application development. WebView, a "science project" that he and a colleague wrote, has now become the commercial product known as Oracle Portal.

Thomas Kyte: Thomas Kyte is the Vice President of the Core Technologies Group at Oracle Corporation. He has been working for Oracle since version 7.0.9 (that's 1993 for people who don't mark time by Oracle versions). However, he has been working with Oracle since about version 5.1.5c. Before coming to work at Oracle, Thomas worked for more than six years as a systems integrator building large scale, heterogeneous databases and applications, mostly for military and government customers. These days, he spends a great deal of time working with the Oracle database and, more specifically, helping people who are using the Oracle database. He works directly with customers, either in specifying and building their systems or, more frequently, helping them rebuild or tune them ('tuning' frequently being a synonym for rebuilding). In addition, he is the Tom behind 'AskTom' in Oracle Magazine, answering people's questions about the Oracle database and tools. On a typical day, he receives and answers 30 to 40 questions at Every two months, he publishes a 'best of' in the magazine (all of the questions asked are available on the Web - stored in an Oracle database of course). He is the author of "Expert One-on-One Oracle" and "Effective Oracle by Design" and the contributing author of "Professional Oracle 8i Application Programming".

Joel Kallman: Joel Kallman is a Web systems technologist for Oracle Corporation. His background over the past 13 years has centered around database and content management, from SGML databases and publishing systems to text and document management. He is currently developing solutions and tools for customers to easily build database-centric Web applications. When he's not consumed by the daily advances in computer technology, he enjoys reading about investment principles as well as working out at the local "Y". Joel is a proud alumnus of The Ohio State University, where he received his bachelor's degree in computer engineering. He and his wife Kristin reside in Powell, Ohio.

Table of Contents

Introductionp. 1
Why Read This Book?p. 2
Who Is This Book for?p. 2
What's Covered in This Book?p. 4
What You Need To Use This Bookp. 5
Conventionsp. 5
Customer Supportp. 7
Chapter 1 Getting To Know Oraclep. 11
Oracle Fundamental Termsp. 11
Using Oraclep. 14
A Family of Databasesp. 19
Summaryp. 21
Chapter 2 SQL*Plus and Basic Queryingp. 23
Introduction To SQL*Plusp. 24
Quick and simple, Querying the Databasep. 31
What Is SQL?p. 42
What's in a Table?p. 44
Data Manipulation Languagep. 46
Other DML Statementsp. 70
Rollbackp. 78
Summaryp. 79
Chapter 3 Creating and Managing Users and Tablesp. 81
Data Definition Languagep. 81
Tablespacesp. 83
Usersp. 84
Oracle Data Typesp. 90
Creating Tablesp. 106
The Data Dictionaryp. 118
Summaryp. 123
Chapter 4 The New 9i Sample Schemasp. 125
The SCOTT Schemap. 125
The Oracle 9i Sample Schemasp. 126
Summaryp. 150
Chapter 5 Architecturep. 153
Why Learn the Architecture?p. 154
Making Connectionsp. 154
Server Processesp. 159
Filesp. 161
Memory Areasp. 172
Background Processesp. 176
Overview of the Architecturep. 182
Summaryp. 183
Chapter 6 Processing Statements in Oraclep. 189
Classes of SQL Statementsp. 189
How Statements Are Executedp. 190
A Query from Start To Finishp. 202
DML from Start To Finishp. 203
DDL Processingp. 207
Summaryp. 208
Chapter 7 Tablesp. 211
An Introduction To Tables in Oraclep. 211
Types of Tablesp. 212
Table Propertiesp. 231
Alter Tablep. 237
Drop Tablep. 246
Truncate Tablep. 249
Summaryp. 250
Chapter 8 Indexesp. 253
Indexes At Workp. 253
Indexes in Oraclep. 254
When Is an Index Useful?p. 258
The Cost of Indexesp. 261
Concatenationp. 275
Indexes and Constraintsp. 287
Reverse Key Indexesp. 291
Function-based Indexesp. 294
Bitmap Indexesp. 300
Bitmap Join Indexesp. 304
Summaryp. 306
Chapter 9 Mastering SQL*Plusp. 309
Advanced Startup Optionsp. 310
Using SQL*Plus Day-to-Dayp. 319
SQL*Plus Commands: A Primerp. 319
Built-In SQL*Plus HELPp. 332
Working with the SQL*Plus Bufferp. 336
Scriptsp. 339
Summaryp. 342
Chapter 10 PL/SQLp. 345
An Overviewp. 345
Block-Based Developmentp. 346
Declarationsp. 349
PL/SQL Data Typesp. 356
PL/SQL Collectionsp. 358
Cursorsp. 372
Control Statementsp. 385
Error Handlingp. 394
Summaryp. 408
Chapter 11 Procedures, Functions, and Packagesp. 411
Advantages and Benefitsp. 411
Proceduresp. 412
Functionsp. 436
Packagesp. 442
Data Dictionaryp. 459
Wrap Utilityp. 461
Summaryp. 463
Chapter 12 Transactions and Concurrencyp. 465
What Is a Transaction?p. 465
Transaction Control Statementsp. 466
ACID Properties of Transactionsp. 475
Concurrency Controlp. 484
Summaryp. 496
Chapter 13 Securityp. 499
Overview of Database Securityp. 500
Fine-Grained Access Controlp. 525
Data Securityp. 535
Summaryp. 540
Chapter 14 Viewsp. 543
Relational Viewsp. 544
Inline Viewsp. 562
Object Viewsp. 565
Materialized Viewsp. 568
Summaryp. 577
Chapter 15 Triggersp. 579
Getting Startedp. 580
Types of Triggersp. 583
Enabling and Disabling Triggersp. 607
Transactions and Triggersp. 609
Getting Trigger Information from the Data Dictionaryp. 610
Event Attribute Functionsp. 611
Summaryp. 616
Chapter 16 Objectsp. 619
Object-Oriented Conceptsp. 620
What Is an Object Type?p. 621
Using Object Typesp. 622
Object Tablesp. 653
Summaryp. 659
Chapter 17 Advanced Queryingp. 661
Herarchical Queriesp. 661
User-Defined Aggregate Functionsp. 672
Table and Pipelined Functionsp. 677
Using Queries To Write SQL with SQLp. 684
Analytic Functionsp. 686
Mergep. 698
Summaryp. 704
Chapter 18 Tuning Toolsp. 707
An Approach To Tuningp. 708
Benchmarkingp. 709
Bind Variablesp. 710
Explain Planp. 716
Autotracep. 718
Trace Files and Timed_Statisticsp. 725
Summaryp. 739
Chapter 19 A SQL Toolkitp. 741
Scriptsp. 743
Summaryp. 784
Case Study 1 Debugging PL/SQLp. 787
Historyp. 787
Requirementsp. 788
Database Design and Setupp. 789
Package Layoutp. 791
Implementationp. 793
Using Debugp. 817
Troubleshooting Debugp. 817
Debug In-Production Codep. 818
Summaryp. 821
Case Study 2 Finding Peoplep. 823
Historyp. 823
The Applicationp. 826
User Interfacep. 846
Summaryp. 853
Appendix A SQL Function Referencep. 855
Dualp. 855
Number Functionsp. 856
Datetime Functionsp. 861
Character Functionsp. 870
Conversion Functionsp. 878
Aggregation Functionsp. 888
Miscellaneous Functionsp. 890
Appendix B Supplied Packagesp. 901
Appendix C Data Dictionaryp. 971
DBA_Tablesp. 978
DBA_Jobsp. 995
DBA_Typesp. 996
DBA_Lobsp. 1001
DBA_Viewsp. 1003
DBA_Sourcep. 1008
DBA_Indexesp. 1013
DBA_Rolesp. 1017
DBA_Directoriesp. 1020
Short Namesp. 1023
Appendix D Installing Sample Schemasp. 1025
Installing the SCOTT Schemap. 1025
Installing the Oracle 9/Schemasp. 1026
Installation Wrap Upp. 1038
Appendix E Options and Featuresp. 1041
V$ Optionp. 1057
Indexp. 1059