Oracle Advanced PL/SQL Developer Professional Guide电子书

Oracle Advanced PL/SQL Developer Professional Guide

Table of Contents

Oracle Advanced PL/SQL Developer Professional Guide

Credits

Foreword

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Overview of PL/SQL Programming Concepts

PL/SQL — the procedural aspect

My first PL/SQL program

PL/SQL development environments

SQL Developer

SQL Developer — the history

Creating a connection

SQL Worksheet

Executing a SQL statement

Calling a SQL script from SQL Developer

Creating and executing an anonymous PL/SQL block

Debugging the PL/SQL code

Editing and saving the scripts

SQL*Plus

Executing a SQL statement in SQL*Plus

Executing an anonymous PL/SQL block

Procedures

Executing a procedure

Functions

Function—execution methods

Restrictions on calling functions from SQL expressions

PL/SQL packages

Cursors—an overview

Cursor execution cycle

Cursor attributes

Cursor FOR loop

Exception handling in PL/SQL

System-defined exceptions

User-defined exceptions

The RAISE_APPLICATION_ERROR procedure

Exception propagation

Managing database dependencies

Displaying the direct and indirect dependencies

Dependency metadata

Dependency issues and enhancements

Reviewing Oracle-supplied packages

Summary

Practice exercise

2. Designing PL/SQL Code

Understanding cursor structures

Cursor execution cycle

Cursor design considerations

Cursor design—guidelines

Cursor attributes

Implicit cursors

Explicit cursors

Cursor variables

Ref cursor types—strong and weak

SYS_REFCURSOR

Processing a cursor variable

Cursor variables as arguments

Cursor variables—restrictions

Subtypes

Subtype classification

Oracle's predefined subtypes

User-defined subtypes

Type compatibility with subtypes

Summary

Practice exercise

3. Using Collections

Collections—an overview

Categorization

Selecting an appropriate collection type

Associative arrays

Nested tables

Nested table collection type as the database object

DML operations on nested table columns

Inserting a nested table instance

Selecting a nested table column

Updating the nested table instance

A nested table collection type in PL/SQL

Additional features of a nested table

Varray

Varray in PL/SQL

Varray as a database collection type

DML operations on varray type columns

Inserting a varray collection type instance

Selecting a varray column

Updating the varray instance

Collections—a comparative study

Common characteristics of collection types

Nested table versus associative arrays

Nested table versus varrays

PL/SQL collection methods

EXISTS

COUNT

LIMIT

FIRST and LAST

PRIOR and NEXT

EXTEND

TRIM

DELETE

Manipulating collection elements

Collection initialization

Summary

Practice exercise

4. Using Advanced Interface Methods

Understanding external routines

Architecture of external routines

Oracle Net Configuration

TNSNAMES.ora

LISTENER.ora

Oracle Net Configuration verification

Benefits of external procedures

Executing external C programs from PL/SQL

Executing C program through external procedure—development steps

Executing Java programs from PL/SQL

Calling a Java class method from PL/SQL

Uploading a Java class into the database—development steps

The loadjava utility—an illustration

Creating packages for Java class methods

Summary

Practice exercise

5. Implementing VPD with Fine Grained Access Control

Fine Grained Access Control

Overview

Virtual Private Database—the alias

How FGAC or VPD works?

Salient features of VPD

VPD implementation—outline and components

Application context

Policy function definition and implementation of row-level security

Associating a policy using the DBMS_RLS package

VPD implementation—demonstrations

Assignment 1—implementing VPD using simple security policy

Assignment 2—implementing VPD using an application context

VPD policy metadata

Policy utilities—refresh and drop

Summary

Practice exercise

6. Working with Large Objects

Introduction to the LOB data types

Internal LOB

External LOB

Understanding the LOB data types

LOB value and LOB locators

BLOB or CLOB!

BFILE

Temporary LOBs

Creating LOB data types

Directories

Creating LOB data type columns in a table

Managing LOB data types

Managing internal LOBs

Securing and managing BFILEs

The DBMS_LOB package—overview

Security model

DBMS_LOB constants

DBMS_LOB data types

DBMS_LOB subprograms

Rules and regulations

Internal LOBs

BFILEs

Working with the CLOB, BLOB, and BFILE data types

Initializing LOB data type columns

Inserting data into a LOB column

Populating a LOB data type using an external file

Selecting LOB data

Modifying the LOB data

Delete LOB data

Miscellaneous LOB notes

LOB column states

Locking a row containing LOB

Opening and closing LOBs

Accessing LOBs

LOB restrictions

Migrating from LONG to LOB

Using temporary LOBs

Temporary LOB operations

Managing temporary LOBs

Validating, creating, and freeing a temporary LOB

Summary

Practice exercise

7. Using SecureFile LOBs

Introduction to SecureFiles

SecureFile LOB—an overview

Architectural enhancements in SecureFiles

SecureFile LOB features

Working with SecureFiles

SecureFile metadata

Enabling advanced features in SecureFiles

Deduplication

Compression

Encryption

Migration from BasicFiles to SecureFiles

Online Redefinition method

Summary

Practice exercise

8. Compiling and Tuning to Improve Performance

Native and interpreted compilation techniques

Real native compilation

Selecting the appropriate compilation mode

When to choose interpreted compilation mode?

When to choose native compilation mode?

Setting the compilation mode

Querying the compilation settings

Compiling a program unit for a native or interpreted compilation

Compiling the database for PL/SQL native compilation (NCOMP)

Tuning PL/SQL code

Comparing SQL and PL/SQL

Avoiding implicit data type conversion

Understanding the NOT NULL constraint

Using the PLS_INTEGER data type for arithmetic operations

Using a SIMPLE_INTEGER data type

Modularizing the PL/SQL code

Using bulk binding

Using SAVE_EXCEPTIONS

Rephrasing the conditional control statements

Conditions with an OR logical operator

Conditions with an AND logical operator

Enabling intra unit inlining

PLSQL_OPTIMIZE_LEVEL—the Oracle initialization parameter

Case 1—PLSQL_OPTIMIZE_LEVEL = 0

Case 2—PLSQL_OPTIMIZE_LEVEL = 1

Case 3—PLSQL_OPTIMIZE_LEVEL = 2

Case 4—PLSQL_OPTIMIZE_LEVEL = 3

PRAGMA INLINE

Summary

Practice exercise

9. Caching to Improve Performance

Introduction to result cache

Server-side result cache

SQL query result cache

PL/SQL function result cache

OCI client results cache

Configuring the database for the server result cache

The DBMS_RESULT_CACHE package

Implementing the result cache in SQL

Manual result cache

Automatic result cache

Result cache metadata

Query result cache dependencies

Cache memory statistics

Invalidation of SQL result cache

Displaying the result cache memory report

Read consistency of the SQL result cache

Limitation of SQL result cache

Implementing result cache in PL/SQL

The RESULT_CACHE clause

Cross-session availability of cached results

Invalidation of PL/SQL result cache

Limitations of PL/SQL function result cache

Argument and return type restrictions

Function structural restrictions

Summary

Practice exercise

10. Analyzing PL/SQL Code

Track coding information

[DBA | ALL | USER]_ARGUMENTS

[DBA | ALL | USER]_OBJECTS

[DBA | ALL | USER]_SOURCE

[DBA | ALL | USER]_PROCEDURES

[DBA | ALL | USER]_DEPENDENCIES

Using SQL Developer to find coding information

The DBMS_DESCRIBE package

DBMS_UTILITY.FORMAT_CALL_STACK

Tracking propagating exceptions in PL/SQL code

Determining identifier types and usages

The PL/Scope tool

The PL/Scope identifier collection

The PL/Scope report

Illustration

Applications of the PL/Scope report

The DBMS_METADATA package

DBMS_METADATA data types and subprograms

Parameter requirements

The DBMS_METADATA transformation parameters and filters

Working with DBMS_METADATA—illustrations

Case 1—retrieve the metadata of a single object

Case 2—retrieve the object dependencies on the F_GET_LOC function

Case 3—retrieve system grants on the ORADEV schema

Case 4—retrieve objects of function type in the ORADEV schema

Summary

Practice exercise

11. Profiling and Tracing PL/SQL Code

Tracing the PL/SQL programs

The DBMS_TRACE package

Installing DBMS_TRACE

DBMS_TRACE subprograms

The PLSQL_DEBUG parameter and the DEBUG option

Viewing the PL/SQL trace information

Demonstrating the PL/SQL tracing

Profiling the PL/SQL programs

Oracle hierarchical profiler—the DBMS_HPROF package

View profiler information

Demonstrating the profiling of a PL/SQL program

The plshprof utility

Sample reports

Summary

Practice exercise

12. Safeguarding PL/SQL Code against SQL Injection Attacks

SQL injection—an introduction

SQL injection—an overview

Types of SQL injection attacks

Preventing SQL injection attacks

Immunizing SQL injection attacks

Reducing the attack's surface

Controlling user privileges

Invoker's and definer's rights

Avoiding dynamic SQL

Bind arguments

Sanitizing inputs using DBMS_ASSERT

The DBMS_ASSERT package

Identifier formatting and verification process

DBMS_ASSERT—usage guidelines

DBMS_ASSERT—limitations

Testing the code for SQL injection flaws

Test strategy

Reviewing the code

Static code analysis

Fuzz tools

Generating test cases

Summary

Practice exercise

A. Answers to Practice Questions

Chapter 1, Overview of PL/SQL Programming Concepts

Chapter 2, Designing PL/SQL Code

Chapter 3, Using Collections

Chapter 4, Using Advanced Interface Methods

Chapter 5, Implementing VPD with Fine Grained Access Control

Chapter 6, Working with Large Objects

Chapter 7, Using SecureFile LOBs

Chapter 8, Compiling and Tuning to Improve Performance

Chapter 9, Caching to Improve Performance

Chapter 10, Analyzing PL/SQL Code

Chapter 11, Profiling and Tracing PL/SQL Code

Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks

Index