®
Oracle Database
11g PL/SQL
Programming
This page intentionally left blank
®
Oracle Database
11g PL/SQL
Programming
Michael McLaughlin
New York Chicago San Francisco
Lisbon London Madrid Mexico City Milan
New Delhi San Juan Seoul Singapore Sydney Toronto
Copyright © 2008 by The McGraw-Hill Companies, Inc. All rights reserved. Manufactured in the United States of America. Except
as permitted under the United States Copyright Act of 1976, no part of this publication may be reproduced or distributed in any form
or by any means, or stored in a database or retrieval system, without the prior written permission of the publisher.
0-07-164356-7
The material in this eBook also appears in the print version of this title: 0-07-149445-6.
All trademarks are trademarks of their respective owners. Rather than put a trademark symbol after every occurrence of a trade-
marked name, we use names in an editorial fashion only, and to the benefit of the trademark owner, with no intention of infringe-
ment of the trademark. Where such designations appear in this book, they have been printed with initial caps.
McGraw-Hill eBooks are available at special quantity discounts to use as premiums and sales promotions, or for use in corporate
training programs. For more information, please contact George Hoare, Special Sales, at
[email protected] or (212)
904-4069.
TERMS OF USE
This is a copyrighted work and The McGraw-Hill Companies, Inc. (“McGraw-Hill”) and its licensors reserve all rights in and to the
work. Use of this work is subject to these terms. Except as permitted under the Copyright Act of 1976 and the right to store and
retrieve one copy of the work, you may not decompile, disassemble, reverse engineer, reproduce, modify, create derivative works
based upon, transmit, distribute, disseminate, sell, publish or sublicense the work or any part of it without McGraw-Hill’s prior con-
sent. You may use the work for your own noncommercial and personal use; any other use of the work is strictly prohibited. Your right
to use the work may be terminated if you fail to comply with these terms.
THE WORK IS PROVIDED “AS IS.” McGRAW-HILL AND ITS LICENSORS MAKE NO GUARANTEES OR WARRANTIES
AS TO THE ACCURACY, ADEQUACY OR COMPLETENESS OF OR RESULTS TO BE OBTAINED FROM USING THE
WORK, INCLUDING ANY INFORMATION THAT CAN BE ACCESSED THROUGH THE WORK VIA HYPERLINK OR OTH-
ERWISE, AND EXPRESSLY DISCLAIM ANY WARRANTY, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO
IMPLIED WARRANTIES OF MERCHANTABILITY OR FITNESS FOR A PARTICULAR PURPOSE. McGraw-Hill and its licen-
sors do not warrant or guarantee that the functions contained in the work will meet your requirements or that its operation will be
uninterrupted or error free. Neither McGraw-Hill nor its licensors shall be liable to you or anyone else for any inaccuracy, error or
omission, regardless of cause, in the work or for any damages resulting therefrom. McGraw-Hill has no responsibility for the con-
tent of any information accessed through the work. Under no circumstances shall McGraw-Hill and/or its licensors be liable for any
indirect, incidental, special, punitive, consequential or similar damages that result from the use of or inability to use the work, even
if any of them has been advised of the possibility of such damages. This limitation of liability shall apply to any claim or cause what-
soever whether such claim or cause arises in contract, tort or otherwise.
DOI: 10.1036/0071494456
Professional
Want to learn more?
We hope you enjoy this
McGraw-Hill eBook! If
you’d like more information about this book,
its author, or related books and websites,
please click here.
To Lisa, my eternal companion, inspiration, wife, and best friend; and to
Sarah, Joseph, Elise, Ian, Ariel, Callie, Nathan, Spencer, and Christianne—
our terrific heaven-sent children. Thank you for your constant support,
patience, and sacrifice that made writing yet another book possible.
About the Author
Michael McLaughlin is a professor at BYU—Idaho in the Computer Information
Technology Department of the Business and Communication College. He is also
the founder of Techtinker.com.
Michael worked at Oracle Corporation for over eight years in consulting,
development, and support. He is the inventor of the ATOMS transaction
architecture (U.S. Patents #7,206,805 and #7,290,056). The patents are
assigned to Oracle Corporation.
Prior to his tenure at Oracle Corporation, Michael worked as an Oracle
developer, systems and business analyst, and DBA beginning with Oracle 6. He
is the author of Oracle Database 10g Express Edition PHP Web Programming.
He is also the co-author of Oracle Database AJAX & PHP Web Application
Development, Oracle Database 10g PL/SQL Programming, and Expert
Oracle PL/SQL.
About the Technical Editor
A. Scott Mikolaitis is an Applications Architect at Oracle Corporation and has
worked at Oracle for over ten years. He works on prototyping and standards
development for the SOA technology in Oracle Fusion.
Scott also enjoys working with web services in Java as well as Jabber for
human and system interaction patterns. He spends his spare time on DIY home
improvement and gas-fueled RC cars.
Contents at a Glance
PART I
PL/SQL Fundamentals
1 Oracle PL/SQL Overview ........................................... 3
2 PL/SQL Basics ....................................................... 25
3 Language Fundamentals ............................................ 45
4 Control Structures .................................................... 91
5 Error Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
PART II
PL/SQL Programming
6 Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
7 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
8 Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
9 Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313
10 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
PART III
PL/SQL Advanced Programming
11 Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
12 Intersession Communication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
13 External Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
14 Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
15 Java Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
16 Web Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
vii
viii Oracle Database 11g PL/SQL Programming
PART IV
Appendixes
A Oracle Database Administration Primer ................................ 571
B Oracle Database SQL Primer ........................................ 599
C PHP Primer ...................................................... 629
D Oracle Database Java Primer ........................................ 685
E Regular Expression Primer .......................................... 729
F Wrapping PL/SQL Code Primer ...................................... 747
G PL/SQL Hierarchical Profiler Primer ................................... 753
H PL/Scope ........................................................ 765
I PL/SQL Reserved Words and Keywords ................................ 769
J PL/SQL Built-in Functions ........................................... 777
Index .......................................................... 821
For more information about this title, click here
Contents
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
PART I
PL/SQL Fundamentals
1 Oracle PL/SQL Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
History and Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Basic Block Structures ................................................. 8
Oracle 10g New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Built-in Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Compile-Time Warnings . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Conditional Compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Number Datatype Behavior . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Optimized PL/SQL Compiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Quoting Alternative . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
Stack Tracing Errors ............................................. 14
Wrapping PL/SQL Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Oracle 11g New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Automatic Subprogram Inlining . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Continue Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Cross-Session PL/SQL Function Result Cache . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Dynamic SQL Enhancements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Mixed Name and Position Notation Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Multiprocess Connection Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
PL/SQL Hierarchical Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
PL/SQL Native Compiler Generates Native Code . . . . . . . . . . . . . . . . . . . . . . . 23
PL/Scope ..................................................... 24
Regular Expression Enhancement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
SIMPLE_INTEGER Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Direct Sequence Calls in SQL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
ix
x Oracle Database 11g PL/SQL Programming
2 PL/SQL Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
Oracle PL/SQL Block Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Variables, Assignments, and Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Conditional Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Iterative Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Stored Functions, Procedures, and Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Stored Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
Packages ..................................................... 40
Transaction Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Single Transaction Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Multiple Transaction Scopes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
Database Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3 Language Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Character and Lexical Units . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Delimiters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Identifiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52
Comments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Block Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
Variable Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60
Scalar Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63
Large Objects (LOBs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Composite Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
System Reference Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Variable Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
4 Control Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91
Conditional Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 92
IF Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
CASE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
Conditional Compilation Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Iterative Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Simple Loop Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
FOR Loop Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
WHILE Loop Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Cursor Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Implicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
Explicit Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Bulk Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122
BULK COLLECT INTO Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 123
FORALL Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129
5 Error Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Exception Types and Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Compilation Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 133
Run-Time Errors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 136
Contents xi
Exception Management Built-in Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
User-Defined Exceptions ............................................... 143
Declaring User-Defined Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
Dynamic User-Defined Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
Exception Stack Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Exception Stack Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
Error Stack Formatting ........................................... 150
Database Trigger Exception Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152
Critical Error Database Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153
Non-Critical Error Database Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
PART II
PL/SQL Programming
6 Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Function and Procedure Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Transaction Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Calling Subroutines ................................................... 172
Positional Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Named Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Mixed Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
Exclusionary Notation ........................................... 173
SQL Call Notation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Creation Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
Pass-by-Value Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186
Pass-by-Reference Functions ...................................... 192
Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
Pass-by-Value Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 195
Pass-by-Reference Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 200
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 206
7 Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
Collection Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Varrays . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Nested Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 225
Associative Arrays .............................................. 238
Collection Set Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
CARDINALITY Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
EMPTY Operator ............................................... 248
MEMBER OF Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 248
MULTISET EXCEPT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
MULTISET INTERSECT Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
MULTISET UNION Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249
SET Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250
SUBMULTISET Operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251
Collection API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
COUNT Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
DELETE Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 255
EXISTS Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 256
xii Oracle Database 11g PL/SQL Programming
EXTEND Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 258
FIRST Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
LAST Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
LIMIT Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260
NEXT Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
PRIOR Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 261
TRIM Method . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263
8 Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 265
Character Large Objects: CLOB and NCLOB Datatypes . . . . . . . . . . . . . . . . . . . . . . . . 266
PL/SQL Reading Files and Writing CLOB or NCLOB Columns . . . . . . . . . . . . . 271
Uploading CLOBs to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274
Binary Large Objects: BLOB Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
PL/SQL Reading Files and Writing BLOB Columns . . . . . . . . . . . . . . . . . . . . . . 277
Uploading BLOBs to the Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
SecureFiles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 280
Binary Files: BFILE Datatype . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Creating and Using Virtual Directories . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 282
Reading Canonical Path Names and Filenames . . . . . . . . . . . . . . . . . . . . . . . . 290
DBMS_LOB Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Package Constants . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 298
Package Exceptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Opening and Closing Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 300
Manipulation Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Introspection Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 306
BFILE Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 309
Temporary LOB Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 310
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 311
9 Packages ........................................................... 313
Package Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315
Forward Referencing ............................................ 315
Overloading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 318
Package Specification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 319
Variables ..................................................... 322
Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
Components: Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 327
Package Body . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 328
Variables ..................................................... 330
Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 331
Components: Functions and Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332
Definer vs. Invoker Rights . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335
Grants and Synonyms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 336
Remote Calls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337
Managing Packages in the Database Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Finding, Validating, and Describing Packages . . . . . . . . . . . . . . . . . . . . . . . . . . 338
Checking Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339
Comparing Validation Methods: Timestamp vs. Signature . . . . . . . . . . . . . . . . . 340
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341
Contents xiii
10 Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 343
Introduction to Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Database Trigger Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Data Definition Language Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348
Event Attribute Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 349
Building DDL Triggers ........................................... 358
Data Manipulation Language Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360
Statement-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 361
Row-Level Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362
Compound Triggers ................................................... 365
Instead-of Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 370
System or Database Event Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374
Trigger Restrictions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
Maximum Trigger Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375
SQL Statements ................................................ 375
LONG and LONG RAW Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
Mutating Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 376
System Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 377
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378
PART III
PL/SQL Advanced Programming
11 Dynamic SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381
Dynamic SQL Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 382
Native Dynamic SQL (NDS) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Dynamic Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383
Dynamic Statements with Inputs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 386
Dynamic Statements with Inputs and Outputs ......................... 388
Dynamic Statements with an Unknown Number of Inputs . . . . . . . . . . . . . . . . 391
DBMS_SQL Package .................................................. 393
Dynamic Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 394
Dynamic Statements with Input Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398
Dynamic Statements with Input and Output Variables . . . . . . . . . . . . . . . . . . . 400
DBMS_SQL Package Definition . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 415
12 Intersession Communication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 417
Introducing Intersession Communication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418
Requiring Permanent or Semipermanent Structures ..................... 418
Not Requiring Permanent or Semipermanent Structures . . . . . . . . . . . . . . . . . . 418
Comparing Intersession Communication Approaches . . . . . . . . . . . . . . . . . . . . 419
The DBMS_PIPE Built-in Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Introducing the DBMS_PIPE Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 420
Defining the DBMS_PIPE Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
Working with the DBMS_PIPE Package .............................. 426
DBMS_ALERT Built-in Package .......................................... 436
Introducing the DBMS_ALERT Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 436
Defining the DBMS_ALERT Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 437
Working with the DBMS_ALERT Package . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 439
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443
xiv Oracle Database 11g PL/SQL Programming
13 External Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 445
Introducing External Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
Working with External Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Defining the extproc Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Defining extproc Oracle Net Services Configuration . . . . . . . . . . . . . . . . . . . . . 449
Defining the Multithreaded External Procedure Agent ................... 456
Working with a C Shared Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Working with a Java Shared Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 465
Troubleshooting the Shared Library . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Configuration of the Listener or Environment . . . . . . . . . . . . . . . . . . . . . . . . . . 470
Configuration of the Shared Library or PL/SQL Library Wrapper . . . . . . . . . . . . 473
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474
14 Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 475
Objects Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 478
Declaring Objects .............................................. 479
Implementing Object Bodies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 481
Getters and Setters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 483
Static Member Methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485
Comparing Objects ............................................. 487
Inheritance and Polymorphism . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 494
Declaring Subclasses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
Implementing Subclasses . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Type Evolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
Implementing Collection Object Bodies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
Declaring Object Type Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500
Implementing Object Type Collections . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504
15 Java Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505
Oracle 11g JVM New Features . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506
Java Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 507
Java Execution Control . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Java Resource Storage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Java Class Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 509
Java Resolvers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
Java Security and Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
Java Threading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
Oracle Java Connection Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
The Client-Side Driver, or JDBC Thin Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . 510
The Oracle Call Interface Driver, or Middle-Tier Thick Driver ............. 511
The Oracle Server-Side Internal Driver, or Server-Tier Thick Driver . . . . . . . . . . 511
Building Java Class Libraries in Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 512
Building Internal Server Java Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 513
Building Internal Server Java Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518
Building Internal Server Java Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 521
Troubleshooting Java Class Libraries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 526
Mapping Oracle Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 530
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 532
Contents xv
16 Web Application Development . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 533
PL/SQL Web Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
Oracle HTTP Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Oracle XML Database Server Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537
Configuring the Standalone Oracle HTTP Server ............................. 539
Describing mod_plsql Cartridge . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540
Configuring the Oracle HTTP Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 541
Configuring the XML DB Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 543
Configuring Static Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546
Configuring Dynamic Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
Configuring Anonymous Authentication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 548
Comparing Web-Enabled PL/SQL Procedures and PSPs . . . . . . . . . . . . . . . . . . . . . . . . 550
Creating Web-Enabled PL/SQL Stored Procedures ............................ 550
Developing Procedures Without Formal Parameters . . . . . . . . . . . . . . . . . . . . . 553
Developing Procedures with Formal Parameters . . . . . . . . . . . . . . . . . . . . . . . . 553
Understanding Advantages and Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
Building and Accessing PL/SQL Server Pages (PSPs) . . . . . . . . . . . . . . . . . . . . . . . . . . . 559
Developing and Running No Formal Parameter PSP Procedures . . . . . . . . . . . . 562
Developing Formal Parameter PSP Procedures . . . . . . . . . . . . . . . . . . . . . . . . . 564
Understanding Advantages and Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . 567
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 568
PART IV
Appendixes
A Oracle Database Administration Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571
Oracle Database Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572
Starting and Stopping the Oracle Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
Unix or Linux Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
Microsoft Windows Operations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
Starting and Stopping the Oracle Listener . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 585
Oracle Roles and Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
Accessing and Using the SQL*Plus Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 590
SQL Command-Line Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592
Bind Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
B Oracle Database SQL Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Oracle SQL*Plus Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601
Data Definition Language (DDL) ......................................... 604
Managing Tables and Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Managing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 609
Managing Stored Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Managing Sequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 612
Managing User-Defined Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616
Data Query Language (DQL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 617
Queries ...................................................... 618
Data Manipulation Language (DML) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
INSERT Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 624
xvi Oracle Database 11g PL/SQL Programming
UPDATE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 626
DELETE Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 627
Data Control Language (DCL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628
C PHP Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
History and Background . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
What Is PHP? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
What Is Zend? ................................................. 631
Developing Web Programming Solutions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
What Goes Where and Why? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 632
What Does Oracle Contribute to PHP? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
Why Is PHP 5 Important? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
How to Use PHP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 633
How to Use PHP and OCI8 to Access the Oracle Database . . . . . . . . . . . . . . . 658
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 684
D Oracle Database Java Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 685
Java and JDBC Architecture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 686
Configuring the Oracle Java Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
Java Programming Language Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
Java Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
Java Assignment Operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692
Java Conditional and Iterative Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
Java Method Definitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695
Java try-catch Blocks ............................................ 696
Testing a Client-Side or Thin-Driver JDBC Connection . . . . . . . . . . . . . . . . . . . . . . . . . 697
Accessing Scalar Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 702
Writing and Accessing Large Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 709
Writing and Accessing a CLOB Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 710
Accessing a BFILE Column . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 718
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 727
E Regular Expression Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 729
Introduction to Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 730
Character Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 730
Collation Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732
Metacharacters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 732
Metasequences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 734
Literals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 735
Oracle 11g Regular Expression Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 736
REGEXP_COUNT Function ....................................... 736
REGEXP_INSTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 737
REGEXP_LIKE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 739
REGEXP_REPLACE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740
REGEXP_SUBSTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740
Using Regular Expressions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 741
REGEXP_COUNT Function ....................................... 742
REGEXP_INSTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 743
REGEXP_LIKE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 744
REGEXP_REPLACE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 744
Contents xvii
REGEXP_SUBSTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 745
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 746
F Wrapping PL/SQL Code Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 747
Limitations of Wrapping PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 748
Limitations of the PL/SQL Wrap Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 748
Limitations of the DBMS_DDL.WRAP Function . . . . . . . . . . . . . . . . . . . . . . . . 749
Using the Wrap Command-Line Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749
Using the DBMS_DDL Command-Line Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749
The WRAP Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 749
The CREATE_WRAPPED Procedure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 751
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 752
G PL/SQL Hierarchical Profiler Primer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 753
Configuring the Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 754
Collecting Profiler Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 756
Understanding Profiler Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 758
Reading the Raw Output ......................................... 759
Defining the PL/SQL Profiler Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 760
Querying the Analyzed Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 762
Using the plshprof Command-Line Utility . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 763
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 764
H PL/Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 765
Configuring PL/Scope Data Collection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 766
Viewing PL/Scope Collected Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 766
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 768
I PL/SQL Reserved Words and Keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 769
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 775
J PL/SQL Built-in Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 777
Character Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778
ASCII Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 778
ASCIISTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
CHR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
CONCAT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 779
INITCAP Function .............................................. 780
INSTR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 780
LENGTH Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 781
LOWER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782
LPAD Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782
LTRIM Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 782
REPLACE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783
RPAD Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 783
RTRIM Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 784
UPPER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 784
Datatype Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
CAST Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 785
CONVERT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 787
TO_CHAR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 788
TO_CLOB Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 790
xviii Oracle Database 11g PL/SQL Programming
TO_DATE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 790
TO_LOB Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 791
TO_NCHAR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 792
TO_NCLOB Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 792
TO_NUMBER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 793
TO_TIMESTAMP Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 794
Error Reporting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 794
SQLCODE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795
SQLERRM Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 795
Miscellaneous ....................................................... 797
BFILENAME Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 797
COALESCE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799
DECODE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800
DUMP Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801
EMPTY_BLOB Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801
EMPTY_CLOB Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 804
GREATEST Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 805
LEAST Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 806
NANVL Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 808
NULLIF Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809
NVL Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 809
SYS_CONTEXT Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 810
USERENV Function ............................................. 813
VSIZE Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 814
Number . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
CEIL Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
FLOOR Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 815
MOD Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 816
POWER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 816
REMAINDER Function . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 818
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 819
Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 821