Oracle PL/SQL Programming, Third Edition

Oracle PL/SQL Programming, Third Edition

by
Released September 2002
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596003814

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon
Start your free trial

Book description

Nearly a quarter-million PL/SQL programmers--novices and experienced developers alike--have found the first and second editions of Oracle PL/SQL Programming to be indispensable references to this powerful language. Packed with examples and recommendations, this book has helped everyone, from Oracle Forms developers to database administrators, make the most of PL/SQL. This new edition is a comprehensive update of the original book, covering all versions of PL/SQL through Oracle9i Release 2. It adds much-requested new chapters on how to create and run PL/SQL programs, call Java methods from within PL/SQL, and define and use database triggers. An extensive new chapter--designed especially for experienced PL/SQL developers--describes PL/SQL's runtime architecture and how to use knowledge of Oracle internals to get the best performance from PL/SQL. The book contains information about the latest Oracle9i PL/SQL features, including:

  • Record-based DML: You can now use records in INSERT and DELETE statements.

  • Table functions: These are functions that return a result set (in the form of a PL/SQL collection). Such functions existed in Oracle8i but they are now much expanded.

  • New and improved datatypes: Oracle now offers dramatically improved support for timestamps, time zone management, and interval calculations. In addition, the XMLType datatype has now been implemented.

  • Inheritance for object types: You can now define a hierarchy of object types (which were first introduced in Oracle8).

  • Enhancements to PL/SQL collections. PL/SQL now supports multiple-level collections (nesting collections within collections), as well as associative arrays (previously called index-by tables), which allow you to index by PLS_INTEGER and VARCHAR2.

  • Native compilation of PL/SQL code: PL/SQL source code can now optionally be compiled into native object code that is linked into Oracle.

  • Part I, Programming in PL/SQL

  • Part II, PL/SQL Program Structure

  • Part III, PL/SQL Program Data

  • Part IV, SQL in PL/SQL

  • Part V, PL/SQL Application Construction

  • Part VI, Advanced PL/SQL Topics

The book is divided into six parts:

Even if you've been a PL/SQL developer for years, you'll find an enormous amount of new and revised information in this third edition and on its companion web site. If you're new to PL/SQL, you'll find Oracle PL/SQL Programming an invaluable companion on the road to mastery.

Publisher resources

Download Example Code

Table of contents

  1. Oracle PL/SQL Programming, 3rd Edition
    1. Preface
      1. Objectives of This Book
      2. Structure of This Book
        1. About the Contents
        2. What This Book Does Not Cover
      3. Conventions Used in This Book
      4. Which Platform or Version?
      5. About the Code
      6. Comments and Questions
      7. Acknowledgments
    2. I. Programming in PL/SQL
      1. 1. Introduction to PL/SQL
        1. What Is PL/SQL?
        2. The Origins of PL/SQL
          1. The Early Years of PL/SQL
          2. Improved Application Portability
          3. Improved Execution Authority and Transaction Integrity
          4. Humble Beginnings, Steady Improvement
        3. So This Is PL/SQL
          1. Integration with SQL
          2. Control and Conditional Logic
          3. When Things Go Wrong
        4. About PL/SQL Versions
          1. Oracle8i New Features
            1. Autonomous transactions
            2. Invoker rights
            3. Native dynamic SQL (NDS)
            4. Bulk binds and collects
            5. New trigger capabilities
            6. Calling Java from PL/SQL
          2. Oracle9i New Features
            1. Record-based DML
            2. Table functions
            3. New and improved datatypes
            4. Inheritance for object types
            5. Enhancements to PL/SQL collections
            6. Native compilation of PL/SQL code
          3. Working with Multiple Versions of PL/SQL
        5. Resources for PL/SQL Developers
          1. The O’Reilly PL/SQL Series
          2. Other Printed Resources
          3. PL/SQL on the Internet
          4. Development Tools and Utilities
        6. Some Words of Advice
          1. Don’t Be in Such a Hurry!
          2. Don’t Be Afraid to Ask for Help
          3. Take a Creative, Even Radical Approach
      2. 2. Creating and Running PL/SQL Code
        1. SQL*Plus
          1. Starting Up SQL*Plus
          2. Running a SQL Statement
          3. Running a PL/SQL Program
          4. Running a Script
          5. Other SQL*Plus Tasks
            1. Setting your preferences
            2. Saving output to a file
            3. Exiting SQL*Plus
            4. Editing a statement
            5. Loading your own custom environment automatically on startup
          6. Error Handling in SQL*Plus
          7. Why You Will Love and Hate SQL*Plus
        2. Performing Essential PL/SQL Tasks
          1. Creating a Stored Program
          2. Executing a Stored Program
          3. Showing Stored Programs
          4. Managing Grants and Synonyms for Stored Programs
          5. Dropping a Stored Program
          6. Hiding the Source Code of a Stored Program
        3. Oracle’s PL/SQL-Based Developer Tools
          1. Moving PL/SQL Programs Between Client and Server
        4. Calling PL/SQL from Other Languages
          1. C: Using Oracle’s Precompiler (Pro*C)
          2. Java: Using JDBC
          3. Perl: Using Perl DBI and DBD::Oracle
          4. PL/SQL Server Pages
        5. And What Else?
      3. 3. Language Fundamentals
        1. PL/SQL Block Structure
          1. Sections of the PL/SQL Block
          2. Anonymous Blocks
            1. The structure of an anonymous block
            2. Examples of anonymous blocks
            3. Anonymous blocks in different environments
          3. Named Blocks
          4. Nested Blocks
          5. Scope
          6. Visibility
            1. “Visible” identifiers
            2. Qualified identifiers
            3. Qualifying identifier names with module names
        2. The PL/SQL Character Set
        3. Identifiers
          1. Reserved Words
            1. Language keywords
            2. Identifiers from STANDARD package
            3. Approaches to avoiding reserved words
          2. Whitespace and Keywords
        4. Literals
          1. Embedding Single Quotes Inside a String
          2. Numeric Literals
          3. Boolean Literals
        5. The Semicolon Delimiter
        6. Comments
          1. Single-Line Comment Syntax
          2. Multiline Comment Syntax
        7. The PRAGMA Keyword
        8. Labels
    3. II. PL/SQL Program Structure
      1. 4. Conditional and Sequential Control
        1. IF Statements
          1. The IF-THEN Combination
          2. The IF-THEN-ELSE Combination
          3. The IF-THEN-ELSIF Combination
          4. Nested IF Statements
        2. CASE Statements
          1. Simple CASE Statements
          2. Searched CASE Statements
          3. Nested CASE Statements
          4. CASE Expressions
        3. The GOTO Statement
          1. Restrictions on the GOTO Statement
            1. At least one executable statement must follow a label
            2. The target label must be in the same scope as the GOTO statement
            3. The target label must be in the same part of the PL/SQL block as the GOTO
        4. The NULL Statement
          1. Improving Program Readability
          2. Nullifying a Raised Exception
          3. Using NULL After a Label
      2. 5. Iterative Processing with Loops
        1. Loop Basics
          1. Examples of Different Loops
          2. Structure of PL/SQL Loops
        2. The Simple Loop
          1. Terminating a Simple Loop: EXIT and EXIT WHEN
          2. Emulating a REPEAT UNTIL Loop
        3. The WHILE Loop
        4. The Numeric FOR Loop
          1. Rules for Numeric FOR Loops
          2. Examples of Numeric FOR Loops
          3. Handling Nontrivial Increments
        5. The Cursor FOR Loop
          1. Example of Cursor FOR Loops
        6. Loop Labels
        7. Tips for Iterative Processing
          1. Use Understandable Names for Loop Indexes
          2. The Proper Way to Say Goodbye
          3. Obtaining Information About FOR Loop Execution
          4. SQL Statement as Loop
      3. 6. Exception Handlers
        1. How PL/SQL Deals with Errors
          1. Adopting an Exception-Handling Strategy
          2. Exception-Handling Concepts and Terminology
        2. Defining Exceptions
          1. Declaring Named Exceptions
          2. Associating Exception Names with Error Codes
            1. Using EXCEPTION_INIT
            2. Recommended uses of EXCEPTION_INIT
          3. About Named System Exceptions
          4. Scope of an Exception
        3. Raising Exceptions
          1. The RAISE Statement
            1. Raising exceptions in nested blocks
          2. Using RAISE_APPLICATION_ERROR
        4. Handling Exceptions
          1. Combining Multiple Exceptions in a Single Handler
          2. Unhandled Exceptions
          3. Using SQLCODE and SQLERRM in Handler Clauses
          4. Continuing Past Exceptions
          5. Propagation of an Unhandled Exception
            1. Losing exception information
            2. Examples of propagation
          6. Using Standardized Error Handler Programs
    4. III. PL/SQL Program Data
      1. 7. Working with Program Data
        1. Naming Your Program Data
        2. Overview of PL/SQL Datatypes
          1. Character Data
          2. Numbers
          3. Dates, Timestamps, and Intervals
          4. Booleans
          5. Binary Data
          6. ROWIDs
          7. REF Cursors
          8. Internet Datatypes
          9. “Any” Datatypes
          10. User-Defined Datatypes
        3. Declaring Program Data
          1. Declaring a Variable
          2. Declaring Constants
          3. Constrained Declarations
          4. The NOT NULL Clause
          5. Anchored Declarations
          6. Anchoring to Cursors and Tables
          7. Benefits of Anchored Declarations
            1. Synchronization with database columns
            2. Normalization of local variables
          8. Anchoring to NOT NULL Datatypes
        4. Programmer-Defined Subtypes
        5. Conversion Between Datatypes
          1. Implicit Data Conversion
            1. Limitations of implicit conversion
            2. Drawbacks of implicit conversion
          2. Explicit Datatype Conversion
            1. The CHARTOROWID function
            2. The CAST function
            3. The CONVERT function
            4. The HEXTORAW function
            5. The RAWTOHEX function
            6. The ROWIDTOCHAR function
      2. 8. Strings
        1. The Impact of Character Sets
          1. What Is a Character Set?
          2. Types of Character Sets
          3. Database Character Set Versus National Language Character Set
          4. Character Set Issues
            1. Bytes versus characters
            2. Oracle9i string declarations
            3. Character function semantics
            4. Code points and code units
            5. Equality of Unicode strings
            6. Sort order
        2. String Datatypes
          1. The VARCHAR2 Datatype
          2. The CHAR Datatype
          3. The NVARCHAR2 and NCHAR Datatypes
          4. String Subtypes
        3. String Issues
          1. Empty Strings Are NULL Strings
          2. Mixing CHAR and VARCHAR2 Values
            1. Database-to-variable conversion
            2. Variable-to-database conversion
            3. String comparisons
            4. Character functions and CHAR arguments
          3. Specifying String Constants
        4. String Functions
          1. ASCII
          2. ASCIISTR
          3. CHR
          4. COMPOSE
          5. CONCAT
          6. DECOMPOSE
          7. GREATEST
          8. INITCAP
          9. INSTR, INSTRB, INSTRC, INSTR2, and INSTR4
          10. LEAST
          11. LENGTH, LENGTHB, LENGTHC, LENGTH2, and LENGTH4
          12. LOWER
          13. LPAD
          14. LTRIM
          15. REPLACE
          16. RPAD
          17. RTRIM
          18. SOUNDEX
          19. SUBSTR, SUBSTRB, SUBSTRC, SUBSTR2, and SUBSTR4
          20. TO_CHAR
          21. TO_MULTI_BYTE
          22. TO_SINGLE_BYTE
          23. TRANSLATE
          24. TRANSLATE...USING
          25. TRIM
          26. UNISTR
          27. UPPER
        5. NLS Functions
          1. NCHR
          2. NLS_INITCAP
          3. NLS_LOWER
          4. NLS_UPPER
          5. NLSSORT
          6. TO_NCHAR
      3. 9. Numbers
        1. Numeric Datatypes
          1. The NUMBER Type
          2. The PLS_INTEGER Type
          3. The BINARY_INTEGER Type
          4. Numeric Subtypes
        2. Number Conversions
          1. Number Format Models
          2. The TO_NUMBER Function
            1. Using TO_NUMBER with no format
            2. Using TO_NUMBER with a format model
            3. Passing NLS settings to TO_NUMBER
          3. The TO_CHAR Function
            1. Using TO_CHAR with no format
            2. Using TO_CHAR with a format model
            3. The V format element
            4. Rounding when converting numbers to character strings
            5. Dealing with spaces when converting numbers to character strings
            6. Passing NLS settings to TO_CHAR
          4. Using CAST
          5. Implicit Conversions
        3. Numeric Functions
          1. Rounding and Truncation Functions
            1. ABS
            2. ACOS
            3. ASIN
            4. ATAN
            5. ATAN2
            6. BITAND
            7. CEIL
            8. COS
            9. COSH
            10. EXP
            11. FLOOR
            12. LN
            13. LOG
            14. MOD
            15. POWER
            16. ROUND
            17. SIGN
            18. SIN
            19. SINH
            20. SQRT
            21. TAN
            22. TANH
            23. TRUNC
      4. 10. Dates and Timestamps
        1. Date and Time Datatypes
          1. The DATE Datatype
            1. Declaring DATE variables
            2. When to use DATE
            3. Limitations of DATE
          2. The TIMESTAMP Datatypes
            1. Declaring TIMESTAMP variables
            2. When to use TIMESTAMPs
          3. The INTERVAL Datatypes
            1. Declaring INTERVAL variables
            2. When to use INTERVALs
        2. Date and Timestamp Conversions
          1. Date Format Models
          2. String-to-Date Conversions
            1. TO_DATE
            2. The TO_TIMESTAMP family
            3. Dealing with time zones
            4. Date and timestamp literals
            5. The FX element
            6. The RR element
          3. Date-to-String Conversions
            1. TO_CHAR
            2. Converting time zones to character strings
            3. The FM element
          4. Interval Conversions
            1. The NUMTO family of functions
            2. The TO_xxINTERVAL functions
            3. Interval value expressions
            4. Formatting intervals for display
          5. The CAST and EXTRACT Functions
            1. The CAST function
            2. The EXTRACT function
        3. Date/Time Arithmetic
          1. Traditional Date Arithmetic
            1. Adding and subtracting numeric values
            2. Computing the difference between two dates
          2. Interval Arithmetic
            1. Adding and subtracting intervals to/from datetimes
            2. Computing the interval between two datetimes
            3. CASTing DATEs to TIMESTAMPs
            4. Adding and subtracting intervals
            5. Multiplying and dividing intervals
            6. Unconstrained INTERVAL types
        4. Date/Time Functions
          1. Functions to Get the Date and Time
          2. The Time Zone Functions
          3. The ADD_MONTHS Function
          4. The FROM_TZ Function
          5. The LAST_DAY Function
          6. The MONTHS_BETWEEN Function
          7. The ROUND and TRUNC Functions
          8. The NEW_TIME Function
          9. The NEXT_DAY Function
      5. 11. Records and Collections
        1. Records in PL/SQL
          1. Benefits of Using Records
            1. Data abstraction
            2. Aggregate operations
            3. Leaner, cleaner code
          2. Declaring Records
          3. Programmer-Defined Records
            1. Declaring programmer-defined record TYPEs
            2. Declaring the record
            3. Examples of programmer-defined record declarations
          4. Working with Records
            1. Record-level operations
            2. Field-level operations
          5. Comparing Records
        2. Collections in PL/SQL
          1. A Simple Collection Example
          2. Types of Collections
          3. Glossary of Collection Terms
          4. Making Sense of Collections
        3. Declaring Collection Types and Collections
          1. Declaring an Associative Array
            1. Defining the table TYPE
            2. Declaring the collection
          2. Declaring a Nested Table or VARRAY
            1. Examples of declaring nested tables and VARRAYs
        4. Where Collections Can Be Used
          1. Collections as Components of a Record
          2. Collections as Program Parameters
          3. Collections as Datatypes of a Function’s Return Value
          4. Collection as “Columns” in a Database Table
          5. Collections as Attributes of an Object Type
        5. Collection Built-Ins (Methods)
          1. The COUNT Method
            1. Boundary considerations
            2. Exceptions possible
          2. The DELETE Method
            1. Boundary considerations
            2. Exceptions possible
          3. The EXISTS Method
            1. Boundary considerations
            2. Exceptions possible
          4. The EXTEND Method
            1. Boundary considerations
            2. Exceptions possible
          5. The FIRST and LAST Methods
            1. Boundary considerations
            2. Exceptions possible
          6. The LIMIT Method
            1. Boundary considerations
            2. Exceptions possible
          7. The PRIOR and NEXT Methods
            1. Boundary considerations
            2. Exceptions possible
          8. The TRIM Method
            1. Boundary considerations
            2. Exceptions possible
        6. Working with Collections
          1. Initializing Collection Variables
            1. Initializing explicitly with a constructor
            2. Initializing implicitly during direct assignment
            3. Initializing implicitly via fetch
            4. VARRAY integration
          2. Assigning Values to Elements
          3. Referencing an Undefined Row
          4. Working with Collections of Composites
            1. Collections of records
            2. Collections of other complex datatypes
            3. Multilevel collections
            4. Unnamed nested collections
          5. Sequential and Nonsequential Associative Arrays
            1. Sequential usage
            2. Nonsequential usage
          6. Passing Associative Arrays as Parameters
          7. PL/SQL-to-Server Integration
          8. Using VARCHAR2 Associative Arrays
          9. Emulating Alternative Indexes in Collections
        7. Collection Pseudo-Functions
          1. The THE Pseudo-Function
          2. The TABLE Pseudo-Function
          3. The CAST Pseudo-Function
            1. Casting a named collection
            2. Casting an unnamed collection
          4. The MULTISET Pseudo-Function
          5. Sorting Contents of Collections
        8. Maintaining Collections
          1. Privileges
          2. Collections and the Data Dictionary
        9. Choosing a Collection Type
      6. 12. Miscellaneous Datatypes
        1. The BOOLEAN Datatype
        2. The RAW Datatype
        3. The UROWID and ROWID Datatypes
          1. Getting at Rowids
          2. Using Rowids
            1. Do rowids ever change?
            2. Using rowids in Oracle Forms
            3. Using rowids in a cursor FOR loop
            4. Is the use of rowids worth the effort?
        4. The LOB Datatypes
          1. The BFILE Datatype
          2. The BLOB Datatype
          3. The CLOB Datatype
          4. The NCLOB Datatype
        5. Working with LOBs
          1. Understanding LOB Locators
          2. Empty Versus NULL LOBs
          3. Creating a LOB
          4. Writing into a LOB
          5. Reading from a LOB
          6. BFILEs Are Different
            1. Creating a BFILE locator
            2. Accessing BFILEs
            3. Using BFILEs to load LOB columns
          7. Temporary LOBs
            1. Creating a temporary LOB
            2. Freeing a temporary LOB
            3. Checking to see whether a LOB is temporary
            4. Managing temporary LOBs
          8. Native LOB Operations in Oracle9i
            1. SQL semantics may yield temporary LOBs
            2. Performance impact of using SQL semantics
          9. LOB Conversion Functions
        6. Predefined Object Types
          1. The XMLType Type
          2. The URI Types
          3. The “Any” Types
    5. IV. SQL in PL/SQL
      1. 13. DML and Transaction Management
        1. DML in PL/SQL
          1. A Quick Introduction to DML
            1. The INSERT statement
            2. The UPDATE statement
            3. The DELETE statement
          2. Cursor Attributes for DML Operations
          3. RETURNING Information from DML Statements
          4. DML and Exception Handling
          5. DML and Records
            1. Record-based inserts
            2. Record-based updates
            3. Using records with the RETURNING clause
            4. Restrictions on record-based inserts and updates
        2. Bulk DML with the FORALL Statement
          1. The FORALL Statement
          2. Context-Switching Problem Scenarios
          3. FORALL Examples
          4. Cursor Attributes for FORALL
          5. ROLLBACK Behavior with FORALL
          6. Continuing Past Exceptions with FORALL
        3. Transaction Management
          1. The COMMIT Statement
          2. The ROLLBACK Statement
          3. The SAVEPOINT Statement
          4. The SET TRANSACTION Statement
          5. The LOCK TABLE Statement
        4. Autonomous Transactions
          1. Defining Autonomous Transactions
          2. When to Use Autonomous Transactions
          3. Rules and Restrictions on Autonomous Transactions
            1. Using autonomous transactions from within SQL
            2. Transaction visibility
          4. Autonomous Transactions Examples
            1. Building an autonomous logging mechanism
            2. Using autonomous transactions in a database trigger
              1. Creating a database trigger
              2. Fine-tuning the database trigger
      2. 14. Data Retrieval
        1. Cursor Basics
          1. Some Data Retrieval Terms
          2. Typical Query Operations
          3. Introduction to Cursor Attributes
            1. The %FOUND attribute
            2. The %NOTFOUND attribute
            3. The %ROWCOUNT attribute
            4. The %ISOPEN attribute
            5. The %BULK_ROWCOUNT attribute
            6. The %BULK_EXCEPTIONS attribute
          4. Referencing PL/SQL Variables in a Cursor
            1. Identifier precedence in a cursor
            2. Using standard naming conventions
          5. Choosing Between Explicit and Implicit Cursors
        2. Working with Implicit Cursors
          1. Implicit Cursor Examples
          2. Error Handling with Implicit Cursors
          3. Implicit SQL Cursor Attributes
        3. Working with Explicit Cursors
          1. Declaring Explicit Cursors
            1. Naming your cursor
            2. Declaring cursors in packages
          2. Opening Explicit Cursors
          3. Fetching from Explicit Cursors
            1. Examples of explicit cursors
            2. Fetching past the last row
          4. Column Aliases in Explicit Cursors
          5. Closing Explicit Cursors
          6. Explicit Cursor Attributes
          7. Cursor Parameters
            1. Generalizing cursors with parameters
            2. Opening cursors with parameters
            3. Scope of cursor parameters
            4. Cursor parameter modes
            5. Default values for parameters
        4. BULK COLLECT
          1. Limiting Rows Retrieved with BULK COLLECT
          2. Bulk Fetching of Multiple Columns
          3. Using the RETURNING Clause with Bulk Operations
        5. SELECT...FOR UPDATE
          1. Releasing Locks with COMMIT
          2. The WHERE CURRENT OF Clause
        6. Cursor Variables
          1. Why Cursor Variables?
          2. Similarities to Static Cursors
          3. Declaring REF CURSOR Types
          4. Declaring Cursor Variables
          5. Opening Cursor Variables
          6. Fetching from Cursor Variables
            1. Handling the ROWTYPE_MISMATCH exception
          7. Rules for Cursor Variables
            1. Compile-time rowtype matching rules
            2. Runtime rowtype matching rules
            3. Cursor variable aliases
            4. Scope of cursor object
          8. Passing Cursor Variables as Arguments
            1. Identifying the REF CURSOR type
            2. Setting the parameter mode
          9. Cursor Variable Restrictions
        7. Cursor Expressions (Oracle9i)
          1. Using Cursor Expressions
          2. Restrictions on Cursor Expressions
      3. 15. Dynamic SQL and Dynamic PL/SQL
        1. NDS Statements
          1. The EXECUTE IMMEDIATE Statement
          2. The OPEN FOR Statement
        2. Multirow Queries with Cursor Variables
          1. FETCH into Variables or Records
          2. The USING Clause in OPEN FOR
          3. Generic GROUP BY Procedure
          4. Generic GROUP BY Package
        3. Binding Variables
          1. Binding Versus Concatenation
          2. Limitations on Binding
          3. Argument Modes
          4. Duplicate Placeholders
          5. Passing NULL Values
        4. Working with Objects and Collections
        5. Building Applications with NDS
          1. Sharing NDS Programs with Invoker Rights
          2. Error Handling
          3. Dynamic PL/SQL
            1. Dramatic code reduction
            2. Generic calculator function
        6. NDS Utility Package
        7. Comparing NDS and DBMS_SQL
          1. Eyeballing Equivalent Implementations
          2. What Are NDS and DBMS_SQL Good For?
    6. V. PL/SQL Application Construction
      1. 16. Procedures, Functions,and Parameters
        1. Modular Code
        2. Procedures
          1. Calling a Procedure
          2. The Procedure Header
          3. The Procedure Body
          4. The END Descriptor
          5. The RETURN Statement
        3. Functions
          1. Structure of a Function
          2. The RETURN Datatype
          3. The END Descriptor
          4. Calling a Function
          5. Functions Without Parameters
          6. The Function Header
          7. The Function Body
          8. The RETURN Statement
            1. RETURN any valid expression
            2. Multiple RETURNs
            3. RETURN as last executable statement
        4. Parameters
          1. Defining Parameters
          2. Actual and Formal Parameters
          3. Matching Actual and Formal Parameters in PL/SQL
            1. Positional notation
            2. Named notation
            3. Benefits of named notation
          4. Parameter Modes
            1. IN mode
            2. OUT mode
            3. IN OUT mode
          5. The NOCOPY Parameter Mode Hint
            1. Restrictions on NOCOPY
            2. Impact of NOCOPY
          6. Default Values
        5. Local Modules
          1. Benefits of Local Modularization
            1. Reducing code volume
            2. Improving readability
          2. Scope of Local Modules
          3. Sprucing Up Your Code with Local Modules
        6. Module Overloading
          1. Benefits of Overloading
            1. Supporting many data combinations
          2. Restrictions on Overloading
        7. Forward Declarations
        8. Advanced Topics
          1. Calling Your Function Inside SQL
            1. Requirements for calling functions in SQL
            2. Restrictions on user-defined functions in SQL
            3. Replacing DECODEs with IF statements
            4. The PRAGMA RESTRICT_REFERENCES (Oracle8 and earlier)
          2. Table Functions
            1. Calling a function in a FROM clause
            2. Creating a pipelined function
            3. Building a transformative function
            4. Enabling a function for parallel execution
          3. Deterministic Functions
        9. Go Forth and Modularize!
      2. 17. Packages
        1. Why Packages?
          1. Demonstrating the Power of the Package
          2. Some Package-Related Concepts
          3. Diagramming Privacy
        2. Rules for Building Packages
          1. The Package Specification
          2. The Package Body
          3. Initializing Packages
            1. Execute complex initialization logic
            2. Cache static session information
            3. Avoid side effects when initializing
            4. When initialization fails
        3. Rules for Calling Packaged Elements
        4. Working with Package Data
          1. Global Within a Single Oracle Session
          2. Global Public Data
          3. Packaged Cursors
            1. Declaring packaged cursors
            2. Working with packaged cursors
          4. Serializable Packages
        5. When to Use Packages
          1. Encapsulating Data Manipulation
          2. Avoiding Hardcoding of Literals
          3. Improving Usability of Built-in Features
          4. Grouping Together Logically Related Functionality
          5. Caching Static Session Data
        6. Packages and Object Types
      3. 18. Triggers
        1. DML Triggers
          1. DML Trigger Concepts
            1. DML trigger scripts
            2. Transaction participation
          2. Creating a DML Trigger
            1. The WHEN clause
            2. Working with NEW and OLD pseudo-records
            3. Determining the DML action within a trigger
          3. DML Trigger Example: No Cheating Allowed!
            1. Applying the WHEN clause
            2. Using pseudo-records to fine-tune trigger execution
          4. Multiple Triggers of the Same Type
          5. Mutating Table Errors: Problem and Solution
            1. Mutating tables and foreign keys
            2. Getting around the mutating table error
            3. The dwindling mutation zone
        2. DDL Triggers
          1. Creating a DDL Trigger
          2. Available Events
          3. Available Attributes
          4. Working with Events and Attributes
            1. What column did I touch?
            2. Lists returned by attribute functions
          5. Dropping the Undroppable
        3. Database Event Triggers
          1. Creating a Database Event Trigger
          2. The STARTUP Trigger
          3. The SHUTDOWN Trigger
          4. The LOGON Trigger
          5. The LOGOFF Trigger
          6. The SERVERERROR Trigger
            1. SERVERERROR examples
            2. Central error handler
          7. Impact of Invalid Triggers
        4. INSTEAD OF Triggers
          1. Creating an INSTEAD OF Trigger
          2. The INSTEAD OF INSERT Trigger
          3. The INSTEAD OF UPDATE Trigger
          4. The INSTEAD OF DELETE Trigger
          5. Populating the Tables
        5. AFTER SUSPEND Triggers
          1. Setting Up for the AFTER SUSPEND Trigger
          2. Looking at the Actual Trigger
          3. Creating the AFTER SUSPEND Trigger
          4. The ORA_SPACE_ERROR_INFO Function
          5. The DBMS_RESUMABLE Package
          6. Trapped Multiple Times
          7. To Fix or Not To Fix?
        6. Maintaining Triggers
          1. Disabling, Enabling, and Dropping Triggers
          2. Viewing Triggers
          3. Checking the Validity of Triggers
      4. 19. Managing PL/SQL Applications
        1. Managing and Analyzing Code in the Database
          1. Data Dictionary Views for PL/SQL Programmers
          2. Displaying Information About Stored Objects
          3. Displaying and Searching Source Code
        2. Protecting Stored Code
          1. How to Wrap Code
          2. Working with Wrapped Code
        3. Using Native Compilation
          1. One-Time DBA Setup
          2. Interpreted Versus Native Compilation Modes
        4. Testing PL/SQL Programs
          1. Typical, Tawdry Testing Techniques
          2. For More Information...
        5. Debugging PL/SQL Programs
          1. The Wrong Way to Debug
            1. Disorganized debugging
            2. Irrational debugging
          2. Debugging Tips and Strategies
            1. Use a source code debugger
            2. Gather data
            3. Remain logical at all times
            4. Analyze instead of trying
            5. Take breaks and ask for help
            6. Change and test one area of code at a time
        6. Tuning PL/SQL Programs
          1. Analyzing Performance of PL/SQL Code
          2. Tracing Execution of Your Code
            1. Installing DBMS_TRACE
            2. DBMS_TRACE programs
            3. Controlling trace file contents
            4. Pausing and resuming the trace process
            5. Format of collected data
        7. Improving Application Performance
          1. Avoid Unnecessary Code Execution
            1. The search for unnecessary code
            2. Check your loops
            3. Defer execution until needed
          2. Be a Good Listener
          3. Use Package Data to Minimize SQL Access
          4. Use BULK COLLECT and FORALL
    7. VI. Advanced PL/SQL Topics
      1. 20. PL/SQL’s Runtime Architecture
        1. Looking Under the Hood
          1. PL/SQL Concepts
          2. Physical Storage of Server-Side PL/SQL
          3. DIANAs Who Grew Too Much
        2. Dependency Management
          1. Dependencies in Server-Side PL/SQL
          2. Healing Invalids
            1. Recompiling by hand
            2. Recompiling by script
            3. Automatic recompilation
          3. Dependencies in Client-Side PL/SQL
          4. Remote Dependencies
        3. PL/SQL’s Use of Memory in the Oracle Server
          1. Server Memory 101
          2. Cursors and Memory
          3. Tips on Reducing Memory Use
            1. Statement sharing
            2. Bind variables
            3. Packaging to improve memory use
            4. Large collections in PL/SQL
            5. Preservation of state
            6. Global, but only within a single Oracle session
          4. A Trace of Memory
        4. The Processing of Server-Side PL/SQL
          1. Compiling an Anonymous Block
          2. Compiling a Stored Object
          3. Executing PL/SQL
        5. PL/SQL Code on the Client
          1. Supported Versions and Features
          2. Limitations of Oracle’s Remote Invocation Model
          3. Client-Side PL/SQL Libraries
            1. Client PL/SQL libraries at design time
            2. Client PL/SQL libraries at runtime
        6. Execution Authority Models
          1. The Definer Rights Model
            1. Advantages of definer rights
            2. Disadvantages of definer rights
              1. Where’d my table go?
              2. How do I maintain all that code?
              3. Dynamic SQL and definer rights
          2. The Invoker Rights Model
            1. Invoker rights syntax
            2. Some rules and restrictions
          3. Combining Rights Models
        7. Hardware for PL/SQL: Bigger = Better?
          1. The Single-Processor Variation
          2. The Symmetric Multiprocessor (SMP) Variation
          3. The Clustered Variation
        8. What You Need to Know
      2. 21. Object-Oriented Aspects of PL/SQL
        1. Introduction to Oracle’s Object Features
        2. An Extended Example
          1. A Tree of Types
            1. Creating a base type
            2. Creating a subtype
          2. Methods
          3. Storing, Retrieving, and Using Persistent Objects
            1. Object identity
            2. The VALUE function
            3. The TREAT function
          4. Evolution and Creation
          5. Back to Pointers?
            1. Using REFs
            2. The REFTOHEX function
            3. The UTL_REF package
            4. REFs and type hierarchies
            5. Dangling REFs
          6. Generic Generics: The ANY Datatypes
            1. Processing an ANYDATA value
            2. Creating a transient type
          7. I Can Do It Myself
          8. Comparing Objects
            1. Attribute-level comparison
            2. The MAP method
            3. The ORDER method
            4. Additional comparison recommendations
        3. Object Views
          1. The Existing Relational System
          2. Object View with a Collection Attribute
          3. Object Subview
          4. Object View with Inverse Relationship
          5. INSTEAD OF Triggers
            1. The case against
            2. The case for
            3. The bigger question
          6. Differences Between Object Views and Object Tables
            1. OID uniqueness
            2. “Storeability” of physical versus virtual REFs
            3. REFs to non-unique OIDs
        4. Maintaining Object Types and Object Views
          1. Privileges
            1. The EXECUTE privilege
            2. The UNDER privilege
            3. The DEBUG privilege
            4. The DML privileges
        5. Pontifications
      3. 22. Calling Java from PL/SQL
        1. Oracle and Java
        2. Getting Ready to Use Java in Oracle
          1. Installing Java
          2. Building and Compiling Your Java Code
          3. Setting Privileges for Java Development and Execution
            1. Oracle8i Java security
            2. Oracle9i Java security
        3. A Simple Demonstration
          1. Finding the Java Functionality
          2. Building a Custom Java Class
          3. Compiling and Loading into Oracle
          4. Building a PL/SQL Wrapper
          5. Deleting Files from PL/SQL
        4. Using loadjava
        5. Using dropjava
        6. Managing Java in the Database
          1. The Java Namespace in Oracle
          2. Examining Loaded Java Elements
        7. Using DBMS_ JAVA
          1. LONGNAME: Converting Java Long Names
          2. GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting Compiler Options
          3. SET_OUTPUT: Enabling Output from Java
          4. EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
        8. Publishing and Using Java in PL/SQL
          1. Call Specs
          2. Some Rules for Java Wrappers
          3. Mapping Datatypes
          4. Calling a Java Method in SQL
          5. Exception Handling with Java
          6. Extending File I/O Capabilities
            1. Polishing up the delete method
            2. Obtaining directory contents
          7. Other Examples
      4. 23. External Procedures
        1. Introduction to External Procedures
          1. Example: Invoking an Operating System Command
          2. Architecture of External Procedures
          3. Limitations of External Procedures
        2. The Oracle Net Configuration
          1. Specifying the Listener Configuration
          2. Security Characteristics of the Configuration
        3. Creating an Oracle Library
        4. Writing the Call Specification
          1. The Call Spec: Overall Syntax
          2. Parameter Mapping: The Example Revisited
          3. Parameter Mapping: The Full Story
          4. More Syntax: The PARAMETERS Clause
          5. PARAMETERS Properties
            1. The INDICATOR property
            2. The LENGTH property
            3. The MAXLEN property
            4. The CHARSETID and CHARSETFORM properties
        5. Raising an Exception from the Called C Program
        6. Nondefault Agents
        7. Maintaining External Procedures
          1. Dropping Libraries
          2. Data Dictionary
          3. Rules and Warnings
    8. Index
    9. Colophon

Product information

  • Title: Oracle PL/SQL Programming, Third Edition
  • Author(s):
  • Release date: September 2002
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596003814