Section
Contents
One
Advanced PL/SQL 11g Introduction
2
- Introduction
3
Two
Loop Processing in PLSQL
4
- Iterative Processing in PLSQL
5
- Basic Looping in PLSQL
6
- While Looping in PLSQL
10
- For Looping in PLSQL
13
- Using Dates in For Loops in PLSQL
17
- Using Characters in For Loops in PLSQL
18
- Implicit Cursor For Looping in PLSQL
19
- Explicit Cursor For Looping in PLSQL
22
- Using other Loops with Explicit Cursors
24
- Loop Labels in PLSQL
27
- Loop Continue and Continue When in PLSQL
29
- Exercise One
33
Three
Advanced PLSQL 11g Techniques
37
- Defining Sub-Blocks in PLSQL
38
- Variables in PLSQL
44
- Quoted Identifier Variables in PLSQL
45
- Numeric Variables in PLSQL
48
- Number Variables in PLSQL
49
- Decimal Variables in PLSQL
52
- Integer Variables in PLSQL
53
- PLS_Integer Variables in PLSQL
54
- Binary_Integer Variables in PLSQL
55
- Natural Variables in PLSQL
56
- Naturaln Variables in PLSQL
57
- Positive Variables in PLSQL
58
- Positiven Variables in PLSQL
59
- Signtype Variables in PLSQL
60
- Simple_Integer Variables in PLSQL
61
- Defining Constants in PLSQL
62
- Using %Rowtype in PLSQL
64
- Declaring a Variable as a %Rowtype
65
- Referencing a %Rowtype Variable
66
- Populating a Variable which is a %Rowtype
67
- Passing a %Rowtype between Processes
70
- Declaring a Cursor %Rowtype
72
- Using Sequences in PLSQL 11g
75
- Using Savepoints in a PLSQL process
76
- Example of Savepoints in a PLSQL process
77
- Timestamps and Intervals in PLSQL
78
- Timestamp Variables in PLSQL
79
- Timestamp in PLSQL
81
- Populating Timestamps in PLSQL
84
- Using Extract in PLSQL
86
- Timestamp with Time Zone
87
- Timestamp Time Zone and Extract
90
- Using sys_extract_utc to Normalise Timestamps
91
- Using the To_Timestamp_TZ function
92
- Using the From_TZ function
94
- Timestamp with Local Time Zone
96
- Intervals
98
- Populating Year to Month Intervals
101
- Populating Day to Second Intervals
102
- Using Intervals in PLSQL
103
- Using Intervals with Extract in PLSQL
108
- Populating Intervals using Timestamps
109
Four
Case Statements and Case Expressions
110
- Introduction
111
- Using Searched Case in PLSQL
112
- Using Case in PLSQL
113
- Using Nested Case in PLSQL
114
- Using Case Expressions in PLSQL
116
- Using Nullif in PLSQL
120
- Using Coalesce in PLSQL
121
- Exercise Two
122
Five
Advanced Cursors in Oracle 11g
125
- For Update with Explicit Cursors
126
- Where Current Of Explicit Cursors
127
- Example of Updating with Explicit Cursors
128
- Using Nowait with Explicit Cursors
129
- Using Skip Locked with Explicit Cursors
130
- Using Wait with an Explicit Cursor
131
- Using Rowid with Explicit Cursors
132
- Using Returning Into in PLSQL
133
- Ref Cursors in PLSQL
136
- Strongly Typed Ref Cursors in PLSQL
137
- Weakly Typed Ref Cursors in PLSQL
139
- Sys_refcursor in PLSQL
140
- Using Sys_Refcursors with SQL
142
- Using Rowtype for DML
146
Six
Error Handling in PLSQL 11g
148
- Exceptions in PLSQL
149
- Testing for Exceptions in PLSQL
150
- Informing the User of the Error
152
- Raise_Application_Error Parameters
154
- Oracle Defined Exceptions
155
- Access_into_Null (ORA-06530 )
156
- Case_Not_Found (ORA-06592 )
158
- Collection_Is_Null (ORA-06531 )
159
- Cursor_Already_Open (ORA-06511 )
161
- Dup_Val_On_Index (ORA-00001 )
162
- Invalid_Cursor (ORA-01001 )
163
- Invalid_Number (ORA-01722 )
164
- Login_Denied (ORA-01017 )
165
- No_Data_Found (ORA-01403 )
167
- Not_Logged_On (ORA-01012 )
168
- Program_Error (ORA-06501 )
169
- Rowtype_Mismatch (ORA-06504 )
170
- Self_is_Null (ORA-30625 )
172
- Storage_Error (ORA-06500 )
175
- Subscript_Beyond_Count (ORA-06533 )
176
- Subscript_outside_limit (ORA-06532)
177
- Sys_Invalid_Rowid (ORA-01410)
179
- Timeout_On_Resource (ORA-00051)
180
- Too_Many_Rows (ORA-01422 )
181
- Value_Error (ORA-06502 )
182
- Zero_divide (ORA-01476 )
183
- Others
184
- Raising User Defined Exceptions in PLSQL
185
- Pragma Exceptions in PLSQL
188
- Alternative Way to Handle SQL Errors in PLSQL
190
- Handling SQL Errors in PLSQL
191
- Advanced SQLErrm
193
- Nesting Exceptions in PLSQL
194
- Re-Raising Exceptions in PLSQL
197
- Handling Exceptions in Sub-Processes in PLSQL
199
- Using Nocopy When Declaring Processes
203
- Using DBMS_UTILITY Functions
207
- Using DBMS_UTILITY.FORMAT_ERROR_STACK
208
- Using DBMS_UTILITY.FORMAT_BACKTRACE
209
Seven
PL/SQL Database Objects
212
- PLSQL Objects
213
- Compiling PLSQL Objects
214
- Metadata For PLSQL Objects
215
- Re-Compiling PLSQL Objects
216
- Dropping PLSQL Objects
218
- Passing Parameters in PLSQL
219
- Passing Parameters using Positional Notation
220
- Passing Parameters Using Named Notation
222
- Using Mixed Notation in SQL
223
- Metadata (User_Source)
225
- Describing PLSQL Objects in SQL PLUS
226
Eight
Programmer Defined Functions
227
- Syntax of Functions in Oracle
228
- Suggested Naming Convention of Functions in Oracle
231
- Function Example in PLSQL
232
- Running Functions in Oracle
233
Nine
Programmer Defined Procedures
235
- Syntax of Procedures in Oracle
236
- Example of Procedures in Oracle
238
- Executing Procedures in Oracle within SQLPLUS
239
- Executing Procedures in Oracle within PLSQL
240
- Exercise Three
241
Ten
Packaging Programmer Defined Programs
247
- Syntax of Packages in Oracle
248
- Compiling of Packages in Oracle
253
- Private Processes in PLSQL Packages
254
- Executing Packages in Oracle
256
- Handling Exceptions in Packages in PLSQL
257
Eleven
DML Triggers in Oracle
259
- Oracle Triggers Introduction
260
- DML Triggers Introduction
261
- DML Triggers Timing Points
264
- DML Triggers When Conditions
265
- DML Triggers When Conditions Examples
266
- DML Triggers Suggested Naming Conventions
268
- Syntax of DML Triggers in PLSQL
270
- Referencing Values within Triggers
271
- Checking the Event which fired the DML Trigger
275
- Using the Follows syntax to dictate Trigger Firing
277
- Using the Disable syntax when Compiling Triggers
282
- Enabling/Disabling Triggers in an Oracle Database
283
- Metadata for Triggers (User_Triggers)
285
- Mutating DML Triggers in Oracle
286
Twelve
Instead Of Triggers
290
- Introduction to Instead Of Triggers
291
- Syntax of an Instead Of Trigger in Oracle
294
- Example of an Instead Of Trigger
296
- Deleting Records in Oracle using an Instead Of
304
- Using Follows in an Instead Of Trigger
305
Thirteen
Compound Triggers
306
- Introduction to Compound Triggers
307
- Syntax for Table Compound Triggers
309
- Declaration Section
310
- Timing Points
311
- BEFORE STATEMENT
312
- BEFORE EACH ROW
313
- AFTER EACH ROW
314
- AFTER STATEMENT
315
- Compound Triggers for Views
316
Fourteen
System Triggers
317
- Introduction to System Triggers
318
- Syntax for System Triggers in Oracle
319
- System Event Triggers
320
- User Event Triggers
321
- Schema Event Triggers
325
- Database Level Attributes for System Triggers
331
- Exercise Four
332
Fifteen
Using Contexts
341
- Introduction
342
- Attributes
343
- User Defined
351
- Creating Contexts
352
- Creating a Package for a Context
353
- Creating a Context
354
- Populating a Context
355
- Accessing a Context
356
- Listing Contents of a Context
357
- Deleting Contents of a Context
359
- Changing Context Behaviour
361
Sixteen
Subprogram Inlining
362
- Introduction
363
- Pragma Inline
364
- Example of Inlining
365
- Inlining Usage
370
- Conclusion
372
Seventeen
PLSQL Compiler
373
- Introduction
374
- Warning Levels
375
- Enabling/Disabling Warning Levels
376
- Using the Alter Session
377
- Using the Error option
380
- Using DBMS_WARNING Package
382
- Using DBMS_WARNING.GET_CATEGORY
383
- Severe Category Errors
384
- Informational Category Errors
386
- Performance Category Errors
388
- Oracle Error Messages
389
- Using dbms_warning.set_warning_setting_string
390
- Using dbms_warning.set_warning_setting_num
391
- Using dbms_warning.set_warning_setting_cat
392
- USER_PLSQL_OBJECT_SETTINGS
393
- Re-Compiling with same Warning settings
395
- PLSQL_OPTIMIZE_LEVEL
396
- Optimize Levels
397
- Timing Optimization with dbms_utility.get_cpu_time
398
- Optimize Levels in user_plsql_object_settings
404
- Re-Compiling with same Optimize settings
405
- High Level Optimization without Pragma Inline
406
Eighteen
Conditional Compilation in PLSQL
408
- Introduction
409
- Dbms_preprocessor
411
- Compiling using PLSQL_CCFLAGS
412
- PLSQL_CCFLAGS in PLSQL
414
- Using $error Directive
422
- Using Constants with CCFlags
424
- $$PLSQL_CODE_TYPE
425
- Oracle Compilation Modes
426
- $$PLSQL_OPTIMIZE_LEVEL
427
- $$PLSQL_LINE
428
- $$PLSQL_UNIT
429
- DBMS_DB_VERSION
430
- Setting Session CCFlags
432
- Plsql_ccflags in user_plsql_object_settings
433
- Plsql_ccflags in Triggers
434
Nineteen
Regular Expressions in PLSQL (Regex)
436
- Introduction
437
- Using REGEXP_LIKE in Where Clauses
439
- Case Sensitivity
441
- Line Anchors
442
- Using the Dot (Any Character)
444
- Counting Characters {Intervals}
447
- Character Lists
451
- Excluding Character Lists
455
- Alternatives to Character Lists
456
- Class Shorthands
459
- Or Patterns (Alternatives)
460
- Or Patterns with Character Lists
461
- Using the Question Mark (Optional)
462
- Using the Plus sign (Mandatory)
465
- Using the Star sign (Optional)
467
- Backreferencing
468
- POSIX
470
- Using POSIX in Oracle - [:upper] example
472
- Using POSIX in Oracle - [:digit] example
473
- Using POSIX in Oracle - Character equivalents
474
- Using Regexp_like in PLSQL
475
- Using Regexp_count in PLSQL
478
- Using Regexp_replace in PLSQL
482
- Using Regexp_instr in PLSQL
484
- Using Regexp_substr in PLSQL
486
- Using Regular Expressions in Oracle
488
Twenty
Using Alternative Quoting Mechanism in PLSQL
489
- Introduction
490
- Using Alternative Quoting Mechanism
491
- Exercise Five
492
Twenty One
File I/O
497
- Reading and Writing from the Operating System in PLSQL
498
- Accessing the File System using Directories
499
- Creating Directories
500
- Simple Write using UTL_FILE.PUT_LINE
502
- Simple Read using UTL_FILE.GET_LINE
503
- Additional Functions in the UTL_FILE Package
504
- Using UTL_FILE.PUTF in PLSQL
505
- UTL_FILE Exceptions
507
- Using UTL_FILE to perform Operating System Commands
509
- Using UTL_FILE.Fcopy in PLSQL
510
- Using UTL_FILE.Fgetattr in PLSQL
511
- Using UTL_FILE.Fremove in PLSQL
512
- Using UTL_FILE.Frename in PLSQL
513
Twenty Two
Working with Clobs and Blobs in PLSQL
514
- Introduction
515
- Creation of Clobs in Tables
516
- Using Clobs in PLSQL
517
- Inserting Clobs into the Database using PLSQL
518
- Writing Clobs to the Filesystem using PLSQL
522
- Introduction to Blobs
523
- Inserting Blobs into the Database using PLSQL
524
- Writing Blobs into the Filesystem using PLSQL
525
- DBMS_LOB exceptions
528
Twenty
Three
Three
DBMS_Metadata
530
- Generating Database Object Scripts
531
- List of Object Types
532
- Example of Using DBMS_Metadata
533
Twenty Four
Sub-programming Techniques
535
- Introduction
536
- Example
537
Twenty Five
Encrypting Code
539
- Introduction
540
- Using Wrap.exe to encrypt processes in Oracle
541
- Example of using Wrap.exe to encrypt processes
544
- Using DBMS_DDL.CREATE_WRAPPED Function
546
Twenty Six
Object Orientated Programming
550
- Introduction
551
- Definition of Objects
552
Twenty
Seven
Seven
Row Objects
556
- Introduction
557
- Creating
558
- Metadata
559
- Data
560
- Indexes
561
- Views
563
- Removing
564
- OIDs
566
Twenty Eight
Column Objects
573
- Introduction
574
- Describing
575
- Inserting into
578
- Selecting from
580
- Updating
585
- Object Views
586
Twenty Nine
Defining Processes within Objects
588
- Introduction
589
- Defining Methods
590
- Member Methods
592
- Map Methods
599
- Order Methods
601
- Constructor Methods
607
Thirty
PLSQL Data Structures and Collections
611
- Programmer Defined Records
612
- Nested Programmer Defined Records
616
- Varrays in PLSQL
618
- Using Varrays in Loops
621
- Populating Varrays
622
- Extending Varrays
623
- Deleting from Varrays
625
- Varrays and Tables
626
- Multilevel Varrays
629
Thirty One
Associate Arrays (Index by Tables)
632
- Introducing Associate Arrays in PLSQL
633
- Declaring PLSQL Tables
635
- Populating PLSQL Tables
636
- Using Count with PLSQL Tables
638
- Using Delete with PLSQL Tables
639
- Using Exists with PLSQL Tables
640
- Using First/Last with PLSQL Tables
641
- Using Next/Prior with PLSQL Tables
642
- Using Varchar2 as an index with a PLSQL Table
643
- Multi-Level PLSQL Table
647
- Nested PLSQL Table
648
- Cardinality with Nested Table
651
- Tidying Nested PLSQL Tables using Set
652
- Nested PLSQL Tables using Set
653
- Trimming Nested PLSQL Tables
655
- Multi-Level Nested PLSQL Tables
657
Thirty Two
Collection Comparisons
658
- Introduction
659
- Check for Equality
660
- Using IN with a Nested Table
663
- Using Member Of with a Nested Table
664
- Using Is Empty with a Nested Table
665
- Multiset Union
666
- Multiset Union Distinct
668
- Multiset Intersect
669
- Multiset Except
670
- Submultiset
671
- Not Submultiset
672
- Not Submultiset Alternative
673
- Exercise Six
674
Thirty Three
Bulk SQL in PLSQL
677
- Introduction to Bulk Processing
678
- Bulk Binding in PLSQL
679
- Bulk Binding in PLSQL Using Associate Arrays
680
- Bulk Binding in PLSQL Using Varrays
681
- Bulk Binding in PLSQL Using Nested Tables
682
- Forall with Save Exceptions
683
- Forall with SQL%BULKEXCEPTIONS
685
- Forall with Indices Of
688
- Forall with Values Of
690
- Bulk Collect with Implicit Cursors (Varray)
691
- Bulk Collect with Implicit Cursors (Associative Array)
692
- Bulk Collect with Implicit Cursors (Nested Table)
693
- Bulk Collect with Explicit Cursors
694
- Bulk Collect and Returning in PLSQL
695
- Problems with Bulk Processing in PLSQL
698
- Bulk Processing and Limit in PLSQL
699
Thirty Four
Invoker Rights in PLSQL
700
- The need to use Invoker Rights
701
- Example of Invoker Rights
702
- Using AUTHID in PLSQL
705
Thirty Five
Overloading Modules in PLSQL 708
- Introduction
709
- Example
710
- User_Procedures
714
Thirty Six
Forward Declaration
715
Thirty Seven
Autonomous Transactions in PLSQL
718
- Introduction
719
- Example of an Autonomous Transaction
721
- Rollback in Autonomous Transactions
728
- Using Autonomous Transactions in Triggers
729
Thirty Eight
Advanced Use of Packages
731
- Introduction
732
- Defining Explicit Cursors in Packages
733
- Flexible Explicit Cursors in Packages
737
- Passing Parameters Using Packages
740
- Using PLSQL Tables as Parameters
743
- Global Variables using Packages
747
- Public and Private Variables in Packages
753
Thirty Nine
Dynamic SQL
755
- Introduction to Dynamic SQL
756
- Native Dynamic SQL (NDS) with Execute Immediate
757
- NDS Error Handling
760
- NDS with Inputs
761
- NDS with Output
763
- Dynamic Cursors and Sys_Refcursor
764
- NDS with Outputs
766
- Introduction to DBMS_SQL
767
- DBMS_SQL.Example
768
- Exercise Seven
769