Oracle PL/SQL Programming. 4th Edition

Spis treści książki

• Oracle PL/SQL Programming, 4th Edition
  • SPECIAL OFFER: Upgrade this ebook with OReilly
  • A Note Regarding Supplemental Files
  • Preface
    • Objectives of This Book
    • Structure of This Book
      • About the Contents
      • What This Book Does Not Cover
    • Conventions Used in This Book
    • Which Platform or Version?
    • About the Code
    • Safari Enabled
    • Comments and Questions
    • Acknowledgments
  • I. Programming in PL/SQL
    • 1. Introduction to PL/SQL
      • 1.1. What Is PL/SQL?
      • 1.2. The Origins of PL/SQL
        • 1.2.1. The Early Years of PL/SQL
        • 1.2.2. Improved Application Portability
        • 1.2.3. Improved Execution Authority and Transaction Integrity
        • 1.2.4. Humble Beginnings, Steady Improvement
        • 1.2.5. The Significance of Oracle Database 10g PL/SQL
      • 1.3. So This Is PL/SQL
        • 1.3.1. Integration with SQL
        • 1.3.2. Control and Conditional Logic
        • 1.3.3. When Things Go Wrong
      • 1.4. About PL/SQL Versions
        • 1.4.1. Oracle Database 10g New Features
          • 1.4.1.1. Optimized compiler
          • 1.4.1.2. Compile-time warnings
          • 1.4.1.3. Conditional compilation
          • 1.4.1.4. Support for nonsequential collections in FORALL
          • 1.4.1.5. Improved datatype support
          • 1.4.1.6. Backtrace an exception to its line number
          • 1.4.1.7. Set operators for nested tables
          • 1.4.1.8. Support for regular expressions
          • 1.4.1.9. Programmer-defined quoting mechanism
          • 1.4.1.10. Many new built-in packages
      • 1.5. Resources for PL/SQL Developers
        • 1.5.1. The OReilly PL/SQL Series
        • 1.5.2. PL/SQL on the Internet
      • 1.6. Some Words of Advice
        • 1.6.1. Don't Be in Such a Hurry!
        • 1.6.2. Don't Be Afraid to Ask for Help
        • 1.6.3. Take a Creative, Even Radical Approach
    • 2. Creating and Running PL/SQL Code
      • 2.1. SQL*Plus
        • 2.1.1. Starting Up SQL*Plus
        • 2.1.2. Running a SQL Statement
        • 2.1.3. Running a PL/SQL Program
        • 2.1.4. Running a Script
        • 2.1.5. What Is the "Current Directory?"
        • 2.1.6. Other SQL*Plus Tasks
          • 2.1.6.1. Setting your preferences
          • 2.1.6.2. Saving output to a file
          • 2.1.6.3. Exiting SQL*Plus
          • 2.1.6.4. Editing a statement
          • 2.1.6.5. Loading your own custom environment automatically on startup
        • 2.1.7. Error Handling in SQL*Plus
        • 2.1.8. Why You Will Love and Hate SQL*Plus
      • 2.2. Performing Essential PL/SQL Tasks
        • 2.2.1. Creating a Stored Program
        • 2.2.2. Executing a Stored Program
        • 2.2.3. Showing Stored Programs
        • 2.2.4. Managing Grants and Synonyms for Stored Programs
        • 2.2.5. Dropping a Stored Program
        • 2.2.6. Hiding the Source Code of a Stored Program
      • 2.3. Calling PL/SQL from Other Languages
        • 2.3.1. C: Using Oracle's Precompiler (Pro*C)
        • 2.3.2. Java: Using JDBC
        • 2.3.3. Perl: Using Perl DBI and DBD::Oracle
        • 2.3.4. PHP: Using Oracle Extensions
        • 2.3.5. PL/SQL Server Pages
        • 2.3.6. And Where Else?
    • 3. Language Fundamentals
      • 3.1. PL/SQL Block Structure
        • 3.1.1. Anonymous Blocks
        • 3.1.2. Named Blocks
        • 3.1.3. Nested Blocks
        • 3.1.4. Scope
        • 3.1.5. Visibility
          • 3.1.5.1. "Visible" identifiers
          • 3.1.5.2. Qualified identifiers
          • 3.1.5.3. Qualifying identifier names with module names
          • 3.1.5.4. Nested programs
      • 3.2. The PL/SQL Character Set
      • 3.3. Identifiers
        • 3.3.1. Reserved Words
          • 3.3.1.1. Language keywords
          • 3.3.1.2. Identifiers from STANDARD package
          • 3.3.1.3. Approaches to avoiding reserved words
        • 3.3.2. Whitespace and Keywords
      • 3.4. Literals
        • 3.4.1. NULLs
        • 3.4.2. Embedding Single Quotes Inside a Literal String
        • 3.4.3. Numeric Literals
        • 3.4.4. Boolean Literals
      • 3.5. The Semicolon Delimiter
      • 3.6. Comments
        • 3.6.1. Single-Line Comment Syntax
        • 3.6.2. Multiline Comment Syntax
      • 3.7. The PRAGMA Keyword
      • 3.8. Labels
  • II. PL/SQL Program Structure
    • 4. Conditional and Sequential Control
      • 4.1. IF Statements
        • 4.1.1. The IF-THEN Combination
        • 4.1.2. The IF-THEN-ELSE Combination
        • 4.1.3. The IF-THEN-ELSIF Combination
        • 4.1.4. Nested IF Statements
        • 4.1.5. Short-Circuit Evaluation
      • 4.2. CASE Statements and Expressions
        • 4.2.1. Simple CASE Statements
        • 4.2.2. Searched CASE Statements
        • 4.2.3. Nested CASE Statements
        • 4.2.4. CASE Expressions
      • 4.3. The GOTO Statement
      • 4.4. The NULL Statement
        • 4.4.1. Improving Program Readability
        • 4.4.2. Nullifying a Raised Exception
        • 4.4.3. Using NULL After a Label
    • 5. Iterative Processing with Loops
      • 5.1. Loop Basics
        • 5.1.1. Examples of Different Loops
        • 5.1.2. Structure of PL/SQL Loops
      • 5.2. The Simple Loop
        • 5.2.1. Terminating a Simple Loop: EXIT and EXIT WHEN
        • 5.2.2. Emulating a REPEAT UNTIL Loop
        • 5.2.3. The Intentionally Infinite Loop
      • 5.3. The WHILE Loop
      • 5.4. The Numeric FOR Loop
        • 5.4.1. Rules for Numeric FOR Loops
        • 5.4.2. Examples of Numeric FOR Loops
        • 5.4.3. Handling Nontrivial Increments
      • 5.5. The Cursor FOR Loop
        • 5.5.1. Example of Cursor FOR Loops
      • 5.6. Loop Labels
      • 5.7. Tips for Iterative Processing
        • 5.7.1. Use Understandable Names for Loop Indexes
        • 5.7.2. The Proper Way to Say Goodbye
        • 5.7.3. Obtaining Information About FOR Loop Execution
        • 5.7.4. SQL Statement as Loop
    • 6. Exception Handlers
      • 6.1. Exception-Handling Concepts and Terminology
      • 6.2. Defining Exceptions
        • 6.2.1. Declaring Named Exceptions
        • 6.2.2. Associating Exception Names with Error Codes
          • 6.2.2.1. Using EXCEPTION_INIT
          • 6.2.2.2. Recommended uses of EXCEPTION_INIT
        • 6.2.3. About Named System Exceptions
        • 6.2.4. Scope of an Exception
      • 6.3. Raising Exceptions
        • 6.3.1. The RAISE Statement
        • 6.3.2. Using RAISE_APPLICATION_ERROR
      • 6.4. Handling Exceptions
        • 6.4.1. Built-in Error Functions
          • 6.4.1.1. More on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
          • 6.4.1.2. Just the line number, please
          • 6.4.1.3. Useful applications of SQLERRM
        • 6.4.2. Combining Multiple Exceptions in a Single Handler
        • 6.4.3. Unhandled Exceptions
        • 6.4.4. Propagation of an Unhandled Exception
          • 6.4.4.1. Losing exception information
          • 6.4.4.2. Examples of propagation
        • 6.4.5. Continuing Past Exceptions
        • 6.4.6. Writing WHEN OTHERS Handling Code
      • 6.5. Building an Effective Error Management Architecture
        • 6.5.1. Decide on Your Error Management Strategy
        • 6.5.2. Organize Use of Application-Specific Error Codes
        • 6.5.3. Work with Your Own Exception "Objects"
        • 6.5.4. Use Standardized Error Management Programs
        • 6.5.5. Create Standard Templates for Common Error Handling
      • 6.6. Making the Most of PL/SQL Error Management
  • III. PL/SQL Program Data
    • 7. Working with Program Data
      • 7.1. Naming Your Program Data
      • 7.2. Overview of PL/SQL Datatypes
        • 7.2.1. Character Data
        • 7.2.2. Numbers
        • 7.2.3. Dates, Timestamps, and Intervals
        • 7.2.4. Booleans
        • 7.2.5. Binary Data
        • 7.2.6. ROWIDs
        • 7.2.7. REF Cursors
        • 7.2.8. Internet Datatypes
        • 7.2.9. "Any" Datatypes
        • 7.2.10. User-Defined Datatypes
      • 7.3. Declaring Program Data
        • 7.3.1. Declaring a Variable
        • 7.3.2. Declaring Constants
        • 7.3.3. The NOT NULL Clause
        • 7.3.4. Anchored Declarations
        • 7.3.5. Anchoring to Cursors and Tables
        • 7.3.6. Benefits of Anchored Declarations
          • 7.3.6.1. Synchronization with database columns
          • 7.3.6.2. Normalization of local variables
        • 7.3.7. Anchoring to NOT NULL Datatypes
      • 7.4. Programmer-Defined Subtypes
      • 7.5. Conversion Between Datatypes
        • 7.5.1. Implicit Data Conversion
          • 7.5.1.1. Limitations of implicit conversion
          • 7.5.1.2. Drawbacks of implicit conversion
        • 7.5.2. Explicit Datatype Conversion
          • 7.5.2.1. The CHARTOROWID function
          • 7.5.2.2. The CAST function
          • 7.5.2.3. The CONVERT function
          • 7.5.2.4. The HEXTORAW function
          • 7.5.2.5. The RAWTOHEX function
          • 7.5.2.6. The ROWIDTOCHAR function
    • 8. Strings
      • 8.1. String Datatypes
        • 8.1.1. The VARCHAR2 Datatype
        • 8.1.2. The CHAR Datatype
        • 8.1.3. String Subtypes
      • 8.2. Working with Strings
        • 8.2.1. Specifying String Constants
        • 8.2.2. Using Nonprintable Characters
        • 8.2.3. Concatenating Strings
        • 8.2.4. Dealing with Case
          • 8.2.4.1. Forcing a string to all upper- or lowercase
          • 8.2.4.2. Making comparisons case-insensitive
          • 8.2.4.3. Capitalizing each word in a string
        • 8.2.5. Traditional Searching, Extracting, and Replacing
        • 8.2.6. Padding
        • 8.2.7. Trimming
        • 8.2.8. Regular Expression Searching, Extracting, and Replacing
          • 8.2.8.1. Detecting a pattern
          • 8.2.8.2. Locating a pattern
          • 8.2.8.3. Extracting text matching a pattern
          • 8.2.8.4. Replacing text (it's magic!)
          • 8.2.8.5. Groking greediness
          • 8.2.8.6. Learning more about regular expressions
        • 8.2.9. Working with Empty Strings
        • 8.2.10. Mixing CHAR and VARCHAR2 Values
          • 8.2.10.1. Database-to-variable conversion
          • 8.2.10.2. Variable-to-database conversion
          • 8.2.10.3. String comparisons
          • 8.2.10.4. Character functions and CHAR arguments
      • 8.3. String Function Quick Reference
    • 9. Numbers
      • 9.1. Numeric Datatypes
        • 9.1.1. The NUMBER Type
        • 9.1.2. The PLS_INTEGER Type
        • 9.1.3. The BINARY_INTEGER Type
        • 9.1.4. The BINARY_FLOAT and BINARY_DOUBLE Types
        • 9.1.5. Numeric Subtypes
      • 9.2. Number Conversions
        • 9.2.1. The TO_NUMBER Function
          • 9.2.1.1. Using TO_NUMBER with no format
          • 9.2.1.2. Using TO_NUMBER with a format model
          • 9.2.1.3. Passing NLS settings to TO_NUMBER
        • 9.2.2. The TO_CHAR Function
          • 9.2.2.1. Using TO_CHAR with no format
          • 9.2.2.2. Using TO_CHAR with a format model
          • 9.2.2.3. The V format element
          • 9.2.2.4. Rounding when converting numbers to character strings
          • 9.2.2.5. Dealing with spaces when converting numbers to character strings
          • 9.2.2.6. Passing NLS settings to TO_CHAR
        • 9.2.3. Using CAST
        • 9.2.4. Implicit Conversions
      • 9.3. Numeric Functions
        • 9.3.1. Rounding and Truncation Functions
        • 9.3.2. Trigonometric Functions
        • 9.3.3. Numeric Function Quick Reference
    • 10. Dates and Timestamps
      • 10.1. Datetime Datatypes
        • 10.1.1. Declaring Datetime Variables
        • 10.1.2. Choosing a Datetime Datatype
      • 10.2. Getting the Date and Time
      • 10.3. Interval Datatypes
        • 10.3.1. Declaring INTERVAL Variables
        • 10.3.2. When to Use INTERVALs
          • 10.3.2.1. Finding the difference between two datetime values
          • 10.3.2.2. Designating periods of time
      • 10.4. Datetime Conversions
        • 10.4.1. From Strings to Datetimes
        • 10.4.2. From Datetimes to Strings
        • 10.4.3. Working with Time Zones
        • 10.4.4. Requiring a Format Mask to Match Exactly
        • 10.4.5. Easing Up on Exact Matches
        • 10.4.6. Interpreting Two-Digit Years in a Sliding Window
        • 10.4.7. Converting Time Zones to Character Strings
        • 10.4.8. Padding Output with Fill Mode
      • 10.5. Date and Timestamp Literals
      • 10.6. Interval Conversions
        • 10.6.1. Going from Numbers to Intervals
        • 10.6.2. Converting Strings to Intervals
        • 10.6.3. Formatting Intervals for Display
      • 10.7. Interval Literals
      • 10.8. CAST and EXTRACT
        • 10.8.1. The CAST Function
        • 10.8.2. The EXTRACT Function
      • 10.9. Datetime Arithmetic
        • 10.9.1. Adding and Subtracting Intervals to/from Datetimes
        • 10.9.2. Computing the Interval Between Two Datetimes
        • 10.9.3. Mixing DATEs and TIMESTAMPs
        • 10.9.4. Adding and Subtracting Intervals
        • 10.9.5. Multiplying and Dividing Intervals
        • 10.9.6. Using Unconstrained INTERVAL Types
      • 10.10. Date/Time Functions
    • 11. Records
      • 11.1. Records in PL/SQL
        • 11.1.1. Benefits of Using Records
          • 11.1.1.1. Data abstraction
          • 11.1.1.2. Aggregate operations
          • 11.1.1.3. Leaner, cleaner code
        • 11.1.2. Declaring Records
        • 11.1.3. Programmer-Defined Records
          • 11.1.3.1. Declaring programmer-defined record TYPEs
          • 11.1.3.2. Declaring the record
          • 11.1.3.3. Examples of programmer-defined record declarations
        • 11.1.4. Working with Records
          • 11.1.4.1. Record-level operations
          • 11.1.4.2. Field-level operations
          • 11.1.4.3. Field-level operations with nested records
          • 11.1.4.4. Field-level operations with package-based records
        • 11.1.5. Comparing Records
        • 11.1.6. Trigger Pseudo-Records
    • 12. Collections
      • 12.1. Collections Overview
        • 12.1.1. Types of Collections
        • 12.1.2. Collections Concepts and Terminology
        • 12.1.3. Collection Examples
          • 12.1.3.1. Using an associative array
          • 12.1.3.2. Using a nested table
          • 12.1.3.3. Using a VARRAY
        • 12.1.4. Where You Can Use Collections
          • 12.1.4.1. Collections as components of a record
          • 12.1.4.2. Collections as program parameters
          • 12.1.4.3. Collections as datatypes of a function's return value
          • 12.1.4.4. Collection as "columns" in a database table
          • 12.1.4.5. Collections as attributes of an object type
        • 12.1.5. Choosing a Collection Type
      • 12.2. Collection Methods (Built-Ins)
        • 12.2.1. The COUNT Method
          • 12.2.1.1. Boundary considerations
          • 12.2.1.2. Exceptions possible
        • 12.2.2. The DELETE Method
          • 12.2.2.1. Boundary considerations
          • 12.2.2.2. Exceptions possible
        • 12.2.3. The EXISTS Method
          • 12.2.3.1. Boundary considerations
          • 12.2.3.2. Exceptions possible
        • 12.2.4. The EXTEND Method
          • 12.2.4.1. Boundary considerations
          • 12.2.4.2. Exceptions possible
        • 12.2.5. The FIRST and LAST Methods
          • 12.2.5.1. Boundary considerations
          • 12.2.5.2. Exceptions possible
        • 12.2.6. The LIMIT Method
          • 12.2.6.1. Boundary considerations
          • 12.2.6.2. Exceptions possible
        • 12.2.7. The PRIOR and NEXT Methods
          • 12.2.7.1. Boundary considerations
          • 12.2.7.2. Exceptions possible
        • 12.2.8. The TRIM Method
          • 12.2.8.1. Boundary considerations
          • 12.2.8.2. Exceptions possible
      • 12.3. Working with Collections
        • 12.3.1. Declaring Collection Types
          • 12.3.1.1. Declaring an associative array collection type
          • 12.3.1.2. Declaring a nested table or VARRAY
          • 12.3.1.3. Changing nested table of VARRAY characteristics
        • 12.3.2. Declaring and Initializing Collection Variables
          • 12.3.2.1. Initializing implicitly during direct assignment
          • 12.3.2.2. Initializing implicitly via FETCH
          • 12.3.2.3. VARRAY integration
        • 12.3.3. Populating Collections with Data
          • 12.3.3.1. Using the assignment operator
          • 12.3.3.2. What row values can I use?
          • 12.3.3.3. Aggregate assignments
          • 12.3.3.4. Assigning rows from a relational table
          • 12.3.3.5. Advantage of nonsequential population of collection
        • 12.3.4. Accessing Data Inside a Collection
        • 12.3.5. Collections of Complex Datatypes
          • 12.3.5.1. Collections of records
          • 12.3.5.2. Collections of objects and other complex types
          • 12.3.5.3. Multilevel collections
          • 12.3.5.4. Unnamed nested collections: multidimensional arrays
          • 12.3.5.5. Basic operations
          • 12.3.5.6. How deeply can I nest collections?
        • 12.3.6. Using String-Indexed Collections
        • 12.3.7. Working with Collections in SQL
          • 12.3.7.1. The CAST pseudo-function
          • 12.3.7.2. The MULTISET pseudo-function
          • 12.3.7.3. The TABLE pseudo-function
          • 12.3.7.4. Sorting contents of collections
      • 12.4. Nested Table Multiset Operations
        • 12.4.1. Testing Equality and Membership of Nested Tables
        • 12.4.2. Checking for Membership of an Element in a Nested Table
        • 12.4.3. Performing High-Level Set Operations
        • 12.4.4. Handling Duplicates in a Nested Table
      • 12.5. Maintaining Schema-Level Collections
        • 12.5.1. Necessary Privileges
        • 12.5.2. Collections and the Data Dictionary
    • 13. Miscellaneous Datatypes
      • 13.1. The BOOLEAN Datatype
      • 13.2. The RAW Datatype
      • 13.3. The UROWID and ROWID Datatypes
        • 13.3.1. Getting at Rowids
        • 13.3.2. Using Rowids
          • 13.3.2.1. Do rowids ever change?
          • 13.3.2.2. Using rowids in Oracle Forms
          • 13.3.2.3. Using rowids in a cursor FOR loop
          • 13.3.2.4. Is the use of rowids worth the effort?
      • 13.4. The LOB Datatypes
      • 13.5. Working with LOBs
        • 13.5.1. Understanding LOB Locators
        • 13.5.2. Empty Versus NULL LOBs
        • 13.5.3. Creating a LOB
        • 13.5.4. Writing into a LOB
        • 13.5.5. Reading from a LOB
        • 13.5.6. BFILEs Are Different
          • 13.5.6.1. Creating a BFILE locator
          • 13.5.6.2. Accessing BFILEs
          • 13.5.6.3. Using BFILEs to load LOB columns
        • 13.5.7. Temporary LOBs
          • 13.5.7.1. Creating a temporary LOB
          • 13.5.7.2. Freeing a temporary LOB
          • 13.5.7.3. Checking to see whether a LOB is temporary
          • 13.5.7.4. Managing temporary LOBs
        • 13.5.8. Native LOB Operations
          • 13.5.8.1. SQL semantics may yield temporary LOBs
          • 13.5.8.2. Performance impact of using SQL semantics
        • 13.5.9. LOB Conversion Functions
      • 13.6. Predefined Object Types
        • 13.6.1. The XMLType Type
        • 13.6.2. The URI Types
        • 13.6.3. The Any Types
  • IV. SQL in PL/SQL
    • 14. DML and Transaction Management
      • 14.1. DML in PL/SQL
        • 14.1.1. A Quick Introduction to DML
          • 14.1.1.1. The INSERT statement
          • 14.1.1.2. The UPDATE statement
          • 14.1.1.3. The DELETE statement
        • 14.1.2. Cursor Attributes for DML Operations
        • 14.1.3. RETURNING Information from DML Statements
        • 14.1.4. DML and Exception Handling
        • 14.1.5. DML and Records
          • 14.1.5.1. Record-based inserts
          • 14.1.5.2. Record-based updates
          • 14.1.5.3. Using records with the RETURNING clause
          • 14.1.5.4. Restrictions on record-based inserts and updates
      • 14.2. Bulk DML with the FORALL Statement
        • 14.2.1. Syntax of the FORALL Statement
        • 14.2.2. Context-Switching Problem Scenarios
        • 14.2.3. FORALL Examples
        • 14.2.4. Cursor Attributes for FORALL
        • 14.2.5. ROLLBACK Behavior with FORALL
        • 14.2.6. Continuing Past Exceptions with FORALL
        • 14.2.7. Driving FORALL with Nonsequential Arrays
          • 14.2.7.1. INDICES OF example
          • 14.2.7.2. VALUES OF example
      • 14.3. Transaction Management
        • 14.3.1. The COMMIT Statement
        • 14.3.2. The ROLLBACK Statement
        • 14.3.3. The SAVEPOINT Statement
        • 14.3.4. The SET TRANSACTION Statement
        • 14.3.5. The LOCK TABLE Statement
      • 14.4. Autonomous Transactions
        • 14.4.1. Defining Autonomous Transactions
        • 14.4.2. Rules and Restrictions on Autonomous Transactions
        • 14.4.3. Transaction Visibility
        • 14.4.4. When to Use Autonomous Transactions
        • 14.4.5. Building an Autonomous Logging Mechanism
    • 15. Data Retrieval
      • 15.1. Cursor Basics
        • 15.1.1. Some Data Retrieval Terms
        • 15.1.2. Typical Query Operations
        • 15.1.3. Introduction to Cursor Attributes
          • 15.1.3.1. The %FOUND attribute
          • 15.1.3.2. The %NOTFOUND attribute
          • 15.1.3.3. The %ROWCOUNT attribute
          • 15.1.3.4. The %ISOPEN attribute
          • 15.1.3.5. The %BULK_ROWCOUNT attribute
          • 15.1.3.6. The %BULK_EXCEPTIONS attribute
        • 15.1.4. Referencing PL/SQL Variables in a Cursor
          • 15.1.4.1. Identifier precedence in a cursor
          • 15.1.4.2. Using standard naming conventions
        • 15.1.5. Choosing Between Explicit and Implicit Cursors
      • 15.2. Working with Implicit Cursors
        • 15.2.1. Implicit Cursor Examples
        • 15.2.2. Error Handling with Implicit Cursors
        • 15.2.3. Implicit SQL Cursor Attributes
      • 15.3. Working with Explicit Cursors
        • 15.3.1. Declaring Explicit Cursors
          • 15.3.1.1. Naming your cursor
          • 15.3.1.2. Declaring cursors in packages
        • 15.3.2. Opening Explicit Cursors
        • 15.3.3. Fetching from Explicit Cursors
          • 15.3.3.1. Examples of explicit cursors
          • 15.3.3.2. Fetching past the last row
        • 15.3.4. Column Aliases in Explicit Cursors
        • 15.3.5. Closing Explicit Cursors
        • 15.3.6. Explicit Cursor Attributes
        • 15.3.7. Cursor Parameters
          • 15.3.7.1. Generalizing cursors with parameters
          • 15.3.7.2. Opening cursors with parameters
          • 15.3.7.3. Scope of cursor parameters
          • 15.3.7.4. Cursor parameter modes
          • 15.3.7.5. Default values for parameters
      • 15.4. BULK COLLECT
        • 15.4.1. Limiting Rows Retrieved with BULK COLLECT
        • 15.4.2. Bulk Fetching of Multiple Columns
        • 15.4.3. Using the RETURNING Clause with Bulk Operations
      • 15.5. SELECT ... FOR UPDATE
        • 15.5.1. Releasing Locks with COMMIT
        • 15.5.2. The WHERE CURRENT OF Clause
      • 15.6. Cursor Variables and REF CURSORs
        • 15.6.1. Why Cursor Variables?
        • 15.6.2. Similarities to Static Cursors
        • 15.6.3. Declaring REF CURSOR Types
        • 15.6.4. Declaring Cursor Variables
        • 15.6.5. Opening Cursor Variables
        • 15.6.6. Fetching from Cursor Variables
          • 15.6.6.1. Handling the ROWTYPE_MISMATCH exception
        • 15.6.7. Rules for Cursor Variables
          • 15.6.7.1. Compile-time rowtype matching rules
          • 15.6.7.2. Runtime rowtype matching rules
          • 15.6.7.3. Cursor variable aliases
          • 15.6.7.4. Scope of cursor object
        • 15.6.8. Passing Cursor Variables as Arguments
          • 15.6.8.1. Identifying the REF CURSOR type
          • 15.6.8.2. Setting the parameter mode
        • 15.6.9. Cursor Variable Restrictions
      • 15.7. Cursor Expressions
        • 15.7.1. Using Cursor Expressions
          • 15.7.1.1. Retrieve subquery as column
          • 15.7.1.2. Implement a streaming function with the CURSOR expression
        • 15.7.2. Restrictions on Cursor Expressions
    • 16. Dynamic SQL and Dynamic PL/SQL
      • 16.1. NDS Statements
        • 16.1.1. The EXECUTE IMMEDIATE Statement
        • 16.1.2. The OPEN FOR Statement
          • 16.1.2.1. FETCH into variables or records
          • 16.1.2.2. The USING clause in OPEN FOR
        • 16.1.3. About the Four Dynamic SQL Methods
          • 16.1.3.1. Method 1
          • 16.1.3.2. Method 2
          • 16.1.3.3. Method 3
          • 16.1.3.4. Method 4
      • 16.2. Binding Variables
        • 16.2.1. Argument Modes
        • 16.2.2. Duplicate Placeholders
        • 16.2.3. Passing NULL Values
      • 16.3. Working with Objects and Collections
      • 16.4. Dynamic PL/SQL
        • 16.4.1. Replace Repetitive Code with Dynamic Block
        • 16.4.2. Implement Method 4 Dynamic SQL in NDS
      • 16.5. Recommendations for NDS
        • 16.5.1. Use Invoker Rights for Shared Programs
        • 16.5.2. Anticipate and Handle Dynamic Errors
        • 16.5.3. Use Binding Rather Than Concatenation
        • 16.5.4. Avoid Code Injection with Binding
      • 16.6. When to Use DBMS_SQL
        • 16.6.1. Parse Very Long Strings
        • 16.6.2. Obtain Information About Query Columns
        • 16.6.3. Meet Method 4 Dynamic SQL Requirements
          • 16.6.3.1. The "in table" procedural interface
          • 16.6.3.2. Steps for intab construction
          • 16.6.3.3. Constructing the SELECT
          • 16.6.3.4. Defining the cursor structure
          • 16.6.3.5. Retrieving and displaying data
        • 16.6.4. Minimize Parsing of Dynamic Cursors
      • 16.7. NDS Utility Package
  • V. PL/SQL Application Construction
    • 17. Procedures, Functions, and Parameters
      • 17.1. Modular Code
      • 17.2. Procedures
        • 17.2.1. Calling a Procedure
        • 17.2.2. The Procedure Header
        • 17.2.3. The Procedure Body
        • 17.2.4. The END Descriptor
        • 17.2.5. The RETURN Statement
      • 17.3. Functions
        • 17.3.1. Structure of a Function
        • 17.3.2. The RETURN Datatype
        • 17.3.3. The END Descriptor
        • 17.3.4. Calling a Function
        • 17.3.5. Functions Without Parameters
        • 17.3.6. The Function Header
        • 17.3.7. The Function Body
        • 17.3.8. The RETURN Statement
          • 17.3.8.1. RETURN any valid expression
          • 17.3.8.2. Multiple RETURNs
          • 17.3.8.3. RETURN as last executable statement
      • 17.4. Parameters
        • 17.4.1. Defining Parameters
        • 17.4.2. Actual and Formal Parameters
        • 17.4.3. Parameter Modes
          • 17.4.3.1. IN mode
          • 17.4.3.2. OUT mode
          • 17.4.3.3. IN OUT mode
        • 17.4.4. Matching Actual and Formal Parameters in PL/SQL
          • 17.4.4.1. Positional notation
          • 17.4.4.2. Named notation
          • 17.4.4.3. Benefits of named notation
        • 17.4.5. The NOCOPY Parameter Mode Hint
          • 17.4.5.1. Restrictions on NOCOPY
          • 17.4.5.2. Impact of NOCOPY
        • 17.4.6. Default Values
      • 17.5. Local Modules
        • 17.5.1. Benefits of Local Modularization
          • 17.5.1.1. Reducing code volume
          • 17.5.1.2. Improving readability
        • 17.5.2. Scope of Local Modules
        • 17.5.3. Sprucing Up Your Code with Local Modules
      • 17.6. Module Overloading
        • 17.6.1. Benefits of Overloading
          • 17.6.1.1. Supporting many data combinations
        • 17.6.2. Restrictions on Overloading
        • 17.6.3. Overloading with Numeric Types
      • 17.7. Forward Declarations
      • 17.8. Advanced Topics
        • 17.8.1. Calling Your Function Inside SQL
          • 17.8.1.1. Requirements for calling functions in SQL
          • 17.8.1.2. Restrictions on user-defined functions in SQL
          • 17.8.1.3. Read consistency and user-defined functions
          • 17.8.1.4. Replacing DECODEs with IF statements
          • 17.8.1.5. The PRAGMA RESTRICT_REFERENCES (Oracle8 Database and earlier)
        • 17.8.2. Table Functions
          • 17.8.2.1. Calling a function in a FROM clause
          • 17.8.2.2. Passing table function results with a cursor variable
          • 17.8.2.3. Creating a streaming function
          • 17.8.2.4. Creating a pipelined function
          • 17.8.2.5. Enabling a function for parallel execution
        • 17.8.3. Deterministic Functions
      • 17.9. Go Forth and Modularize!
    • 18. Packages
      • 18.1. Why Packages?
        • 18.1.1. Demonstrating the Power of the Package
        • 18.1.2. Some Package-Related Concepts
        • 18.1.3. Diagramming Privacy
      • 18.2. Rules for Building Packages
        • 18.2.1. The Package Specification
        • 18.2.2. The Package Body
        • 18.2.3. Initializing Packages
          • 18.2.3.1. Execute complex initialization logic
          • 18.2.3.2. Cache static session information
          • 18.2.3.3. Avoid side effects when initializing
          • 18.2.3.4. When initialization fails
      • 18.3. Rules for Calling Packaged Elements
      • 18.4. Working with Package Data
        • 18.4.1. Global Within a Single Oracle Session
        • 18.4.2. Global Public Data
        • 18.4.3. Packaged Cursors
          • 18.4.3.1. Declaring packaged cursors
          • 18.4.3.2. Working with packaged cursors
        • 18.4.4. Serializable Packages
      • 18.5. When to Use Packages
        • 18.5.1. Encapsulating Data Manipulation
        • 18.5.2. Avoid Hardcoding Literals
        • 18.5.3. Improve Usability of Built-in Features
        • 18.5.4. Group Together Logically Related Functionality
        • 18.5.5. Cache Static Session Data
      • 18.6. Packages and Object Types
    • 19. Triggers
      • 19.1. DML Triggers
        • 19.1.1. DML Trigger Concepts
          • 19.1.1.1. DML trigger scripts
          • 19.1.1.2. Transaction participation
        • 19.1.2. Creating a DML Trigger
          • 19.1.2.1. The WHEN clause
          • 19.1.2.2. Working with NEW and OLD pseudo-records
          • 19.1.2.3. Determining the DML action within a trigger
        • 19.1.3. DML Trigger Example: No Cheating Allowed!
          • 19.1.3.1. Applying the WHEN clause
          • 19.1.3.2. Using pseudo-records to fine-tune trigger execution
        • 19.1.4. Multiple Triggers of the Same Type
        • 19.1.5. Mutating Table Errors
      • 19.2. DDL Triggers
        • 19.2.1. Creating a DDL Trigger
        • 19.2.2. Available Events
        • 19.2.3. Available Attributes
        • 19.2.4. Working with Events and Attributes
          • 19.2.4.1. What column did I touch?
          • 19.2.4.2. Lists returned by attribute functions
        • 19.2.5. Dropping the Undroppable
        • 19.2.6. The INSTEAD OF CREATE Trigger
      • 19.3. Database Event Triggers
        • 19.3.1. Creating a Database Event Trigger
        • 19.3.2. The STARTUP Trigger
        • 19.3.3. The SHUTDOWN Trigger
        • 19.3.4. The LOGON Trigger
        • 19.3.5. The LOGOFF Trigger
        • 19.3.6. The SERVERERROR Trigger
          • 19.3.6.1. SERVERERROR examples
          • 19.3.6.2. Central error handler
      • 19.4. INSTEAD OF Triggers
        • 19.4.1. Creating an INSTEAD OF Trigger
        • 19.4.2. The INSTEAD OF INSERT Trigger
        • 19.4.3. The INSTEAD OF UPDATE Trigger
        • 19.4.4. The INSTEAD OF DELETE Trigger
        • 19.4.5. Populating the Tables
        • 19.4.6. INSTEAD OF Triggers on Nested Tables
      • 19.5. AFTER SUSPEND Triggers
        • 19.5.1. Setting Up for the AFTER SUSPEND Trigger
        • 19.5.2. Looking at the Actual Trigger
        • 19.5.3. The ORA_SPACE_ERROR_INFO Function
        • 19.5.4. The DBMS_RESUMABLE Package
        • 19.5.5. Trapped Multiple Times
        • 19.5.6. To Fix or Not to Fix?
      • 19.6. Maintaining Triggers
        • 19.6.1. Disabling, Enabling, and Dropping Triggers
        • 19.6.2. Viewing Triggers
        • 19.6.3. Checking the Validity of Triggers
    • 20. Managing PL/SQL Code
      • 20.1. Managing Code in the Database
        • 20.1.1. Data Dictionary Views for PL/SQL Programmers
          • 20.1.1.1. Display information about stored objects
          • 20.1.1.2. Display and search source code
          • 20.1.1.3. Use program size to determine pinning requirements
          • 20.1.1.4. Obtain properties of stored code
          • 20.1.1.5. Analyze and modify trigger state through views
          • 20.1.1.6. Analyze argument information
        • 20.1.2. Recompiling Invalid Code
          • 20.1.2.1. Recompile individual program units
          • 20.1.2.2. Use UTL_RECOMP
      • 20.2. Using Native Compilation
        • 20.2.1. Perform One-Time DBA Setup
        • 20.2.2. Step 1: Get a Supported C Compiler
        • 20.2.3. Step 2: Set Up the Directories
        • 20.2.4. Step 3: Check $ORACLE_HOME/plsql/spnc_commands
        • 20.2.5. Interpreted Versus Native Compilation Mode
      • 20.3. Using the Optimizing Compiler and Compile-Time Warnings
        • 20.3.1. The Optimizing Compiler
        • 20.3.2. Compile-Time Warnings
          • 20.3.2.1. A quick example
          • 20.3.2.2. If you see a "no message file" message
          • 20.3.2.3. Verify your SQL*Plus version
          • 20.3.2.4. How to turn on compile-time warnings
        • 20.3.3. Warnings Available in Oracle Database 10g
          • 20.3.3.1. PLW-05000: mismatch in NOCOPY qualification between specification and body
          • 20.3.3.2. PLW-05001: previous use of 'string' (at line string) conflicts with this use
          • 20.3.3.3. PLW-05003: same actual parameter(string and string) at IN and NOCOPY may have side effects
          • 20.3.3.4. PLW-05004: identifier string is also declared in STANDARD or is a SQL built-in
          • 20.3.3.5. PLW-05005: function string returns without value at line string
          • 20.3.3.6. PLW-06002: unreachable code
          • 20.3.3.7. PLW-07203: parameter 'string' may benefit from use of the NOCOPY compiler hint
          • 20.3.3.8. PLW-07204: conversion away from column type may result in sub-optimal query plan
      • 20.4. Conditional Compilation
        • 20.4.1. Examples of Conditional Compilation
          • 20.4.1.1. Use application package constants in $IF directive
          • 20.4.1.2. Toggle tracing through conditional compilation flags
        • 20.4.2. The Inquiry Directive
          • 20.4.2.1. The DBMS_DB_VERSION package
          • 20.4.2.2. Setting compilation environment parameters
          • 20.4.2.3. Referencing unit name and line number
          • 20.4.2.4. Using the PLSQL_CCFLAGS parameter
        • 20.4.3. The $IF Directive
        • 20.4.4. The $ERROR Directive
        • 20.4.5. Synchronizing Code with Packaged Constants
        • 20.4.6. Program-Specific Settings with Inquiry Directives
        • 20.4.7. Working with Postprocessed Code
      • 20.5. Testing PL/SQL Programs
        • 20.5.1. Typical, Tawdry Testing Techniques
        • 20.5.2. utPLSQL: A Unit-Testing Framework
          • 20.5.2.1. Using utPLSQL with betwnstr
          • 20.5.2.2. Where to find utPLSQL and Ounit
      • 20.6. Debugging PL/SQL Programs
        • 20.6.1. The Wrong Way to Debug
          • 20.6.1.1. Disorganized debugging
          • 20.6.1.2. Irrational debugging
        • 20.6.2. Debugging Tips and Strategies
          • 20.6.2.1. Use a source code debugger
          • 20.6.2.2. Gather data
          • 20.6.2.3. Remain logical at all times
          • 20.6.2.4. Analyze instead of trying
          • 20.6.2.5. Take breaks, and ask for help
          • 20.6.2.6. Change and test one area of code at a time
        • 20.6.3. Tracing Execution of Your Code
          • 20.6.3.1. DBMS_UTILITY.FORMAT_CALL_STACK
          • 20.6.3.2. Installing DBMS_TRACE
          • 20.6.3.3. DBMS_TRACE programs
          • 20.6.3.4. Controlling trace file contents
          • 20.6.3.5. Pausing and resuming the trace process
          • 20.6.3.6. Format of collected data
      • 20.7. Tuning PL/SQL Programs
        • 20.7.1. Analyzing Performance of PL/SQL Code
        • 20.7.2. Optimizing PL/SQL Performance
          • 20.7.2.1. Use the most aggressive compiler optimization level possible
          • 20.7.2.2. Use BULK COLLECT when querying multiple rows
          • 20.7.2.3. Use FORALL when modifying multiple rows
          • 20.7.2.4. Use the NOCOPY hint when passing large structures
          • 20.7.2.5. Use PLS_INTEGER for intensive integer computations.
          • 20.7.2.6. Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
          • 20.7.2.7. Group together related programs in a package
          • 20.7.2.8. Pin into shared memory large and frequently executed programs.
      • 20.8. Protecting Stored Code
        • 20.8.1. Restrictions on and Limitations of Wrapping
        • 20.8.2. Using the Wrap Executable
        • 20.8.3. Dynamic Wrapping with DBMS_DDL
        • 20.8.4. Guidelines for Working with Wrapped Code
    • 21. I/O and PL/SQL
      • 21.1. Displaying Information
        • 21.1.1. Enabling DBMS_OUTPUT
        • 21.1.2. Write Lines to a Buffer
        • 21.1.3. Read the Contents of a Buffer
      • 21.2. Reading and Writing Files
        • 21.2.1. The UTL_FILE_DIR Parameter
          • 21.2.1.1. Setting up directories
          • 21.2.1.2. Specifying file locations
        • 21.2.2. Work with Oracle Directories
        • 21.2.3. Open Files
        • 21.2.4. Is the File Already Open?
        • 21.2.5. Close Files
        • 21.2.6. Read from Files
          • 21.2.6.1. GET_LINE exceptions
          • 21.2.6.2. Handy encapsulation for GET_LINE
        • 21.2.7. Write to Files
          • 21.2.7.1. Writing formatted text to file
        • 21.2.8. Copy Files
        • 21.2.9. Delete Files
        • 21.2.10. Rename and Move Files
        • 21.2.11. Retrieve File Attributes
      • 21.3. Sending Email
        • 21.3.1. Oracle Prerequisites by Release
        • 21.3.2. Send a Short (32K or Less) Plaintext Message
        • 21.3.3. Include "Friendly" Names in Email Addresses
        • 21.3.4. Send a Plaintext Message of Arbitrary Length
        • 21.3.5. Send a Message with a Short (< 32K) Attachment
        • 21.3.6. Send a Small File (< 32K) as an Attachment
        • 21.3.7. Attach a File of Arbitrary Size
      • 21.4. Working with Web-Based Data (HTTP)
        • 21.4.1. Retrieve a Web Page in "Pieces"
        • 21.4.2. Retrieve a Web Page into a LOB
        • 21.4.3. Authenticate Using HTTP Username/Password
        • 21.4.4. Retrieve an SSL-Encrypted Web Page (Via https)
        • 21.4.5. Submit Data to a Web Page via GET or POST
        • 21.4.6. Disable Cookies or Make Cookies Persistent
        • 21.4.7. Retrieve Data from an FTP Server
        • 21.4.8. Use a Proxy Server
      • 21.5. Other Types of I/O Available in PL/SQL
        • 21.5.1. Database Pipes, Queues, and Alerts
        • 21.5.2. TCP Sockets
        • 21.5.3. Oracle's Built-in Web Server
  • VI. Advanced PL/SQL Topics
    • 22. Application Security and PL/SQL
      • 22.1. Security Overview
      • 22.2. Encryption
        • 22.2.1. Key Length
        • 22.2.2. Algorithms
        • 22.2.3. Padding and Chaining
        • 22.2.4. The DBMS_CRYPTO Package
          • 22.2.4.1. Algorithms
          • 22.2.4.2. Padding and chaining
        • 22.2.5. Encrypting Data
        • 22.2.6. Encrypting LOBs
        • 22.2.7. Decrypting Data
        • 22.2.8. Performing Key Generation
        • 22.2.9. Performing Key Management
          • 22.2.9.1. A single key for the database
          • 22.2.9.2. A single key for each row
          • 22.2.9.3. A combined approach
        • 22.2.10. Cryptographic Hashing
        • 22.2.11. Using Message Authentication Code
        • 22.2.12. Using Transparent Data Encryption (TDE)
      • 22.3. Row-Level Security
        • 22.3.1. Why Learn About RLS?
        • 22.3.2. A Simple RLS Example
        • 22.3.3. Using Dynamic Policies
          • 22.3.3.1. Shared static policy
          • 22.3.3.2. Context-sensitive policy
          • 22.3.3.3. Shared context sensitive policy
        • 22.3.4. Using Column-Sensitive RLS
        • 22.3.5. RLS Debugging
          • 22.3.5.1. Interpreting errors
          • 22.3.5.2. Performing direct path operations
          • 22.3.5.3. Viewing SQL statements
      • 22.4. Application Contexts
        • 22.4.1. Using Application Contexts
        • 22.4.2. Security in Contexts
        • 22.4.3. Contexts as Predicates in RLS
        • 22.4.4. Identifying Non-Database Users
      • 22.5. Fine-Grained Auditing
        • 22.5.1. Why Learn About FGA?
        • 22.5.2. A Simple FGA Example
        • 22.5.3. Access How Many Columns?
        • 22.5.4. Checking the Audit Trail
        • 22.5.5. Using Bind Variables
        • 22.5.6. Using Handler Modules
    • 23. Inside PL/SQL
      • 23.1. Looking Under the Hood
        • 23.1.1. PL/SQL Concepts
        • 23.1.2. Physical Storage of Server-Side PL/SQL
        • 23.1.3. Compiler Limits
      • 23.2. PL/SQL's Optimizing Compiler
        • 23.2.1. Runtime Optimization of Fetch Loops
      • 23.3. Dependency Management
        • 23.3.1. Dependencies in Server-Side PL/SQL
        • 23.3.2. Healing Invalids
          • 23.3.2.1. Recompiling by hand
          • 23.3.2.2. Recompiling by script
          • 23.3.2.3. Automatic recompilation
        • 23.3.3. Remote Dependencies
      • 23.4. Execution Authority Models
        • 23.4.1. The Definer Rights Model
          • 23.4.1.1. Advantages of definer rights
          • 23.4.1.2. Disadvantages of definer rights
            • 23.4.1.2.1. Where'd my table go?
            • 23.4.1.2.2. How do I maintain all that code?
            • 23.4.1.2.3. Dynamic SQL and definer rights.
        • 23.4.2. The Invoker Rights Model
          • 23.4.2.1. Invoker rights syntax
          • 23.4.2.2. Some rules and restrictions
        • 23.4.3. Combining Rights Models
      • 23.5. PL/SQL and Oracle Memory
        • 23.5.1. PGA, UGA, and CGA
        • 23.5.2. Cursors, Memory, and More
        • 23.5.3. Tips on Reducing Memory Use
          • 23.5.3.1. Statement sharing
          • 23.5.3.2. Bind variables
          • 23.5.3.3. Packaging to improve memory use and performance
          • 23.5.3.4. Large collections in PL/SQL
          • 23.5.3.5. Preservation of state
        • 23.5.4. What to Do if You Run Out of Memory
      • 23.6. Server-Side PL/SQL Processing: Reprise
        • 23.6.1. Compiling an Anonymous Block
        • 23.6.2. Compiling a Stored Object
        • 23.6.3. Executing PL/SQL
      • 23.7. What You Need to Know
    • 24. Globalization and Localization in PL/SQL
      • 24.1. Overview and Terminology
      • 24.2. Unicode Primer
        • 24.2.1. National Character Set Datatypes
        • 24.2.2. Character Encoding
        • 24.2.3. Globalization Support Parameters
        • 24.2.4. Unicode Functions
          • 24.2.4.1. ASCIISTR
          • 24.2.4.2. COMPOSE
          • 24.2.4.3. DECOMPOSE
          • 24.2.4.4. INSTR/INSTRB/INSTRC/INSTR2/INSTR4
          • 24.2.4.5. LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH4
          • 24.2.4.6. SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4
          • 24.2.4.7. UNISTR
      • 24.3. Character Semantics
      • 24.4. String Sort Order
        • 24.4.1. Binary Sort
        • 24.4.2. Monolingual Sort
        • 24.4.3. Multilingual Sort
      • 24.5. Multilingual Information Retrieval
        • 24.5.1. IR and PL/SQL
      • 24.6. Date/Time
        • 24.6.1. Timestamp Datatypes
        • 24.6.2. Date/Time Formatting
      • 24.7. Currency Conversion
      • 24.8. Globalization Development Kit for PL/SQL
        • 24.8.1. UTL_118N Utility Package
        • 24.8.2. UTL_LMS Error-Handling Package
        • 24.8.3. GDK Implementation Options
          • 24.8.3.1. Method 1: Locale buttons
          • 24.8.3.2. Method 2: User administration
          • 24.8.3.3. Method 3: Hybrid
    • 25. Object-Oriented Aspects of PL/SQL
      • 25.1. Introduction to Oracle's Object Features
      • 25.2. An Extended Example
        • 25.2.1. A Tree of Types
          • 25.2.1.1. Creating a base type
          • 25.2.1.2. Creating a subtype
        • 25.2.2. Methods
        • 25.2.3. Storing, Retrieving, and Using Persistent Objects
          • 25.2.3.1. Object identity
          • 25.2.3.2. The VALUE function
          • 25.2.3.3. The TREAT function
        • 25.2.4. Evolution and Creation
        • 25.2.5. Back to Pointers?
          • 25.2.5.1. Using REFs
          • 25.2.5.2. The UTL_REF package
          • 25.2.5.3. REFs and type hierarchies
          • 25.2.5.4. Dangling REFs
        • 25.2.6. Generic Data: The ANY Types
          • 25.2.6.1. Preview: What ANYDATA is not
          • 25.2.6.2. Dealing with an ANYDATA
          • 25.2.6.3. Creating a transient type
        • 25.2.7. I Can Do It Myself
        • 25.2.8. Comparing Objects
          • 25.2.8.1. Attribute-level comparison
          • 25.2.8.2. The MAP method
          • 25.2.8.3. The ORDER method
          • 25.2.8.4. Additional comparison recommendations
      • 25.3. Object Views
        • 25.3.1. A Sample Relational System
        • 25.3.2. Object View with a Collection Attribute
        • 25.3.3. Object Subview
        • 25.3.4. Object View with Inverse Relationship
        • 25.3.5. INSTEAD OF Triggers
          • 25.3.5.1. The case against
          • 25.3.5.2. The case for
          • 25.3.5.3. The bigger question
        • 25.3.6. Differences Between Object Views and Object Tables
          • 25.3.6.1. OID uniqueness
          • 25.3.6.2. "Storeability" of physical versus virtual REFs
          • 25.3.6.3. REFs to nonunique OIDs
      • 25.4. Maintaining Object Types and Object Views
        • 25.4.1. Privileges
          • 25.4.1.1. The EXECUTE privilege
          • 25.4.1.2. The UNDER privilege
          • 25.4.1.3. The DEBUG privilege
          • 25.4.1.4. The DML privileges
      • 25.5. Pontifications
    • 26. Calling Java from PL/SQL
      • 26.1. Oracle and Java
      • 26.2. Getting Ready to Use Java in Oracle
        • 26.2.1. Installing Java
        • 26.2.2. Building and Compiling Your Java Code
        • 26.2.3. Setting Permissions for Java Development and Execution
          • 26.2.3.1. Java security for Oracle through 8.1.5
          • 26.2.3.2. Java security for Oracle for 8.1.6 through Oracle Database 10g
      • 26.3. A Simple Demonstration
        • 26.3.1. Finding the Java Functionality
        • 26.3.2. Building a Custom Java Class
        • 26.3.3. Compiling and Loading into Oracle
        • 26.3.4. Building a PL/SQL Wrapper
        • 26.3.5. Deleting Files from PL/SQL
      • 26.4. Using loadjava
      • 26.5. Using dropjava
      • 26.6. Managing Java in the Database
        • 26.6.1. The Java Namespace in Oracle
        • 26.6.2. Examining Loaded Java Elements
      • 26.7. Using DBMS_JAVA
        • 26.7.1. LONGNAME: Converting Java Long Names
        • 26.7.2. GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options
        • 26.7.3. SET_OUTPUT: Enabling Output from Java
        • 26.7.4. EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
      • 26.8. Publishing and Using Java in PL/SQL
        • 26.8.1. Call Specs
        • 26.8.2. Some Rules for Call Specs
        • 26.8.3. Mapping Datatypes
        • 26.8.4. Calling a Java Method in SQL
        • 26.8.5. Exception Handling with Java
        • 26.8.6. Extending File I/O Capabilities
          • 26.8.6.1. Polishing up the delete method
          • 26.8.6.2. Obtaining directory contents
        • 26.8.7. Other Examples
    • 27. External Procedures
      • 27.1. Introduction to External Procedures
        • 27.1.1. Example: Invoking an Operating System Command
        • 27.1.2. Architecture of External Procedures
      • 27.2. The Oracle Net Configuration
        • 27.2.1. Specifying the Listener Configuration
        • 27.2.2. Security Characteristics of the Configuration
      • 27.3. Setting Up Multithreaded Mode
      • 27.4. Creating an Oracle Library
      • 27.5. Writing the Call Specification
        • 27.5.1. The Call Spec: Overall Syntax
        • 27.5.2. Parameter Mapping: The Example Revisited
        • 27.5.3. Parameter Mapping: The Full Story
        • 27.5.4. More Syntax: The PARAMETERS Clause
        • 27.5.5. PARAMETERS Properties
          • 27.5.5.1. The INDICATOR property
          • 27.5.5.2. The LENGTH property
          • 27.5.5.3. The MAXLEN property
          • 27.5.5.4. The CHARSETID and CHARSETFORM properties
      • 27.6. Raising an Exception from the Called C Program
      • 27.7. Nondefault Agents
      • 27.8. Maintaining External Procedures
        • 27.8.1. Dropping Libraries
        • 27.8.2. Data Dictionary
        • 27.8.3. Rules and Warnings
  • VII. Appendixes
    • A. Regular Expression Metacharacters and Function Parameters
      • A.1. Metacharacters
      • A.2. Function Parameters
    • B. Number Format Models
    • C. Date Format Models
  • About the Authors
  • Colophon
  • SPECIAL OFFER: Upgrade this ebook with OReilly

pokaż cały spis treści

ukryj recenzje