Preface p. xiii
Programming in PL/SQL
Introduction to PL/SQL p. 3
What Is PL/SQL? p. 3
The Origins of PL/SQL p. 4
So This Is PL/SQL p. 7
About PL/SQL Versions p. 10
Resources for PL/SQL Developers p. 21
Some Words of Advice p. 25
Creating and Running PL/SQL Code p. 29
SQL*Plus p. 30
Performing Essential PL/SQL Tasks p. 44
Oracle's PL/SQL-Based Developer Tools p. 51
Calling PL/SQL from Other Languages p. 53
And What Else? p. 59
Language Fundamentals p. 60
Pl/SQL Block Structure p. 60
The PL/SQL Character Set p. 69
Identifiers p. 70
Literals p. 75
The Semicolon Delimiter p. 77
Comments p. 78
The PRAGMA Keyword p. 79
Labels p. 80
PL/SQL Program Structure
Conditional and Sequential Control p. 85
IF Statements p. 85
CASE Statements p. 93
The GOTO Statement p. 100
The NULL Statement p. 105
Iterative Processing with Loops p. 108
Loop Basics p. 108
The Simple Loop p. 111
The WHILE Loop p. 113
The Numeric FOR Loop p. 114
The Cursor FOR Loop p. 117
Loop Labels p. 120
Tips for Iterative Processing p. 121
Exception Handlers p. 126
How PL/SQL Deals with Errors p. 126
Defining Exceptions p. 129
Raising Exceptions p. 137
Handling Exceptions p. 140
PL/SQL Program Data
Working with Program Data p. 153
Naming Your Program Data p. 153
Overview of PL/SQL Datatypes p. 155
Declaring Program Data p. 160
Programmer-Defined Subtypes p. 167
Conversion Between Datatypes p. 168
Strings p. 176
The Impact of Character Sets p. 176
String Datatypes p. 185
String Issues p. 190
String Functions p. 195
NLS Functions p. 222
Numbers p. 226
Numeric Datatypes p. 226
Number Conversions p. 233
Numeric Functions p. 246
Dates and Timestamps p. 256
Date and Time Datatypes p. 257
Date and Timestamp Conversions p. 266
Date/Time Arithmetic p. 294
Date/Time Functions p. 305
Records and Collections p. 322
Records in PL/SQL p. 322
Collections in PL/SQL p. 336
Declaring Collection Types and Collections p. 341
Where Collections Can Be Used p. 345
Collection Built-Ins (Methods) p. 349
Working with Collections p. 358
Collection Pseudo-Functions p. 384
Maintaining Collections p. 391
Choosing a Collection Type p. 392
Miscellaneous Datatypes p. 395
The BOOLEAN Datatype p. 395
The RAW Datatype p. 397
The UROWID and ROWID Datatypes p. 397
The LOB Datatypes p. 402
Working with LOBs p. 405
Predefined Object Types p. 428
SQL in PL/SQL
DML and Transaction Management p. 439
DML in PL/SQL p. 440
Bulk DML with the FORALL Statement p. 450
Transaction Management p. 459
Autonomous Transactions p. 463
Data Retrieval p. 476
Cursor Basics p. 477
Working with Implicit Cursors p. 486
Working with Explicit Cursors p. 492
Bulk Collect p. 507
Select...For Update p. 512
Cursor Variables p. 517
Cursor Expressions (Oracle9i) p. 530
Dynamic SQL and Dynamic PL/SQL p. 534
NDS Statements p. 535
Multirow Queries with Cursor Variables p. 539
Binding Variables p. 546
Working with Objects and Collections p. 552
Building Applications with NDS p. 554
NDS Utility Package p. 562
Comparing NDS and DBMS_SQL p. 563
PL/SQL Application Construction
Procedures, Functions, and Parameters p. 569
Modular Code p. 570
Procedures p. 571
Functions p. 575
Parameters p. 582
Local Modules p. 594
Module Overloading p. 598
Forward Declarations p. 603
Advanced Topics p. 604
Go Forth and Modularize! p. 619
Packages p. 621
Why Packages? p. 621
Rules for Building Packages p. 628
Rules for Calling Packaged Elements p. 635
Working with Package Data p. 636
When to Use Packages p. 645
Packages and Object Types p. 651
Triggers p. 653
DML Triggers p. 654
DDL Triggers p. 677
Database Event Triggers p. 686
Instead of Triggers p. 693
After Suspend Triggers p. 698
Maintaining Triggers p. 706
Managing PL/SQL Applications p. 710
Managing and Analyzing Code in the Database p. 711
Protecting Stored Code p. 716
Using Native Compilation p. 718
Testing PL/SQL Programs p. 721
Debugging PL/SQL Programs p. 728
Tuning PL/SQL Programs p. 736
Improving Application Performance p. 743
Advanced PL/SQL Topics
PL/SQL's Runtime Architecture p. 755
Looking Under the Hood p. 756
Dependency Management p. 766
PL/SQL's Use of Memory in the Oracle Server p. 776
The Processing of Server-Side PL/SQL p. 791
PL/SQL Code on the Client p. 794
Execution Authority Models p. 803
Hardware for PL/SQL: Bigger = Better? p. 811
What You Need to Know p. 813
Object-Oriented Aspects of PL/SQL p. 815
Introduction to Oracle's Object Features p. 815
An Extended Example p. 817
Object Views p. 856
Maintaining Object Types and Object Views p. 868
Pontifications p. 871
Calling Java from PL/SQL p. 874
Oracle and Java p. 874
Getting Ready to Use Java in Oracle p. 876
A Simple Demonstration p. 879
Using loadjava p. 885
Using dropjava p. 888
Managing Java in the Database p. 889
Using DBMS_JAVA p. 891
Publishing and Using Java in PL/SQL p. 896
External Procedures p. 910
Introduction to External Procedures p. 911
The Oracle Net Configuration p. 914
Creating an Oracle Library p. 917
Writing the Call Specification p. 919
Raising an Exception from the Called C Program p. 929
Nondefault Agents p. 932
Maintaining External Procedures p. 935
Index p. 937