Section
Contents
Page
One
PLSQL Introduction
2
- Procedural Language for SQL
3
Two
PLSQL Structure
5
- Basic Structure of PLSQL
6
- The Declaration Section in PLSQL
7
- Local Variables in PLSQL
8
- Naming Local Variables in PLSQL
10
- Quoted Identifier Variables
12
- Variable Datatypes (varchar2)
16
- Variable Datatypes (numbers)
17
- Variable Datatypes (number)
18
- Variable Datatypes (decimal)
21
- Variable Datatypes (integer)
22
- Variable Datatypes (pls_integer)
23
- Variable Datatypes (binary_integer)
24
- Variable Datatypes (natural)
25
- Variable Datatypes (naturaln)
26
- Variable Datatypes (positive)
27
- Variable Datatypes (positiven)
28
- Variable Datatypes (signtype)
29
- Variable Datatypes (simple_integer)
30
- Variable Datatypes (boolean)
31
- Variable Datatypes - Constants
33
- Running PLSQL in SQL*Plus
35
- Compiling PLSQL in SQL*Plus
37
- Common Compilation Problems
40
- Commenting PLSQL
42
- Documenting PLSQL
44
- Exercise One
45
Three
Performing Simple DML in PLSQL
47
- Introduction
48
- Using Local Variables
49
- Declaring Variables Using %Type
52
- Using Substitution Values in PLSQL
56
- Exercise Two
59
Four
Performing Conditional Statements in PLSQL
61
- If .. Then Statements
62
- If .. Then Else Statements
65
- If .. Then Elsif Statements
67
- Combining Tests
68
- Nesting Conditions
69
- Boolean Conditions
70
Five
Communicating from PLSQL
71
- Messaging Via PLSQL
72
- Exercise Three
77
Six
Exception Handling
81
- Introduction
82
- The When Clause
85
- The When Others Clause
88
- Raise_Application_Error
90
- Oracle Defined Exceptions
94
- Access_into_Null (ORA-06530)
95
- Case_Not_Found (ORA-06592 )
97
- Collection_Is_Null (ORA-06531)
98
- Cursor_Already_Open (ORA-06511)
100
- Dup_Val_On_Index (ORA-00001)
101
- Invalid_Cursor (ORA-01001)
102
- Invalid_Number (ORA-01722)
103
- Login_Denied (ORA-01017)
104
- No_Data_Found (ORA-01403)
106
- Not_Logged_On (ORA-01012)
107
- Program_Error (ORA-06501)
108
- Rowtype_Mismatch (ORA-06504)
109
- Self_is_Null (ORA-30625)
111
- Storage_Error (ORA-06500)
114
- Subscript_Beyond_Count (ORA-06533)
115
- Subscript_outside_limit (ORA-06532)
116
- Sys_Invalid_Rowid (ORA-01410)
118
- Timeout_On_Resource (ORA-00051)
119
- Too_Many_Rows (ORA-01422)
120
- Value_Error (ORA-06502)
121
- Zero_divide (ORA-01476)
122
- Raising User Defined Exceptions
123
- Pragma Exceptions in PLSQL
126
- Grouping Exceptions in PLSQL
130
- Alternative Way to Handle SQL Errors in PLSQL
131
- Handling SQL Errors in PLSQL
132
- Advanced SQLErrm
134
- Exercise Four
135
Seven
Implicit Cursors
138
- Using Implicit Cursors in PLSQL
139
- Using Implicit Cursors to Select Records
141
- Exceptions in PLSQL
142
- Attributes of Implicit Cursors
144
- SQL%FOUND
145
- SQL%NOTFOUND
146
- SQL%ROWCOUNT
147
Eight
Sub-Blocking in PLSQL
148
- Introduction
149
- Defining
150
- Re-Raising Exceptions in PLSQL
152
- Exercise Five
155
Nine
Explicit Cursors
159
- Using Explicit Cursors in PLSQL
160
- How Explicit Cursors Work
162
- Attributes of an Explicit Cursor
165
- Passing Parameters to Explicit Cursors
168
- Explicit Cursors Versus Implicit Cursors
171
- Exercise Six
172
Ten
Loop Processing
175
- Iterative Processing in PLSQL
176
- Basic Looping in PLSQL
177
- While Looping in PLSQL
181
- For Looping in PLSQL
184
- Using Dates in For Loops in PLSQL
188
- Using Characters in For Loops in PLSQL
189
- Implicit Cursor Looping in PLSQL
190
- Explicit Cursor Looping in PLSQL
193
- Using other Loops with Explicit Cursors
195
- Loop Labels in PLSQL
198
- Loop Continue and Continue When
200
- Exercise Seven
204
Eleven
Case Statements and Case Expressions
210
- Introduction
211
- Using Searched Case in PLSQL
212
- Using Case in PLSQL
213
- Using Nested Case in PLSQL
214
- Using Case Expressions in PLSQL
216
- Using Nullif in PLSQL
220
- Using Coalesce in PLSQL
221
Twelve
Rowtypes in PLSQL
222
- Using %Rowtype in PLSQL
223
- Declaring a Variable as a %Rowtype
224
- Referencing a %Rowtype Variable
225
- Populating a Variable which is a %Rowtype
226
- Passing a %Rowtype between Processes
229
- Declaring a Cursor %Rowtype
231
Thirteen
Using Sequences in PLSQL 11g
234
Fourteen
Savepoints in PLSQL
237
- Using Savepoints in a PLSQL process
238
- Example of Savepoints in PLSQL process
239
- Exercise Eight
240
Fifteen
PL/SQL Database Objects
244
- PLSQL Objects
245
- Compiling PLSQL Objects
246
- Metadata For PLSQL Objects
247
- Re-Compiling PLSQL Objects
248
- Dropping PLSQL Objects
250
- Passing Parameters in PLSQL
251
- Passing Parameters in PLSQL using Positional Notation
252
- Passing Parameters in PLSQL using Named Notation
254
- Using Mixed Notation in SQL
255
- Metadata (User Source)
257
- Describing PLSQL Objects in SQL*Plus
258
Sixteen
Programmer Defined Functions
259
- Syntax of Functions in Oracle
260
- Suggested Naming Convention of Functions in Oracle
263
- Function Example in PLSQL
264
- Running Functions in SQL and PLSQL
266
- Running Functions in Oracle
267
- Exercise Nine
268
Seventeen
Programmer Defined Procedures
277
- Syntax of Procedures in Oracle
278
- Example of Procedures in Oracle
280
- Executing Procedures in Oracle within SQL Plus
281
- Executing Procedures in Oracle within PLSQL
282
- Exercise Ten
268
Eighteen
Sub-programming Techniques
283
- Introduction
284
- Example
285
Nineteen
Advanced Cursors in Oracle 11g
295
- For Update with Explicit Cursors
296
- Where Current of Explicit Cursors
297
- Example of Updating with Explicit Cursors
298
- Using Nowait with Explicit Cursors
299
- Using Skip Locked with Explicit Cursors
300
- Using Wait with an Explicit Cursors
301
- Using Rowid with Explicit Cursors
302
- Using Returning Into in PLSQL
303
- Ref Cursors in PLSQL
306
- Strongly Typed Ref Cursors in PLSQL
307
- Weakly Typed Ref Cursors in PLSQL
309
- Sys_refcursor in PLSQL
310
- Using Sys_Refcursors with SQL
312
- Using Rowtype for DML
316
Twenty
Encrypting Code
318
- Introduction
319
- Using Wrap.exe to encrypt processes in Oracle
320
- Example of using Wrap.exe to encrypt processes
323
- Using DBMS_DDL.CREATE_WRAPPED Function
325
Twenty One
Invoker Rights in PLSQL
329
- The need to use Invoker Rights
330
- Example of Invoker Rights
331
- Using AUTHID in PLSQL
334
Twenty Two
Forward Declaration
337
Twenty Three
Autonomous Transactions in PLSQL
340
- Introduction
341
- Example of an Autonomous Transaction
343
- Rollback in Autonomous Transactions
350
- Using Autonomous Transactions in Triggers
351
Twenty Four
Advanced Exceptions
353
- Handling Exceptions in Sub-Processes in PLSQL
354
- Using Nocopy when Declaring Processes
356
- Using DBMS_UTILITY Functions
360
- DBMS_UTILITY.FORMAT_ERROR_STACK
361
- DBMS_UTILITY.FORMAT_BACKTRACE
362
- Exercise Eleven
365
Twenty Five
Packaging Programmer Defined Programs
368
- Syntax of Packages in Oracle
369
- Compiling of Packages in Oracle
374
- Private Processes in PLSQL Packages
375
- Executing Packages in Oracle
377
- Handling Exceptions in Packages in PLSQL
378
Twenty Six
Overloading Modules in PLSQL
380
- Introduction
381
- Example
382
- User_Procedures
386
- Exercise Twelve
387
Twenty Seven
DML Triggers in Oracle
394
- Oracle Triggers Introduction
395
- DML Triggers Introduction
396
- DML Triggers Timing Points
399
- DML Triggers When Conditions
400
- DML Triggers When Conditions Examples
401
- DML Triggers Suggested Naming Conventions
403
- Syntax of DML Triggers in PLSQL
405
- Referencing Values within Triggers
406
- Checking the Event which fired the DML Trigger
410
- Using the Follows syntax to dictate Trigger Firing
412
- Using the Disable syntax when Compiling Triggers
417
- Enabling/Disabling Triggers in an Oracle Database
418
- Metadata for Triggers (User_Triggers)
420
- Mutating DML Triggers in Oracle
421
- Exercise Thirteen
425
Twenty Eight
Instead Of Triggers
432
- Introduction to Instead Of Triggers
433
- Syntax of an Instead Of Trigger in Oracle
436
- Example of an Instead Of Trigger
438
- Deleting Records in Oracle using an Instead Of
446
- Using Follows in an Instead Of Trigger
447
Twenty Nine
Compound Triggers
448
- Introduction to Compound Triggers
449
- Syntax for Table Compound Triggers
451
- Declaration Section
452
- Timing Points
453
- Before Statement
454
- Before Each Row
455
- After Each Row
456
- After Statement
457
- Compound Triggers for Views
458
Thirty
System Triggers
459
- Introduction to System Triggers
460
- Syntax for System Triggers in Oracle
461
- System Event Triggers
462
- User Event Triggers
463
- Schema Event Triggers
467
- Database Level Attributes for System Triggers
473
- Exercise Fourteen
474
Thirty One
Regular Expressions in PLSQL (Regex)
482
- Introduction
483
- Using REGEXP_LIKE in Where Clauses
484
- Case Sensitivity
486
- Line Anchors
487
- Using the Dot (Any Character)
489
- Counting Characters {Intervals}
492
- Character Lists
496
- Excluding Character Lists
500
- Alternatives to Character Lists
501
- Class Shorthands
504
- Or Patterns (Alternatives)
505
- Or Patterns with Character Lists
506
- Using the Question Mark (Optional)
507
- Using the Plus sign (Mandatory)
510
- Using the Star sign (Optional)
512
- Backreferencing
513
- POSIX
515
- Using POSIX in Oracle - [:upper:] example
517
- Using POSIX in Oracle - [:digit:] example
518
- Using POSIX in Oracle - Character equivalents
519
- Using Regexp_like in PLSQL
520
- Using Regexp_count in PLSQL
523
- Using Regexp_replace in PLSQL
527
- Using Regexp_instr in PLSQL
529
- Using Regexp_substr in PLSQL
531
- Using Regular Expressions in Oracle
533
Thirty Two
Using Alternative Quoting Mechanism in PLSQL
534
- Introduction
535
- Using Alternative Quoting Mechanism
536
- Exercise Fifteen
537
Thirty Three
File I/O
541
- Reading and Writing Files in PLSQL
542
- Accessing the File System using Directories
543
- Creating Directories
544
- Simple Write using UTL_FILE.PUT_LINE
546
- Simple Read Using UTL_FILE.GET_LINE
547
- Additional Functions in the UTL_FILE Package
548
- Using UTL_FILE.PUTF in PLSQL
549
- UTL_FILE Exceptions
551
- Using UTL_FILE to perform Operating System Commands
553
- Using UTL_FILE.Fcopy in PLSQL
554
- Using UTL_FILE.Fgetattr in PLSQL
555
- Using UTL_FILE.Fremove in PLSQL
556
- Using UTL_FILE.Frename in PLSQL
557
Thirty Four
Working with Clobs and Blobs in PLSQL
558
- Introduction
559
- Creation of Clobs in Tables
560
- Using Clobs in PLSQL
561
- Inserting Clobs into the Database using PLSQL
562
- Writing Clobs to the Filesystem using PLSQL
566
- Introduction to Blobs
567
- Inserting Blobs into the Database using PLSQL
568
- Writing Blobs into the Filesystem using PLSQL
569
- DBMS_LOB exceptions
572
Thirty Five
DBMS_METADATA
574
- Generating Database Object Scripts
575
- List of Object Types
576
- Example of Using Dbms_Metadata
577
- Exercise Sixteen
579
Thirty Six
Using Contexts
581
- Introduction
582
- Attributes
583
- User Defined
591
- Creating Contexts
592
- Creating a Package for a Context
593
- Creating a Context
594
- Populating a Context
595
- Accessing a Context
596
- Listing Contents of a Context
597
- Deleting Contents of a Context
599
- Changing Context Behaviour
601
Thirty Seven
Subprogram Inlining
602
- Introduction
603
- Pragma Inline
604
- Example of Inlining
605
- Inlining Usage
610
- Conclusion
612
Thirty Eight
PLSQL Compiler
613
- Introduction
614
- Warning Levels
615
- Enabling/Disabling Warning Levels
616
- Using the Alter Session
617
- Using the Error option
620
- Using the Dbms_warning Package
622
- Using the Dbms_warning.get_category
623
- Severe Category Errors
624
- Informational Category Errors
626
- Performance Category Errors
628
- Oracle Error Messages
629
- Using Dbms_warning.set_warning_setting_string
630
- Using Dbms_warning.set_warning_setting_num
631
- Using Dbms_warning.set_warning_setting_cat
632
- User_plsql_object_settings
633
- Re-Compiling with same Warning settings
635
- PLSQL_Optimize_level
636
- Optimize Levels
637
- Timing Optimization with Dbms_utility.get_cpu_time
638
- Optimize Levels in User_plsql_object_settings
644
- Re-Compiling with same Optimize settings
645
- High Level Optimization without Pragma Inline
646
Thirty Nine
Conditional Compilation in PLSQL
648
- Introduction
649
- Dbms_preprocessor
651
- Compiling using PLSQL_CCFLAGS
652
- PLSQL_CCFLAGS in PLSQL
654
- Using $error Directive
662
- Using Constants with CCFlags
664
- $$PLSQL_CODE_TYPE
665
- Oracle Compilation Modes
666
- $$PLSQL_OPTIMIZE_LEVEL
667
- $$PLSQL_LINE
668
- $$PLSQL_UNIT
669
- DBMS_DB_VERSION
670
- Setting Session CCFLags
672
- PLSQL_ccflags in user_plsql_object_settings
673
- PLSQL_ccflags in Triggers
674
Forty
Timestamps in PLSQL
676
- Timestamps and Intervals in PLSQL
677
- Timestamp Variables in PLSQL
678
- Timestamp in PLSQL
680
- Populating Timestamps in PLSQL
683
- Using Extract in PLSQL
685
- Timestamp with Time Zone
686
- Timestamp Time Zone and Extract
689
- Using sys_extract_utc to Normalise Timestamps
690
- Using the To_Timestamp_TZ function
691
- Using the From_TZ function
693
- Timestamp with Local Time Zone
695
- Intervals
697
- Populating Year to Month Intervals
700
- Populating Day to Second Intervals
701
- Using Intervals in PLSQL
702
- Using Intervals with Extract in PLSQL
707
- Populating Intervals using Timestamps
708
- Exercise Seventeen
709
Forty One
PLSQL Data Structures and Collections
713
- Programmer Defined Records
714
- Nested Programmer Defined Records
718
- Varrays in PLSQL
720
- Using Varrays in Loops
723
- Populating Varrays
724
- Extending Varrays
725
- Deleting from Varrays
727
- Varrays and Tables
728
- Multilevel Varrays
731
Forty Two
Associate Arrays (Index by Tables)
734
- Introducing Associate Arrays in PLSQL
735
- Declaring PLSQL Tables
737
- Populating PLSQL Tables
738
- Using Count with PLSQL Tables
740
- Using Delete with PLSQL Tables
741
- Using Exists with PLSQL Tables
742
- Using First/Last with PLSQL Tables
743
- Using Next/Prior with PLSQL Tables
744
- Using Varchar2 as an index with a PLSQL Table
745
- Multi-level PLSQL Table
749
- Nested PLSQL Table
750
- Cardinality with Nested Table
753
- Tidying Nested PLSQL Tables using Set
754
- Nested PLSQL Tables using Set
755
- Trimming Nested PLSQL Tables
757
- Multi-Level Nested PLSQL Tables
759
Forty Three
Collection Comparisons
760
- Introduction
761
- Check for Equality
762
- Using IN with a Nested Table
765
- Using Member Of with a Nested Table
766
- Using Is Empty with a Nested Table
767
- Multiset Union
768
- Multiset Union Distinct
770
- Multiset Intersect
771
- Multiset Except
772
- Submultiset
773
- Not Submultiset
774
- Not Submultiset Alternative
775
- Exercise Eighteen
776
Forty Four
Bulk SQL in PLSQL
778
- Introduction to Bulk Processing
779
- Bulk Binding in PLSQL
780
- Bulk Binding in PLSQL using Associate Arrays
781
- Bulk Binding in PLSQL using Varrays
782
- Bulk Binding in PLSQL using Nested Tables
783
- Forall with Save Exceptions
784
- Forall with SQL%BULKEXCEPTIONS
786
- Forall with Indices Of
789
- Forall with Values Of
791
- Bulk Collect with Implicit Cursors (Varray)
792
- Bulk Collect with Implicit Cursors (Associative Array)
793
- Bulk Collect with Implicit Cursors (Nested Table)
794
- Bulk Collect with Explicit Cursors
795
- Bulk Collect and Returning in PLSQL
796
- Problems with Bulk Processing in PLSQL
799
- Bulk Processing and Limit in PLSQL
800
Forty Five
Advanced Use of Packages
801
- Introduction
802
- Defining Explicit Cursors in Packages
803
- Flexible Explicit Cursors in Packages
807
- Passing Parameters Using Packages
810
- Using PLSQL Tables as Parameters
813
- Global Variables using Packages
817
- Public and Private Variables in Packages
823
Forty Six
Dynamic SQL
825
- Introduction to Dynamic SQL
826
- Native Dynamic SQL (NDS) with Execute Immediate
827
- NDS Error Handling
830
- NDS with Inputs
831
- NDS with Output
833
- Dynamic Cursors and Sys_Refcursor
834
- NDS with Outputs
836
- Introduction to DBMS_SQL
837
- DBMS_SQL Example
838
- Exercise Nineteen
839