Oracle PL/SQL for DBAs

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
Released October 2005
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596005870

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

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

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

PL/SQL, Oracle's powerful procedural language, has been the cornerstone of Oracle application development for nearly 15 years. Although primarily a tool for developers, PL/SQL has also become an essential tool for database administration, as DBAs take increasing responsibility for site performance and as the lines between developers and DBAs blur.

Until now, there has not been a book focused squarely on the language topics of special concern to DBAs Oracle PL/SQL for DBAs fills the gap. Covering the latest Oracle version, Oracle Database 10g Release 2 and packed with code and usage examples, it contains:

  • A quick tour of the PL/SQL language, providing enough basic information about language fundamentals to get DBAs up and running
  • Extensive coverage of security topics for DBAs: Encryption (including both traditional methods and Oracle's new Transparent Data Encryption, TDE); Row-Level Security(RLS), Fine-Grained Auditing (FGA); and random value generation
  • Methods for DBAs to improve query and database performance with cursors and table functions
  • Coverage of Oracle scheduling, which allows jobs such as database monitoring andstatistics gathering to be scheduled for regular execution

Using Oracle's built-in packages (DBMS_CRYPTO, DBMS_RLS, DBMS_FGA, DBMS_RANDOM,DBMS_SCHEDULING) as a base, the book describes ways of building on top of these packages to suit particular organizational needs. Authors are Arup Nanda, Oracle Magazine 2003 DBA of the Year, and Steven Feuerstein, the world's foremost PL/SQL expert and coauthor of the classic reference, Oracle PL/SQL Programming.

DBAs who have not yet discovered how helpful PL/SQL can be will find this book a superb introduction to the language and its special database administration features. Even if you have used PL/SQL for years, you'll find the detailed coverage in this book to be an invaluable resource.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. PL/SQL for DBAs
      1. Securing the Database
      2. Optimizing Performance
      3. Fully Leveraging Oracle Utilities and Features
      4. Mentoring New Developers and DBAs
    2. About This Book
    3. Conventions Used in This Book
    4. About PL/SQL Versions
    5. Resources for Developing PL/SQL Expertise
      1. The O’Reilly PL/SQL Series
      2. PL/SQL on the Internet
    6. About the Code
    7. Using Code Examples
    8. Comments and Questions
    9. Safari® Enabled
    10. Acknowledgments
  3. 1. Introduction to PL/SQL
    1. 1.1. What Is PL/SQL?
    2. 1.2. Basic PL/SQL Syntax Elements
      1. 1.2.1. PL/SQL Block Structure
        1. 1.2.1.1. Sections of the block
        2. 1.2.1.2. Anonymous blocks
      2. 1.2.2. The PL/SQL Character Set
      3. 1.2.3. Identifiers
        1. 1.2.3.1. NULLs
      4. 1.2.4. Literals
        1. 1.2.4.1. String literals
        2. 1.2.4.2. Numeric literals
        3. 1.2.4.3. Boolean literals
      5. 1.2.5. The Semicolon Delimiter
      6. 1.2.6. Comments
        1. 1.2.6.1. Single-line comment syntax
        2. 1.2.6.2. Multi-line comment syntax
    3. 1.3. Program Data
      1. 1.3.1. Types of PL/SQL Datatypes
        1. 1.3.1.1. Character data
        2. 1.3.1.2. Numbers
        3. 1.3.1.3. Dates, timestamps, and intervals
        4. 1.3.1.4. Booleans
        5. 1.3.1.5. Binary data
        6. 1.3.1.6. ROWIDs
        7. 1.3.1.7. REF CURSOR datatype
        8. 1.3.1.8. Internet datatypes
        9. 1.3.1.9. “Any” datatypes
      2. 1.3.2. Declaring Program Data
        1. 1.3.2.1. Declaring a variable
        2. 1.3.2.2. Declaring constants
        3. 1.3.2.3. Anchored declarations
    4. 1.4. Control Statements
      1. 1.4.1. IF Statements
      2. 1.4.2. CASE Statements and Expressions
        1. 1.4.2.1. Simple CASE statement
        2. 1.4.2.2. Searched CASE statement
    5. 1.5. Loops in PL/SQL
      1. 1.5.1. Simple Loop
      2. 1.5.2. FOR Loop
      3. 1.5.3. WHILE Loop
    6. 1.6. Exception Handling
      1. 1.6.1. Defining Exceptions
      2. 1.6.2. Raising Exceptions
        1. 1.6.2.1. RAISE statement
        2. 1.6.2.2. Using RAISE_APPLICATION_ERROR
      3. 1.6.3. Handling Exceptions
        1. 1.6.3.1. Built-in error functions
        2. 1.6.3.2. Unhandled exceptions
        3. 1.6.3.3. Propagation of an unhandled exception
    7. 1.7. Records
      1. 1.7.1. Declaring Records
      2. 1.7.2. Working with Records
        1. 1.7.2.1. Record-level operations
        2. 1.7.2.2. Field-level operations
    8. 1.8. Collections
      1. 1.8.1. Types of Collections
      2. 1.8.2. Working with Collections
        1. 1.8.2.1. Using an associative array
        2. 1.8.2.2. Using a nested table
        3. 1.8.2.3. Using a VARRAY
      3. 1.8.3. Collection Methods (Built-ins)
    9. 1.9. Procedures, Functions, and Packages
      1. 1.9.1. Procedures
        1. 1.9.1.1. Structure of a procedure
        2. 1.9.1.2. Calling a procedure
      2. 1.9.2. Functions
        1. 1.9.2.1. Structure of a function
        2. 1.9.2.2. Calling a function
      3. 1.9.3. Parameters
        1. 1.9.3.1. Defining parameters
        2. 1.9.3.2. Actual and formal parameters
        3. 1.9.3.3. Parameter modes
      4. 1.9.4. Packages
        1. 1.9.4.1. Rules for building packages
        2. 1.9.4.2. Rules for calling packaged elements
        3. 1.9.4.3. Package data
    10. 1.10. Querying Data
      1. 1.10.1. Typical Query Operations
      2. 1.10.2. Cursor Attributes
      3. 1.10.3. Implicit Cursors
        1. 1.10.3.1. Error handling with implicit cursors
        2. 1.10.3.2. Implicit SQL cursor attributes
      4. 1.10.4. Explicit Cursors
      5. 1.10.5. BULK COLLECT
        1. 1.10.5.1. Limiting rows retrieved with BULK COLLECT
      6. 1.10.6. Cursor Variables and REF Cursors
        1. 1.10.6.1. Declaring REF CURSOR types
        2. 1.10.6.2. Declaring cursor variables
        3. 1.10.6.3. Opening cursor variables
        4. 1.10.6.4. Fetching from cursor variables
    11. 1.11. Changing Data
      1. 1.11.1. The INSERT Statement
      2. 1.11.2. The UPDATE Statement
      3. 1.11.3. The DELETE Statement
      4. 1.11.4. Cursor Attributes for DML Operations
      5. 1.11.5. DML and Exception Handling
      6. 1.11.6. Bulk DML with the FORALL Statement
        1. 1.11.6.1. Syntax of the FORALL Statement
        2. 1.11.6.2. FORALL Examples
    12. 1.12. Managing Transactions in PL/SQL
      1. 1.12.1. The COMMIT Statement
      2. 1.12.2. The ROLLBACK Statement
      3. 1.12.3. Autonomous Transactions
    13. 1.13. Database Triggers
      1. 1.13.1. DML Triggers
        1. 1.13.1.1. Transaction participation
        2. 1.13.1.2. Creating a DML trigger
        3. 1.13.1.3. The WHEN clause
        4. 1.13.1.4. Working with NEW and OLD pseudo -records
        5. 1.13.1.5. Determining the DML action within a trigger
      2. 1.13.2. DDL Triggers
        1. 1.13.2.1. Creating a DDL Trigger
      3. 1.13.3. Database Event Triggers
        1. 1.13.3.1. Creating a database event trigger
    14. 1.14. Dynamic SQL and Dynamic PL/SQL
      1. 1.14.1. The EXECUTE IMMEDIATE Statement
      2. 1.14.2. The OPEN FOR Statement
      3. 1.14.3. Dynamic PL/SQL
    15. 1.15. Conclusion: From Fundamentals to Applying PL/SQL
  4. 2. Cursors
    1. 2.1. Reusing Cursors
      1. 2.1.1. Hard Parsing and Soft Parsing
      2. 2.1.2. Planning Cursor Use
      3. 2.1.3. How Oracle Decides To Share
      4. 2.1.4. PL/SQL Cursor Reformatting
      5. 2.1.5. Literal Values
      6. 2.1.6. Matching Algorithms
      7. 2.1.7. Text Matching Might Not Be Enough
    2. 2.2. Using Explicit Cursors Versus Implicit Cursors
      1. 2.2.1. What’s the Difference?
      2. 2.2.2. Cursor Attributes
      3. 2.2.3. Cursor Parameters
      4. 2.2.4. Mixing but not Matching
    3. 2.3. Soft-Closing Cursors
      1. 2.3.1. Explicit and Implicit Open Cursors
      2. 2.3.2. Native Dynamic SQL
    4. 2.4. Using Cursors for More Than Queries
      1. 2.4.1. Bulking Up
      2. 2.4.2. REF Cursors
        1. 2.4.2.1. Strong vs. weak REF cursors
        2. 2.4.2.2. REF cursor attributes
        3. 2.4.2.3. Dynamic data access
      3. 2.4.3. Cursor Parameters
      4. 2.4.4. Cursor Expressions
    5. 2.5. Conclusion
  5. 3. Table Functions
    1. 3.1. Why Table Functions?
      1. 3.1.1. A Simple Example
      2. 3.1.2. Calling a Table Function
      3. 3.1.3. Defining the Result Set Structure
    2. 3.2. Cursors, Pipelining, and Nesting
      1. 3.2.1. Cursors
      2. 3.2.2. Pipelined Table Functions
      3. 3.2.3. Nested Table Functions
    3. 3.3. Parallelizing Table Functions
      1. 3.3.1. Taking Advantage of Parallel Query
      2. 3.3.2. Distributing Records
        1. 3.3.2.1. Random partitioning (PARTITION BY ANY)
        2. 3.3.2.2. Range partitioning (PARTITION BY RANGE)
        3. 3.3.2.3. Order streaming (ORDER)
        4. 3.3.2.4. Hash partitioning (PARTITION BY HASH)
        5. 3.3.2.5. Cluster streaming (CLUSTER)
      3. 3.3.3. Which Options Should You Choose?
      4. 3.3.4. What Does Oracle Do?
      5. 3.3.5. Number of PQ Servers to Use
    4. 3.4. Using Table Functions
      1. 3.4.1. The Function Header
      2. 3.4.2. The Basic Loop
      3. 3.4.3. Bulk Fetching the Criteria
      4. 3.4.4. Identifying Potential Repeats
      5. 3.4.5. Finding Genuine Repeats
      6. 3.4.6. The Final Function
      7. 3.4.7. Running the Function
      8. 3.4.8. The Totaling Function
    5. 3.5. Table Function Examples
      1. 3.5.1. Tracing
      2. 3.5.2. Establishing Time Limits
      3. 3.5.3. Enabling Nested Cursors
    6. 3.6. Tips for Working with Table Functions
      1. 3.6.1. The Case Against SYS_REFCURSOR
      2. 3.6.2. REF Cursors and Nesting
      3. 3.6.3. Applying Criteria
      4. 3.6.4. Standardizing Object and Collection Names
      5. 3.6.5. Beware of Unhandled Exceptions
      6. 3.6.6. Passing Objects Instead Of Cursors
      7. 3.6.7. Not Read Committed
    7. 3.7. Conclusion
  6. 4. Data Encryption and Hashing
    1. 4.1. Introduction to Encryption
      1. 4.1.1. Encryption Components
      2. 4.1.2. The Effects of Key Length
      3. 4.1.3. Symmetric Encryption Versus Asymmetric Encryption
      4. 4.1.4. Encryption Algorithms
      5. 4.1.5. Padding and Chaining
    2. 4.2. Encryption in Oracle9i Database
      1. 4.2.1. Encrypting Data
      2. 4.2.2. Specifying an Initialization Vector
      3. 4.2.3. Decrypting Data
      4. 4.2.4. Encrypting RAW Data
      5. 4.2.5. Performing Multi-Pass Encryption
      6. 4.2.6. Putting It Together
      7. 4.2.7. Generating Keys
        1. 4.2.7.1. Using DES3GETKEY
        2. 4.2.7.2. Using the key in encryption
      8. 4.2.8. A Practical Encryption Example
      9. 4.2.9. Storing the Keys
    3. 4.3. Encryption in Oracle Database 10g
      1. 4.3.1. Differences Between DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT
      2. 4.3.2. Generating Keys
      3. 4.3.3. Encrypting Data
        1. 4.3.3.1. Specifying the encryption type
        2. 4.3.3.2. Specifying chaining
        3. 4.3.3.3. Specifying padding
        4. 4.3.3.4. Combining options in the typ parameter
        5. 4.3.3.5. Handling and converting RAW data
        6. 4.3.3.6. Specifying the encryption algorithm
        7. 4.3.3.7. Putting it together
      4. 4.3.4. Decrypting Data
    4. 4.4. Key Management in Oracle Database 10g
      1. 4.4.1. Using a Single Key
      2. 4.4.2. Using a Key for Each Row
      3. 4.4.3. Using a Combined Approach
    5. 4.5. Transparent Data Encryption in Oracle Database 10g Release 2
      1. 4.5.1. Setting Up TDE
      2. 4.5.2. Adding TDE to Existing Tables
      3. 4.5.3. Performing TDE Key and Password Management
      4. 4.5.4. Adding Salt
    6. 4.6. Cryptographic Hashing
      1. 4.6.1. The Case of the Suspicious Sandwich
      2. 4.6.2. MD5 Hashing in Oracle9i Database
      3. 4.6.3. SHA-1 Hashing in Oracle Database 10g
      4. 4.6.4. Other Uses of Hashing
      5. 4.6.5. Message Authentication Code in Oracle Database 10g
    7. 4.7. Building a Practical Encryption System
    8. 4.8. Conclusion
  7. 5. Row-Level Security
    1. 5.1. Introduction to RLS
      1. 5.1.1. Why Learn About RLS?
      2. 5.1.2. A Simple Example
    2. 5.2. Using RLS
      1. 5.2.1. Performing an Update Check
      2. 5.2.2. Static RLS Policies
        1. 5.2.2.1. Problems with static policies
        2. 5.2.2.2. Using a pragma
      3. 5.2.3. Defining a Dynamic Policy
      4. 5.2.4. Improving Performance
      5. 5.2.5. Controlling the Type of Table Access
    3. 5.3. RLS in Oracle Database 10g
      1. 5.3.1. Column-Sensitive RLS
      2. 5.3.2. Other Classes of Dynamism
        1. 5.3.2.1. Shared static policy
        2. 5.3.2.2. Context-sensitive policy
        3. 5.3.2.3. Shared context-sensitive policy
    4. 5.4. Troubleshooting RLS
      1. 5.4.1. Interpreting Errors
      2. 5.4.2. Direct-Path Operations
      3. 5.4.3. Checking the Query Rewrite
        1. 5.4.3.1. Data dictionary view
        2. 5.4.3.2. Event-based tracing
    5. 5.5. RLS Interactions with Other Oracle Features
    6. 5.6. Application Contexts
      1. 5.6.1. A Simple Example
      2. 5.6.2. The Security of Application Contexts
      3. 5.6.3. Contexts as Predicates in RLS
      4. 5.6.4. Identifying Non-Database Users
    7. 5.7. Conclusion
  8. 6. Fine-Grained Auditing
    1. 6.1. Introduction to FGA
      1. 6.1.1. What Is Auditing?
      2. 6.1.2. Why Learn About FGA?
      3. 6.1.3. A Simple Example
      4. 6.1.4. Auditing Differences Between Oracle Database Versions
      5. 6.1.5. What Else Does FGA Capture?
      6. 6.1.6. Using FGA with Flashback Query
    2. 6.2. Customizing FGA
      1. 6.2.1. Specifying Audit Columns
      2. 6.2.2. Specifying Audit Conditions
      3. 6.2.3. Recording Bind Variables
        1. 6.2.3.1. Turning off bind variable capture
      4. 6.2.4. Specifying a Handler Module
        1. 6.2.4.1. Drawbacks with the default FGA approach
        2. 6.2.4.2. Creating a user-defined audit facility
    3. 6.3. Administering FGA
      1. 6.3.1. The DBA_AUDIT_POLICIES View
      2. 6.3.2. Using DBMS_FGA Procedures
        1. 6.3.2.1. The ADD_POLICY procedure
        2. 6.3.2.2. The DROP_POLICY procedure
        3. 6.3.2.3. The DISABLE_POLICY procedure
        4. 6.3.2.4. The ENABLE_POLICY procedure
    4. 6.4. FGA in Oracle Database 10g
      1. 6.4.1. Additional DML Statements
      2. 6.4.2. Additional Data Dictionary Views and Columns
        1. 6.4.2.1. The DBA_FGA_AUDIT_TRAIL view
        2. 6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
      3. 6.4.3. Combination of Columns
    5. 6.5. FGA and Other Oracle Auditing Techniques
      1. 6.5.1. FGA Compared with Triggers
        1. 6.5.1.1. The case for FGA
        2. 6.5.1.2. The case for triggers
      2. 6.5.2. FGA Compared with Regular Auditing
    6. 6.6. The Challenge of Non-Database Users
      1. 6.6.1. Client Identifier
      2. 6.6.2. Application Contexts
    7. 6.7. Troubleshooting FGA
    8. 6.8. Conclusion
  9. 7. Generating Random Values
    1. 7.1. Generating Random Numbers
      1. 7.1.1. Generating Positive Numbers
        1. 7.1.1.1. Controlling the precision
        2. 7.1.1.2. Controlling the range
      2. 7.1.2. Generating Negative Numbers
        1. 7.1.2.1. The RANDOM function
      3. 7.1.3. Seeding Random Number Generation
    2. 7.2. Generating Characters
    3. 7.3. Checking for Randomness
    4. 7.4. Following Statistical Patterns
      1. 7.4.1. Generating Strings
      2. 7.4.2. Generating Random Values with NULLs
      3. 7.4.3. Generating Random Strings of Random Length
      4. 7.4.4. Putting It All Together
    5. 7.5. Conclusion
  10. 8. Scheduling
    1. 8.1. Why Schedule Jobs from Within Oracle?
    2. 8.2. Managing Jobs
      1. 8.2.1. A Simple Example
        1. 8.2.1.1. Running OS executables and anonymous blocks
        2. 8.2.1.2. DBA_SCHEDULER_JOBS view
      2. 8.2.2. Simple Job Management
        1. 8.2.2.1. Enabling and disabling jobs
        2. 8.2.2.2. Stopping running jobs
        3. 8.2.2.3. Running a job
        4. 8.2.2.4. Dropping a job
    3. 8.3. Managing the Calendar and Schedule
      1. 8.3.1. Calendar Strings
        1. 8.3.1.1. Examples of calendar strings
        2. 8.3.1.2. Determining future calendar strings
      2. 8.3.2. Named Schedules
      3. 8.3.3. Owner of the Schedule
    4. 8.4. Managing Named Programs
      1. 8.4.1. Creating a Program
      2. 8.4.2. Running Other Users’ Programs
    5. 8.5. Managing Priorities
      1. 8.5.1. Using the Resource Manager
      2. 8.5.2. Job Class
    6. 8.6. Managing Windows
      1. 8.6.1. Creating a Window
      2. 8.6.2. Prioritizing Windows
      3. 8.6.3. Specifying an End Date for Windows
      4. 8.6.4. Getting Information About Windows
      5. 8.6.5. Dropping Windows
      6. 8.6.6. Disabling and Enabling Windows
      7. 8.6.7. Forcing Windows Open and Closed
      8. 8.6.8. Window Groups
    7. 8.7. Managing Logging
      1. 8.7.1. Job Logs
        1. 8.7.1.1. DBA_SCHEDULER_JOB_LOG
        2. 8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS
        3. 8.7.1.3. Pruning the job log
        4. 8.7.1.4. Log levels
        5. 8.7.1.5. Setting the retention period
      2. 8.7.2. Window Logs
        1. 8.7.2.1. DBA_SCHEDULER_WINDOW_LOG
        2. 8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS
    8. 8.8. Managing Attributes
      1. 8.8.1. Jobs
      2. 8.8.2. Job Classes
      3. 8.8.3. Schedules
      4. 8.8.4. Programs
      5. 8.8.5. Windows
      6. 8.8.6. Window Groups
    9. 8.9. Conclusion
  11. A. Quick Reference
    1. A.1. DBMS_OBFUSCATION_TOOLKIT
    2. A.2. DBMS_CRYPTO
    3. A.3. DBMS_RLS
      1. Reference Section
      2. Reference Section
      3. Reference Section
      4. Reference Section
      5. A.3.1. RLS Data Dictionary Views
    4. A.4. DBMS_FGA
      1. Reference Section
      2. Reference Section
      3. Reference Section
      4. Reference Section
      5. A.4.1. FGA Data Dictionary Views
    5. A.5. DBMS_RANDOM
    6. A.6. DBMS_SCHEDULER
      1. Reference Section
      2. Reference Section
      3. Reference Section
      4. Reference Section
      5. Reference Section
      6. Reference Section
      7. Reference Section
      8. Reference Section
      9. Reference Section
      10. Reference Section
      11. Reference Section
      12. Reference Section
      13. Reference Section
      14. Reference Section
      15. Reference Section
      16. Reference Section
      17. Reference Section
      18. A.6.1. Scheduler Data Dictionary Views
  12. About the Authors
  13. Colophon
  14. Copyright

Product information

  • Title: Oracle PL/SQL for DBAs
  • Author(s): Arup Nanda, Steven Feuerstein
  • Release date: October 2005
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596005870