Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
About the OakTable Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
CHAPTER 1 Performance Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
CHAPTER 2 Oracle Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
CHAPTER 3 Introduction to JDBC. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
CHAPTER 4 Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
CHAPTER 5 Statement and PreparedStatement. . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
CHAPTER 6 CallableStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
CHAPTER 7 Result Sets Explored. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
CHAPTER 8 Oracle Objects: An Objective Analysis . . . . . . . . . . . . . . . . . . . . . . . . . 277
CHAPTER 9 Using Weakly Typed Struct Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . 323
CHAPTER 10 Using Strongly Typed Interfaces with JPublisher . . . . . . . . . . . . . . . 345
CHAPTER 11 Using Oracle Collections and References . . . . . . . . . . . . . . . . . . . . . . 387
CHAPTER 12 Using LOBs and BFILEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
CHAPTER 13 Statement Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
CHAPTER 14 Connection Pooling and Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
CHAPTER 15 Security-Related Issues. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575
CHAPTER 16 Locking-Related Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
CHAPTER 17 Selected PL/SQL Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
APPENDIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693
v
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii
About the OakTable Network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix
¦CHAPTER 1 Performance Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Setting Up the SQL*Plus Environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Setting Up autotrace in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Effect of Setting echo on in SQL*Plus . . . . . . . . . . . . . . . . . . . . . . . . . . 6
Setting Up the SCOTT/TIGER Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7
Setting Up the BENCHMARK/BENCHMARK Schema . . . . . . . . . . . . . . . . . . . 7
Performance Tools. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
timed_statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
sql_trace and tkprof . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
runstats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
JDBC Wrapper for runstats . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Timing Java Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
A Utility to Pause in a Java Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
¦CHAPTER 2 Oracle Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
Selected Oracle Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Database vs. Instance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Tablespaces . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
Data Blocks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
What Makes Oracle Different? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Oracle’s Locking Mechanism. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Multiversion Read Consistency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Writing Effective Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Use Bind Variables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
Minimize I/O, Undo, and Redo . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Understand SQL and PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Get Your Database Schema Design Right . . . . . . . . . . . . . . . . . . . . . . 70 vii
General Guidelines for Writing High-Performance Code. . . . . . . . . . . . . . . 73
Know the Features Offered by Oracle. . . . . . . . . . . . . . . . . . . . . . . . . . 73
Design to Optimize the Most Important Business Functions First. . 74
Incorporate Performance from Day One . . . . . . . . . . . . . . . . . . . . . . . 75
Instrument Your Code Extensively. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
Test for Performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Elapsed Time Is Less Than Half the Story . . . . . . . . . . . . . . . . . . . . . . 76
Beware Universal Truths. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
¦CHAPTER 3 Introduction to JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
What Is JDBC? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
JDBC Driver Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
Oracle JDBC Drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
JDBC Thin Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
JDBC OCI Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
JDBC Server-Side Thin Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
JDBC Server-Side Internal Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Choosing the Right Driver . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82
Software Requirements and Setup Instructions. . . . . . . . . . . . . . . . . . . . . . 83
JDBC Thin Driver on UNIX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
JDBC Thin Driver on Windows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
JDBC OCI Driver on UNIX and Windows (10g Only) . . . . . . . . . . . . . . 85
JDBC OCI Driver on UNIX and Windows (9i) . . . . . . . . . . . . . . . . . . . . 86
Overview of JDBC API. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Connecting to a Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89
Using DriverManager . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90
Using a Data Source . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 96
A Complete JDBC Program . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
Potential Errors When Executing Your First Program. . . . . . . . . . . . . . . . . 109
Exception Handling in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110
Introducing JDBCUtil. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 111
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
¦CHAPTER 4 Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
What Is a Transaction? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Committing a Transaction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
Rolling Back a Transaction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Transaction Isolation Levels . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
Transaction Isolation Levels in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . 119
viii ¦CONTENTS
Sizing Your Transaction Resources According to Your Business Needs . 124
The Autocommit Feature and Turning It Off . . . . . . . . . . . . . . . . . . . . . . . . 129
Transaction Savepoints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130
Using Savepoints in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
An Example of Using Savepoints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138
¦CHAPTER 5 Statement and PreparedStatement . . . . . . . . . . . . . . . . . . . . . . . 139
Overview of How Oracle Processes SQL Statements (DML) . . . . . . . . . . 140
JDBC API for Statements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
The Statement Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
The PreparedStatement Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150
Creating a PreparedStatement Object . . . . . . . . . . . . . . . . . . . . . . . . 150
Using Bind Variables with PreparedStatements . . . . . . . . . . . . . . . . 151
Example of Using PreparedStatement to Query Data . . . . . . . . . . . 152
Example of Using PreparedStatement to Modify Data . . . . . . . . . . 157
Using Bind Variables Makes Your Program More Secure . . . . . . . . 160
Nuances of Bind Variable Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
Update Batching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
Types of Statements Supported
by Oracle’s Batching Implementation . . . . . . . . . . . . . . . . . . . . . . 168
Standard Update Batching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
Oracle Update Batching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 172
Mixing Interdependent Statements in a Batch . . . . . . . . . . . . . . . . . 175
Oracle Update Batching vs. Standard Update Batching . . . . . . . . . 179
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
¦CHAPTER 6 CallableStatement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 187
A Brief Introduction to Stored Procedures and PL/SQL. . . . . . . . . . . . . . . 187
Invoking Stored Procedures from JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . 189
Formulating the CallableStatement String. . . . . . . . . . . . . . . . . . . . . 190
Creating a CallableStatement Object . . . . . . . . . . . . . . . . . . . . . . . . . 191
Binding Input (in or in out) Parameters . . . . . . . . . . . . . . . . . . . . . . . 191
Registering Output (out or in out) Parameters . . . . . . . . . . . . . . . . . 193
Executing CallableStatement and Retrieving Results . . . . . . . . . . . 195
Putting It All Together in a Working Example. . . . . . . . . . . . . . . . . . . 195
CallableStatement Common Errors and Resolutions. . . . . . . . . . . . . . . . . 202
Where Should Your SQL Statements Reside, in Java or PL/SQL? . . . . . . 204
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
¦CONTENTS ix
¦CHAPTER 7 Result Sets Explored. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209
Handling Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
Prefetching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Setting and Getting Fetch Size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
Prefetching Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Performance Impact of Fetch Size . . . . . . . . . . . . . . . . . . . . . . . . . . . 226
Scrollability, Positioning, and Sensitivity . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Updatability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 231
Creating Different Categories of Result Sets . . . . . . . . . . . . . . . . . . . . . . . 232
Result Set Limitations and Downgrade Rules . . . . . . . . . . . . . . . . . . . . . . 233
Result Set Limitations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Result Set Downgrade Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 234
Positioning in a Scrollable Result Set. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 236
Methods to Move Within a Result Set. . . . . . . . . . . . . . . . . . . . . . . . . 236
Methods to Check Current Position Within a Result Set . . . . . . . . . 237
Example of Positioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 238
Updating, Inserting, and Deleting Result Set Rows . . . . . . . . . . . . . . . . . . 241
Deleting a Row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Updating a Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Inserting a Row . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Example of Updatability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 242
Lost Updates. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Refetching Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Example of Refetching Rows. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246
Refetching and Scroll-Sensitive Result Sets . . . . . . . . . . . . . . . . . . . 249
Database Changes Visible to a Result Set. . . . . . . . . . . . . . . . . . . . . . . . . . 251
A Result Set’s Ability to Detect Database Changes . . . . . . . . . . . . . . . . . . 252
Paginating Through a Result Set . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 252
Dynamically Building a Query with an
Unknown Number of Bind Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . 259
PreparedStatement-Based Solution . . . . . . . . . . . . . . . . . . . . . . . . . . 260
CallableStatement-Based Solution . . . . . . . . . . . . . . . . . . . . . . . . . . . 264
ResultSetMetaData . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 269
DatabaseMetaData . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275
x ¦CONTENTS
¦CHAPTER 8 Oracle Objects: An Objective Analysis . . . . . . . . . . . . . . . . . . . . 277
Introducing Oracle Objects and Collections . . . . . . . . . . . . . . . . . . . . . . . . 278
Object Type (Equivalent of a Java Class) . . . . . . . . . . . . . . . . . . . . . . 278
Declaring and Using Object Variables in PL/SQL . . . . . . . . . . . . . . . 282
Collections (Nested Tables and Varrays) . . . . . . . . . . . . . . . . . . . . . . 283
Using Objects As Programming Constructs . . . . . . . . . . . . . . . . . . . . . . . . 287
Using the Object-Oriented Features of Oracle Objects . . . . . . . . . . 287
Using Objects to Store Data (Not Recommended). . . . . . . . . . . . . . . . . . . 289
Using Varrays to Store Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290
Using Nested Tables to Store Data . . . . . . . . . . . . . . . . . . . . . . . . . . . 293
Storage Considerations for Nested Table Columns . . . . . . . . . . . . . 298
Why It Is Not Recommended to Use Objects to Store Data . . . . . . 300
Using Object Views on Top of Relational Tables . . . . . . . . . . . . . . . . . . . . . 301
Defining an Object Type . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 302
Defining an Object View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 303
Performing Queries on the Object View . . . . . . . . . . . . . . . . . . . . . . . 303
Defining instead of Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 304
Object Views vs. Nested Tables vs. Relational Tables . . . . . . . . . . . . . . . . 313
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321
¦CHAPTER 9 Using Weakly Typed Struct Objects. . . . . . . . . . . . . . . . . . . . . . . 323
Weakly Typed Struct Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 324
The Struct Interface. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 325
The oracle.sql.STRUCT Class. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 326
Performing DML Operations Using Struct Objects. . . . . . . . . . . . . . . . . . . 327
Creating the Example Database Schema . . . . . . . . . . . . . . . . . . . . . 327
Using the Struct Interface to Select Oracle Objects. . . . . . . . . . . . . 330
Using the oracle.sql.STRUCT Class to Insert Oracle Objects . . . . . 335
Using the oracle.sql.STRUCT Class to Update Oracle Objects . . . . 338
Deleting Objects. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 344
¦CHAPTER 10 Using Strongly Typed Interfaces with JPublisher. . . . . . . . 345
Strongly Typed Interfaces. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 345
An Introduction to JPublisher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 346
Setting Up the JPublisher Environment . . . . . . . . . . . . . . . . . . . . . . . 347
JPublisher Commonly Used Options. . . . . . . . . . . . . . . . . . . . . . . . . . 347
JPublisher Property File Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
JPublisher Input File Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 350
¦CONTENTS xi
Using the SQLData Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352
Generating Custom Classes That Implement SQLData . . . . . . . . . . 352
Manually Adding a Method to a Generated Class. . . . . . . . . . . . . . . 358
Using a Type Map to Map Object Types to Java Classes . . . . . . . . 360
Performing DML Using Custom SQLData Classes . . . . . . . . . . . . . . 361
Generating Wrapper Method(s) Automatically . . . . . . . . . . . . . . . . . 366
Using the ORAData and ORADataFactory Interfaces . . . . . . . . . . . . . . . . . 371
Generating Custom Classes that Implement the
ORAData and ORADataFactory Interfaces . . . . . . . . . . . . . . . . . . 372
SQLData vs. ORAData and ORADataFactory. . . . . . . . . . . . . . . . . . . . . . . . 384
A Note on Separating Domain Objects
from the Persistence Mechanism. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385
¦CHAPTER 11 Using Oracle Collections and References . . . . . . . . . . . . . . . . 387
Weakly Typed Collection Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
The java.sql.Array Interface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388
The oracle.sql.ARRAY Class . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389
Strongly Typed Collection Classes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 390
Materializing Collections of Built-in Types As Weakly Typed Objects . . . 390
Creating the Schema for Collections of Built-in Types . . . . . . . . . . 390
Manifesting Collections of Built-in Types As ARRAY Objects . . . . . 392
Materializing Collections of Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . 407
Creating a Schema for a Collection of Object Types . . . . . . . . . . . . 407
Accessing a Collection of Oracle Objects As STRUCT Objects. . . . 409
Accessing a Collection of Oracle Objects Using Custom Classes . . 411
ARRAY Class Performance Extensions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 422
ARRAY Automatic Element Buffering . . . . . . . . . . . . . . . . . . . . . . . . . 422
ARRAY Automatic Indexing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 423
Benchmarking Auto-buffering and Auto-indexing . . . . . . . . . . . . . . 423
References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429
A Brief Introduction to References . . . . . . . . . . . . . . . . . . . . . . . . . . . 430
Using References in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433
Using Strongly Typed Custom Classes to Query References . . . . . 439
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 446
xii ¦CONTENTS
¦CHAPTER 12 Using LOBs and BFILEs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
What Are LOBs?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
LOBs vs. LONGs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447
Types of LOBs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448
LOB Locator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 449
Internal LOBs (CLOBs, NCLOBs, and BLOBs) . . . . . . . . . . . . . . . . . . . . . . . 449
Using Internal LOBs in SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 450
Using Internal LOBs in PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 451
External LOBs (BFILEs) in SQL and PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . 457
Using LOBs in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459
Reading from and Writing to a CLOB . . . . . . . . . . . . . . . . . . . . . . . . . 460
Reading from and Writing to a BLOB . . . . . . . . . . . . . . . . . . . . . . . . . 468
Reading BFILE Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 473
Alternatives to BFILEs for File Operations . . . . . . . . . . . . . . . . . . . . . . . . . 480
Using the UTL_FILE PL/SQL Package to
Read from and Write to a Text File. . . . . . . . . . . . . . . . . . . . . . . . . 480
Using the UTL_FILE PL/SQL Package to
Read from and Write to a Binary File . . . . . . . . . . . . . . . . . . . . . . 483
Using External Tables to Read Text Files . . . . . . . . . . . . . . . . . . . . . . 485
BFILE vs. UTL_FILE vs. External Table . . . . . . . . . . . . . . . . . . . . . . . . 486
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 493
¦CHAPTER 13 Statement Caching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
Cursors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495
Cursors in PL/SQL (Explicit and Implicit) . . . . . . . . . . . . . . . . . . . . . . 495
Ref Cursors (or Cursor Variables) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 497
Prepare Once, Bind and Execute Many Times . . . . . . . . . . . . . . . . . . . . . . 500
Session Cursor Cache and “Softer” Soft Parses . . . . . . . . . . . . . . . . . . . . 504
Session Cursor Cache in Action. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504
PL/SQL Cursor Cache . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 508
Statement Caching in JDBC . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
Statement Caching Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . 511
Implicit and Explicit Statement Caching . . . . . . . . . . . . . . . . . . . . . . 512
Session Cursor Cache vs. PL/SQL Cursor Cache
vs. JDBC Statement Caching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 528
¦CONTENTS xiii
¦CHAPTER 14 Connection Pooling and Caching . . . . . . . . . . . . . . . . . . . . . . . . . 529
Connections and Sessions in Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 529
Client/Server Applications and Connections. . . . . . . . . . . . . . . . . . . . . . . . 531
Web Applications and Connection-Related Challenges . . . . . . . . . . . . . . 532
Cost of Opening and Closing a Connection. . . . . . . . . . . . . . . . . . . . . . . . . 532
What Is Connection Pooling?. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534
What Is Connection Caching? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 535
Oracle9i Connection Pooling Framework . . . . . . . . . . . . . . . . . . . . . . . . . . 536
Related JDBC Standard and Oracle Interfaces . . . . . . . . . . . . . . . . . 536
Example of Creating a Pooled Connection
and Obtaining a Logical Connection . . . . . . . . . . . . . . . . . . . . . . . 538
Oracle9i Connection Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 540
Oracle’s Implementation of Connection Cache. . . . . . . . . . . . . . . . . 541
Steps in Using OracleConnectionCacheImpl . . . . . . . . . . . . . . . . . . . 542
Example of Using OracleConnectionCacheImpl . . . . . . . . . . . . . . . . 544
Oracle 10g Implicit Connection Caching . . . . . . . . . . . . . . . . . . . . . . . . . . . 547
Using the Oracle 10g Implicit Connection Cache . . . . . . . . . . . . . . . 547
An Example of Using Implicit Connection Caching. . . . . . . . . . . . . . 551
The OracleConnectionCacheManager Class . . . . . . . . . . . . . . . . . . . 554
Using Connection Attributes and Attribute Weights (10g Only) . . . 558
OCI Connection Pooling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 560
Creating an OCI Connection Pool . . . . . . . . . . . . . . . . . . . . . . . . . . . . 561
Configuring the OCI Connection Pool Properties . . . . . . . . . . . . . . . 561
Retrieving a Connection from the OCI Connection Pool . . . . . . . . . 563
Analyzing Connections and Sessions When
OCI Connection Pooling Is in Use. . . . . . . . . . . . . . . . . . . . . . . . . . 563
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 573
¦CHAPTER 15 Security-Related Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 575
The Principle of Least Privilege and Defense in Depth . . . . . . . . . . . . . . . 575
Mapping an End User to a Database User . . . . . . . . . . . . . . . . . . . . . . . . . 576
Separating the End User Database Schema, Data Schema,
and Data Access Layer Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578
An Example Application . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 579
Creating the Database Schemas. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 580
Creating the Application Data Tables . . . . . . . . . . . . . . . . . . . . . . . . . 581
Granting DML Privileges to db_data_access_layer. . . . . . . . . . . . . 582
Creating the PL/SQL Packages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582
Creating Database Roles and Schema for End Users . . . . . . . . . . . 585
xiv ¦CONTENTS
Authenticating an Application End User to the Database . . . . . . . . . . . . . 587
Authentication Using the End User’s Database Password . . . . . . . 588
Proxy Authentication (N-Tier Authentication) . . . . . . . . . . . . . . . . . . 589
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598
¦CHAPTER 16 Locking-Related Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Locking in Oracle. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Lost Updates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
Setting the Transaction Isolation Level to SERIALIZABLE . . . . . . . . 603
Pessimistic Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605
Optimistic Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 607
Pessimistic Locking vs. Optimistic Locking. . . . . . . . . . . . . . . . . . . . 627
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 628
¦CHAPTER 17 Selected PL/SQL Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
Further Motivation for Using PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
PL/SQL Is Close to Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 629
PL/SQL Is Portable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 630
PL/SQL Code Is Compact (Fewer Lines of Code) . . . . . . . . . . . . . . . 630
Tuning SQL in PL/SQL Is Easier . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
Adding Code to PL/SQL Is Easier . . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
PL/SQL Code May Result in Fewer Round-trips . . . . . . . . . . . . . . . . 631
Common Mistakes When Using PL/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 631
Using PL/SQL When a SQL Solution Exists . . . . . . . . . . . . . . . . . . . . 631
Reinventing the Wheel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
Selected PL/SQL Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
Packaging Matters! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635
Using Bulk Operations to Boost Performance. . . . . . . . . . . . . . . . . . 640
Preferring Static SQL over Dynamic SQL. . . . . . . . . . . . . . . . . . . . . . 651
Returning Data via a Ref Cursor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 653
Understanding the Invoker Rights and Definer Rights Modes . . . . 653
PL/SQL Debugging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 662
Seamless Instrumentation of PL/SQL and JDBC Code . . . . . . . . . . 668
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 687
¦APPENDIX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 689
Цена: 200руб.