book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein

October 2005

Intermediate to advanced

454 pages

14h 44m

English

O'Reilly Media, Inc.

Read now

Unlock full access

A Note Regarding Supplemental Files
Preface
PL/SQL for DBAs
Securing the Database
Optimizing Performance
Fully Leveraging Oracle Utilities and Features
Mentoring New Developers and DBAs
About This Book
Conventions Used in This Book
About PL/SQL Versions

Resources for Developing PL/SQL Expertise
The O’Reilly PL/SQL Series
PL/SQL on the Internet
About the Code
Using Code Examples
Comments and Questions
Safari® Enabled
Acknowledgments
1. Introduction to PL/SQL
1.1. What Is PL/SQL?
1.2. Basic PL/SQL Syntax Elements
1.2.1. PL/SQL Block Structure
1.2.1.1. Sections of the block1.2.1.2. Anonymous blocks
1.2.2. The PL/SQL Character Set
1.2.3. Identifiers
1.2.3.1. NULLs
1.2.4. Literals
1.2.4.1. String literals1.2.4.2. Numeric literals1.2.4.3. Boolean literals
1.2.5. The Semicolon Delimiter
1.2.6. Comments
1.2.6.1. Single-line comment syntax1.2.6.2. Multi-line comment syntax
1.3. Program Data
1.3.1. Types of PL/SQL Datatypes
1.3.1.1. Character data1.3.1.2. Numbers1.3.1.3. Dates, timestamps, and intervals1.3.1.4. Booleans1.3.1.5. Binary data1.3.1.6. ROWIDs1.3.1.7. REF CURSOR datatype1.3.1.8. Internet datatypes1.3.1.9. “Any” datatypes
1.3.2. Declaring Program Data
1.3.2.1. Declaring a variable1.3.2.2. Declaring constants1.3.2.3. Anchored declarations
1.4. Control Statements
1.4.1. IF Statements
1.4.2. CASE Statements and Expressions
1.4.2.1. Simple CASE statement1.4.2.2. Searched CASE statement
1.5. Loops in PL/SQL
1.5.1. Simple Loop
1.5.2. FOR Loop
1.5.3. WHILE Loop
1.6. Exception Handling
1.6.1. Defining Exceptions
1.6.2. Raising Exceptions
1.6.2.1. RAISE statement1.6.2.2. Using RAISE_APPLICATION_ERROR
1.6.3. Handling Exceptions
1.6.3.1. Built-in error functions1.6.3.2. Unhandled exceptions1.6.3.3. Propagation of an unhandled exception
1.7. Records
1.7.1. Declaring Records
1.7.2. Working with Records
1.7.2.1. Record-level operations1.7.2.2. Field-level operations
1.8. Collections
1.8.1. Types of Collections
1.8.2. Working with Collections
1.8.2.1. Using an associative array1.8.2.2. Using a nested table1.8.2.3. Using a VARRAY
1.8.3. Collection Methods (Built-ins)
1.9. Procedures, Functions, and Packages
1.9.1. Procedures
1.9.1.1. Structure of a procedure1.9.1.2. Calling a procedure
1.9.2. Functions
1.9.2.1. Structure of a function1.9.2.2. Calling a function
1.9.3. Parameters
1.9.3.1. Defining parameters1.9.3.2. Actual and formal parameters1.9.3.3. Parameter modes
1.9.4. Packages
1.9.4.1. Rules for building packages1.9.4.2. Rules for calling packaged elements1.9.4.3. Package data
1.10. Querying Data
1.10.1. Typical Query Operations
1.10.2. Cursor Attributes
1.10.3. Implicit Cursors
1.10.3.1. Error handling with implicit cursors1.10.3.2. Implicit SQL cursor attributes
1.10.4. Explicit Cursors
1.10.5. BULK COLLECT
1.10.5.1. Limiting rows retrieved with BULK COLLECT
1.10.6. Cursor Variables and REF Cursors
1.10.6.1. Declaring REF CURSOR types1.10.6.2. Declaring cursor variables1.10.6.3. Opening cursor variables1.10.6.4. Fetching from cursor variables
1.11. Changing Data
1.11.1. The INSERT Statement
1.11.2. The UPDATE Statement
1.11.3. The DELETE Statement
1.11.4. Cursor Attributes for DML Operations
1.11.5. DML and Exception Handling
1.11.6. Bulk DML with the FORALL Statement
1.11.6.1. Syntax of the FORALL Statement1.11.6.2. FORALL Examples
1.12. Managing Transactions in PL/SQL
1.12.1. The COMMIT Statement
1.12.2. The ROLLBACK Statement
1.12.3. Autonomous Transactions
1.13. Database Triggers
1.13.1. DML Triggers
1.13.1.1. Transaction participation1.13.1.2. Creating a DML trigger1.13.1.3. The WHEN clause1.13.1.4. Working with NEW and OLD pseudo -records1.13.1.5. Determining the DML action within a trigger
1.13.2. DDL Triggers
1.13.2.1. Creating a DDL Trigger
1.13.3. Database Event Triggers
1.13.3.1. Creating a database event trigger
1.14. Dynamic SQL and Dynamic PL/SQL
1.14.1. The EXECUTE IMMEDIATE Statement
1.14.2. The OPEN FOR Statement
1.14.3. Dynamic PL/SQL
1.15. Conclusion: From Fundamentals to Applying PL/SQL
2. Cursors
2.1. Reusing Cursors
2.1.1. Hard Parsing and Soft Parsing
2.1.2. Planning Cursor Use
2.1.3. How Oracle Decides To Share
2.1.4. PL/SQL Cursor Reformatting
2.1.5. Literal Values
2.1.6. Matching Algorithms
2.1.7. Text Matching Might Not Be Enough
2.2. Using Explicit Cursors Versus Implicit Cursors
2.2.1. What’s the Difference?
2.2.2. Cursor Attributes
2.2.3. Cursor Parameters
2.2.4. Mixing but not Matching
2.3. Soft-Closing Cursors
2.3.1. Explicit and Implicit Open Cursors
2.3.2. Native Dynamic SQL
2.4. Using Cursors for More Than Queries
2.4.1. Bulking Up
2.4.2. REF Cursors
2.4.2.1. Strong vs. weak REF cursors2.4.2.2. REF cursor attributes2.4.2.3. Dynamic data access
2.4.3. Cursor Parameters
2.4.4. Cursor Expressions
2.5. Conclusion
3. Table Functions
3.1. Why Table Functions?
3.1.1. A Simple Example
3.1.2. Calling a Table Function
3.1.3. Defining the Result Set Structure
3.2. Cursors, Pipelining, and Nesting
3.2.1. Cursors
3.2.2. Pipelined Table Functions
3.2.3. Nested Table Functions
3.3. Parallelizing Table Functions
3.3.1. Taking Advantage of Parallel Query
3.3.2. Distributing Records
3.3.2.1. Random partitioning (PARTITION BY ANY)3.3.2.2. Range partitioning (PARTITION BY RANGE)3.3.2.3. Order streaming (ORDER)3.3.2.4. Hash partitioning (PARTITION BY HASH)3.3.2.5. Cluster streaming (CLUSTER)
3.3.3. Which Options Should You Choose?
3.3.4. What Does Oracle Do?
3.3.5. Number of PQ Servers to Use
3.4. Using Table Functions
3.4.1. The Function Header
3.4.2. The Basic Loop
3.4.3. Bulk Fetching the Criteria
3.4.4. Identifying Potential Repeats
3.4.5. Finding Genuine Repeats
3.4.6. The Final Function
3.4.7. Running the Function
3.4.8. The Totaling Function
3.5. Table Function Examples
3.5.1. Tracing
3.5.2. Establishing Time Limits
3.5.3. Enabling Nested Cursors
3.6. Tips for Working with Table Functions
3.6.1. The Case Against SYS_REFCURSOR
3.6.2. REF Cursors and Nesting
3.6.3. Applying Criteria
3.6.4. Standardizing Object and Collection Names
3.6.5. Beware of Unhandled Exceptions
3.6.6. Passing Objects Instead Of Cursors
3.6.7. Not Read Committed
3.7. Conclusion
4. Data Encryption and Hashing
4.1. Introduction to Encryption
4.1.1. Encryption Components
4.1.2. The Effects of Key Length
4.1.3. Symmetric Encryption Versus Asymmetric Encryption
4.1.4. Encryption Algorithms
4.1.5. Padding and Chaining
4.2. Encryption in Oracle9i Database
4.2.1. Encrypting Data
4.2.2. Specifying an Initialization Vector
4.2.3. Decrypting Data
4.2.4. Encrypting RAW Data
4.2.5. Performing Multi-Pass Encryption
4.2.6. Putting It Together
4.2.7. Generating Keys
4.2.7.1. Using DES3GETKEY4.2.7.2. Using the key in encryption
4.2.8. A Practical Encryption Example
4.2.9. Storing the Keys
4.3. Encryption in Oracle Database 10g
4.3.1. Differences Between DBMS_CRYPTO and DBMS_OBFUSCATION_TOOLKIT
4.3.2. Generating Keys
4.3.3. Encrypting Data
4.3.3.1. Specifying the encryption type4.3.3.2. Specifying chaining4.3.3.3. Specifying padding4.3.3.4. Combining options in the typ parameter4.3.3.5. Handling and converting RAW data4.3.3.6. Specifying the encryption algorithm4.3.3.7. Putting it together
4.3.4. Decrypting Data
4.4. Key Management in Oracle Database 10g
4.4.1. Using a Single Key
4.4.2. Using a Key for Each Row
4.4.3. Using a Combined Approach
4.5. Transparent Data Encryption in Oracle Database 10g Release 2
4.5.1. Setting Up TDE
4.5.2. Adding TDE to Existing Tables
4.5.3. Performing TDE Key and Password Management
4.5.4. Adding Salt
4.6. Cryptographic Hashing
4.6.1. The Case of the Suspicious Sandwich
4.6.2. MD5 Hashing in Oracle9i Database
4.6.3. SHA-1 Hashing in Oracle Database 10g
4.6.4. Other Uses of Hashing
4.6.5. Message Authentication Code in Oracle Database 10g
4.7. Building a Practical Encryption System
4.8. Conclusion
5. Row-Level Security
5.1. Introduction to RLS
5.1.1. Why Learn About RLS?
5.1.2. A Simple Example
5.2. Using RLS
5.2.1. Performing an Update Check
5.2.2. Static RLS Policies
5.2.2.1. Problems with static policies5.2.2.2. Using a pragma
5.2.3. Defining a Dynamic Policy
5.2.4. Improving Performance
5.2.5. Controlling the Type of Table Access
5.3. RLS in Oracle Database 10g
5.3.1. Column-Sensitive RLS
5.3.2. Other Classes of Dynamism
5.3.2.1. Shared static policy5.3.2.2. Context-sensitive policy5.3.2.3. Shared context-sensitive policy
5.4. Troubleshooting RLS
5.4.1. Interpreting Errors
5.4.2. Direct-Path Operations
5.4.3. Checking the Query Rewrite
5.4.3.1. Data dictionary view5.4.3.2. Event-based tracing
5.5. RLS Interactions with Other Oracle Features
5.6. Application Contexts
5.6.1. A Simple Example
5.6.2. The Security of Application Contexts
5.6.3. Contexts as Predicates in RLS
5.6.4. Identifying Non-Database Users
5.7. Conclusion
6. Fine-Grained Auditing
6.1. Introduction to FGA
6.1.1. What Is Auditing?
6.1.2. Why Learn About FGA?
6.1.3. A Simple Example
6.1.4. Auditing Differences Between Oracle Database Versions
6.1.5. What Else Does FGA Capture?
6.1.6. Using FGA with Flashback Query
6.2. Customizing FGA
6.2.1. Specifying Audit Columns
6.2.2. Specifying Audit Conditions
6.2.3. Recording Bind Variables
6.2.3.1. Turning off bind variable capture
6.2.4. Specifying a Handler Module
6.2.4.1. Drawbacks with the default FGA approach6.2.4.2. Creating a user-defined audit facility
6.3. Administering FGA
6.3.1. The DBA_AUDIT_POLICIES View
6.3.2. Using DBMS_FGA Procedures
6.3.2.1. The ADD_POLICY procedure6.3.2.2. The DROP_POLICY procedure6.3.2.3. The DISABLE_POLICY procedure6.3.2.4. The ENABLE_POLICY procedure
6.4. FGA in Oracle Database 10g
6.4.1. Additional DML Statements
6.4.2. Additional Data Dictionary Views and Columns
6.4.2.1. The DBA_FGA_AUDIT_TRAIL view6.4.2.2. The FLASHBACK_TRANSACTION_QUERY view
6.4.3. Combination of Columns
6.5. FGA and Other Oracle Auditing Techniques
6.5.1. FGA Compared with Triggers
6.5.1.1. The case for FGA6.5.1.2. The case for triggers
6.5.2. FGA Compared with Regular Auditing
6.6. The Challenge of Non-Database Users
6.6.1. Client Identifier
6.6.2. Application Contexts
6.7. Troubleshooting FGA
6.8. Conclusion
7. Generating Random Values
7.1. Generating Random Numbers
7.1.1. Generating Positive Numbers
7.1.1.1. Controlling the precision7.1.1.2. Controlling the range
7.1.2. Generating Negative Numbers
7.1.2.1. The RANDOM function
7.1.3. Seeding Random Number Generation
7.2. Generating Characters
7.3. Checking for Randomness
7.4. Following Statistical Patterns
7.4.1. Generating Strings
7.4.2. Generating Random Values with NULLs
7.4.3. Generating Random Strings of Random Length
7.4.4. Putting It All Together
7.5. Conclusion
8. Scheduling
8.1. Why Schedule Jobs from Within Oracle?
8.2. Managing Jobs
8.2.1. A Simple Example
8.2.1.1. Running OS executables and anonymous blocks8.2.1.2. DBA_SCHEDULER_JOBS view
8.2.2. Simple Job Management
8.2.2.1. Enabling and disabling jobs8.2.2.2. Stopping running jobs8.2.2.3. Running a job8.2.2.4. Dropping a job
8.3. Managing the Calendar and Schedule
8.3.1. Calendar Strings
8.3.1.1. Examples of calendar strings8.3.1.2. Determining future calendar strings
8.3.2. Named Schedules
8.3.3. Owner of the Schedule
8.4. Managing Named Programs
8.4.1. Creating a Program
8.4.2. Running Other Users’ Programs
8.5. Managing Priorities
8.5.1. Using the Resource Manager
8.5.2. Job Class
8.6. Managing Windows
8.6.1. Creating a Window
8.6.2. Prioritizing Windows
8.6.3. Specifying an End Date for Windows
8.6.4. Getting Information About Windows
8.6.5. Dropping Windows
8.6.6. Disabling and Enabling Windows
8.6.7. Forcing Windows Open and Closed
8.6.8. Window Groups
8.7. Managing Logging
8.7.1. Job Logs
8.7.1.1. DBA_SCHEDULER_JOB_LOG8.7.1.2. DBA_SCHEDULER_JOB_RUN_DETAILS8.7.1.3. Pruning the job log8.7.1.4. Log levels8.7.1.5. Setting the retention period
8.7.2. Window Logs
8.7.2.1. DBA_SCHEDULER_WINDOW_LOG8.7.2.2. DBA_SCHEDULER_WINDOW_DETAILS
8.8. Managing Attributes
8.8.1. Jobs
8.8.2. Job Classes
8.8.3. Schedules
8.8.4. Programs
8.8.5. Windows
8.8.6. Window Groups
8.9. Conclusion
A. Quick Reference
A.1. DBMS_OBFUSCATION_TOOLKIT
A.2. DBMS_CRYPTO
A.3. DBMS_RLS
Reference Section
Reference Section
Reference Section
Reference Section
A.3.1. RLS Data Dictionary Views
A.4. DBMS_FGA
Reference Section
Reference Section
Reference Section
Reference Section
A.4.1. FGA Data Dictionary Views
A.5. DBMS_RANDOM
A.6. DBMS_SCHEDULER
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
Reference Section
A.6.1. Scheduler Data Dictionary Views
About the Authors
Colophon
Copyright

Content preview from Oracle PL/SQL for DBAs

Distributing Records

One really cool thing about parallel table functions is that you can tell Oracle how to distribute records among the parallel function instances. Two different aspects of distribution can be specified. The first is partitioning — how Oracle should decide which records to send to which function instance. The second is streaming— how each function instance should order the records partitioned to it. The options for each are shown below.

Partitioning: Records can be partitioned based on ranges of one or more columns, hash values based on one or more columns, or simply as Oracle sees fit. The PARTITION BY clause specifies the specific partitioning option for the table function, as shown in the following examples.
Streaming: Records can be ordered or clustered by specific columns. The ORDER or CLUSTER keyword specifies the specific streaming option for the table function, as shown in the following examples.

I’ll explain each of these components and how they interact while working through development of my account transaction summary function.

Random partitioning (PARTITION BY ANY)

First, I’ll set up the function and tell Oracle to run it in parallel, but simply partition the records as it sees fit. The PARALLEL_ENABLE clause in the function header tells Oracle that this function has been coded with parallelism in mind and that it would really like to take advantage of this feature. The PARTITION BY ANY parameter supplied in this example states that the records returned ...

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

Publisher Resources

ISBN: 0596005873Errata Page

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein

Distributing Records

Random partitioning (PARTITION BY ANY)

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.

More than 5,000 organizations count on O’Reilly

Julian F.

Addison B.

Amir M.

Mark W.

You might also like

Oracle Database 12c PL/SQL Programming

Oracle PL/SQL Best Practices

Resilient Oracle PL/SQL

Expert PL/SQL Practices for Oracle Developers and DBAs

Publisher Resources