Expert Oracle PL/SQL

Experienced PL/SQL programmers and Oracle developers will learn to master Oracle's procedural extension to industry-standard SQL. Using this authoritative resource, readers will become experts at developing robust, high-performance enterprise applications with PL/SQL. Detailed examples describe the practical aspect of each feature, and provide ideas to the reader of how they can best exploit the functionality.

Ron Hardman, OCP, is a Consulting Technical Specialist with Oracle Worldwide Technical Support. Prior to joining Oracle Corporation, he was an Oracle database developer and consultant. He is a frequent presenter on the topics of Oracle Text and Ultrasearch at Oracle User Group conferences, and teaches classes on SQL and PL/SQL. He is the coauthor of Oracle Database 10g PL/SQL Programming and has published articles with Oracle Magazine and other online magazines on subjects related to information retrieval.

Michael McLaughlin, D. CS., is the Senior Application Upgrade Manager for Oracle Applications Release Engineering. He is currently working on the upgrade architecture for the next release of Oracle Applications. He has worked with PL/SQL since its first version in Oracle 6, and has authored customer support notes on customizing Oracle Applications with Pro*C and PL/SQL. He has taught computer science and information technology at Regis University and Colorado Technical University, including courses in database development, SQL, PL/SQL, and Java. He is the coauthor of Oracle Database 10g PL/SQL Programming.

ACKNOWLEDGMENTS

xv

INTRODUCTION

xvii

PART I Advanced Concepts, Internals, and Debugging Concepts

1 Introduction to Advanced Concepts, Patterns, and Techniques

3

(22)

What Is an Expert?

4

(1)

10g Release 2 New Features

5

(18)

Obfuscation of PL/SQL Source Code

6

(3)

Conditional Compilation

9

(3)

Asynchronous Commit

12

(1)

Data Mining—Using Predictive Analytics

12

(6)

String Comparisons in PL/SQL

18

(3)

DBMS_OUTPUT.PUT_LINE

21

(2)

Summary

23

(2)

2 PL/SQL Internals

25

(20)

A Database Architecture Primer

26

(7)

User and Server Processes

27

(1)

The Process Global Area

28

(2)

An Instance

30

(3)

The Database

33

(1)

A PL/SQL Architecture Overview

33

(1)

Early vs. Late Binding

34

(1)

The PL/SQL Compiler

34

(7)

Compilation Steps

34

(5)

Interpreted vs. Native Compilation

39

(2)

PL/SQL Virtual Machine

41

(2)

Summary

43

(2)

3 Debugging PL/SQL Applications

45

(28)

Introducing Error-Handling Mechanisms

47

(9)

Differences Between Compile and Runtime Errors

47

(3)

The Standard Syntax for Exception Management

50

(6)

Discussing the Error Stack and Demonstrating Stack Management

56

(13)

Introducing and Demonstrating Error Stack Management

57

(7)

Introducing and Demonstrating 10g New Error Stack Formatting

64

(5)

Error Management in Database Triggers

69

(1)

Summary

69

(4)

PART II Invoker's Rights, Java libraries, and Object Patterns

4 Invoker's-Rights Architecture

73

(30)

Introducing Definer's-Rights and Invoker's-Rights Concepts

75

(13)

Definer's-Rights Concepts

75

(8)

Invoker's-Rights Concepts

83

(5)

Understanding the Architectures

88

(13)

Understanding Definer's-Rights Architecture

89

(8)

Understanding Invoker's-Rights Architecture

97

(4)

Comparing and Contrasting Implementation Strategies

101

(1)

Definer's-Rights

102

(1)

Invoker's-Rights

102

(1)

Summary

102

(1)

5 Extending PL/SQL with Java Libraries

103

(38)

Java Architecture in Oracle

105

(4)

Oracle JDBC Connection Types

109

(2)

The Client-Side Driver, or JDBC Thin Driver

109

(1)

The Oracle Call Interface Driver, or Middle-Tier Thick Driver

110

(1)

The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver

111

(1)

Building Java Class Libraries in Oracle

111

(27)

Building Internal Server Java Functions

114

(5)

Building Internal Server Java Procedures

119

(5)

Building Internal Server Java Objects

124

(9)

Troubleshooting Java Class Library Build, Load, Drop, and Use

133

(5)

Mapping Oracle Types

138

(2)

Summary

140

(1)

6 Implementing PL/SQL Objects

141

(56)

Introducing Objects and Object Types

143

(10)

What Is an Oracle Object?

143

(10)

Instantiating Object Types

153

(3)

Extending PL/SQL Object Types to Compound Object Types

156

(9)

Using PL/SQL Object Types as a Subsystem Facade

165

(29)

Summary

194

(3)

PART III Optimizing PL/SQL Solutions

7 Threading PL/SQL Execution

197

(58)

Introducing Parallel PL/SQL Concepts and Components

199

(12)

Introducing and Demonstrating the DBMS_JOB Package

200

(7)

Introducing, Comparing, and Contrasting DBMS_PIPE and DBMS_ALERT

207

(2)

Introducing and Discussing Threading Concepts for Parallel Programs

209

(2)

Demonstrating How to Build a Parallel PL/SQL Application

211

(43)

Demonstrating a Thread-of-Control

217

(32)

Demonstrating Threads-of-Execution

249

(4)

Demonstrating Parallel Execution

253

(1)

Summary

254

(1)

8 High Performance PL/SQL

255

(32)

PL/SQL Optimization in Oracle 10g

258

(1)

SQL Tuning Review

259

(8)

Tracing and TKPROF

260

(2)

Indexes

262

(2)

The CBO and Statistics

264

(3)

Finding PL/SQL Performance Problems

267

(12)

Compiler Warnings

268

(6)

PL/SQL Profiler

274

(5)

Native Compilation

279

(2)

Performance Tuning Strategy

281

(3)

SQL Steps

281

(2)

PL/SQL Steps

283

(1)

Summary

284

(3)

PART IV Text Management Using PL/SQL

9 Introduction to Information Retrieval

287

(48)

Information Retrieval Overview

288

(6)

IR Models

289

(1)

Text Processing

289

(2)

Queries

291

(3)

Intro to Oracle Text

294

(21)

Oracle Text and IR

295

(1)

Features Overview

296

(1)

Index Creation

296

(14)

Query Processing

310

(5)

Building a Search Application Using PL/SQL

315

(18)

PL/SQL Search and Retrieval

315

(9)

PSP Search

324

(9)

Summary

333

(2)

10 Introduction to Regular Expressions

335

(28)

Foundations of Regular Expressions

336

(1)

History

336

(1)

Oracle and Regular Expressions

337

(12)

Features Overview

338

(1)

Metacharacters

338

(1)

Regex Operator and Functions

339

(10)

PL/SQL and Regular Expressions

349

(11)

Common Uses

349

(6)

Development Tools

355

(5)

Summary

360

(3)

PART V PL/SQL Server Pages and Database Management

11 Leveraging the PL/SQL Toolkit

363

(34)

Criteria for the Selection Between PL/SQL Toolkit and PSPs

365

(1)

Describing and Configuring the Standalone HTTP Server

366

(2)

Describing and Configuring the Standalone Oracle HTTP Server

368

(2)

Describing and Configuring the mod_plsql Cartridge

368

(2)

Configuring the Oracle HTTP Server

370

(3)

Configuring the Oracle 9i HTTP Server

371

(1)

Configuring the Oracle 1OgR1 HTTP Server

372

(1)

Building and Accessing PL/SQL Toolkit Stored Procedures

373

(11)

Developing and Running No Formal Parameter Procedures

373

(3)

Developing Formal Parameter Procedures

376

(7)

Understanding Advantages and Limitations

383

(1)

Building and Accessing PL/SQL Server Pages (PSPs)

384

(12)

Developing and Running No Formal Parameter PSP Procedures

387

(2)

Developing Formal Parameter PSP Procedures

389

(6)

Understanding Advantages and Limitations

395

(1)

Summary

396

(1)

12 Managing the Database

397

(30)

Database Performance

398

(3)

DBMS_STATS: Table and Index Statistics

398

(3)

Moving Data

401

(16)

DBMS_DATAPUMP: Import and Export Data

401

(11)

DBMS_FILE_TRANSFER: Move Binary Files

412

(1)

UTL_COMPRESS: Compress Database Files

413

(4)

Data Security

417

(7)

Etarvqitcrja

417

(3)

The DBMS_CRYPTO Encryption Toolkit

420

(4)

Summary

424

(3)

PART VI Appendixes

A Regular Expression Metacharacters and Functions

427

(10)

POSIX Bracket Expressions

428

(2)

POSIX Metacharacters

430

(3)

Non-POSIX Metacharacters

433

(3)

Functions

436

(1)

B Oracle Text Supplied Packages

437

(18)

Package Creation

438

(1)

The Packages

438

(13)

CTX_ADM

438

(1)

CTX_CLS

439

(1)

CTX_DDL

440

(3)

CTX_DOC

443

(1)

CTX_OUTPUT

444

(1)

CTX_QUERY

445

(2)

CTX_REPORT

447

(1)

CTX_THES

448

(3)

The Most Common Packages

451

(4)

CTX_DDL Examples

451

(1)

CTX_REPORT Examples

452

(3)

C PL/SQL Web Toolkit Packages

455

(10)

The Packages

456

(9)

HTF

456

(1)

HTP

457

(3)

OWA_CACHE

460

(1)

OWA_COOKIE

461

(1)

OWA_CUSTOM

461

(1)

OWA_IMAGE

461

(1)

OWA_OPT_LOCK

462

(1)

OWA_PATTERN

462

(1)

OWA_SEC

462

(1)

OWA_TEXT

463

(1)

OWA_UTIL

463

(1)

WPG_DOCLOAD

464

(1)

D Basic Primer on Java and Java Database Connectivity (JDBC)

465

(12)

Java and JDBC Architecture

466

(1)

Configuring the Oracle Java Environment

466

(2)

Testing a Client-Side or Thin-Driver JDBC Connection

468

(9)

Index

477

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Rent More, Save More! Use code: BBRENTAL

5% off 1 book, 7% off 2 books, 10% off 3+ books

Summary

Author Biography

Table of Contents

Supplemental Materials