SUMMARY

SEARCH

Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.

BOOK ORACLE PLSQL RECIPES

SUMMARY

219

1-1. Creating a Block of Code 1
1-2. Executing a Block of Code in SQL*Plus 2
1-3. Storing Code in a Script 3
1-4. Executing a Stored Script 4
1-5. Accepting User Input from the Keyboard 5
1-6. Displaying Results in SQL*Plus 7
1-7. Commenting Your Code 8
1-8. Referencing a Block of Code 9
1-9. Referring to Variables from Nested Blocks 10
1-10. Ignoring Substitution Variables 12
1-11. Changing the Substitution Variable Character 13
1-12. Creating a Variable to Match a Database Column Type 14
Chapter 2: Essential SQL 15
2-1. Retrieving a Single Row from the Database 15
2-2. Qualifying Column and Variable Names 18
2-3. Declaring Variable Types That Match Column Types 20
2-4. Returning Queried Data into a PL/SQL Record 21
2-5. Creating Your Own Records to Receive Query Results 22
2-6. Looping Through Rows from a Query 24
2-7. Obtaining Environment and Session Information 25
2-8. Formatting Query Results 29
2-9. Updating Rows Returned by a Query 31
2-10. Updating Rows Returned by a Cursor 33
2-11. Deleting Rows Returned by a Cursor 34
2-12. Performing a Transaction 35
2-13. Ensuring That Multiple Queries “See” the Same Data 37
2-14. Executing One Transaction from Within Another 38
2-15. Finding and Removing Duplicate Table Rows 40
Chapter 3: Looping and Logic 43
3-1. Choosing When to Execute Code 43
3-2. Choosing Between Two Mutually Exclusive Conditions 44
3-3. Evaluating Multiple Mutually Exclusive Conditions 45
3-4. Driving from an Expression Having Multiple Outcomes 48
3-5. Looping Until a Specified Condition Is Met 49
3-6. Iterating Cursor Results Until All Rows Have Been Returned 51
3-7. Iterating Until a Condition Evaluates to FALSE 52
3-8. Bypassing the Current Loop Iteration 53
3-9. Iterating a Fixed Number of Times 55
3-10. Iterating Backward Through a Range 56
3-11. Iterating in Increments Other Than One 57
3-12. Stepping Through a Loop Based on Odd-Numbered Increments. 58
3-13. Exiting an Outer Loop Prematurely. 59
3-14. Jumping to a Designated Location in Code. 60
Chapter 4: Functions, Packages, and Procedures . 63
4-1. Creating a Stored Function. 63
4-2. Executing a Stored Function from a Query. 67
4-3. Optimizing a Function That Will Always Return the Same Result for a
Given Input 68
4-4. Creating a Stored Procedure. 69
4-5. Executing a Stored Procedure 71
4-6. Creating Functions Within a Procedure or Code Block 73
4-7. Passing Parameters by Name 74
4-8. Setting Default Parameter Values 75
4-9. Collecting Related Routines into a Single Unit 76
4-10. Writing Initialization Code for a Package 79
4-11. Granting the Ability to Create and Execute Stored Programs 80
4-12. Executing Packaged Procedures and Functions 81
4-13. Creating a Public Name for a Stored Program 82
4-14. Executing Package Programs in Sequence 83
4-15. Implementing a Failure Flag 85
4-16. Forcing Data Access to Go Through Packages 87
4-17. Executing Stored Code Under Your Own Privilege Set 88
4-18. Accepting Multiple Parameter Sets in One Function 89
4-19. Listing the Functions, Procedures, and Packages in a Schema 90
4-20. Viewing Source Code for Stored Programs 91
Chapter 5: Triggers 93
5-1. Automatically Generating Column Values 93
5-2. Keeping Related Values in Sync 95
5-3. Responding to an Update of a Specific Table Column 97
5-4. Making a View Updatable 98
5-5. Altering the Functionality of Applications 101
5-6. Validating Input Data 103
5-7. Scrubbing Input Data 104
5-8. Replacing a Column’s Value 105
5-9. Triggering on a System Event 107
5-10. Triggering on a Schema-Related Event 109
5-11. Firing Two Triggers on the Same Event 111
5-12. Creating a Trigger That Fires on Multiple Events 113
5-13. Creating a Trigger in a Disabled State 115
Chapter 6: Type Conversion 119
6-1. Converting a String to a Number 119
6-2. Converting a String to a Date 121
6-3. Converting a Number to a String 123
6-4. Converting a Date to a String 124
6-5. Converting Strings to Timestamps 127
6-6. Writing ANSI-Compliant Conversions 129
6-7. Implicitly Converting Between PLS_INTEGER and NUMBER 131
Chapter 7: Numbers, Strings, and Dates 133
7-1. Concatenating Strings 133
7-2. Adding Some Number of Days to a Date 134
7-3. Adding a Number of Months to a Date 135
7-4. Adding Years to a Date 137
7-5. Determining the Interval Between Two Dates 138
7-6. Adding Hours, Minutes, Seconds, or Days to a Given Date 140
7-7. Returning the First Day of a Given Month 142
7-8. Returning the Last Day of a Given Month 143
7-9. Rounding a Number 144
7-10. Rounding a Datetime Value 145
7-11. Tracking Time to a Millisecond 146
7-12. Associating a Time Zone with a Date and Time 147
7-13. Finding a Pattern Within a String 148
7-14. Determining the Position of a Pattern Within a String 150
7-15. Finding and Replacing Text Within a String 151
Chapter 8: Dynamic SQL 155
8-1. Executing a Single Row Query That Is Unknown at Compile Time 155
8-2. Executing a Multiple Row Query That Is Unknown at Compile Time 159
8-3. Writing a Dynamic INSERT Statement 161
8-4. Writing a Dynamic Update Statement 165
8-5. Writing a Dynamic Delete Statement 169
8-6. Returning Data from a Dynamic Query into a Record 170
8-7. Executing a Dynamic Block of PL/SQL 172
8-8. Creating a Table at Runtime 174
8-9. Altering a Table at Runtime 175
8-10. Finding All Tables That Include a Specific Column Value 176
8-11 Storing Dynamic SQL in Large Objects. 179
8-12. Passing NULL Values to Dynamic SQL. 181
8-13. Switching Between DBMS_SQL and Native Dynamic SQL. 182
8-14. Guarding Against SQL Injection Attacks. 185
Chapter 9: Exceptions. 187
9-1. Trapping an Exception. 187
9-2. Catching Unknown Exceptions. 192
9-3. Creating and Raising Named Programmer-Defined Exceptions 194
9-4. Determining Which Error Occurred Inside the OTHERS Handler 197
9-5. Raising User-Defined Exceptions Without an Exception Handler 200
9-6. Redirecting Control After an Exception Is Raised 202
9-7. Raising Exceptions and Continuing Processing 204
9-8. Associating Error Numbers with Exceptions That Have No Name 206
9-9. Tracing an Exception to Its Origin 207
9-10. Displaying PL/SQL Compiler Warnings 211
Chapter 10: PL/SQL Collections and Records 215
10-1. Creating and Accessing a VARRAY 215
10-2. Creating and Accessing an Indexed Table 216
10-3. Creating Simple Records 217
10-4. Creating and Accessing Record Collections 218
10-5. Creating and Accessing Hash Array Collections 219
10-6. Creating and Accessing Complex Collections 220
10-7. Passing a Collection As a Parameter 223
10-8. Returning a Collection As a Parameter 224
10-9. Counting the Members in a Collection 226
10-10. Deleting a Record from a Collection 227
10-11. Checking Whether an Element Exists 228
10-12. Increasing the Size of a Collection 229
10-13. Navigating Collections 230
10-14. Trimming a Collection 232
Chapter 11: Automating Routine Tasks 233
11-1. Scheduling Recurring Jobs 233
11-2. E-mailing Output from a Scheduled Job 234
11-3. Using E-mail for Job Status Notification 235
11-4. Refreshing a Materialized View on a Timed Interval 236
11-5. Synchronizing Data with a Remote Data Source 238
11-6. Scheduling a Job Chain 240
Chapter 12: Oracle SQL Developer 247
12-1. Creating Standard and Privileged Database Connections 247
12-2. Obtaining Information About Tables 249
12-3. Enabling Output to Be Displayed 251
12-4. Writing and Executing PL/SQL 253
12-5. Creating and Executing a Script 256
12-6. Accepting User Input for Substitution Variables 258
12-7. Saving Pieces of Code for Quick Access 259
12-8. Creating a Function 261
12-9. Creating a Stored Procedure 265
12-10. Creating a Package Header and Body 268
12-11. Creating a Trigger 272
12-12. Debugging Stored Code 276
12-13. Compiling Code Within the Navigator 278
Chapter 13: Analyzing and Improving Performance 281
13-1. Installing DBMS_PROFILER 281
13-2. Identifying Bottlenecks 283
13-3. Speeding Up Read/Write Loops 285
13-4. Passing Large or Complex Collections as OUT Parameters 287
13-5. Optimizing Computationally Intensive Code 288
13-6. Improving Initial Execution Running Time 290
Chapter 14: Using PL/SQL on the Web 291
14-1. Running a PL/SQL Procedure on the Web 291
14-2. Creating a Common Set of HTML Page Generation Procedures 292
14-3 Creating an Input Form 295
14-4. Creating a Web–based Report Using PL/SQL Procedures 299
14-5. Displaying Data from Tables 302
14-6. Creating a Web Form Dropdown List from a Database Query 303
14-7. Creating a Sortable Web Report 305
14-8. Passing Data Between Web Pages 308
14-9. Viewing Errors for Debugging Web Apps 310
14-10. Generating JavaScript via PL/SQL 311
14-11. Generating XML Output 314
14-12. Creating an Input Form with AJAX 315
Chapter 15: Java in the Database 319
15-1. Creating a Java Database Class 319
15-2. Loading a Java Database Class into a Database 321
15-3. Loading a Compiled Java Class Into the Database 323
15-4. Exposing a Java Class As a Stored Procedure . 325
15-5. Executing a Java Stored Procedure . 325
15-6. Calling a Java Stored Procedure from PL/SQL . 326
15-7. Passing Parameters Between PL/SQL and Java . 328
15-8. Creating and Calling a Java Database Function. 330
15-9. Creating a Java Database Trigger . 332
15-10. Passing Data Objects from PL/SQL to Java. 334
15-11. Embedding a Java Class Into a PL/SQL Package 336
15-12. Loading Java Libraries Into the Database 338
15-13. Removing a Java Class 340
15-14. Retrieving Database Metadata with Java 341
15-15. Querying the Database to Help Resolve Java Compilation Issues 343
Chapter 16: Accessing PL/SQL from JDBC, HTTP, Groovy, and Jython 345
16-1. Accessing a PL/SQL Stored Procedure via JDBC 345
16-2. Accessing a PL/SQL Stored Function from JDBC 348
16-3. Accessing PL/SQL Web Procedures with HTTP 350
16-4. Accessing PL/SQL from Jython 355
16-5. Accessing PL/SQL from Groovy 358
Chapter 17: Unit Testing With utPLSQL 361
17-1. Testing Stored PL/SQL Code Without Unit Tests 361
17-2. Installing the utPLSQL Unit Testing Framework 363
17-3. Building a utPLSQL Test Package 365
17-4. Writing a utPLSQL Unit Test Procedure 367
17-5. Running a utPLSQL Test 369
17-6. Building a utPLSQL Test Suite 373
17-7. Running a utPLSQL Test Suite 374
17-8. Reconfiguring utPLSQL Parameters 381
17-9. Redirecting upPLSQL Test Results to a File 384
17-10. Automating Unit Tests for PL/SQL and Java Stored Procedures Using Ant 385
Index 391