Index of Oracle PLSQL 10g training course
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 (boolean)
30
- Variable Datatypes - Constants
32
- Running PLSQL in SQL*Plus
34
- Compiling PLSQL in SQL*Plus
36
- Common Compilation Problems
39
- Commenting PLSQL
41
- Documenting PLSQL
43
- Exercise One
44
Three
Performing Simple DML in PLSQL
46
- Introduction
47
- Using Local Variables
48
- Declaring Variables Using %Type
51
- Using Substitution Values in PLSQL
55
- Exercise Two
58
Four
Performing Conditional Statements in PLSQL
60
- If .. Then Statements
61
- If .. Then Else Statements
64
- If .. Then Elsif Statements
66
- Combining Tests
67
- Nesting Conditions
68
- Boolean Conditions
69
Five
Communicating from PLSQL
70
- Messaging Via PLSQL
71
- Exercise Three
76
Six
Exception Handling
80
- Introduction
81
- The When Clause
84
- The When Others Clause
87
- Raise_Application_Error
89
- Oracle Defined Exceptions
93
- Access_into_Null (ORA-06530)
94
- Case_Not_Found (ORA-06592 )
96
- Collection_Is_Null (ORA-06531)
97
- Cursor_Already_Open (ORA-06511)
99
- Dup_Val_On_Index (ORA-00001)
100
- Invalid_Cursor (ORA-01001)
101
- Invalid_Number (ORA-01722)
102
- Login_Denied (ORA-01017)
103
- No_Data_Found (ORA-01403)
105
- Not_Logged_On (ORA-01012)
106
- Program_Error (ORA-06501)
107
- Rowtype_Mismatch (ORA-06504)
108
- Self_is_Null (ORA-30625)
110
- Storage_Error (ORA-06500)
113
- Subscript_Beyond_Count (ORA-06533)
114
- Subscript_outside_limit (ORA-06532)
115
- Sys_Invalid_Rowid (ORA-01410)
117
- Timeout_On_Resource (ORA-00051)
118
- Too_Many_Rows (ORA-01422)
119
- Value_Error (ORA-06502)
120
- Zero_divide (ORA-01476)
121
- Raising User Defined Exceptions
122
- Pragma Exceptions in PLSQL
125
- Grouping Exceptions in PLSQL
129
- Alternative Way to Handle SQL Errors in PLSQL
130
- Handling SQL Errors in PLSQL
131
- Advanced SQLErrm
133
- Exercise Four
134
Seven
Implicit Cursors
137
- Using Implicit Cursors in PLSQL
138
- Using Implicit Cursors to Select Records
140
- Exceptions in PLSQL
141
- Attributes of Implicit Cursors
143
- SQL%FOUND
144
- SQL%NOTFOUND
145
- SQL%ROWCOUNT
146
Eight
Sub-Blocking in PLSQL
147
- Introduction
148
- Defining
149
- Re-Raising Exceptions in PLSQL
151
- Exercise Five
154
Nine
Explicit Cursors
158
- Using Explicit Cursors in PLSQL
159
- How Explicit Cursors Work
161
- Attributes of an Explicit Cursor
164
- Passing Parameters to Explicit Cursors
167
- Explicit Cursors Versus Implicit Cursors
170
- Exercise Six
171
Ten
Loop Processing
174
- Iterative Processing in PLSQL
175
- Basic Looping in PLSQL
176
- While Looping in PLSQL
180
- For Looping in PLSQL
183
- Using Dates in For Loops in PLSQL
187
- Using Characters in For Loops in PLSQL
188
- Implicit Cursor Looping in PLSQL
189
- Explicit Cursor Looping in PLSQL
192
- Using other Loops with Explicit Cursors
194
- Loop Labels in PLSQL
197
- Exercise Seven
199
Eleven
Case Statements and Case Expressions
205
- Introduction
206
- Using Searched Case in PLSQL
207
- Using Case in PLSQL
208
- Using Nested Case in PLSQL
209
- Using Case Expressions in PLSQL
211
- Using Nullif in PLSQL
215
- Using Coalesce in PLSQL
216
Twelve
Rowtypes in PLSQL
217
- Using %Rowtype in PLSQL
218
- Declaring a Variable as a %Rowtype
219
- Referencing a %Rowtype Variable
220
- Populating a Variable which is a %Rowtype
221
- Passing a %Rowtype between Processes
224
- Declaring a Cursor %Rowtype
226
Thirteen
Savepoints in PLSQL
229
- Using Savepoints in a PLSQL process
230
- Example of Savepoints in PLSQL process
231
- Exercise Eight
232
Fourteen
PL/SQL Database Objects
236
- PLSQL Objects
237
- Compiling PLSQL Objects
238
- Metadata For PLSQL Objects
239
- Re-Compiling PLSQL Objects
240
- Dropping PLSQL Objects
242
- Passing Parameters in PLSQL
243
- Passing Parameters in PLSQL using Positional Notation
244
- Passing Parameters in PLSQL using Named Notation
246
- Using Mixed Notation in SQL
247
- Metadata (User Source)
249
- Describing PLSQL Objects in SQL*Plus
250
Fifteen
Programmer Defined Functions
251
- Syntax of Functions in Oracle
252
- Suggested Naming Convention of Functions in Oracle
255
- Function Example in PLSQL
256
- Running Functions in SQL and PLSQL
258
- Running Functions in Oracle
259
- Exercise Nine
260
Sixteen
Programmer Defined Procedures
269
- Syntax of Procedures in Oracle
270
- Example of Procedures in Oracle
272
- Executing Procedures in Oracle within SQL Plus
273
- Executing Procedures in Oracle within PLSQL
274
Seventeen
Sub-programming Techniques
275
- Introduction
276
- Example
277
- Exercise Ten
279
Eighteen
Advanced Cursors in Oracle 10g
287
- For Update with Explicit Cursors
288
- Where Current of Explicit Cursors
289
- Example of Updating with Explicit Cursors
290
- Using Nowait with Explicit Cursors
291
- Using Skip Locked with Explicit Cursors
292
- Using Wait with an Explicit Cursors
293
- Using Rowid with Explicit Cursors
294
- Using Returning Into in PLSQL
295
- Ref Cursors in PLSQL
298
- Strongly Typed Ref Cursors in PLSQL
299
- Weakly Typed Ref Cursors in PLSQL
301
- Sys_refcursor in PLSQL
302
- Using Sys_Refcursors with SQL
304
- Using Rowtype for DML
308
Nineteen
Encrypting Code
310
- Introduction
311
- Using Wrap.exe to encrypt processes in Oracle
312
- Example of using Wrap.exe to encrypt processes
315
- Using DBMS_DDL.CREATE_WRAPPED Function
317
Twenty
Invoker Rights in PLSQL
321
- The need to use Invoker Rights
322
- Example of Invoker Rights
323
- Using AUTHID in PLSQL
326
Twenty One
Forward Declaration
329
Twenty Two
Autonomous Transactions in PLSQL
332
- Introduction
333
- Example of an Autonomous Transaction
335
- Rollback in Autonomous Transactions
342
- Using Autonomous Transactions in Triggers
343
Twenty Three
Advanced Exceptions
345
- Handling Exceptions in Sub-Processes in PLSQL
346
- Using Nocopy when Declaring Processes
348
- Using DBMS_UTILITY Functions
352
- DBMS_UTILITY.FORMAT_ERROR_STACK
353
- DBMS_UTILITY.FORMAT_BACKTRACE
354
- Exercise Eleven
357
Twenty Four
Packaging Programmer Defined Programs
360
- Syntax of Packages in Oracle
361
- Compiling of Packages in Oracle
366
- Private Processes in PLSQL Packages
367
- Executing Packages in Oracle
369
- Handling Exceptions in Packages in PLSQL
370
Twenty Five
Overloading Modules in PLSQL
372
- Introduction
373
- Example
374
- User_Procedures
378
- Exercise Twelve
379
Twenty Six
DML Triggers in Oracle
386
- Oracle Triggers Introduction
387
- DML Triggers Introduction
388
- DML Triggers Timing Points
391
- DML Triggers When Conditions
392
- DML Triggers When Conditions Examples
393
- DML Triggers Suggested Naming Conventions
395
- Syntax of DML Triggers in PLSQL
397
- Referencing Values within Triggers
398
- Checking the Event which fired the DML Trigger
402
- Enabling/Disabling Triggers in an Oracle Database
404
- Metadata for Triggers (User_Triggers)
406
- Mutating DML Triggers in Oracle
407
- Exercise Thirteen
411
Twenty Seven
Instead Of Triggers
418
- Introduction to Instead Of Triggers
419
- Syntax of an Instead Of Trigger in Oracle
422
- Example of an Instead Of Trigger
424
- Deleting Records in Oracle using an Instead Of
432
Twenty Eight
System Triggers
433
- Introduction to System Triggers
434
- Syntax for System Triggers in Oracle
435
- System Event Triggers
436
- User Event Triggers
437
- Schema Event Triggers
441
- Database Level Attributes for System Triggers
447
- Exercise Fourteen
448
Twenty Nine
Regular Expressions in PLSQL (Regex)
452
- Introduction
453
- Using REGEXP_LIKE in Where Clauses
454
- Case Sensitivity
456
- Line Anchors
457
- Using the Dot (Any Character)
459
- Counting Characters {Intervals}
462
- Character Lists
466
- Excluding Character Lists
470
- Alternatives to Character Lists
471
- Class Shorthands
474
- Or Patterns (Alternatives)
475
- Or Patterns with Character Lists
476
- Using the Question Mark (Optional)
477
- Using the Plus sign (Mandatory)
480
- Using the Star sign (Optional)
482
- Backreferencing
483
- POSIX
485
- Using POSIX in Oracle - [:upper:] example
487
- Using POSIX in Oracle - [:digit:] example
488
- Using POSIX in Oracle - Character equivalents
489
- Using Regexp_like in PLSQL
490
- Using Regexp_count in PLSQL
493
- Using Regexp_replace in PLSQL
497
- Using Regexp_instr in PLSQL
499
- Using Regexp_substr in PLSQL
501
- Using Regular Expressions in Oracle
503
Thirty
Using Alternative Quoting Mechanism in PLSQL
504
- Introduction
505
- Using Alternative Quoting Mechanism
506
- Exercise Fifteen
507
Thirty One
File I/O
511
- Reading and Writing Files in PLSQL
512
- Accessing the File System using Directories
513
- Creating Directories
514
- Simple Write using UTL_FILE.PUT_LINE
516
- Simple Read Using UTL_FILE.GET_LINE
517
- Additional Functions in the UTL_FILE Package
518
- Using UTL_FILE.PUTF in PLSQL
519
- UTL_FILE Exceptions
521
- Using UTL_FILE to perform Operating System Commands
523
- Using UTL_FILE.Fcopy in PLSQL
524
- Using UTL_FILE.Fgetattr in PLSQL
525
- Using UTL_FILE.Fremove in PLSQL
526
- Using UTL_FILE.Frename in PLSQL
527
Thirty Two
Working with Clobs and Blobs in PLSQL
528
- Introduction
529
- Creation of Clobs in Tables
530
- Using Clobs in PLSQL
531
- Inserting Clobs into the Database using PLSQL
532
- Writing Clobs to the Filesystem using PLSQL
536
- Introduction to Blobs
537
- Inserting Blobs into the Database using PLSQL
538
- Writing Blobs into the Filesystem using PLSQL
539
- DBMS_LOB exceptions
542
Thirty Three
DBMS_METADATA
544
- Generating Database Object Scripts
545
- List of Object Types
546
- Example of Using Dbms_Metadata
547
- Exercise Sixteen
549
Thirty Four
Using Contexts
551
- Introduction
552
- Attributes
553
- User Defined
561
- Creating Contexts
562
- Creating a Package for a Context
563
- Creating a Context
564
- Populating a Context
565
- Accessing a Context
566
- Listing Contents of a Context
567
- Deleting Contents of a Context
569
- Changing Context Behaviour
571
Thirty Five
PLSQL Compiler
572
- Introduction
573
- Warning Levels
574
- Enabling/Disabling Warning Levels
575
- Using the Alter Session
576
- Using the Error option
579
- Using the Dbms_warning Package
580
- Using the Dbms_warning.get_category
581
- Severe Category Errors
582
- Informational Category Errors
584
- Performance Category Errors
586
- Using Dbms_warning.set_warning_setting_string
587
- Using Dbms_warning.set_warning_setting_num
588
- Using Dbms_warning.set_warning_setting_cat
589
- User_plsql_object_settings
590
- Re-Compiling with same Warning settings
592
- PLSQL_Optimize_level
593
- Optimize Levels
594
- Timing Optimization with Dbms_utility.get_cpu_time
595
- Optimize Levels in User_plsql_object_settings
601
- Re-Compiling with same Optimize settings
602
Thirty Six
Conditional Compilation in PLSQL
603
- Introduction
604
- Dbms_preprocessor
606
- Compiling using PLSQL_CCFLAGS
607
- PLSQL_CCFLAGS in PLSQL
609
- Using $error Directive
617
- Using Constants with CCFlags
619
- $$PLSQL_CODE_TYPE
620
- $$PLSQL_OPTIMIZE_LEVEL
621
- $$PLSQL_LINE
622
- $$PLSQL_UNIT
623
- DBMS_DB_VERSION
624
- Setting Session CCFLags
626
- PLSQL_ccflags in user_plsql_object_settings
627
- PLSQL_ccflags in Triggers
628
Thirty Seven
Timestamps in PLSQL
630
- Timestamps and Intervals in PLSQL
631
- Timestamp Variables in PLSQL
632
- Timestamp in PLSQL
634
- Populating Timestamps in PLSQL
637
- Using Extract in PLSQL
639
- Timestamp with Time Zone
640
- Timestamp Time Zone and Extract
643
- Using sys_extract_utc to Normalise Timestamps
644
- Using the To_Timestamp_TZ function
645
- Using the From_TZ function
647
- Timestamp with Local Time Zone
649
- Intervals
651
- Populating Year to Month Intervals
654
- Populating Day to Second Intervals
655
- Using Intervals in PLSQL
656
- Using Intervals with Extract in PLSQL
661
- Populating Intervals using Timestamps
662
- Exercise Seventeen
663
Thirty Eight
PLSQL Data Structures and Collections
667
- Programmer Defined Records
668
- Nested Programmer Defined Records
672
- Varrays in PLSQL
674
- Using Varrays in Loops
677
- Populating Varrays
678
- Extending Varrays
679
- Deleting from Varrays
681
- Varrays and Tables
682
- Multilevel Varrays
685
Thirty Nine
Associate Arrays (Index by Tables)
688
- Introducing Associate Arrays in PLSQL
689
- Declaring PLSQL Tables
691
- Populating PLSQL Tables
692
- Using Count with PLSQL Tables
694
- Using Delete with PLSQL Tables
695
- Using Exists with PLSQL Tables
696
- Using First/Last with PLSQL Tables
697
- Using Next/Prior with PLSQL Tables
698
- Using Varchar2 as an index with a PLSQL Table
699
- Multi-level PLSQL Table
703
- Nested PLSQL Table
704
- Cardinality with Nested Table
707
- Tidying Nested PLSQL Tables using Set
708
- Nested PLSQL Tables using Set
709
- Trimming Nested PLSQL Tables
711
- Multi-Level Nested PLSQL Tables
713
Forty
Collection Comparisons
714
- Introduction
715
- Check for Equality
716
- Using IN with a Nested Table
719
- Using Member Of with a Nested Table
720
- Using Is Empty with a Nested Table
721
- Multiset Union
722
- Multiset Union Distinct
724
- Multiset Intersect
725
- Multiset Except
726
- Submultiset
727
- Not Submultiset
728
- Not Submultiset Alternative
729
- Exercise Eighteen
730
Forty One
Bulk SQL in PLSQL
732
- Introduction to Bulk Processing
733
- Bulk Binding in PLSQL
734
- Bulk Binding in PLSQL using Associate Arrays
735
- Bulk Binding in PLSQL using Varrays
736
- Bulk Binding in PLSQL using Nested Tables
737
- Forall with Save Exceptions
738
- Forall with SQL%BULKEXCEPTIONS
740
- Forall with Indices Of
743
- Forall with Values Of
745
- Bulk Collect with Implicit Cursors (Varray)
746
- Bulk Collect with Implicit Cursors (Associative Array)
747
- Bulk Collect with Implicit Cursors (Nested Table)
748
- Bulk Collect with Explicit Cursors
749
- Bulk Collect and Returning in PLSQL
750
- Problems with Bulk Processing in PLSQL
753
- Bulk Processing and Limit in PLSQL
754
Forty Two
Advanced Use of Packages
755
- Introduction
756
- Defining Explicit Cursors in Packages
757
- Flexible Explicit Cursors in Packages
761
- Passing Parameters Using Packages
764
- Using PLSQL Tables as Parameters
767
- Global Variables using Packages
771
- Public and Private Variables in Packages
777
Forty Three
Dynamic SQL
779
- Introduction to Dynamic SQL
780
- Native Dynamic SQL (NDS) with Execute Immediate
781
- NDS Error Handling
784
- NDS with Inputs
785
- NDS with Output
787
- Dynamic Cursors and Sys_Refcursor
788
- NDS with Outputs
790
- Introduction to DBMS_SQL
791
- DBMS_SQL Example
792
- Exercise Nineteen
793