Oracle PL/SQL Programming by Feuerstein, Steven -- Read -- Imperial Library of Trantor

Index

Oracle PL/SQL Programming Dedication 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 O’Reilly PL/SQL Series PL/SQL on the Internet

Some Words of Advice

Don’t Be in Such a Hurry! Don’t 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 Oracle’s 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 function’s 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 of 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 11g’s PL/Scope)

Managing Dependencies and Recompiling Code

Analyzing Dependencies with Data Dictionary Views Fine-Grained Dependency (Oracle Database 11g) Remote Dependencies Limitations of Oracle’s 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 Oracle’s 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

Where’d 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 Oracle’s 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 Colophon Copyright

← Prev
Back
Next →

← Prev
Back
Next →