Oracle PL/SQL Programming, 2nd Edition

Larger Cover

By Steven Feuerstein, Bill Pribyl

Publisher: O'Reilly Media

Released: September 1997

Pages: 1024

The first edition of Oracle PL/SQL Programming quickly became an indispensable reference for both novice and experienced PL/SQL developers. Packed with examples and recommendations, it helped everyone using PL/SQL (from the Oracle Forms developer to the database administrator) make the most of this powerful language.

Oracle8 presents PL/SQL programmers with new challenges by increasing both the possibilities and complexities of the language. This new edition updates the original book for Oracle8, adding chapters describing the new PL/SQL object features (object types, collections, object views, and external procedures). The second edition also contains a much-requested chapter on tuning PL/SQL, as well as expanded discussions of debugging and tracing PL/SQL execution.

The book comes with a companion diskette containing the Companion Utilities Guide for Oracle PL/SQL Programming, an online tool developed by RevealNet, Inc. The online guide contains more than 100 file of source code and documentation prepared by the authors.

Even if you've already read the first edition of Oracle PL/SQL Programming, you'll find an enormous amount of new and revised information in this second edition and on its companion diskette. If you're new to PL/SQL, you'll soon find yourself on the road to mastery.

NOTE: To make room for the new material, some summaries and code from the first edition have been moved to the companion disk.

Oracle PL/SQL Programming is divided into seven parts:

Foreword Preface

Part I: Programming in PL/SQL

1. Introduction to PL/SQL
2. PL/SQL Language Fundamentals
3. Effective Coding Style

Part II: PL/SQL Language Elements

4. Variables and Program Data

5. Conditional and Sequential Control

6. Database Interaction and Cursors

7. Loops

8. Exception Handlers

9. Records in PL/SQL

10.PL/SQL Tables

Part III: Built-in Functions and Packages

11. Character Functions
12. Date Functions
13. Numeric, LOB, and Miscellaneous Functions
14. Conversion Functions

Part IV: Modular Code

15. Procedures and Functions
16. Packages
17. Calling Stored PL/SQL Functions from SQL

Part V: PL/SQL Oracle8 Features

18. Object Types
19. Nested Tables and VARRAYs
20. Object Views
21. External Procedures

Part VI: Making PL/SQL Programs Work

22. Code Design Tips
23. Managing Code in the Database
24. Debugging PL/SQL
25. Tracing PL/SQL
26. Tuning PL/SQL

Part VII: Appendixes

A. What's on the Companion Disk?
B. Calling Stored Procedures from PL/SQL Version 1.1
C. Built-in Packages

Programming in PL/SQL
1. Chapter 1 Introduction to PL/SQL
  1. What Is PL/SQL?
  2. The Concept of Programming in Oracle Applications
  3. The Origins of PL/SQL
  4. PL/SQL Versions
  5. Advice for Oracle Programmers
  6. A Few of My Favorite (PL/SQL) Things
  7. Best Practices for PL/SQL Excellence
2. Chapter 2 PL/SQL Language Fundamentals
  1. The PL/SQL Character Set
  2. Identifiers
  3. Literals
  4. The Semicolon Delimiter
  5. Comments
  6. The PRAGMA Keyword
  7. Block Structure
3. Chapter 3 Effective Coding Style
  1. Fundamentals of Effective Layout
  2. Formatting SQL Statements
  3. Formatting Control Structures
  4. Formatting PL/SQL Blocks
  5. Formatting Packages
  6. Using Comments Effectively
  7. Documenting the Entire Package
PL/SQL Language Elements
1. Chapter 4 Variables and Program Data
  1. Identifiers
  2. Scalar Datatypes
  3. NULLs in PL/SQL
  4. Variable Declarations
  5. Anchored Declarations
  6. Programmer-Defined Subtypes
  7. Tips for Creating and Using Variables
2. Chapter 5 Conditional and Sequential Control
  1. Conditional Control Statements
  2. Sequential Control Statements
3. Chapter 6 Database Interaction and Cursors
  1. Transaction Management
  2. Cursors in PL/SQL
  3. Implicit and Explicit Cursors
  4. Declaring Cursors
  5. Opening Cursors
  6. Fetching from Cursors
  7. Column Aliases in Cursors
  8. Closing Cursors
  9. Cursor Attributes
  10. Cursor Parameters
  11. SELECT FOR UPDATE in Cursors
  12. Cursor Variables
  13. Working with Cursors
4. Chapter 7 Loops
  1. Loop Basics
  2. The Simple Loop
  3. The Numeric FOR Loop
  4. The Cursor FOR Loop
  5. The WHILE Loop
  6. Managing Loop Execution
  7. Tips for PL/SQL Loops
5. Chapter 8 Exception Handlers
  1. Why Exception Handling?
  2. The Exception Section
  3. Types of Exceptions
  4. Determining Exception-Handling Behavior
  5. Raising an Exception
  6. Handling Exceptions
  7. Client-Server Error Communication
  8. NO_DATA_FOUND: Multipurpose Exception
  9. Exception Handler as IF Statement
  10. RAISE Nothing but Exceptions
6. Chapter 9 Records in PL/SQL
  1. Record Basics
  2. Table-Based Records
  3. Cursor-Based Records
  4. Programmer-Defined Records
  5. Assigning Values to and from Records
  6. Record Types and Record Compatibility
  7. Nested Records
7. Chapter 10 PL/SQL Tables
  1. PL/SQL Tables and Other Collections
  2. Characteristics of PL/SQL Tables
  3. PL/SQL Tables and DML Statements
  4. Declaring a PL/SQL Table
  5. Referencing and Modifying PL/SQL Table Rows
  6. Filling the Rows of a PL/SQL Table
  7. Clearing the PL/SQL Table
  8. PL/SQL Table Enhancements in PL/SQL Release 2.3
  9. Working with PL/SQL Tables
Built-In Functions
1. Chapter 11 Character Functions
  1. Character Function Descriptions
  2. Character Function Examples
2. Chapter 12 Date Functions
  1. Date Function Descriptions
  2. Date Function Examples
3. Chapter 13 Numeric, LOB, and Miscellaneous Functions
  1. Numeric Function Descriptions
  2. LOB Function Descriptions
  3. Miscellaneous Function Descriptions
4. Chapter 14 Conversion Functions
  1. Conversion Formats
  2. Conversion Function Descriptions
  3. Conversion Function Examples
Modular Code
1. Chapter 15 Procedures and Functions
  1. Modular Code
  2. Review of PL/SQL Block Structure
  3. The Anonymous PL/SQL Block
  4. Procedures
  5. Functions
  6. Parameters
  7. Local Modules
  8. Module Overloading
  9. Forward Declarations
  10. Go Forth and Modularize!
2. Chapter 16 Packages
  1. The Benefits of Packages
  2. Overview of Package Structure
  3. The Package Specification
  4. The Package Body
  5. Package Data
  6. Package Initialization
3. Chapter 17 Calling PL/SQL Functions in SQL
  1. Looking at the Problem
  2. Syntax for Calling Stored Functions in SQL
  3. Requirements for Stored Functions in SQL
  4. Restrictions on PL/SQL Functions in SQL
  5. Calling Packaged Functions in SQL
  6. Column/Function Name Precedence
  7. Realities: Calling PL/SQL Functions in SQL
  8. Examples of Embedded PL/SQL
New PL/SQL8 Features
1. Chapter 18 Object Types
  1. Introduction to Oracle8 Objects
  2. Oracle Objects Example
  3. Syntax for Creating Object Types
  4. Manipulating Objects in PL/SQL and SQL
  5. Modifying Persistent Objects
  6. Object Housekeeping
  7. Making the Objects Option Work
2. Chapter 19 Nested Tables and VARRAYs
  1. Types of Collections
  2. Creating the New Collections
  3. Syntax for Declaring Collection Datatypes
  4. Using Collections
  5. Collection Pseudo-Functions
  6. Collection Built-Ins
  7. Example: PL/SQL-to-Server Integration
  8. Collections Housekeeping
  9. Which Collection Type Should I Use?
3. Chapter 20 Object Views
  1. Example: Using Object Views
  2. INSTEAD OF Triggers
  3. Syntax for Object Views
  4. Differences Between Object Views and Object Tables
  5. Not All Views with Objects Are Object Views
  6. Schema Evolution
  7. Object Views Housekeeping
  8. Postscript: Using the BFILE Datatype
4. Chapter 21 External Procedures
  1. Introduction to External Procedures
  2. Steps in Creating an External Procedure
  3. Syntax for External Procedures
  4. Mapping Parameters
  5. OCI Service Routines
  6. External Procedure Housekeeping
  7. Examples
Making PL/SQL Programs Work
1. Chapter 22 Code Design Tips
  1. Select Meaningful Module and Parameter Names
  2. Build the Most Functional Functions
  3. Take Full Advantage of Local Modularization
  4. Be Wary of Modules Without Any Parameters
  5. Create Independent Modules
  6. Construct Abstract Data Types (ADTs)
  7. Tips for Parameter Design
2. Chapter 23 Managing Code in the Database
  1. Executing Stored Code
  2. Transaction Integrity and Execute Authority
  3. Module Validation and Dependency Management
  4. Remote Procedure Calls
  5. Managing Stored Objects with SQL*Plus
  6. Using SQL to Examine Stored Objects
  7. Encrypting Stored Code
3. Chapter 24 Debugging PL/SQL
  1. The Wrong Way to Debug
  2. Debugging Tips and Strategies
4. Chapter 25 Tuning PL/SQL Applications
  1. Analyzing Program Performance
  2. Tuning Access to Compiled Code
  3. Tuning Access to Your Data
  4. Tuning Your Algorithms
  5. Overview of PL/SQL8 Enhancements
5. Chapter 26 Tracing PL/SQL Execution
  1. The PL/SQL Trace Facility
  2. Tracing for Production Support
  3. Free Format Filtering
  4. Structured Interface Filtering
  5. Quick-and-Dirty Tracing
Appendixes
1. Appendix B Calling Stored Procedures from PL/SQL Version 1.1
  1. Using Stubs to Talk to Server-Side PL/SQL
  2. Restrictions on Calling Stored Procedures
2. Appendix C Built-In Packages
  1. Using the Built-in Packages
  2. DBMS_ALERT
  3. Oracle AQ, the Advanced Queueing Facility
  4. DBMS_DDL
  5. DBMS_ JOB
  6. DBMS_LOB (PL/SQL8 Only)
  7. DBMS_LOCK
  8. DBMS_MAIL
  9. DBMS_OUTPUT
  10. DBMS_PIPE
  11. DBMS_ROWID (PL/SQL8 Only)
  12. DBMS_SESSION
  13. DBMS_SNAPSHOT
  14. DBMS_SQL
  15. DBMS_TRANSACTION
  16. DBMS_UTILITY
  17. UTL_FILE

Colophon

Title:

Oracle PL/SQL Programming, 2nd Edition

By:

Steven Feuerstein, Bill Pribyl

Publisher:

O'Reilly Media

Formats:

Print:

September 1997

Pages:

1024

Print ISBN:

978-1-56592-335-5

| ISBN 10:

1-56592-335-9

Steven Feuerstein

Steven Feuerstein is considered one of the world's leading experts on the Oracle PL/SQL language. He is the author or coauthor of Oracle PL/SQL Programming, Oracle PL/SQL Best Practices, Oracle PL/SQL Programming: Guide to Oracle8i Features, Oracle PL/SQL Developer's Workbook, Oracle Built-in Packages, Advanced Oracle PL/SQL Programming with Packages, and several pocket reference books (all from O'Reilly & Associates). Steven is a Senior Technology Advisor with Quest Software, has been developing software since 1980, and worked for Oracle Corporation from 1987 to 1992.

View Steven Feuerstein's full profile page.
Bill Pribyl

Bill Pribyl, author, teacher, and software consultant,is the primary author of Learning Oracle PL/SQL and the coauthor of Oracle PL/SQL Programming and its companion pocket reference, all published by O'Reilly & Associates. An Oracle user since 1986, Bill has consulted on many aspects of using Oracle products. He recently spearheaded PLNet.org, a Web-based repository where developers can share open source PL/SQL.

View Bill Pribyl's full profile page.

Colophon

Our look is the result of reader comments, our own experimentation, and feedback from distribution channels. Distinctive covers complement our distinctive approach to technical topics, breathing personality and life into potentially dry subjects. Ants are featured on the cover of Oracle PL/SQL Programming, Second Edition. At least 8,000 different species of ants can be found everywhere on Earth except the North and South Poles. Ants preserved in amber suggest that these insects existed 50 million years before humans.

Queen ants establish new communities, or nests, after their mating flight. On this flight the queen mates with several males. After mating, the males fall to Earth and die. In some species of red ant, the queen snaps the male in half after mating with him. The queen then finds an uninhabited nest, settles into it, and pulls her wings off. She will never fly again, and after removing her wings she is able to absorb the wing muscles as nutrients for her eggs. She will continue to lay eggs, thousands of them, for years.

During the three-stage development process, which takes about two months, the eggs, larvae, and pupae are cared for by the nurse ants, who feed, clean, and care fully move the young to warmer or cooler places in the nest, depending on the temperature. These nurse ants are, in turn, cared for by other worker ants, who feed the nurses with regurgitated food. The workers and the nurses will fight together to defend the young against enemies if the nest is invaded, either by another group of ants or by a larger animal. Edie Freedman designed this cover and the entire bestiary that appears on the Nutshell Handbooks. The beasts themselves are adapted from 19th-century engravings from the Dover Pictorial Archive. The cover layout was produced with Quark XPress 3.3 using the ITC Garamond font. Whenever possible, our books use RepKoverTM, a durable and flexible lay-flat binding. If the page count exceeds RepKover's limit, perfect binding is used.

The inside layout was designed by Edie Freedman and Nancy Priest and implemented in FrameMaker 5.0 by Mike Sierra. The text and heading fonts are ITC Garamond Light and Garamond Book. The illustrations that appear in the book were created in Macromedia Freehand 5.0 by Chris Reilley and Robert Romano. This colophon was written by Clairemarie Fisher O'Leary.

Description

Table of Contents

Product Details

About the Author

Colophon