Oracle PL/SQL Programming. 6th Edition Steven Feuerstein, Bill Pribyl

Spis treści ebooka

• Oracle PL/SQL Programming
• Dedication
• 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
  • Using Code Examples
  • Safari Books Online
  • How to Contact Us
  • Acknowledgments
• I. Programming in PL/SQL
  • 1. Introduction to PL/SQL
    • What Is PL/SQL?
    • The Origins of PL/SQL
      • The Early Years of PL/SQL
      • Improved Application Portability
      • Improved Execution Authority and Transaction Integrity
      • Humble Beginnings, Steady Improvement
    • So This Is PL/SQL
      • Integration with SQL
      • Control and Conditional Logic
      • When Things Go Wrong
    • About PL/SQL Versions
      • Oracle Database 12c New PL/SQL Features
        • More PL/SQL-only datatypes cross PL/SQL-to-SQL interface
        • ACCESSIBLE_BY clause
        • Implicit statement results
        • BEQUEATH CURRENT_USER views
        • Grant roles to program units
        • New conditional compilation directives
        • Optimizing function execution in SQL
        • Using %ROWTYPE with invisible columns
        • FETCH FIRST clause and BULK COLLECT
        • The UTL_CALL_STACK package
    • Resources for PL/SQL Developers
      • The OReilly PL/SQL Series
      • PL/SQL on the Internet
    • Some Words of Advice
      • Dont Be in Such a Hurry!
      • Dont Be Afraid to Ask for Help
      • Take a Creative, Even Radical Approach
  • 2. Creating and Running PL/SQL Code
    • Navigating the Database
    • Creating and Editing Source Code
    • SQL*Plus
      • Starting Up SQL*Plus
      • Running a SQL Statement
      • Running a PL/SQL Program
      • Running a Script
      • What Is the Current Directory?
      • Other SQL*Plus Tasks
        • Setting your preferences
        • Saving output to a file
        • Exiting SQL*Plus
        • Editing a statement
        • Loading your own custom environment automatically on startup
      • Error Handling in SQL*Plus
      • Why You Will Love and Hate SQL*Plus
    • Performing Essential PL/SQL Tasks
      • Creating a Stored Program
      • Executing a Stored Program
      • Showing Stored Programs
      • Managing Grants and Synonyms for Stored Programs
      • Dropping a Stored Program
      • Hiding the Source Code of a Stored Program
    • Editing Environments for PL/SQL
    • Calling PL/SQL from Other Languages
      • C: Using Oracles Precompiler (Pro*C)
      • Java: Using JDBC
      • Perl: Using Perl DBI and DBD::Oracle
      • PHP: Using Oracle Extensions
      • PL/SQL Server Pages
      • And Where Else?
  • 3. Language Fundamentals
    • PL/SQL Block Structure
      • Anonymous Blocks
      • Named Blocks
      • Nested Blocks
      • Scope
      • Qualify All References to Variables and Columns in SQL Statements
        • Improve readability
        • Avoid bugs through qualifiers
      • Visibility
        • Visible identifiers
        • Qualified identifiers
        • Qualifying identifier names with module names
        • Nested programs
    • The PL/SQL Character Set
    • Identifiers
      • Reserved Words
        • Reserved words
        • Identifiers from STANDARD package
        • How to avoid using reserved words
      • Whitespace and Keywords
    • Literals
      • NULLs
      • Embedding Single Quotes Inside a Literal String
      • Numeric Literals
      • Boolean Literals
    • The Semicolon Delimiter
    • Comments
      • Single-Line Comment Syntax
      • Multiline Comment Syntax
    • The PRAGMA Keyword
    • Labels
• II. PL/SQL Program Structure
  • 4. Conditional and Sequential Control
    • IF Statements
      • The IF-THEN Combination
      • The IF-THEN-ELSE Combination
      • The IF-THEN-ELSIF Combination
      • Avoiding IF Syntax Gotchas
      • Nested IF Statements
      • Short-Circuit Evaluation
    • CASE Statements and Expressions
      • Simple CASE Statements
      • Searched CASE Statements
      • Nested CASE Statements
      • CASE Expressions
    • The GOTO Statement
    • The NULL Statement
      • Improving Program Readability
      • Using NULL After a Label
  • 5. Iterative Processing with Loops
    • Loop Basics
      • Examples of Different Loops
      • Structure of PL/SQL Loops
    • The Simple Loop
      • Terminating a Simple Loop: EXIT and EXIT WHEN
      • Emulating a REPEAT UNTIL Loop
      • The Intentionally Infinite Loop
    • The WHILE Loop
    • The Numeric FOR Loop
      • Rules for Numeric FOR Loops
      • Examples of Numeric FOR Loops
      • Handling Nontrivial Increments
    • The Cursor FOR Loop
      • Example of Cursor FOR Loops
    • Loop Labels
    • The CONTINUE Statement
    • Tips for Iterative Processing
      • Use Understandable Names for Loop Indexes
      • The Proper Way to Say Goodbye
      • Obtaining Information About FOR Loop Execution
      • SQL Statement as Loop
  • 6. Exception Handlers
    • Exception-Handling Concepts and Terminology
    • Defining Exceptions
      • Declaring Named Exceptions
      • Associating Exception Names with Error Codes
        • Using EXCEPTION_INIT
        • Recommended uses of EXCEPTION_INIT
      • About Named System Exceptions
      • Scope of an Exception
    • Raising Exceptions
      • The RAISE Statement
      • Using RAISE_APPLICATION_ERROR
    • Handling Exceptions
      • Built-in Error Functions
        • More on DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
        • Just the line number, please
        • Useful applications of SQLERRM
      • Combining Multiple Exceptions in a Single Handler
      • Unhandled Exceptions
      • Propagation of Unhandled Exceptions
        • Losing exception information
        • Examples of exception propagation
      • Continuing Past Exceptions
      • Writing WHEN OTHERS Handling Code
    • Building an Effective Error Management Architecture
      • Decide on Your Error Management Strategy
      • Standardize Handling of Different Types of Exceptions
        • Deliberate exceptions
        • Unfortunate and unexpected exceptions
        • How to benefit from this categorization
      • Organize Use of Application-Specific Error Codes
      • Use Standardized Error Management Programs
      • Work with Your Own Exception Objects
      • Create Standard Templates for Common Error Handling
    • Making the Most of PL/SQL Error Management
• III. PL/SQL Program Data
  • 7. Working with Program Data
    • Naming Your Program Data
    • Overview of PL/SQL Datatypes
      • Character Data
      • Numbers
      • Dates, Timestamps, and Intervals
      • Booleans
      • Binary Data
      • ROWIDs
      • REF CURSORs
      • Internet Datatypes
      • Any Datatypes
      • User-Defined Datatypes
    • Declaring Program Data
      • Declaring a Variable
      • Declaring Constants
      • The NOT NULL Clause
      • Anchored Declarations
      • Anchoring to Cursors and Tables
      • Benefits of Anchored Declarations
        • Synchronization with database columns
        • Normalization of local variables
      • Anchoring to NOT NULL Datatypes
    • Programmer-Defined Subtypes
    • Conversion Between Datatypes
      • Implicit Data Conversion
        • Limitations of implicit conversion
        • Drawbacks of implicit conversion
      • Explicit Datatype Conversion
        • The CHARTOROWID function
        • The CAST function
        • The CONVERT function
        • The HEXTORAW function
        • The RAWTOHEX function
        • The ROWIDTOCHAR function
  • 8. Strings
    • String Datatypes
      • The VARCHAR2 Datatype
      • The CHAR Datatype
      • String Subtypes
    • Working with Strings
      • Specifying String Constants
      • Using Nonprintable Characters
      • Concatenating Strings
      • Dealing with Case
        • Forcing a string to all upper- or lowercase
        • Making comparisons case insensitive
        • Case insensitivity and indexes
        • Capitalizing each word in a string
      • Traditional Searching, Extracting, and Replacing
      • Padding
      • Trimming
      • Regular Expression Searching, Extracting, and Replacing
        • Detecting a pattern
        • Locating a pattern
        • Extracting text matching a pattern
        • Counting regular expression matches
        • Replacing text
        • Groking greediness
        • Learning more about regular expressions
      • Working with Empty Strings
      • Mixing CHAR and VARCHAR2 Values
        • Database-to-variable conversion
        • Variable-to-database conversion
        • String comparisons
        • Character functions and CHAR arguments
    • String Function Quick Reference
  • 9. Numbers
    • Numeric Datatypes
      • The NUMBER Type
      • The PLS_INTEGER Type
      • The BINARY_INTEGER Type
      • The SIMPLE_INTEGER Type
      • The BINARY_FLOAT and BINARY_DOUBLE Types
      • The SIMPLE_FLOAT and SIMPLE_DOUBLE Types
      • Numeric Subtypes
    • Number Conversions
      • The TO_NUMBER Function
        • Using TO_NUMBER with no format
        • Using TO_NUMBER with a format model
        • Passing NLS settings to TO_NUMBER
      • The TO_CHAR Function
        • Using TO_CHAR with no format
        • Using TO_CHAR with a format model
        • The V format element
        • Rounding when converting numbers to character strings
        • Dealing with spaces when converting numbers to character strings
        • Passing NLS settings to TO_CHAR
      • The CAST Function
      • Implicit Conversions
    • Numeric Operators
    • Numeric Functions
      • Rounding and Truncation Functions
      • Trigonometric Functions
      • Numeric Function Quick Reference
  • 10. Dates and Timestamps
    • Datetime Datatypes
      • Declaring Datetime Variables
      • Choosing a Datetime Datatype
    • Getting the Current Date and Time
    • Interval Datatypes
      • Declaring INTERVAL Variables
      • When to Use INTERVALs
        • Finding the difference between two datetime values
        • Designating periods of time
    • Datetime Conversions
      • From Strings to Datetimes
      • From Datetimes to Strings
      • Working with Time Zones
      • Requiring a Format Mask to Match Exactly
      • Easing Up on Exact Matches
      • Interpreting Two-Digit Years in a Sliding Window
      • Converting Time Zones to Character Strings
      • Padding Output with Fill Mode
    • Date and Timestamp Literals
    • Interval Conversions
      • Converting from Numbers to Intervals
      • Converting Strings to Intervals
      • Formatting Intervals for Display
    • Interval Literals
    • CAST and EXTRACT
      • The CAST Function
      • The EXTRACT Function
    • Datetime Arithmetic
      • Date Arithmetic with Intervals and Datetimes
      • Date Arithmetic with DATE Datatypes
      • Computing the Interval Between Two Datetimes
      • Mixing DATEs and TIMESTAMPs
      • Adding and Subtracting Intervals
      • Multiplying and Dividing Intervals
      • Using Unconstrained INTERVAL Types
    • Date/Time Function Quick Reference
  • 11. Records
    • Records in PL/SQL
      • Benefits of Using Records
        • Data abstraction
        • Aggregate operations
        • Leaner, cleaner code
      • Declaring Records
      • Programmer-Defined Records
        • Declaring programmer-defined record TYPEs
        • Declaring the record
        • Examples of programmer-defined record declarations
      • Working with Records
        • Record-level operations
        • Field-level operations
        • Field-level operations with nested records
        • Field-level operations with package-based records
      • Comparing Records
      • Trigger Pseudorecords
        • %ROWTYPE and invisible columns (Oracle Database 12c)
  • 12. Collections
    • Collections Overview
      • Collections Concepts and Terminology
      • Types of Collections
      • Collection Examples
        • Using an associative array
        • Using a nested table
        • Using a VARRAY
      • Where You Can Use Collections
        • Collections as components of a record
        • Collections as program parameters
        • Collection as datatype of a functions return value
        • Collection as columns in a database table
        • Collections as attributes of an object type
      • Choosing a Collection Type
    • Collection Methods (Built-ins)
      • The COUNT Method
        • Boundary considerations
        • Exceptions possible
      • The DELETE Method
        • Boundary considerations
        • Exceptions possible
      • The EXISTS Method
        • Boundary considerations
        • Exceptions possible
      • The EXTEND Method
        • Boundary considerations
        • Exceptions possible
      • The FIRST and LAST Methods
        • Boundary considerations
        • Exceptions possible
      • The LIMIT Method
        • Boundary considerations
        • Exceptions possible
      • The PRIOR and NEXT Methods
        • Boundary considerations
        • Exceptions possible
      • The TRIM Method
        • Boundary considerations
        • Exceptions possible
    • Working with Collections
      • Declaring Collection Types
        • Declaring an associative array collection type
        • Declaring a nested table or VARRAY
        • Changing a nested table or VARRAY characteristics
      • Declaring and Initializing Collection Variables
        • Initializing implicitly during direct assignment
        • Initializing implicitly via FETCH
        • VARRAY integration
      • Populating Collections with Data
        • Using the assignment operator
        • What index values can I use?
        • Aggregate assignments
        • Assigning rows from a relational table
        • Advantage of nonsequential population of collection
      • Accessing Data Inside a Collection
      • Using String-Indexed Collections
        • Simplifying algorithmic logic with string indexes
        • Emulating primary keys and unique indexes
        • Performance of string-indexed collections
        • Other examples of string-indexed collections
      • Collections of Complex Datatypes
        • Collections of records
        • Collections of objects and other complex types
      • Multilevel Collections
        • Unnamed multilevel collections: Emulation of multidimensional arrays
        • Exploring the multdim API
        • Extending string_tracker with multilevel collections
        • How deeply can I nest collections?
      • Working with Collections in SQL
        • The CAST pseudofunction
        • The COLLECT pseudofunction
        • The MULTISET pseudofunction
        • The TABLE pseudofunction
        • Sorting contents of collections
    • Nested Table Multiset Operations
      • Testing Equality and Membership of Nested Tables
      • Checking for Membership of an Element in a Nested Table
      • Performing High-Level Set Operations
      • Handling Duplicates in a Nested Table
    • Maintaining Schema-Level Collections
      • Necessary Privileges
      • Collections and the Data Dictionary
  • 13. Miscellaneous Datatypes
    • The BOOLEAN Datatype
    • The RAW Datatype
    • The UROWID and ROWID Datatypes
      • Getting ROWIDs
      • Using ROWIDs
    • The LOB Datatypes
    • Working with LOBs
      • Understanding LOB Locators
      • Empty Versus NULL LOBs
      • Writing into a LOB
      • Reading from a LOB
      • BFILEs Are Different
        • Creating a BFILE locator
        • Accessing BFILEs
        • Using BFILEs to load LOB columns
      • SecureFiles Versus BasicFiles
        • Deduplication
        • Compression
        • Encryption
      • Temporary LOBs
        • Creating a temporary LOB
        • Freeing a temporary LOB
        • Checking to see whether a LOB is temporary
        • Managing temporary LOBs
      • Native LOB Operations
        • SQL semantics
        • SQL semantics may yield temporary LOBs
        • Performance impact of using SQL semantics
      • LOB Conversion Functions
    • Predefined Object Types
      • The XMLType Type
      • The URI Types
      • The Any Types
• IV. SQL in PL/SQL
  • 14. DML and Transaction Management
    • DML in PL/SQL
      • A Quick Introduction to DML
        • The INSERT statement
        • The UPDATE statement
        • The DELETE statement
        • The MERGE statement
      • Cursor Attributes for DML Operations
      • RETURNING Information from DML Statements
      • DML and Exception Handling
      • DML and Records
        • Record-based inserts
        • Record-based updates
        • Using records with the RETURNING clause
        • Restrictions on record-based inserts and updates
    • Transaction Management
      • The COMMIT Statement
      • The ROLLBACK Statement
      • The SAVEPOINT Statement
      • The SET TRANSACTION Statement
      • The LOCK TABLE Statement
    • Autonomous Transactions
      • Defining Autonomous Transactions
      • Rules and Restrictions on Autonomous Transactions
      • Transaction Visibility
      • When to Use Autonomous Transactions
      • Building an Autonomous Logging Mechanism
  • 15. Data Retrieval
    • Cursor Basics
      • Some Data Retrieval Terms
      • Typical Query Operations
      • Introduction to Cursor Attributes
        • The %FOUND attribute
        • The %NOTFOUND attribute
        • The %ROWCOUNT attribute
        • The %ISOPEN attribute
        • The %BULK_ROWCOUNT attribute
        • The %BULK_EXCEPTIONS attribute
      • Referencing PL/SQL Variables in a Cursor
      • Choosing Between Explicit and Implicit Cursors
    • Working with Implicit Cursors
      • Implicit Cursor Examples
      • Error Handling with Implicit Cursors
      • Implicit SQL Cursor Attributes
    • Working with Explicit Cursors
      • Declaring Explicit Cursors
        • Naming your cursor
        • Declaring cursors in packages
      • Opening Explicit Cursors
      • Fetching from Explicit Cursors
        • Examples of explicit cursors
        • Fetching past the last row
      • Column Aliases in Explicit Cursors
      • Closing Explicit Cursors
      • Explicit Cursor Attributes
      • Cursor Parameters
        • Generalizing cursors with parameters
        • Opening cursors with parameters
        • Scope of cursor parameters
        • Cursor parameter modes
        • Default values for parameters
    • SELECT...FOR UPDATE
      • Releasing Locks with COMMIT
      • The WHERE CURRENT OF Clause
    • Cursor Variables and REF CURSORs
      • Why Use Cursor Variables?
      • Similarities to Static Cursors
      • Declaring REF CURSOR Types
      • Declaring Cursor Variables
      • Opening Cursor Variables
      • Fetching from Cursor Variables
        • Handling the ROWTYPE_MISMATCH exception
      • Rules for Cursor Variables
        • Compile-time rowtype matching rules
        • Runtime rowtype matching rules
        • Cursor variable aliases
        • Scope of cursor object
      • Passing Cursor Variables as Arguments
        • Identifying the REF CURSOR type
        • Setting the parameter mode
      • Cursor Variable Restrictions
    • Cursor Expressions
      • Using Cursor Expressions
        • Retrieving a subquery as a column
        • Implementing a streaming function with the CURSOR expression
      • Restrictions on Cursor Expressions
  • 16. Dynamic SQL and Dynamic PL/SQL
    • NDS Statements
      • The EXECUTE IMMEDIATE Statement
      • The OPEN FOR Statement
        • FETCH into variables or records
        • The USING clause in OPEN FOR
      • About the Four Dynamic SQL Methods
        • Method 1
        • Method 2
        • Method 3
        • Method 4
    • Binding Variables
      • Argument Modes
      • Duplicate Placeholders
      • Passing NULL Values
    • Working with Objects and Collections
    • Dynamic PL/SQL
      • Build Dynamic PL/SQL Blocks
      • Replace Repetitive Code with Dynamic Blocks
    • Recommendations for NDS
      • Use Invoker Rights for Shared Programs
      • Anticipate and Handle Dynamic Errors
      • Use Binding Rather than Concatenation
      • Minimize the Dangers of Code Injection
        • Restrict privileges tightly on user schemas
        • Use bind variables whenever possible
        • Check dynamic text for dangerous text
        • Use DBMS_ASSERT to validate inputs
    • When to Use DBMS_SQL
      • Obtain Information About Query Columns
      • Meeting Method 4 Dynamic SQL Requirements
        • The in table procedural interface
        • Steps for intab construction
        • Constructing the SELECT
        • Defining the cursor structure
        • Retrieving and displaying data
      • Minimizing Parsing of Dynamic Cursors
      • Oracle Database 11g New Dynamic SQL Features
        • DBMS_SQL.TO_REFCURSOR function
        • DBMS_SQL.TO_CURSOR function
      • Enhanced Security for DBMS_SQL
        • Unpredictable cursor numbers
        • Denial of access to DBMS_SQL when bad cursor number is used (ORA-24971)
        • Rejection of DBMS_SQL operation when effective user changes (ORA-24970)
• V. PL/SQL Application Construction
  • 17. Procedures, Functions, and Parameters
    • Modular Code
    • Procedures
      • Calling a Procedure
      • The Procedure Header
      • The Procedure Body
      • The END Label
      • The RETURN Statement
    • Functions
      • Structure of a Function
      • The RETURN Datatype
      • The END Label
      • Calling a Function
      • Functions Without Parameters
      • The Function Header
      • The Function Body
      • The RETURN Statement
        • RETURN any valid expression
        • Multiple RETURNs
        • RETURN as the last executable statement
    • Parameters
      • Defining Parameters
      • Actual and Formal Parameters
      • Parameter Modes
        • IN mode
        • OUT mode
        • IN OUT mode
      • Explicit Association of Actual and Formal Parameters in PL/SQL
        • Positional notation
        • Named notation
        • Benefits of named notation
      • The NOCOPY Parameter Mode Qualifier
      • Default Values
    • Local or Nested Modules
      • Benefits of Local Modularization
        • Reducing code volume
        • Improving readability
      • Scope of Local Modules
      • Sprucing Up Your Code with Nested Subprograms
    • Subprogram Overloading
      • Benefits of Overloading
        • Supporting many data combinations
      • Restrictions on Overloading
      • Overloading with Numeric Types
    • Forward Declarations
    • Advanced Topics
      • Calling Your Function from Inside SQL
        • Requirements for calling functions in SQL
        • Restrictions on user-defined functions in SQL
        • Read consistency and user-defined functions
        • Defining PL/SQL subprograms in SQL statements (12.1 and higher)
      • Table Functions
        • Calling a function in a FROM clause
        • Passing table function results with a cursor variable
        • Creating a streaming function
        • Creating a pipelined function
        • Enabling a function for parallel execution
      • Deterministic Functions
      • Implicit Cursor Results (Oracle Database 12c)
    • Go Forth and Modularize!
  • 18. Packages
    • Why Packages?
      • Demonstrating the Power of the Package
      • Some Package-Related Concepts
      • Diagramming Privacy
    • Rules for Building Packages
      • The Package Specification
      • The Package Body
      • Initializing Packages
        • Execute complex initialization logic
        • Cache static session information
        • Avoid side effects when initializing
        • When initialization fails
    • Rules for Calling Packaged Elements
    • Working with Package Data
      • Global Within a Single Oracle Session
      • Global Public Data
      • Packaged Cursors
        • Declaring packaged cursors
        • Working with packaged cursors
      • Serializable Packages
    • When to Use Packages
      • Encapsulate Data Access
      • Avoid Hardcoding Literals
      • Improve Usability of Built-in Features
      • Group Together Logically Related Functionality
      • Cache Static Session Data
    • Packages and Object Types
  • 19. Triggers
    • DML Triggers
      • DML Trigger Concepts
        • DML trigger scripts
        • Transaction participation
      • Creating a DML Trigger
        • The WHEN clause
        • Working with NEW and OLD pseudo-records
        • Determining the DML action within a trigger
      • DML Trigger Example: No Cheating Allowed!
        • Applying the WHEN clause
        • Using pseudo-records to fine-tune trigger execution
      • Multiple Triggers of the Same Type
      • Who Follows Whom
      • Mutating Table Errors
      • Compound Triggers: Putting It All in One Place
        • Just like a package
        • Not just like a package
        • Compound following
    • DDL Triggers
      • Creating a DDL Trigger
      • Available Events
      • Available Attributes
      • Working with Events and Attributes
        • What column did I touch?
        • Lists returned by attribute functions
      • Dropping the Undroppable
      • The INSTEAD OF CREATE Trigger
    • Database Event Triggers
      • Creating a Database Event Trigger
      • The STARTUP Trigger
      • The SHUTDOWN Trigger
      • The LOGON Trigger
      • The LOGOFF Trigger
      • The SERVERERROR Trigger
        • SERVERERROR examples
        • Central error handler
    • INSTEAD OF Triggers
      • Creating an INSTEAD OF Trigger
      • The INSTEAD OF INSERT Trigger
      • The INSTEAD OF UPDATE Trigger
      • The INSTEAD OF DELETE Trigger
      • Populating the Tables
      • INSTEAD OF Triggers on Nested Tables
    • AFTER SUSPEND Triggers
      • Setting Up for the AFTER SUSPEND Trigger
      • Looking at the Actual Trigger
      • The ORA_SPACE_ERROR_INFO Function
      • The DBMS_RESUMABLE Package
      • Trapped Multiple Times
      • To Fix or Not to Fix?
    • Maintaining Triggers
      • Disabling, Enabling, and Dropping Triggers
      • Creating Disabled Triggers
      • Viewing Triggers
      • Checking the Validity of Triggers
  • 20. Managing PL/SQL Code
    • Managing Code in the Database
      • Overview of Data Dictionary Views
      • Display Information About Stored Objects
      • Display and Search Source Code
      • Use Program Size to Determine Pinning Requirements
      • Obtain Properties of Stored Code
      • Analyze and Modify Trigger State Through Views
      • Analyze Argument Information
      • Analyze Identifier Usage (Oracle Database 11gs PL/Scope)
    • Managing Dependencies and Recompiling Code
      • Analyzing Dependencies with Data Dictionary Views
      • Fine-Grained Dependency (Oracle Database 11g)
      • Remote Dependencies
      • Limitations of Oracles Remote Invocation Model
      • Recompiling Invalid Program Units
        • Automatic runtime compilation
        • ALTER...COMPILE recompilation
        • Schema-level recompilation
    • Compile-Time Warnings
      • A Quick Example
      • Enabling Compile-Time Warnings
      • Some Handy Warnings
        • PLW-05000: Mismatch in NOCOPY qualification between specification and body
        • PLW-05001: Previous use of string (at line string) conflicts with this use
        • PLW-05003: Same actual parameter (string and string) at IN and NOCOPY may have side effects
        • PLW-05004: Identifier string is also declared in STANDARD or is a SQL built-in
        • PLW-05005: Function string returns without value at line string
        • PLW-06002: Unreachable code
        • PLW-07203: Parameter string may benefit from use of the NOCOPY compiler hint
        • PLW-07204: Conversion away from column type may result in suboptimal query plan
        • PLW-06009: Procedure string OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
    • Testing PL/SQL Programs
      • Typical, Tawdry Testing Techniques
      • General Advice for Testing PL/SQL Code
      • Automated Testing Options for PL/SQL
    • Tracing PL/SQL Execution
      • DBMS_UTILITY.FORMAT_CALL_STACK
      • UTL_CALL_STACK (Oracle Database 12c)
      • DBMS_APPLICATION_INFO
      • Tracing with opp_trace
      • The DBMS_TRACE Facility
        • Installing DBMS_TRACE
        • DBMS_TRACE programs
        • Control trace file contents
        • Pause and resume the trace process
        • Format of collected data
    • Debugging PL/SQL Programs
      • The Wrong Way to Debug
        • Disorganized debugging
        • Irrational debugging
      • Debugging Tips and Strategies
        • Use a source code debugger
        • Gather data
        • Remain logical at all times
        • Analyze instead of trying
        • Take breaks, and ask for help
        • Change and test one area of code at a time
    • Using Whitelisting to Control Access to Program Units
    • Protecting Stored Code
      • Restrictions on and Limitations of Wrapping
      • Using the Wrap Executable
      • Dynamic Wrapping with DBMS_DDL
      • Guidelines for Working with Wrapped Code
    • Introduction to Edition-Based Redefinition (Oracle Database 11g Release 2)
  • 21. Optimizing PL/SQL Performance
    • Tools to Assist in Optimization
      • Analyzing Memory Usage
      • Identifying Bottlenecks in PL/SQL Code
        • DBMS_PROFILER
        • The hierarchical profiler
      • Calculating Elapsed Time
      • Choosing the Fastest Program
      • Avoiding Infinite Loops
      • Performance-Related Warnings
    • The Optimizing Compiler
      • Insights on How the Optimizer Works
      • Runtime Optimization of Fetch Loops
    • Data Caching Techniques
      • Package-Based Caching
        • When to use package-based caching
        • A simple example of package-based caching
        • Caching table contents in a package
        • Just-in-time caching of table data
      • Deterministic Function Caching
      • THe Function Result Cache (Oracle Database 11g)
        • Enabling the function result cache
        • The RELIES_ON clause (deprecated in 11.2)
        • Function result cache example: A deterministic function
        • Function result cache example: Querying data from a table
        • Function result cache example: Caching a collection
        • When to use the function result cache
        • When not to use the function result cache
        • Useful details of function result cache behavior
        • Managing the function result cache
        • Fine-grained dependencies in 11.2 and higher
        • The virtual private database and function result caching
      • Caching Summary
    • Bulk Processing for Repeated SQL Statement Execution
      • High-Speed Querying with BULK COLLECT
        • Limiting rows retrieved with BULK COLLECT
        • Bulk fetching of multiple columns
        • Using the RETURNING clause with bulk operations
      • High-Speed DML with FORALL
        • Syntax of the FORALL statement
        • FORALL examples
        • Cursor attributes for FORALL
        • ROLLBACK behavior with FORALL
        • Continuing past exceptions with SAVE EXCEPTIONS
        • Driving FORALL with nonsequential arrays
          • INDICES OF example
          • VALUES OF example
    • Improving Performance with Pipelined Table Functions
      • Replacing Row-Based Inserts with Pipelined Function-Based Loads
        • A pipelined function implementation
        • Loading from a pipelined function
        • Tuning pipelined functions with array fetches
        • Exploiting parallel pipelined functions for ultimate performance
        • Enabling parallel pipelined function execution
      • Tuning Merge Operations with Pipelined Functions
        • Row-based PL/SQL merge processing
        • Using pipelined functions for set-based MERGE
      • Asynchronous Data Unloading with Parallel Pipelined Functions
        • A typical data-extract program
        • A parallel-enabled pipelined function unloader
      • Performance Implications of Partitioning and Streaming Clauses in Parallel Pipelined Functions
        • Relative performance of partitioning and streaming combinations
        • Partitioning with skewed data
      • Pipelined Functions and the Cost-Based Optimizer
        • Cardinality heuristics for pipelined table functions
        • Using optimizer dynamic sampling for pipelined functions
        • Providing cardinality statistics to the optimizer
        • Extensible Optimizer and pipelined function cardinality
      • Tuning Complex Data Loads with Pipelined Functions
        • One source, two targets
        • Piping multiple record types from pipelined functions
        • Using object-relational features
        • A multitype pipelined function
        • Querying a multitype pipelined function
        • Loading multiple tables from a multitype pipelined function
        • An alternative multitype method
      • A Final Word on Pipelined Functions
    • Specialized Optimization Techniques
      • Using the NOCOPY Parameter Mode Hint
        • Restrictions on NOCOPY
        • Performance benefits of NOCOPY
        • The downside of NOCOPY
      • Using the Right Datatype
        • Avoid implicit conversions
        • Use PLS_INTEGER for intensive integer computations
        • Use BINARY_FLOAT or BINARY_DOUBLE for floating-point arithmetic
      • Optimizing Function Performance in SQL (12.1 and higher)
    • Stepping Back for the Big Picture on Performance
  • 22. I/O and PL/SQL
    • Displaying Information
      • Enabling DBMS_OUTPUT
      • Write Lines to the Buffer
      • Read the Contents of the Buffer
    • Reading and Writing Files
      • The UTL_FILE_DIR Parameter
        • Setting up directories
        • Specifying file locations when opening files
      • Working with Oracle Directories
      • Open Files
      • Is the File Already Open?
      • Close Files
      • Read from Files
        • GET_LINE exceptions
        • Handy encapsulation for GET_LINE
      • Write to Files
        • Writing formatted text to file
      • Copy Files
      • Delete Files
      • Rename and Move Files
      • Retrieve File Attributes
    • Sending Email
      • Oracle Prerequisites
      • Configuring Network Security
      • Send a Short (32,767 Bytes or Less) Plain-Text Message
      • Include Friendly Names in Email Addresses
      • Send a Plain-Text Message of Arbitrary Length
      • Send a Message with a Short (32,767 Bytes or Less) Attachment
      • Send a Small File (32,767 Bytes or Less) as an Attachment
      • Attach a File of Arbitrary Size
    • Working with Web-Based Data (HTTP)
      • Retrieve a Web Page in Pieces
      • Retrieve a Web Page into a LOB
      • Authenticate Using HTTP Username/Password
      • Retrieve an SSL-Encrypted Web Page (via HTTPS)
      • Submit Data to a Web Page via GET or POST
      • Disable Cookies or Make Cookies Persistent
      • Retrieve Data from an FTP Server
      • Use a Proxy Server
    • Other Types of I/O Available in PL/SQL
      • Database Pipes, Queues, and Alerts
      • TCP Sockets
      • Oracles Built-in Web Server
• VI. Advanced PL/SQL Topics
  • 23. Application Security and PL/SQL
    • Security Overview
    • Encryption
      • Key Length
      • Algorithms
      • Padding and Chaining
      • The DBMS_CRYPTO Package
        • Algorithms
        • Padding and chaining
      • Encrypting Data
      • Encrypting LOBs
      • SecureFiles
      • Decrypting Data
      • Performing Key Generation
      • Performing Key Management
        • A single key for the database
        • A single key for each row
        • A combined approach
      • Cryptographic Hashing
      • Using Message Authentication Codes
      • Using Transparent Data Encryption
      • Transparent Tablespace Encryption
    • Row-Level Security
      • Why Learn About RLS?
      • A Simple RLS Example
      • Static Versus Dynamic Policies
        • Shared static policies
        • Context-sensitive policies
        • Shared context-sensitive policies
      • Using Column-Sensitive RLS
      • RLS Debugging
        • Interpreting errors
        • Performing direct path operations
        • Viewing SQL statements
    • Application Contexts
      • Using Application Contexts
      • Security in Contexts
      • Contexts as Predicates in RLS
      • Identifying Nondatabase Users
    • Fine-Grained Auditing
      • Why Learn About FGA?
      • A Simple FGA Example
      • Access How Many Columns?
      • Checking the Audit Trail
      • Using Bind Variables
      • Using Handler Modules
  • 24. PL/SQL Architecture
    • DIANA
    • How Oracle Executes PL/SQL Code
      • An Example
      • Compiler Limits
    • The Default Packages of PL/SQL
    • Execution Authority Models
      • The Definer Rights Model
        • Advantages of definer rights
        • Disadvantages of definer rights
          • Whered my table go?
          • How do I maintain all that code?
          • Dynamic SQL and definer rights
          • Privilege escalation and SQL injection
      • The Invoker Rights Model
        • Invoker rights syntax
        • Rules and restrictions on invoker rights
      • Combining Rights Models
      • Granting Roles to PL/SQL Program Units (Oracle Database 12c)
      • Who Invoked Me? Functions (Oracle Database 12c)
      • BEQUEATH CURRENT_USER for Views (Oracle Database 12c)
      • Constraining Invoker Rights Privileges (Oracle Database 12c)
    • Conditional Compilation
      • Examples of Conditional Compilation
        • Use application package constants in $IF directive
        • Toggle tracing through conditional compilation flags
      • The Inquiry Directive
        • The DBMS_DB_VERSION package
        • Setting compilation environment parameters
        • Referencing unit name and line number
        • Using the PLSQL_CCFLAGS parameter
      • The $IF Directive
      • The $ERROR Directive
      • Synchronizing Code with Packaged Constants
      • Program-Specific Settings with Inquiry Directives
      • Working with Postprocessed Code
    • PL/SQL and Database Instance Memory
      • The SGA, PGA, and UGA
        • System global area (SGA)
        • Process global area (PGA)
        • User global area (UGA)
      • Cursors, Memory, and More
      • Tips on Reducing Memory Use
        • Statement sharing
        • Bind variables
        • Packaging to improve memory use and performance
        • Large collections in PL/SQL
        • BULK COLLECT...LIMIT operations
        • Preservation of state
      • What to Do If You Run Out of Memory
    • Native Compilation
      • When to Run in Interpreted Mode
      • When to Go Native
      • Native Compilation and Database Release
    • What You Need to Know
  • 25. Globalization and Localization in PL/SQL
    • Overview and Terminology
    • Unicode Primer
      • National Character Set Datatypes
      • Character Encoding
      • Globalization Support Parameters
      • Unicode Functions
        • ASCIISTR
        • COMPOSE
        • DECOMPOSE
        • INSTR/INSTRB/INSTRC/INSTR2/INSTR4
        • LENGTH/LENGTHB/LENGTHC/LENGTH2/LENGTH4
        • SUBSTR/SUBSTRB/SUBSTRC/SUBSTR2/SUBSTR4
        • UNISTR
    • Character Semantics
    • String Sort Order
      • Binary Sort
      • Monolingual Sort
      • Multilingual Sort
    • Multilingual Information Retrieval
      • IR and PL/SQL
    • Date/Time
      • Timestamp Datatypes
      • Date/Time Formatting
    • Currency Conversion
    • Globalization Development Kit for PL/SQL
      • UTL_118N Utility Package
      • UTL_LMS Error-Handling Package
      • GDK Implementation Options
        • Method 1: Locale buttons
        • Method 2: User administration
        • Method 3: Hybrid
  • 26. Object-Oriented Aspects of PL/SQL
    • Introduction to Oracles Object Features
    • Object Types by Example
      • Creating a Base Type
      • Creating a Subtype
      • Methods
      • Invoking Supertype Methods in Oracle Database 11g and Later
      • Storing, Retrieving, and Using Persistent Objects
        • Object identity
        • The VALUE function
        • The TREAT function
      • Evolution and Creation
      • Back to Pointers?
        • Using REFs
        • The UTL_REF package
        • REFs and type hierarchies
        • Dangling REFs
      • Generic Data: The ANY Types
        • Preview: What ANYDATA is not
        • Dealing with ANYDATA
        • Creating a transient type
      • I Can Do It Myself
      • Comparing Objects
        • Attribute-level comparison
        • The MAP method
        • The ORDER method
        • Additional comparison recommendations
    • Object Views
      • A Sample Relational System
      • Object View with a Collection Attribute
      • Object Subview
      • Object View with Inverse Relationship
      • INSTEAD OF Triggers
        • The case against
        • The case for
        • The bigger question
      • Differences Between Object Views and Object Tables
        • OID uniqueness
        • Storeability of physical versus virtual REFs
        • REFs to nonunique OIDs
    • Maintaining Object Types and Object Views
      • Data Dictionary
      • Privileges
        • The EXECUTE privilege
        • The UNDER privilege
        • The DEBUG privilege
        • The DML privileges
    • Concluding Thoughts from a (Mostly) Relational Developer
• 27. Calling Java from PL/SQL
  • Oracle and Java
  • Getting Ready to Use Java in Oracle
    • Installing Java
    • Building and Compiling Your Java Code
    • Setting Permissions for Java Development and Execution
      • Java security for Oracle through 8.1.5
      • Java security for Oracle from 8.1.6
  • A Simple Demonstration
    • Finding the Java Functionality
    • Building a Custom Java Class
    • Compiling and Loading into Oracle
    • Building a PL/SQL Wrapper
    • Deleting Files from PL/SQL
  • Using loadjava
  • Using dropjava
  • Managing Java in the Database
    • The Java Namespace in Oracle
    • Examining Loaded Java Elements
  • Using DBMS_JAVA
    • LONGNAME: Converting Java Long Names
    • GET_, SET_, and RESET_COMPILER_OPTION: Getting and Setting (a Few) Compiler Options
    • SET_OUTPUT: Enabling Output from Java
    • EXPORT_SOURCE, EXPORT_RESOURCE, and EXPORT_CLASS: Exporting Schema Objects
  • Publishing and Using Java in PL/SQL
    • Call Specs
    • Some Rules for Call Specs
    • Mapping Datatypes
    • Calling a Java Method in SQL
    • Exception Handling with Java
    • Extending File I/O Capabilities
      • Polishing up the delete method
      • Obtaining directory contents
    • Other Examples
• 28. External Procedures
  • Introduction to External Procedures
    • Example: Invoking an Operating System Command
    • Architecture of External Procedures
  • Oracle Net Configuration
    • Specifying the Listener Configuration
    • Security Characteristics of the Configuration
  • Setting Up Multithreaded Mode
  • Creating an Oracle Library
  • Writing the Call Specification
    • The Call Spec: Overall Syntax
    • Parameter Mapping: The Example Revisited
    • Parameter Mapping: The Full Story
    • More Syntax: The PARAMETERS Clause
    • PARAMETERS Properties
      • The INDICATOR property
      • The LENGTH property
      • The MAXLEN property
      • The CHARSETID and CHARSETFORM properties
  • Raising an Exception from the Called C Program
  • Nondefault Agents
  • Maintaining External Procedures
    • Dropping Libraries
    • Data Dictionary
    • Rules and Warnings
• A. Regular Expression Metacharacters and Function Parameters
  • Metacharacters
  • Functions and Parameters
    • Regular Expression Functions
      • REGEXP_COUNT (Oracle Database 11g and later)
      • REGEXP_INSTR
      • REGEXP_LIKE
      • REGEXP_REPLACE
      • REGEXP_SUBSTR
    • Regular Expression Parameters
• B. Number Format Models
  • Denoting Monetary Units
• C. Date Format Models
• Index
• About the Authors
• Colophon
• Copyright

pokaż cały spis treści

ukryj recenzje