book

Oracle PL/SQL Programming, 4th Edition

by Steven Feuerstein, Bill Pribyl

August 2005

Intermediate to advanced

1198 pages

33h 25m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Oracle PL/SQL Programming, 4th Edition
Dedication
A Note Regarding Supplemental Files
Preface
Objectives of This BookStructure of This BookAbout the ContentsWhat This Book Does Not CoverConventions Used in This BookWhich Platform or Version?About the CodeSafari EnabledComments and QuestionsAcknowledgments
I. Programming in PL/SQL
1. Introduction to PL/SQL
1.1. What Is PL/SQL?1.2. The Origins of PL/SQL1.2.1. The Early Years of PL/SQL1.2.2. Improved Application Portability1.2.3. Improved Execution Authority and Transaction Integrity1.2.4. Humble Beginnings, Steady Improvement1.2.5. The Significance of Oracle Database 10g PL/SQL1.3. So This Is PL/SQL1.3.1. Integration with SQL1.3.2. Control and Conditional Logic1.3.3. When Things Go Wrong1.4. About PL/SQL Versions1.4.1. Oracle Database 10g New Features1.4.1.1. Optimized compiler1.4.1.2. Compile-time warnings1.4.1.3. Conditional compilation1.4.1.4. Support for nonsequential collections in FORALL1.4.1.5. Improved datatype support1.4.1.6. Backtrace an exception to its line number1.4.1.7. Set operators for nested tables1.4.1.8. Support for regular expressions1.4.1.9. Programmer-defined quoting mechanism1.4.1.10. Many new built-in packages1.5. Resources for PL/SQL Developers1.5.1. The O’Reilly PL/SQL Series1.5.2. PL/SQL on the Internet1.6. Some Words of Advice1.6.1. Don’t Be in Such a Hurry!1.6.2. Don’t Be Afraid to Ask for Help1.6.3. Take a Creative, Even Radical Approach
2. Creating and Running PL/SQL Code
2.1. SQL*Plus2.1.1. Starting Up SQL*Plus2.1.2. Running a SQL Statement2.1.3. Running a PL/SQL Program2.1.4. Running a Script2.1.5. What Is the “Current Directory?”2.1.6. Other SQL*Plus Tasks2.1.6.1. Setting your preferences2.1.6.2. Saving output to a file2.1.6.3. Exiting SQL*Plus2.1.6.4. Editing a statement2.1.6.5. Loading your own custom environment automatically on startup2.1.7. Error Handling in SQL*Plus2.1.8. Why You Will Love and Hate SQL*Plus2.2. Performing Essential PL/SQL Tasks2.2.1. Creating a Stored Program2.2.2. Executing a Stored Program2.2.3. Showing Stored Programs2.2.4. Managing Grants and Synonyms for Stored Programs2.2.5. Dropping a Stored Program2.2.6. Hiding the Source Code of a Stored Program2.3. Calling PL/SQL from Other Languages2.3.1. C: Using Oracle’s Precompiler (Pro*C)2.3.2. Java: Using JDBC2.3.3. Perl: Using Perl DBI and DBD::Oracle2.3.4. PHP: Using Oracle Extensions2.3.5. PL/SQL Server Pages2.3.6. And Where Else?
3. Language Fundamentals
3.1. PL/SQL Block Structure3.1.1. Anonymous Blocks3.1.2. Named Blocks3.1.3. Nested Blocks3.1.4. Scope3.1.5. Visibility3.1.5.1. “Visible” identifiers3.1.5.2. Qualified identifiers3.1.5.3. Qualifying identifier names with module names3.1.5.4. Nested programs3.2. The PL/SQL Character Set3.3. Identifiers3.3.1. Reserved Words3.3.1.1. Language keywords3.3.1.2. Identifiers from STANDARD package3.3.1.3. Approaches to avoiding reserved words3.3.2. Whitespace and Keywords3.4. Literals3.4.1. NULLs3.4.2. Embedding Single Quotes Inside a Literal String3.4.3. Numeric Literals3.4.4. Boolean Literals3.5. The Semicolon Delimiter3.6. Comments3.6.1. Single-Line Comment Syntax3.6.2. Multiline Comment Syntax3.7. The PRAGMA Keyword3.8. Labels
II. PL/SQL Program Structure
4. Conditional and Sequential Control
4.1. IF Statements4.1.1. The IF-THEN Combination4.1.2. The IF-THEN-ELSE Combination4.1.3. The IF-THEN-ELSIF Combination4.1.4. Nested IF Statements4.1.5. Short-Circuit Evaluation4.2. CASE Statements and Expressions4.2.1. Simple CASE Statements4.2.2. Searched CASE Statements4.2.3. Nested CASE Statements4.2.4. CASE Expressions4.3. The GOTO Statement4.4. The NULL Statement4.4.1. Improving Program Readability4.4.2. Nullifying a Raised Exception4.4.3. Using NULL After a Label

5. Iterative Processing with Loops
5.1. Loop Basics5.1.1. Examples of Different Loops5.1.2. Structure of PL/SQL Loops5.2. The Simple Loop5.2.1. Terminating a Simple Loop: EXIT and EXIT WHEN5.2.2. Emulating a REPEAT UNTIL Loop5.2.3. The Intentionally Infinite Loop5.3. The WHILE Loop5.4. The Numeric FOR Loop5.4.1. Rules for Numeric FOR Loops5.4.2. Examples of Numeric FOR Loops5.4.3. Handling Nontrivial Increments5.5. The Cursor FOR Loop5.5.1. Example of Cursor FOR Loops5.6. Loop Labels5.7. Tips for Iterative Processing5.7.1. Use Understandable Names for Loop Indexes5.7.2. The Proper Way to Say Goodbye5.7.3. Obtaining Information About FOR Loop Execution5.7.4. SQL Statement as Loop
6. Exception Handlers
6.1. Exception-Handling Concepts and Terminology6.2. Defining Exceptions6.2.1. Declaring Named Exceptions6.2.2. Associating Exception Names with Error Codes6.2.2.1. Using EXCEPTION_INIT6.2.2.2. Recommended uses of EXCEPTION_INIT6.2.3. About Named System Exceptions6.2.4. Scope of an Exception6.3. Raising Exceptions6.3.1. The RAISE Statement6.3.2. Using RAISE_APPLICATION_ERROR6.4. Handling Exceptions6.4.1. Built-in Error Functions6.4.1.1. More on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE6.4.1.2. Just the line number, please6.4.1.3. Useful applications of SQLERRM6.4.2. Combining Multiple Exceptions in a Single Handler6.4.3. Unhandled Exceptions6.4.4. Propagation of an Unhandled Exception6.4.4.1. Losing exception information6.4.4.2. Examples of propagation6.4.5. Continuing Past Exceptions6.4.6. Writing WHEN OTHERS Handling Code6.5. Building an Effective Error Management Architecture6.5.1. Decide on Your Error Management Strategy6.5.2. Organize Use of Application-Specific Error Codes6.5.3. Work with Your Own Exception “Objects”6.5.4. Use Standardized Error Management Programs6.5.5. Create Standard Templates for Common Error Handling6.6. Making the Most of PL/SQL Error Management
III. PL/SQL Program Data
7. Working with Program Data
7.1. Naming Your Program Data7.2. Overview of PL/SQL Datatypes7.2.1. Character Data7.2.2. Numbers7.2.3. Dates, Timestamps, and Intervals7.2.4. Booleans7.2.5. Binary Data7.2.6. ROWIDs7.2.7. REF Cursors7.2.8. Internet Datatypes7.2.9. “Any” Datatypes7.2.10. User-Defined Datatypes7.3. Declaring Program Data7.3.1. Declaring a Variable7.3.2. Declaring Constants7.3.3. The NOT NULL Clause7.3.4. Anchored Declarations7.3.5. Anchoring to Cursors and Tables7.3.6. Benefits of Anchored Declarations7.3.6.1. Synchronization with database columns7.3.6.2. Normalization of local variables7.3.7. Anchoring to NOT NULL Datatypes7.4. Programmer-Defined Subtypes7.5. Conversion Between Datatypes7.5.1. Implicit Data Conversion7.5.1.1. Limitations of implicit conversion7.5.1.2. Drawbacks of implicit conversion7.5.2. Explicit Datatype Conversion7.5.2.1. The CHARTOROWID function7.5.2.2. The CAST function7.5.2.3. The CONVERT function7.5.2.4. The HEXTORAW function7.5.2.5. The RAWTOHEX function7.5.2.6. The ROWIDTOCHAR function
8. Strings
8.1. String Datatypes8.1.1. The VARCHAR2 Datatype8.1.2. The CHAR Datatype8.1.3. String Subtypes8.2. Working with Strings8.2.1. Specifying String Constants8.2.2. Using Nonprintable Characters8.2.3. Concatenating Strings8.2.4. Dealing with Case8.2.4.1. Forcing a string to all upper- or lowercase8.2.4.2. Making comparisons case-insensitive8.2.4.3. Capitalizing each word in a string8.2.5. Traditional Searching, Extracting, and Replacing8.2.6. Padding8.2.7. Trimming8.2.8. Regular Expression Searching, Extracting, and Replacing8.2.8.1. Detecting a pattern8.2.8.2. Locating a pattern8.2.8.3. Extracting text matching a pattern8.2.8.4. Replacing text (it’s magic!)8.2.8.5. Groking greediness8.2.8.6. Learning more about regular expressions8.2.9. Working with Empty Strings8.2.10. Mixing CHAR and VARCHAR2 Values8.2.10.1. Database-to-variable conversion8.2.10.2. Variable-to-database conversion8.2.10.3. String comparisons8.2.10.4. Character functions and CHAR arguments8.3. String Function Quick Reference
9. Numbers
9.1. Numeric Datatypes9.1.1. The NUMBER Type9.1.2. The PLS_INTEGER Type9.1.3. The BINARY_INTEGER Type9.1.4. The BINARY_FLOAT and BINARY_DOUBLE Types9.1.5. Numeric Subtypes9.2. Number Conversions9.2.1. The TO_NUMBER Function9.2.1.1. Using TO_NUMBER with no format9.2.1.2. Using TO_NUMBER with a format model9.2.1.3. Passing NLS settings to TO_NUMBER9.2.2. The TO_CHAR Function9.2.2.1. Using TO_CHAR with no format9.2.2.2. Using TO_CHAR with a format model9.2.2.3. The V format element9.2.2.4. Rounding when converting numbers to character strings9.2.2.5. Dealing with spaces when converting numbers to character strings9.2.2.6. Passing NLS settings to TO_CHAR9.2.3. Using CAST9.2.4. Implicit Conversions9.3. Numeric Functions9.3.1. Rounding and Truncation Functions9.3.2. Trigonometric Functions9.3.3. Numeric Function Quick Reference
10. Dates and Timestamps
10.1. Datetime Datatypes10.1.1. Declaring Datetime Variables10.1.2. Choosing a Datetime Datatype10.2. Getting the Date and Time10.3. Interval Datatypes10.3.1. Declaring INTERVAL Variables10.3.2. When to Use INTERVALs10.3.2.1. Finding the difference between two datetime values10.3.2.2. Designating periods of time10.4. Datetime Conversions10.4.1. From Strings to Datetimes10.4.2. From Datetimes to Strings10.4.3. Working with Time Zones10.4.4. Requiring a Format Mask to Match Exactly10.4.5. Easing Up on Exact Matches10.4.6. Interpreting Two-Digit Years in a Sliding Window10.4.7. Converting Time Zones to Character Strings10.4.8. Padding Output with Fill Mode10.5. Date and Timestamp Literals10.6. Interval Conversions10.6.1. Going from Numbers to Intervals10.6.2. Converting Strings to Intervals10.6.3. Formatting Intervals for Display10.7. Interval Literals10.8. CAST and EXTRACT10.8.1. The CAST Function10.8.2. The EXTRACT Function10.9. Datetime Arithmetic10.9.1. Adding and Subtracting Intervals to/from Datetimes10.9.2. Computing the Interval Between Two Datetimes10.9.3. Mixing DATEs and TIMESTAMPs10.9.4. Adding and Subtracting Intervals10.9.5. Multiplying and Dividing Intervals10.9.6. Using Unconstrained INTERVAL Types10.10. Date/Time Functions
11. Records
11.1. Records in PL/SQL11.1.1. Benefits of Using Records11.1.1.1. Data abstraction11.1.1.2. Aggregate operations11.1.1.3. Leaner, cleaner code11.1.2. Declaring Records11.1.3. Programmer-Defined Records11.1.3.1. Declaring programmer-defined record TYPEs11.1.3.2. Declaring the record11.1.3.3. Examples of programmer-defined record declarations11.1.4. Working with Records11.1.4.1. Record-level operations11.1.4.2. Field-level operations11.1.4.3. Field-level operations with nested records11.1.4.4. Field-level operations with package-based records11.1.5. Comparing Records11.1.6. Trigger Pseudo-Records
12. Collections
12.1. Collections Overview12.1.1. Types of Collections12.1.2. Collections Concepts and Terminology12.1.3. Collection Examples12.1.3.1. Using an associative array12.1.3.2. Using a nested table12.1.3.3. Using a VARRAY12.1.4. Where You Can Use Collections12.1.4.1. Collections as components of a record12.1.4.2. Collections as program parameters12.1.4.3. Collections as datatypes of a function’s return value12.1.4.4. Collection as “columns” in a database table12.1.4.5. Collections as attributes of an object type12.1.5. Choosing a Collection Type12.2. Collection Methods (Built-Ins)12.2.1. The COUNT Method12.2.1.1. Boundary considerations12.2.1.2. Exceptions possible12.2.2. The DELETE Method12.2.2.1. Boundary considerations12.2.2.2. Exceptions possible12.2.3. The EXISTS Method12.2.3.1. Boundary considerations12.2.3.2. Exceptions possible12.2.4. The EXTEND Method12.2.4.1. Boundary considerations12.2.4.2. Exceptions possible12.2.5. The FIRST and LAST Methods12.2.5.1. Boundary considerations12.2.5.2. Exceptions possible12.2.6. The LIMIT Method12.2.6.1. Boundary considerations12.2.6.2. Exceptions possible12.2.7. The PRIOR and NEXT Methods12.2.7.1. Boundary considerations12.2.7.2. Exceptions possible12.2.8. The TRIM Method12.2.8.1. Boundary considerations12.2.8.2. Exceptions possible12.3. Working with Collections12.3.1. Declaring Collection Types12.3.1.1. Declaring an associative array collection type12.3.1.2. Declaring a nested table or VARRAY12.3.1.3. Changing nested table of VARRAY characteristics12.3.2. Declaring and Initializing Collection Variables12.3.2.1. Initializing implicitly during direct assignment12.3.2.2. Initializing implicitly via FETCH12.3.2.3. VARRAY integration12.3.3. Populating Collections with Data12.3.3.1. Using the assignment operator12.3.3.2. What row values can I use?12.3.3.3. Aggregate assignments12.3.3.4. Assigning rows from a relational table12.3.3.5. Advantage of nonsequential population of collection12.3.4. Accessing Data Inside a Collection12.3.5. Collections of Complex Datatypes12.3.5.1. Collections of records12.3.5.2. Collections of objects and other complex types12.3.5.3. Multilevel collections12.3.5.4. Unnamed nested collections: multidimensional arrays12.3.5.5. Basic operations12.3.5.6. How deeply can I nest collections?12.3.6. Using String-Indexed Collections12.3.7. Working with Collections in SQL12.3.7.1. The CAST pseudo-function12.3.7.2. The MULTISET pseudo-function12.3.7.3. The TABLE pseudo-function12.3.7.4. Sorting contents of collections12.4. Nested Table Multiset Operations12.4.1. Testing Equality and Membership of Nested Tables12.4.2. Checking for Membership of an Element in a Nested Table12.4.3. Performing High-Level Set Operations12.4.4. Handling Duplicates in a Nested Table12.5. Maintaining Schema-Level Collections12.5.1. Necessary Privileges12.5.2. Collections and the Data Dictionary
13. Miscellaneous Datatypes
13.1. The BOOLEAN Datatype13.2. The RAW Datatype13.3. The UROWID and ROWID Datatypes13.3.1. Getting at Rowids13.3.2. Using Rowids13.3.2.1. Do rowids ever change?13.3.2.2. Using rowids in Oracle Forms13.3.2.3. Using rowids in a cursor FOR loop13.3.2.4. Is the use of rowids worth the effort?13.4. The LOB Datatypes13.5. Working with LOBs13.5.1. Understanding LOB Locators13.5.2. Empty Versus NULL LOBs13.5.3. Creating a LOB13.5.4. Writing into a LOB13.5.5. Reading from a LOB13.5.6. BFILEs Are Different13.5.6.1. Creating a BFILE locator13.5.6.2. Accessing BFILEs13.5.6.3. Using BFILEs to load LOB columns13.5.7. Temporary LOBs13.5.7.1. Creating a temporary LOB13.5.7.2. Freeing a temporary LOB13.5.7.3. Checking to see whether a LOB is temporary13.5.7.4. Managing temporary LOBs13.5.8. Native LOB Operations13.5.8.1. SQL semantics may yield temporary LOBs13.5.8.2. Performance impact of using SQL semantics13.5.9. LOB Conversion Functions13.6. Predefined Object Types13.6.1. The XMLType Type13.6.2. The URI Types13.6.3. The Any Types
IV. SQL in PL/SQL
14. DML and Transaction Management
14.1. DML in PL/SQL14.1.1. A Quick Introduction to DML14.1.1.1. The INSERT statement14.1.1.2. The UPDATE statement14.1.1.3. The DELETE statement14.1.2. Cursor Attributes for DML Operations14.1.3. RETURNING Information from DML Statements14.1.4. DML and Exception Handling14.1.5. DML and Records14.1.5.1. Record-based inserts14.1.5.2. Record-based updates14.1.5.3. Using records with the RETURNING clause14.1.5.4. Restrictions on record-based inserts and updates14.2. Bulk DML with the FORALL Statement14.2.1. Syntax of the FORALL Statement14.2.2. Context-Switching Problem Scenarios14.2.3. FORALL Examples14.2.4. Cursor Attributes for FORALL14.2.5. ROLLBACK Behavior with FORALL14.2.6. Continuing Past Exceptions with FORALL14.2.7. Driving FORALL with Nonsequential Arrays14.2.7.1. INDICES OF example14.2.7.2. VALUES OF example14.3. Transaction Management14.3.1. The COMMIT Statement14.3.2. The ROLLBACK Statement14.3.3. The SAVEPOINT Statement14.3.4. The SET TRANSACTION Statement14.3.5. The LOCK TABLE Statement14.4. Autonomous Transactions14.4.1. Defining Autonomous Transactions14.4.2. Rules and Restrictions on Autonomous Transactions14.4.3. Transaction Visibility14.4.4. When to Use Autonomous Transactions14.4.5. Building an Autonomous Logging Mechanism
15. Data Retrieval
15.1. Cursor Basics15.1.1. Some Data Retrieval Terms15.1.2. Typical Query Operations15.1.3. Introduction to Cursor Attributes15.1.3.1. The %FOUND attribute15.1.3.2. The %NOTFOUND attribute15.1.3.3. The %ROWCOUNT attribute15.1.3.4. The %ISOPEN attribute15.1.3.5. The %BULK_ROWCOUNT attribute15.1.3.6. The %BULK_EXCEPTIONS attribute15.1.4. Referencing PL/SQL Variables in a Cursor15.1.4.1. Identifier precedence in a cursor15.1.4.2. Using standard naming conventions15.1.5. Choosing Between Explicit and Implicit Cursors15.2. Working with Implicit Cursors15.2.1. Implicit Cursor Examples15.2.2. Error Handling with Implicit Cursors15.2.3. Implicit SQL Cursor Attributes15.3. Working with Explicit Cursors15.3.1. Declaring Explicit Cursors15.3.1.1. Naming your cursor15.3.1.2. Declaring cursors in packages15.3.2. Opening Explicit Cursors15.3.3. Fetching from Explicit Cursors15.3.3.1. Examples of explicit cursors15.3.3.2. Fetching past the last row15.3.4. Column Aliases in Explicit Cursors15.3.5. Closing Explicit Cursors15.3.6. Explicit Cursor Attributes15.3.7. Cursor Parameters15.3.7.1. Generalizing cursors with parameters15.3.7.2. Opening cursors with parameters15.3.7.3. Scope of cursor parameters15.3.7.4. Cursor parameter modes15.3.7.5. Default values for parameters15.4. BULK COLLECT15.4.1. Limiting Rows Retrieved with BULK COLLECT15.4.2. Bulk Fetching of Multiple Columns15.4.3. Using the RETURNING Clause with Bulk Operations15.5. SELECT ... FOR UPDATE15.5.1. Releasing Locks with COMMIT15.5.2. The WHERE CURRENT OF Clause15.6. Cursor Variables and REF CURSORs15.6.1. Why Cursor Variables?15.6.2. Similarities to Static Cursors15.6.3. Declaring REF CURSOR Types15.6.4. Declaring Cursor Variables15.6.5. Opening Cursor Variables15.6.6. Fetching from Cursor Variables15.6.6.1. Handling the ROWTYPE_MISMATCH exception15.6.7. Rules for Cursor Variables15.6.7.1. Compile-time rowtype matching rules15.6.7.2. Runtime rowtype matching rules15.6.7.3. Cursor variable aliases15.6.7.4. Scope of cursor object15.6.8. Passing Cursor Variables as Arguments15.6.8.1. Identifying the REF CURSOR type15.6.8.2. Setting the parameter mode15.6.9. Cursor Variable Restrictions15.7. Cursor Expressions15.7.1. Using Cursor Expressions15.7.1.1. Retrieve subquery as column15.7.1.2. Implement a streaming function with the CURSOR expression15.7.2. Restrictions on Cursor Expressions
16. Dynamic SQL and Dynamic PL/SQL
16.1. NDS Statements16.1.1. The EXECUTE IMMEDIATE Statement16.1.2. The OPEN FOR Statement16.1.2.1. FETCH into variables or records16.1.2.2. The USING clause in OPEN FOR16.1.3. About the Four Dynamic SQL Methods16.1.3.1. Method 116.1.3.2. Method 216.1.3.3. Method 316.1.3.4. Method 416.2. Binding Variables16.2.1. Argument Modes16.2.2. Duplicate Placeholders16.2.3. Passing NULL Values16.3. Working with Objects and Collections16.4. Dynamic PL/SQL16.4.1. Replace Repetitive Code with Dynamic Block16.4.2. Implement Method 4 Dynamic SQL in NDS16.5. Recommendations for NDS16.5.1. Use Invoker Rights for Shared Programs16.5.2. Anticipate and Handle Dynamic Errors16.5.3. Use Binding Rather Than Concatenation16.5.4. Avoid Code Injection with Binding16.6. When to Use DBMS_SQL16.6.1. Parse Very Long Strings16.6.2. Obtain Information About Query Columns16.6.3. Meet Method 4 Dynamic SQL Requirements16.6.3.1. The “in table” procedural interface16.6.3.2. Steps for intab construction16.6.3.3. Constructing the SELECT16.6.3.4. Defining the cursor structure16.6.3.5. Retrieving and displaying data16.6.4. Minimize Parsing of Dynamic Cursors16.7. NDS Utility Package
V. PL/SQL Application Construction
17. Procedures, Functions, and Parameters
17.1. Modular Code17.2. Procedures17.2.1. Calling a Procedure17.2.2. The Procedure Header17.2.3. The Procedure Body17.2.4. The END Descriptor17.2.5. The RETURN Statement17.3. Functions17.3.1. Structure of a Function17.3.2. The RETURN Datatype17.3.3. The END Descriptor17.3.4. Calling a Function17.3.5. Functions Without Parameters17.3.6. The Function Header17.3.7. The Function Body17.3.8. The RETURN Statement17.3.8.1. RETURN any valid expression17.3.8.2. Multiple RETURNs17.3.8.3. RETURN as last executable statement17.4. Parameters17.4.1. Defining Parameters17.4.2. Actual and Formal Parameters17.4.3. Parameter Modes17.4.3.1. IN mode17.4.3.2. OUT mode17.4.3.3. IN OUT mode17.4.4. Matching Actual and Formal Parameters in PL/SQL17.4.4.1. Positional notation17.4.4.2. Named notation17.4.4.3. Benefits of named notation17.4.5. The NOCOPY Parameter Mode Hint17.4.5.1. Restrictions on NOCOPY17.4.5.2. Impact of NOCOPY17.4.6. Default Values17.5. Local Modules17.5.1. Benefits of Local Modularization17.5.1.1. Reducing code volume17.5.1.2. Improving readability17.5.2. Scope of Local Modules17.5.3. Sprucing Up Your Code with Local Modules17.6. Module Overloading17.6.1. Benefits of Overloading17.6.1.1. Supporting many data combinations17.6.2. Restrictions on Overloading17.6.3. Overloading with Numeric Types17.7. Forward Declarations17.8. Advanced Topics17.8.1. Calling Your Function Inside SQL17.8.1.1. Requirements for calling functions in SQL17.8.1.2. Restrictions on user-defined functions in SQL17.8.1.3. Read consistency and user-defined functions17.8.1.4. Replacing DECODEs with IF statements17.8.1.5. The PRAGMA RESTRICT_REFERENCES (Oracle8 Database and earlier)17.8.2. Table Functions17.8.2.1. Calling a function in a FROM clause17.8.2.2. Passing table function results with a cursor variable17.8.2.3. Creating a streaming function17.8.2.4. Creating a pipelined function17.8.2.5. Enabling a function for parallel execution17.8.3. Deterministic Functions17.9. Go Forth and Modularize!
18. Packages
18.1. Why Packages?18.1.1. Demonstrating the Power of the Package18.1.2. Some Package-Related Concepts18.1.3. Diagramming Privacy18.2. Rules for Building Packages18.2.1. The Package Specification18.2.2. The Package Body18.2.3. Initializing Packages18.2.3.1. Execute complex initialization logic18.2.3.2. Cache static session information18.2.3.3. Avoid side effects when initializing18.2.3.4. When initialization fails18.3. Rules for Calling Packaged Elements18.4. Working with Package Data18.4.1. Global Within a Single Oracle Session18.4.2. Global Public Data18.4.3. Packaged Cursors18.4.3.1. Declaring packaged cursors18.4.3.2. Working with packaged cursors18.4.4. Serializable Packages18.5. When to Use Packages18.5.1. Encapsulating Data Manipulation18.5.2. Avoid Hardcoding Literals18.5.3. Improve Usability of Built-in Features18.5.4. Group Together Logically Related Functionality18.5.5. Cache Static Session Data18.6. Packages and Object Types
19. Triggers
19.1. DML Triggers19.1.1. DML Trigger Concepts19.1.1.1. DML trigger scripts19.1.1.2. Transaction participation19.1.2. Creating a DML Trigger19.1.2.1. The WHEN clause19.1.2.2. Working with NEW and OLD pseudo-records19.1.2.3. Determining the DML action within a trigger19.1.3. DML Trigger Example: No Cheating Allowed!19.1.3.1. Applying the WHEN clause19.1.3.2. Using pseudo-records to fine-tune trigger execution19.1.4. Multiple Triggers of the Same Type19.1.5. Mutating Table Errors19.2. DDL Triggers19.2.1. Creating a DDL Trigger19.2.2. Available Events19.2.3. Available Attributes19.2.4. Working with Events and Attributes19.2.4.1. What column did I touch?19.2.4.2. Lists returned by attribute functions19.2.5. Dropping the Undroppable19.2.6. The INSTEAD OF CREATE Trigger19.3. Database Event Triggers19.3.1. Creating a Database Event Trigger19.3.2. The STARTUP Trigger19.3.3. The SHUTDOWN Trigger19.3.4. The LOGON Trigger19.3.5. The LOGOFF Trigger19.3.6. The SERVERERROR Trigger19.3.6.1. SERVERERROR examples19.3.6.2. Central error handler19.4. INSTEAD OF Triggers19.4.1. Creating an INSTEAD OF Trigger19.4.2. The INSTEAD OF INSERT Trigger19.4.3. The INSTEAD OF UPDATE Trigger19.4.4. The INSTEAD OF DELETE Trigger19.4.5. Populating the Tables19.4.6. INSTEAD OF Triggers on Nested Tables19.5. AFTER SUSPEND Triggers19.5.1. Setting Up for the AFTER SUSPEND Trigger19.5.2. Looking at the Actual Trigger19.5.3. The ORA_SPACE_ERROR_INFO Function19.5.4. The DBMS_RESUMABLE Package19.5.5. Trapped Multiple Times19.5.6. To Fix or Not to Fix?19.6. Maintaining Triggers19.6.1. Disabling, Enabling, and Dropping Triggers19.6.2. Viewing Triggers19.6.3. Checking the Validity of Triggers
20. Managing PL/SQL Code
20.1. Managing Code in the Database20.1.1. Data Dictionary Views for PL/SQL Programmers20.1.1.1. Display information about stored objects20.1.1.2. Display and search source code20.1.1.3. Use program size to determine pinning requirements20.1.1.4. Obtain properties of stored code20.1.1.5. Analyze and modify trigger state through views20.1.1.6. Analyze argument information20.1.2. Recompiling Invalid Code20.1.2.1. Recompile individual program units20.1.2.2. Use UTL_RECOMP20.2. Using Native Compilation20.2.1. Perform One-Time DBA Setup20.2.2. Step 1: Get a Supported C Compiler20.2.3. Step 2: Set Up the Directories20.2.4. Step 3: Check $ORACLE_HOME/plsql/spnc_commands20.2.5. Interpreted Versus Native Compilation Mode20.3. Using the Optimizing Compiler and Compile-Time Warnings20.3.1. The Optimizing Compiler20.3.2. Compile-Time Warnings20.3.2.1. A quick example20.3.2.2. If you see a “no message file” message20.3.2.3. Verify your SQL*Plus version20.3.2.4. How to turn on compile-time warnings20.3.3. Warnings Available in Oracle Database 10g20.3.3.1. PLW-05000: mismatch in NOCOPY qualification between specification and body20.3.3.2. PLW-05001: previous use of ’string’ (at line string) conflicts with this use20.3.3.3. PLW-05003: same actual parameter(string and string) at IN and NOCOPY may have side effects20.3.3.4. PLW-05004: identifier string is also declared in STANDARD or is a SQL built-in20.3.3.5. PLW-05005: function string returns without value at line string20.3.3.6. PLW-06002: unreachable code20.3.3.7. PLW-07203: parameter ’string’ may benefit from use of the NOCOPY compiler hint20.3.3.8. PLW-07204: conversion away from column type may result in sub-optimal query plan20.4. Conditional Compilation20.4.1. Examples of Conditional Compilation20.4.1.1. Use application package constants in $IF directive20.4.1.2. Toggle tracing through conditional compilation flags20.4.2. The Inquiry Directive20.4.2.1. The DBMS_DB_VERSION package20.4.2.2. Setting compilation environment parameters20.4.2.3. Referencing unit name and line number20.4.2.4. Using the PLSQL_CCFLAGS parameter20.4.3. The $IF Directive20.4.4. The $ERROR Directive20.4.5. Synchronizing Code with Packaged Constants20.4.6. Program-Specific Settings with Inquiry Directives20.4.7. Working with Postprocessed Code20.5. Testing PL/SQL Programs20.5.1. Typical, Tawdry Testing Techniques20.5.2. utPLSQL: A Unit-Testing Framework20.5.2.1. Using utPLSQL with betwnstr20.5.2.2. Where to find utPLSQL and Ounit20.6. Debugging PL/SQL Programs20.6.1. The Wrong Way to Debug20.6.1.1. Disorganized debugging20.6.1.2. Irrational debugging20.6.2. Debugging Tips and Strategies20.6.2.1. Use a source code debugger20.6.2.2. Gather data20.6.2.3. Remain logical at all times20.6.2.4. Analyze instead of trying20.6.2.5. Take breaks, and ask for help20.6.2.6. Change and test one area of code at a time20.6.3. Tracing Execution of Your Code20.6.3.1. DBMS_UTILITY.FORMAT_CALL_STACK20.6.3.2. Installing DBMS_TRACE20.6.3.3. DBMS_TRACE programs20.6.3.4. Controlling trace file contents20.6.3.5. Pausing and resuming the trace process20.6.3.6. Format of collected data20.7. Tuning PL/SQL Programs20.7.1. Analyzing Performance of PL/SQL Code20.7.2. Optimizing PL/SQL Performance20.7.2.1. Use the most aggressive compiler optimization level possible20.7.2.2. Use BULK COLLECT when querying multiple rows20.7.2.3. Use FORALL when modifying multiple rows20.7.2.4. Use the NOCOPY hint when passing large structures20.7.2.5. Use PLS_INTEGER for intensive integer computations.20.7.2.6. Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic20.7.2.7. Group together related programs in a package20.7.2.8. Pin into shared memory large and frequently executed programs.20.8. Protecting Stored Code20.8.1. Restrictions on and Limitations of Wrapping20.8.2. Using the Wrap Executable20.8.3. Dynamic Wrapping with DBMS_DDL20.8.4. Guidelines for Working with Wrapped Code
21. I/O and PL/SQL
21.1. Displaying Information21.1.1. Enabling DBMS_OUTPUT21.1.2. Write Lines to a Buffer21.1.3. Read the Contents of a Buffer21.2. Reading and Writing Files21.2.1. The UTL_FILE_DIR Parameter21.2.1.1. Setting up directories21.2.1.2. Specifying file locations21.2.2. Work with Oracle Directories21.2.3. Open Files21.2.4. Is the File Already Open?21.2.5. Close Files21.2.6. Read from Files21.2.6.1. GET_LINE exceptions21.2.6.2. Handy encapsulation for GET_LINE21.2.7. Write to Files21.2.7.1. Writing formatted text to file21.2.8. Copy Files21.2.9. Delete Files21.2.10. Rename and Move Files21.2.11. Retrieve File Attributes21.3. Sending Email21.3.1. Oracle Prerequisites by Release21.3.2. Send a Short (32K or Less) Plaintext Message21.3.3. Include “Friendly” Names in Email Addresses21.3.4. Send a Plaintext Message of Arbitrary Length21.3.5. Send a Message with a Short (< 32K) Attachment21.3.6. Send a Small File (< 32K) as an Attachment21.3.7. Attach a File of Arbitrary Size21.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 LOB21.4.3. Authenticate Using HTTP Username/Password21.4.4. Retrieve an SSL-Encrypted Web Page (Via https)21.4.5. Submit Data to a Web Page via GET or POST21.4.6. Disable Cookies or Make Cookies Persistent21.4.7. Retrieve Data from an FTP Server21.4.8. Use a Proxy Server21.5. Other Types of I/O Available in PL/SQL21.5.1. Database Pipes, Queues, and Alerts21.5.2. TCP Sockets21.5.3. Oracle’s Built-in Web Server
VI. Advanced PL/SQL Topics
22. Application Security and PL/SQL
22.1. Security Overview22.2. Encryption22.2.1. Key Length22.2.2. Algorithms22.2.3. Padding and Chaining22.2.4. The DBMS_CRYPTO Package22.2.4.1. Algorithms22.2.4.2. Padding and chaining22.2.5. Encrypting Data22.2.6. Encrypting LOBs22.2.7. Decrypting Data22.2.8. Performing Key Generation22.2.9. Performing Key Management22.2.9.1. A single key for the database22.2.9.2. A single key for each row22.2.9.3. A combined approach22.2.10. Cryptographic Hashing22.2.11. Using Message Authentication Code22.2.12. Using Transparent Data Encryption (TDE)22.3. Row-Level Security22.3.1. Why Learn About RLS?22.3.2. A Simple RLS Example22.3.3. Using Dynamic Policies22.3.3.1. Shared static policy22.3.3.2. Context-sensitive policy22.3.3.3. Shared context sensitive policy22.3.4. Using Column-Sensitive RLS22.3.5. RLS Debugging22.3.5.1. Interpreting errors22.3.5.2. Performing direct path operations22.3.5.3. Viewing SQL statements22.4. Application Contexts22.4.1. Using Application Contexts22.4.2. Security in Contexts22.4.3. Contexts as Predicates in RLS22.4.4. Identifying Non-Database Users22.5. Fine-Grained Auditing22.5.1. Why Learn About FGA?22.5.2. A Simple FGA Example22.5.3. Access How Many Columns?22.5.4. Checking the Audit Trail22.5.5. Using Bind Variables22.5.6. Using Handler Modules
23. Inside PL/SQL
23.1. Looking Under the Hood23.1.1. PL/SQL Concepts23.1.2. Physical Storage of Server-Side PL/SQL23.1.3. Compiler Limits23.2. PL/SQL’s Optimizing Compiler23.2.1. Runtime Optimization of Fetch Loops23.3. Dependency Management23.3.1. Dependencies in Server-Side PL/SQL23.3.2. Healing Invalids23.3.2.1. Recompiling by hand23.3.2.2. Recompiling by script23.3.2.3. Automatic recompilation23.3.3. Remote Dependencies23.4. Execution Authority Models23.4.1. The Definer Rights Model23.4.1.1. Advantages of definer rights23.4.1.2. Disadvantages of definer rights23.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 Model23.4.2.1. Invoker rights syntax23.4.2.2. Some rules and restrictions23.4.3. Combining Rights Models23.5. PL/SQL and Oracle Memory23.5.1. PGA, UGA, and CGA23.5.2. Cursors, Memory, and More23.5.3. Tips on Reducing Memory Use23.5.3.1. Statement sharing23.5.3.2. Bind variables23.5.3.3. Packaging to improve memory use and performance23.5.3.4. Large collections in PL/SQL23.5.3.5. Preservation of state23.5.4. What to Do if You Run Out of Memory23.6. Server-Side PL/SQL Processing: Reprise23.6.1. Compiling an Anonymous Block23.6.2. Compiling a Stored Object23.6.3. Executing PL/SQL23.7. What You Need to Know
24. Globalization and Localization in PL/SQL
24.1. Overview and Terminology24.2. Unicode Primer24.2.1. National Character Set Datatypes24.2.2. Character Encoding24.2.3. Globalization Support Parameters24.2.4. Unicode Functions24.2.4.1. ASCIISTR24.2.4.2. COMPOSE24.2.4.3. DECOMPOSE24.2.4.4. INSTR/INSTRB/INSTRC/INSTR2/INSTR424.2.4.5. LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH424.2.4.6. SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR424.2.4.7. UNISTR24.3. Character Semantics24.4. String Sort Order24.4.1. Binary Sort24.4.2. Monolingual Sort24.4.3. Multilingual Sort24.5. Multilingual Information Retrieval24.5.1. IR and PL/SQL24.6. Date/Time24.6.1. Timestamp Datatypes24.6.2. Date/Time Formatting24.7. Currency Conversion24.8. Globalization Development Kit for PL/SQL24.8.1. UTL_118N Utility Package24.8.2. UTL_LMS Error-Handling Package24.8.3. GDK Implementation Options24.8.3.1. Method 1: Locale buttons24.8.3.2. Method 2: User administration24.8.3.3. Method 3: Hybrid
25. Object-Oriented Aspects of PL/SQL
25.1. Introduction to Oracle’s Object Features25.2. An Extended Example25.2.1. A Tree of Types25.2.1.1. Creating a base type25.2.1.2. Creating a subtype25.2.2. Methods25.2.3. Storing, Retrieving, and Using Persistent Objects25.2.3.1. Object identity25.2.3.2. The VALUE function25.2.3.3. The TREAT function25.2.4. Evolution and Creation25.2.5. Back to Pointers?25.2.5.1. Using REFs25.2.5.2. The UTL_REF package25.2.5.3. REFs and type hierarchies25.2.5.4. Dangling REFs25.2.6. Generic Data: The ANY Types25.2.6.1. Preview: What ANYDATA is not25.2.6.2. Dealing with an ANYDATA25.2.6.3. Creating a transient type25.2.7. I Can Do It Myself25.2.8. Comparing Objects25.2.8.1. Attribute-level comparison25.2.8.2. The MAP method25.2.8.3. The ORDER method25.2.8.4. Additional comparison recommendations25.3. Object Views25.3.1. A Sample Relational System25.3.2. Object View with a Collection Attribute25.3.3. Object Subview25.3.4. Object View with Inverse Relationship25.3.5. INSTEAD OF Triggers25.3.5.1. The case against25.3.5.2. The case for25.3.5.3. The bigger question25.3.6. Differences Between Object Views and Object Tables25.3.6.1. OID uniqueness25.3.6.2. “Storeability” of physical versus virtual REFs25.3.6.3. REFs to nonunique OIDs25.4. Maintaining Object Types and Object Views25.4.1. Privileges25.4.1.1. The EXECUTE privilege25.4.1.2. The UNDER privilege25.4.1.3. The DEBUG privilege25.4.1.4. The DML privileges25.5. Pontifications
26. Calling Java from PL/SQL
26.1. Oracle and Java26.2. Getting Ready to Use Java in Oracle26.2.1. Installing Java26.2.2. Building and Compiling Your Java Code26.2.3. Setting Permissions for Java Development and Execution26.2.3.1. Java security for Oracle through 8.1.526.2.3.2. Java security for Oracle for 8.1.6 through Oracle Database 10g26.3. A Simple Demonstration26.3.1. Finding the Java Functionality26.3.2. Building a Custom Java Class26.3.3. Compiling and Loading into Oracle26.3.4. Building a PL/SQL Wrapper26.3.5. Deleting Files from PL/SQL26.4. Using loadjava26.5. Using dropjava26.6. Managing Java in the Database26.6.1. The Java Namespace in Oracle26.6.2. Examining Loaded Java Elements26.7. Using DBMS_JAVA26.7.1. LONGNAME: Converting Java Long Names26.7.2. GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options26.7.3. SET_OUTPUT: Enabling Output from Java26.7.4. EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects26.8. Publishing and Using Java in PL/SQL26.8.1. Call Specs26.8.2. Some Rules for Call Specs26.8.3. Mapping Datatypes26.8.4. Calling a Java Method in SQL26.8.5. Exception Handling with Java26.8.6. Extending File I/O Capabilities26.8.6.1. Polishing up the delete method26.8.6.2. Obtaining directory contents26.8.7. Other Examples
27. External Procedures
27.1. Introduction to External Procedures27.1.1. Example: Invoking an Operating System Command27.1.2. Architecture of External Procedures27.2. The Oracle Net Configuration27.2.1. Specifying the Listener Configuration27.2.2. Security Characteristics of the Configuration27.3. Setting Up Multithreaded Mode27.4. Creating an Oracle Library27.5. Writing the Call Specification27.5.1. The Call Spec: Overall Syntax27.5.2. Parameter Mapping: The Example Revisited27.5.3. Parameter Mapping: The Full Story27.5.4. More Syntax: The PARAMETERS Clause27.5.5. PARAMETERS Properties27.5.5.1. The INDICATOR property27.5.5.2. The LENGTH property27.5.5.3. The MAXLEN property27.5.5.4. The CHARSETID and CHARSETFORM properties27.6. Raising an Exception from the Called C Program27.7. Nondefault Agents27.8. Maintaining External Procedures27.8.1. Dropping Libraries27.8.2. Data Dictionary27.8.3. Rules and Warnings
VII. Appendixes
A. Regular Expression Metacharacters and Function Parameters
A.1. MetacharactersA.2. Function Parameters
B. Number Format Models
C. Date Format Models
About the Authors
Colophon
Copyright

Overview

For the past ten years, O'Reilly's Oracle PL/SQL Programming has been the bestselling book on PL/SQL, Oracle's powerful procedural language. Packed with examples and helpful recommendations, the book has helped everyone--from novices to experienced developers, and from Oracle Forms developers to database administrators--make the most of PL/SQL.

The fourth edition is a comprehensive update, adding significant new content and extending coverage to include the very latest Oracle version, Oracle Database 10g Release 2. It describes such new features as the PL/SQL optimizing compiler, conditional compilation, compile-time warnings, regular expressions, set operators for nested tables, nonsequential collections in FORALL, the programmer-defined quoting mechanism, the ability to backtrace an exception to a line number, a variety of new built-in packages, and support for IEEE 754 compliant floating-point numbers.

The new edition adds brand-new chapters on security (including encryption, row-level security, fine-grained auditing, and application contexts), file, email, and web I/O (including the built-in packages DBMS_OUTPUT, UTL_FILE, UTL_MAIL, UTL_SMTP, and UTL_HTTP) and globalization and localization.

Co-authored by the world's foremost PL/SQL authority, Steven Feuerstein, this classic reference provides language syntax, best practices, and extensive code, ranging from simple examples to complete applications--making it a must-have on your road to PL/SQL mastery. A companion web site contains many more examples and additional technical content for enhanced learning.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Oracle PL/SQL Programming, Third Edition

Publisher Resources

ISBN: 0596009771Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills