Oracle PL/SQL Language Pocket Reference

Local Disk......Page 0
Oracle PL/SQL Language Pocket Reference......Page 1
Oracle PL/SQL Language Pocket Reference Search Page......Page 3
[Chapter 1] Oracle PL/SQL Language Pocket Reference......Page 4
[Chapter 1] 1.2 Acknowledgments......Page 6
[Chapter 1] 1.3 Conventions......Page 7
[Chapter 1] 1.4 PL/SQL Language Fundamentals......Page 8
[Chapter 1] 1.5 Variables and Program Data......Page 14
[Chapter 1] 1.6 Conditional and Sequential Control......Page 23
[Chapter 1] 1.7 Loops......Page 26
[Chapter 1] 1.8 Database Interaction and Cursors......Page 30
[Chapter 1] 1.9 Cursors in PL/SQL......Page 35
[Chapter 1] 1.10 Exception Handling......Page 43
[Chapter 1] 1.11 Records in PL/SQL......Page 49
[Chapter 1] 1.12 Named Program Units......Page 53
[Chapter 1] 1.13 Triggers......Page 62
[Chapter 1] 1.14 Packages......Page 66
[Chapter 1] 1.15 Calling PL/SQL Functions in SQL......Page 71
[Chapter 1] 1.16 Oracle8 Objects......Page 75
[Chapter 1] 1.17 Collections......Page 83
[Chapter 1] 1.18 External Procedures......Page 93
[Chapter 1] 1.19 Java Language Integration......Page 99
Oracle PL/SQL Programming, 2nd Edition......Page 103
Oracle PL/SQL Programming Search Page......Page 105
Index......Page 106
Index......Page 107
Index......Page 110
Index......Page 112
Index......Page 121
Index......Page 128
Index......Page 133
Index......Page 137
Index......Page 138
Index......Page 139
Index......Page 143
Index......Page 144
Index......Page 145
Index......Page 148
Index......Page 151
Index......Page 155
Index......Page 159
Index......Page 166
Index......Page 167
Index......Page 173
Index......Page 180
Index......Page 184
Index......Page 186
Index......Page 189
Index......Page 191
Index......Page 192
[Chapter 8] 8.3 Types of Exceptions......Page 193
[Chapter 8] 8.2 The Exception Section......Page 202
[Chapter 8] Exception Handlers......Page 204
[Chapter 7] 7.7 Tips for PL/SQL Loops......Page 207
[Chapter 7] 7.6 Managing Loop Execution......Page 215
[Chapter 7] 7.5 The WHILE Loop......Page 221
[Chapter 7] 7.4 The Cursor FOR Loop......Page 224
[Chapter 7] 7.3 The Numeric FOR Loop......Page 229
[Chapter 7] 7.2 The Simple Loop......Page 233
[Chapter 7] Loops......Page 237
[Chapter 6] 6.13 Working with Cursors......Page 241
[Chapter 6] 6.12 Cursor Variables......Page 249
[Chapter 6] 6.11 SELECT FOR UPDATE in Cursors......Page 266
[Chapter 6] 6.10 Cursor Parameters......Page 271
[Chapter 6] 6.9 Cursor Attributes......Page 275
[Chapter 6] 6.8 Closing Cursors......Page 282
[Chapter 6] 6.7 Column Aliases in Cursors......Page 284
[Chapter 6] 6.6 Fetching from Cursors......Page 286
[Chapter 6] 6.5 Opening Cursors......Page 290
[Chapter 6] 6.4 Declaring Cursors......Page 292
[Chapter 6] 6.3 Implicit and Explicit Cursors......Page 298
[Chapter 6] 6.2 Cursors in PL/SQL......Page 303
[Chapter 6] Database Interaction and Cursors......Page 307
[Chapter 5] 5.2 Sequential Control Statements......Page 313
[Chapter 5] Conditional and Sequential Control......Page 321
[Chapter 4] 4.7 Tips for Creating and Using Variables......Page 332
[Chapter 4] 4.6 Programmer-Defined Subtypes......Page 346
[Chapter 4] 4.5 Anchored Declarations......Page 351
[Chapter 4] 4.4 Variable Declarations......Page 357
[Chapter 4] 4.3 NULLs in PL/SQL......Page 361
[Chapter 4] 4.2 Scalar Datatypes......Page 365
[Chapter 4] Variables and Program Data......Page 391
[Part II] PL/SQL Language Elements......Page 394
[Chapter 3] 3.7 Documenting the Entire Package......Page 395
[Chapter 3] 3.6 Using Comments Effectively......Page 399
[Chapter 3] 3.5 Formatting Packages......Page 408
[Chapter 3] 3.4 Formatting PL/SQL Blocks......Page 410
[Chapter 3] 3.3 Formatting Control Structures......Page 412
[Chapter 3] 3.2 Formatting SQL Statements......Page 416
[Chapter 3] Effective Coding Style......Page 419
[Chapter 2] 2.7 Block Structure......Page 429
[Chapter 2] 2.6 The PRAGMA Keyword......Page 433
[Chapter 2] 2.5 Comments......Page 435
[Chapter 2] 2.4 The Semicolon Delimiter......Page 437
[Chapter 2] 2.3 Literals......Page 439
[Chapter 2] 2.2 Identifiers......Page 441
[Chapter 2] PL/SQL Language Fundamentals......Page 444
[Chapter 1] 1.7 Best Practices for PL/SQL Excellence......Page 448
[Chapter 1] 1.6 A Few of My Favorite (PL/SQL) Things......Page 454
[Chapter 1] 1.5 Advice for Oracle Programmers......Page 459
[Chapter 1] 1.4 PL/SQL Versions......Page 463
[Chapter 1] 1.3 The Origins of PL/SQL......Page 484
[Chapter 1] 1.2 The Concept of Programming in Oracle Applications......Page 487
[Chapter 1] Introduction to PL/SQL......Page 490
[Part I] Programming in PL/SQL......Page 494
[Preface] Acknowledgments......Page 495
[Preface] Comments and Questions......Page 500
[Preface] About the Disk......Page 501
[Preface] Which Platform or Version?......Page 502
[Preface] Conventions Used in This Book......Page 503
[Preface] Audience......Page 505
[Preface] Structure of This Book......Page 508
Preface......Page 512
Foreword......Page 516
Dedication......Page 519
[Chapter 8] 8.4 Determining Exception-Handling Behavior......Page 520
[Chapter 8] 8.5 Raising an Exception......Page 530
[Chapter 8] 8.6 Handling Exceptions......Page 536
[Chapter 8] 8.7 Client-Server Error Communication......Page 543
[Chapter 8] 8.8 NO_DATA_FOUND: Multipurpose Exception......Page 546
[Chapter 8] 8.9 Exception Handler as IF Statement......Page 549
[Chapter 8] 8.10 RAISE Nothing but Exceptions......Page 552
[Chapter 9] Records in PL/SQL......Page 556
[Chapter 9] 9.2 Table-Based Records......Page 563
[Chapter 9] 9.3 Cursor-Based Records......Page 565
[Chapter 9] 9.4 Programmer-Defined Records......Page 569
[Chapter 9] 9.5 Assigning Values to and from Records......Page 573
[Chapter 9] 9.6 Record Types and Record Compatibility......Page 577
[Chapter 9] 9.7 Nested Records......Page 581
[Chapter 10] PL/SQL Tables......Page 588
[Chapter 10] 10.2 Characteristics of PL/SQL Tables......Page 591
[Chapter 10] 10.3 PL/SQL Tables and DML Statements......Page 593
[Chapter 10] 10.4 Declaring a PL/SQL Table......Page 595
[Chapter 10] 10.5 Referencing and Modifying PL/SQL Table Rows......Page 598
[Chapter 10] 10.6 Filling the Rows of a PL/SQL Table......Page 604
[Chapter 10] 10.7 Clearing the PL/SQL Table......Page 607
[Chapter 10] 10.8 PL/SQL Table Enhancements in PL/SQL Release 2.3......Page 609
[Chapter 10] 10.9 Working with PL/SQL Tables......Page 619
[Part III] Built-In Functions......Page 649
[Chapter 11] Character Functions......Page 650
[Chapter 11] 11.2 Character Function Examples......Page 676
[Chapter 12] Date Functions......Page 696
[Chapter 12] 12.2 Date Function Examples......Page 711
[Chapter 13] Numeric, LOB, and Miscellaneous Functions......Page 719
[Chapter 13] 13.2 LOB Function Descriptions......Page 733
[Chapter 13] 13.3 Miscellaneous Function Descriptions......Page 738
[Chapter 14] Conversion Functions......Page 748
[Chapter 14] 14.2 Conversion Function Descriptions......Page 757
[Chapter 14] 14.3 Conversion Function Examples......Page 764
[Part IV] Modular Code......Page 789
[Chapter 15] Procedures and Functions......Page 790
[Chapter 15] 15.2 Review of PL/SQL Block Structure......Page 793
[Chapter 15] 15.3 The Anonymous PL/SQL Block......Page 797
[Chapter 15] 15.4 Procedures......Page 812
[Chapter 15] 15.5 Functions......Page 816
[Chapter 15] 15.6 Parameters......Page 825
[Chapter 15] 15.7 Local Modules......Page 836
[Chapter 15] 15.8 Module Overloading......Page 842
[Chapter 15] 15.9 Forward Declarations......Page 849
[Chapter 15] 15.10 Go Forth and Modularize!......Page 851
[Chapter 16] Packages......Page 853
[Chapter 16] 16.2 Overview of Package Structure......Page 857
[Chapter 16] 16.3 The Package Specification......Page 867
[Chapter 16] 16.4 The Package Body......Page 875
[Chapter 16] 16.5 Package Data......Page 878
[Chapter 16] 16.6 Package Initialization......Page 884
[Chapter 17] Calling PL/SQL Functions in SQL......Page 889
[Chapter 17] 17.2 Syntax for Calling Stored Functions in SQL......Page 894
[Chapter 17] 17.3 Requirements for Stored Functions in SQL......Page 896
[Chapter 17] 17.4 Restrictions on PL/SQL Functions in SQL......Page 898
[Chapter 17] 17.5 Calling Packaged Functions in SQL......Page 901
[Chapter 17] 17.6 Column/Function Name Precedence......Page 906
[Chapter 17] 17.7 Realities: Calling PL/SQL Functions in SQL......Page 908
[Chapter 17] 17.8 Examples of Embedded PL/SQL......Page 911
[Part V] New PL/SQL8 Features......Page 928
[Chapter 18] Object Types......Page 929
[Chapter 18] 18.2 Oracle Objects Example......Page 942
[Chapter 18] 18.3 Syntax for Creating Object Types......Page 953
[Chapter 18] 18.4 Manipulating Objects in PL/SQL and SQL......Page 968
[Chapter 18] 18.5 Modifying Persistent Objects......Page 980
[Chapter 18] 18.6 Object Housekeeping......Page 998
[Chapter 18] 18.7 Making the Objects Option Work......Page 1003
[Chapter 19] Nested Tables and VARRAYs......Page 1005
[Chapter 19] 19.2 Creating the New Collections......Page 1012
[Chapter 19] 19.3 Syntax for Declaring Collection Datatypes......Page 1021
[Chapter 19] 19.4 Using Collections......Page 1024
[Chapter 19] 19.5 Collection Pseudo-Functions......Page 1035
[Chapter 19] 19.6 Collection Built-Ins......Page 1045
[Chapter 19] 19.7 Example: PL/SQL-to-Server Integration......Page 1054
[Chapter 19] 19.8 Collections Housekeeping......Page 1058
[Chapter 19] 19.9 Which Collection Type Should I Use?......Page 1061
[Chapter 20] Object Views......Page 1063
[Chapter 20] 20.2 INSTEAD OF Triggers......Page 1070
[Chapter 20] 20.3 Syntax for Object Views......Page 1075
[Chapter 20] 20.4 Differences Between Object Views and Object Tables......Page 1079
[Chapter 20] 20.5 Not All Views with Objects Are Object Views......Page 1090
[Chapter 20] 20.6 Schema Evolution......Page 1092
[Chapter 20] 20.7 Object Views Housekeeping......Page 1095
[Chapter 20] 20.8 Postscript: Using the BFILE Datatype......Page 1098
[Chapter 21] External Procedures......Page 1102
[Chapter 21] 21.2 Steps in Creating an External Procedure......Page 1109
[Chapter 21] 21.3 Syntax for External Procedures......Page 1117
[Chapter 21] 21.4 Mapping Parameters......Page 1121
[Chapter 21] 21.5 OCI Service Routines......Page 1131
[Chapter 21] 21.6 External Procedure Housekeeping......Page 1132
[Chapter 21] 21.7 Examples......Page 1136
[Part VI] Making PL/SQL Programs Work......Page 1147
[Chapter 22] Code Design Tips......Page 1148
[Chapter 22] 22.2 Build the Most Functional Functions......Page 1155
[Chapter 22] 22.3 Take Full Advantage of Local Modularization......Page 1171
[Chapter 22] 22.4 Be Wary of Modules Without Any Parameters......Page 1174
[Chapter 22] 22.5 Create Independent Modules......Page 1177
[Chapter 22] 22.6 Construct Abstract Data Types (ADTs)......Page 1182
[Chapter 22] 22.7 Tips for Parameter Design......Page 1191
[Chapter 23] Managing Code in the Database......Page 1201
[Chapter 23] 23.2 Transaction Integrity and Execute Authority......Page 1208
[Chapter 23] 23.3 Module Validation and Dependency Management......Page 1212
[Chapter 23] 23.4 Remote Procedure Calls......Page 1216
[Chapter 23] 23.5 Managing Stored Objects with SQL*Plus......Page 1218
[Chapter 23] 23.6 Using SQL to Examine Stored Objects......Page 1224
[Chapter 23] 23.7 Encrypting Stored Code......Page 1235
[Chapter 24] Debugging PL/SQL......Page 1240
[Chapter 24] 24.2 Debugging Tips and Strategies......Page 1244
[Chapter 25] Tuning PL/SQL Applications......Page 1256
[Chapter 25] 25.2 Tuning Access to Compiled Code......Page 1261
[Chapter 25] 25.3 Tuning Access to Your Data......Page 1268
[Chapter 25] 25.4 Tuning Your Algorithms......Page 1283
[Chapter 25] 25.5 Overview of PL/SQL8 Enhancements......Page 1307
[Chapter 26] Tracing PL/SQL Execution......Page 1309
[Chapter 26] 26.2 Tracing for Production Support......Page 1314
[Chapter 26] 26.3 Free Format Filtering......Page 1319
[Chapter 26] 26.4 Structured Interface Filtering......Page 1321
[Chapter 26] 26.5 Quick-and-Dirty Tracing......Page 1323
[Part VII] Appendixes......Page 1329
[Appendix A] What\'s on the Companion Disk?......Page 1330
[Appendix A] A.2 Using the Guide......Page 1332
[Appendix B] Calling Stored Procedures from PL/SQL Version 1.1......Page 1334
[Appendix B] B.2 Restrictions on Calling Stored Procedures......Page 1337
[Appendix C] Built-In Packages......Page 1344
[Appendix C] C.2 DBMS_ALERT......Page 1348
[Appendix C] C.3 Oracle AQ, the Advanced Queueing Facility......Page 1351
[Appendix C] C.4 DBMS_DDL......Page 1356
[Appendix C] C.5 DBMS_ JOB......Page 1358
[Appendix C] C.6 DBMS_LOB (PL/SQL8 Only)......Page 1361
[Appendix C] C.7 DBMS_LOCK......Page 1368
[Appendix C] C.8 DBMS_MAIL......Page 1371
[Appendix C] C.9 DBMS_OUTPUT......Page 1372
[Appendix C] C.10 DBMS_PIPE......Page 1375
[Appendix C] C.11 DBMS_ROWID (PL/SQL8 Only)......Page 1379
[Appendix C] C.12 DBMS_SESSION......Page 1383
[Appendix C] C.13 DBMS_SNAPSHOT......Page 1386
[Appendix C] C.14 DBMS_SQL......Page 1389
[Appendix C] C.15 DBMS_TRANSACTION......Page 1396
[Appendix C] C.16 DBMS_UTILITY......Page 1403
[Appendix C] C.17 UTL_FILE......Page 1407