Index of Advanced PLSQL 10g training course
Section
Contents
One
Advanced PL/SQL 10g 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
- Exercise One
29
Three
Advanced PLSQL 10g Techniques
33
- Defining Sub-Blocks in PLSQL
34
- Variables in PLSQL
40
- Quoted Identifier Variables in PLSQL
41
- Numeric Variables in PLSQL
44
- Number Variables in PLSQL
45
- Decimal Variables in PLSQL
48
- Integer Variables in PLSQL
49
- PLS_Integer Variables in PLSQL
50
- Binary_Integer Variables in PLSQL
51
- Natural Variables in PLSQL
52
- Naturaln Variables in PLSQL
53
- Positive Variables in PLSQL
54
- Positiven Variables in PLSQL
55
- Signtype Variables in PLSQL
56
- Defining Constants in PLSQL
57
- Using %Rowtype in PLSQL
59
- Declaring a Variable as a %Rowtype
60
- Referencing a %Rowtype Variable
61
- Populating a Variable which is a %Rowtype
62
- Passing a %Rowtype between Processes
65
- Declaring a Cursor %Rowtype
67
- Using Savepoints in a PLSQL process
70
- Example of Savepoints in a PLSQL process
71
- Timestamps and Intervals in PLSQL
72
- Timestamp Variables in PLSQL
73
- Timestamp in PLSQL
75
- Populating Timestamps in PLSQL
78
- Using Extract in PLSQL
80
- Timestamp with Time Zone
81
- Timestamp Time Zone and Extract
84
- Using sys_extract_utc to Normalise Timestamps
85
- Using the To_Timestamp_TZ function
86
- Using the From_TZ function
88
- Timestamp with Local Time Zone
90
- Intervals
92
- Populating Year to Month Intervals
95
- Populating Day to Second Intervals
96
- Using Intervals in PLSQL
97
- Using Intervals with Extract in PLSQL
102
- Populating Intervals using Timestamps
103
Four
Case Statements and Case Expressions
104
- Introduction
105
- Using Searched Case in PLSQL
106
- Using Case in PLSQL
107
- Using Nested Case in PLSQL
108
- Using Case Expressions in PLSQL
110
- Using Nullif in PLSQL
114
- Using Coalesce in PLSQL
115
- Exercise Two
116
Five
Advanced Cursors in Oracle 10g
119
- For Update with Explicit Cursors
120
- Where Current Of Explicit Cursors
121
- Example of Updating with Explicit Cursors
122
- Using Nowait with Explicit Cursors
123
- Using Skip Locked with Explicit Cursors
124
- Using Wait with an Explicit Cursor
125
- Using Rowid with Explicit Cursors
126
- Using Returning Into in PLSQL
127
- Ref Cursors in PLSQL
130
- Strongly Typed Ref Cursors in PLSQL
131
- Weakly Typed Ref Cursors in PLSQL
133
- Sys_refcursor in PLSQL
134
- Using Sys_Refcursors with SQL
136
- Using Rowtype for DML
140
Six
Error Handling in PLSQL 10g
142
- Exceptions in PLSQL
143
- Testing for Exceptions in PLSQL
144
- Informing the User of the Error
146
- Raise_Application_Error Parameters
148
- Oracle Defined Exceptions
149
- Access_into_Null (ORA-06530 )
150
- Case_Not_Found (ORA-06592 )
152
- Collection_Is_Null (ORA-06531 )
153
- Cursor_Already_Open (ORA-06511 )
155
- Dup_Val_On_Index (ORA-00001 )
156
- Invalid_Cursor (ORA-01001 )
157
- Invalid_Number (ORA-01722 )
158
- Login_Denied (ORA-01017 )
159
- No_Data_Found (ORA-01403 )
161
- Not_Logged_On (ORA-01012 )
162
- Program_Error (ORA-06501 )
163
- Rowtype_Mismatch (ORA-06504 )
164
- Self_is_Null (ORA-30625 )
166
- Storage_Error (ORA-06500 )
169
- Subscript_Beyond_Count (ORA-06533 )
170
- Subscript_outside_limit (ORA-06532)
171
- Sys_Invalid_Rowid (ORA-01410)
173
- Timeout_On_Resource (ORA-00051)
174
- Too_Many_Rows (ORA-01422 )
175
- Value_Error (ORA-06502 )
176
- Zero_divide (ORA-01476 )
177
- Others
178
- Raising User Defined Exceptions in PLSQL
179
- Pragma Exceptions in PLSQL
182
- Alternative Way to Handle SQL Errors in PLSQL
184
- Handling SQL Errors in PLSQL
185
- Advanced SQLErrm
187
- Nesting Exceptions in PLSQL
188
- Re-Raising Exceptions in PLSQL
191
- Handling Exceptions in Sub-Processes in PLSQL
193
- Using Nocopy When Declaring Processes
197
- Using DBMS_UTILITY Functions
201
- Using DBMS_UTILITY.FORMAT_ERROR_STACK
202
- Using DBMS_UTILITY.FORMAT_BACKTRACE
203
Seven
PL/SQL Database Objects
206
- PLSQL Objects
207
- Compiling PLSQL Objects
208
- Metadata For PLSQL Objects
209
- Re-Compiling PLSQL Objects
210
- Dropping PLSQL Objects
212
- Passing Parameters in PLSQL
213
- Passing Parameters using Positional Notation
214
- Passing Parameters Using Named Notation
216
- Using Mixed Notation in SQL
217
- Metadata (User_Source)
219
- Describing PLSQL Objects in SQL PLUS
220
Eight
Programmer Defined Functions
221
- Syntax of Functions in Oracle
222
- Suggested Naming Convention of Functions in Oracle
225
- Function Example in PLSQL
226
- Running Functions in Oracle
227
Nine
Programmer Defined Procedures
229
- Syntax of Procedures in Oracle
230
- Example of Procedures in Oracle
232
- Executing Procedures in Oracle within SQLPLUS
233
- Executing Procedures in Oracle within PLSQL
234
- Exercise Three
235
Ten
Packaging Programmer Defined Programs
241
- Syntax of Packages in Oracle
242
- Compiling of Packages in Oracle
247
- Private Processes in PLSQL Packages
248
- Executing Packages in Oracle
250
- Handling Exceptions in Packages in PLSQL
251
Eleven
DML Triggers in Oracle
253
- Oracle Triggers Introduction
254
- DML Triggers Introduction
255
- DML Triggers Timing Points
258
- DML Triggers When Conditions
259
- DML Triggers When Conditions Examples
260
- DML Triggers Suggested Naming Conventions
262
- Syntax of DML Triggers in PLSQL
264
- Referencing Values within Triggers
265
- Checking the Event which fired the DML Trigger
269
- Enabling/Disabling Triggers in an Oracle Database
271
- Metadata for Triggers (User_Triggers)
273
- Mutating DML Triggers in Oracle
274
Twelve
Instead Of Triggers
278
- Introduction to Instead Of Triggers
279
- Syntax of an Instead Of Trigger in Oracle
282
- Example of an Instead Of Trigger
284
- Deleting Records in Oracle using an Instead Of
292
Thirteen
System Triggers
293
- Introduction to System Triggers
294
- Syntax for System Triggers in Oracle
295
- System Event Triggers
296
- User Event Triggers
297
- Schema Event Triggers
301
- Database Level Attributes for System Triggers
307
- Exercise Four
308
Fourteen
Using Contexts
313
- Introduction
314
- Attributes
315
- User Defined
323
- Creating Contexts
324
- Creating a Package for a Context
325
- Creating a Context
326
- Populating a Context
327
- Accessing a Context
328
- Listing Contents of a Context
329
- Deleting Contents of a Context
331
- Changing Context Behaviour
333
Fifteen
PLSQL Compiler
334
- Introduction
335
- Warning Levels
336
- Enabling/Disabling Warning Levels
337
- Using the Alter Session
338
- Using the Error option
341
- Using DBMS_WARNING Package
343
- Using DBMS_WARNING.GET_CATEGORY
344
- Severe Category Errors
345
- Informational Category Errors
347
- Performance Category Errors
349
- Using dbms_warning.set_warning_setting_string
350
- Using dbms_warning.set_warning_setting_num
351
- Using dbms_warning.set_warning_setting_cat
352
- USER_PLSQL_OBJECT_SETTINGS
353
- Re-Compiling with same Warning settings
355
- PLSQL_OPTIMIZE_LEVEL
356
- Optimize Levels
357
- Timing Optimization with dbms_utility.get_cpu_time
358
- Optimize Levels in user_plsql_object_settings
364
- Re-Compiling with same Optimize settings
365
Sixteen
Conditional Compilation in PLSQL
366
- Introduction
367
- Dbms_preprocessor
369
- Compiling using PLSQL_CCFLAGS
370
- PLSQL_CCFLAGS in PLSQL
372
- Using $error Directive
380
- Using Constants with CCFlags
382
- $$PLSQL_CODE_TYPE
383
- $$PLSQL_OPTIMIZE_LEVEL
384
- $$PLSQL_LINE
385
- $$PLSQL_UNIT
386
- DBMS_DB_VERSION
387
- Setting Session CCFlags
389
- Plsql_ccflags in user_plsql_object_settings
390
- Plsql_ccflags in Triggers
391
Seventeen
Regular Expressions in PLSQL (Regex)
393
- Introduction
394
- Using REGEXP_LIKE in Where Clauses
395
- Case Sensitivity
397
- Line Anchors
398
- Using the Dot (Any Character)
400
- Counting Characters {Intervals}
403
- Character Lists
407
- Excluding Character Lists
411
- Alternatives to Character Lists
412
- Class Shorthands
415
- Or Patterns (Alternatives)
416
- Or Patterns with Character Lists
417
- Using the Question Mark (Optional)
418
- Using the Plus sign (Mandatory)
421
- Using the Star sign (Optional)
423
- Backreferencing
424
- POSIX
426
- Using POSIX in Oracle - [:upper] example
428
- Using POSIX in Oracle - [:digit] example
429
- Using POSIX in Oracle - Character equivalents
430
- Using Regexp_like in PLSQL
431
- Using Regexp_count in PLSQL
434
- Using Regexp_replace in PLSQL
438
- Using Regexp_instr in PLSQL
440
- Using Regexp_substr in PLSQL
442
- Using Regular Expressions in Oracle
444
Eighteen
Using Alternative Quoting Mechanism in PLSQL
445
- Introduction
446
- Using Alternative Quoting Mechanism
447
- Exercise Five
448
Nineteen
File I/O
453
- Reading and Writing from the Operating System in PLSQL
454
- Accessing the File System using Directories
455
- Creating Directories
456
- Simple Write using UTL_FILE.PUT_LINE
458
- Simple Read using UTL_FILE.GET_LINE
459
- Additional Functions in the UTL_FILE Package
460
- Using UTL_FILE.PUTF in PLSQL
461
- UTL_FILE Exceptions
463
- Using UTL_FILE to perform Operating System Commands
465
- Using UTL_FILE.Fcopy in PLSQL
466
- Using UTL_FILE.Fgetattr in PLSQL
467
- Using UTL_FILE.Fremove in PLSQL
468
- Using UTL_FILE.Frename in PLSQL
469
Twenty
Working with Clobs and Blobs in PLSQL
470
- Introduction
471
- Creation of Clobs in Tables
472
- Using Clobs in PLSQL
473
- Inserting Clobs into the Database using PLSQL
474
- Writing Clobs to the Filesystem using PLSQL
478
- Introduction to Blobs
479
- Inserting Blobs into the Database using PLSQL
480
- Writing Blobs into the Filesystem using PLSQL
481
- DBMS_LOB exceptions
484
Twenty One
DBMS_Metadata
486
- Generating Database Object Scripts
487
- List of Object Types
488
- Example of Using DBMS_Metadata
489
Twenty Two
Sub-programming Techniques
491
- Introduction
492
- Example
493
Twenty Three
Encrypting Code
495
- Introduction
496
- Using Wrap.exe to encrypt processes in Oracle
497
- Example of using Wrap.exe to encrypt processes
500
- Using DBMS_DDL.CREATE_WRAPPED Function
502
Twenty Four
Object Orientated Programming
506
- Introduction
507
- Definition of Objects
508
Twenty Five
Row Objects
512
- Introduction
513
- Creating
514
- Metadata
515
- Data
516
- Indexes
517
- Views
519
- Removing
520
- OIDs
522
Twenty Six
Column Objects
529
- Introduction
530
- Describing
531
- Inserting into
534
- Selecting from
536
- Updating
541
- Object Views
542
Twenty Seven
Defining Processes within Objects
544
- Introduction
545
- Defining Methods
546
- Member Methods
548
- Map Methods
555
- Order Methods
557
- Constructor Methods
563
Twenty Eight
PLSQL Data Structures and Collections
567
- Programmer Defined Records
568
- Nested Programmer Defined Records
572
- Varrays in PLSQL
574
- Using Varrays in Loops
577
- Populating Varrays
578
- Extending Varrays
579
- Deleting from Varrays
581
- Varrays and Tables
582
- Multilevel Varrays
585
Twenty Nine
Associate Arrays (Index by Tables)
588
- Introducing Associate Arrays in PLSQL
589
- Declaring PLSQL Tables
591
- Populating PLSQL Tables
592
- Using Count with PLSQL Tables
594
- Using Delete with PLSQL Tables
595
- Using Exists with PLSQL Tables
596
- Using First/Last with PLSQL Tables
597
- Using Next/Prior with PLSQL Tables
598
- Using Varchar2 as an index with a PLSQL Table
599
- Multi-Level PLSQL Table
603
- Nested PLSQL Table
604
- Cardinality with Nested Table
607
- Tidying Nested PLSQL Tables using Set
608
- Nested PLSQL Tables using Set
609
- Trimming Nested PLSQL Tables
611
- Multi-Level Nested PLSQL Tables
613
Thirty
Collection Comparisons
614
- Introduction
615
- Check for Equality
616
- Using IN with a Nested Table
619
- Using Member Of with a Nested Table
620
- Using Is Empty with a Nested Table
621
- Multiset Union
622
- Multiset Union Distinct
624
- Multiset Intersect
625
- Multiset Except
626
- Submultiset
627
- Not Submultiset
628
- Not Submultiset Alternative
629
- Exercise Six
630
Thirty One
Bulk SQL in PLSQL
633
- Introduction to Bulk Processing
634
- Bulk Binding in PLSQL
635
- Bulk Binding in PLSQL Using Associate Arrays
636
- Bulk Binding in PLSQL Using Varrays
637
- Bulk Binding in PLSQL Using Nested Tables
638
- Forall with Save Exceptions
639
- Forall with SQL%BULKEXCEPTIONS
641
- Forall with Indices Of
644
- Forall with Values Of
646
- Bulk Collect with Implicit Cursors (Varray)
647
- Bulk Collect with Implicit Cursors (Associative Array)
648
- Bulk Collect with Implicit Cursors (Nested Table)
649
- Bulk Collect with Explicit Cursors
650
- Bulk Collect and Returning in PLSQL
651
- Problems with Bulk Processing in PLSQL
654
- Bulk Processing and Limit in PLSQL
655
Thirty Two
Invoker Rights in PLSQL
656
- The need to use Invoker Rights
657
- Example of Invoker Rights
658
- Using AUTHID in PLSQL
661
Thirty Three
Overloading Modules in PLSQL
664
- Introduction
665
- Example
666
- User_Procedures
670
Thirty Four
Forward Declaration
671
Thirty Five
Autonomous Transactions in PLSQL
674
- Introduction
675
- Example of an Autonomous Transaction
677
- Rollback in Autonomous Transactions
684
- Using Autonomous Transactions in Triggers
685
Thirty Six
Advanced Use of Packages
687
- Introduction
688
- Defining Explicit Cursors in Packages
689
- Flexible Explicit Cursors in Packages
693
- Passing Parameters Using Packages
696
- Using PLSQL Tables as Parameters
699
- Global Variables using Packages
703
- Public and Private Variables in Packages
709
Thirty Seven
Dynamic SQL
711
- Introduction to Dynamic SQL
712
- Native Dynamic SQL (NDS) with Execute Immediate
713
- NDS Error Handling
716
- NDS with Inputs
717
- NDS with Output
719
- Dynamic Cursors and Sys_Refcursor
720
- NDS with Outputs
722
- Introduction to DBMS_SQL
723
- DBMS_SQL.Example
724
Thirty Eight
Virtual Private Databases (VPD)
725
- Introduction
726
- DBMS_RLS
727
- Creating Functions for use with VPD
728
- Applying Policies
730
- Viewing Policies in Metadata
733
- Row Level Security (RLS)
734
- Removing Row Level Security (RLS)
735
- Using Policy Types with DBMS_RLS
737
- Introducing Flexibility to VPD
738
- Column Level Masks
740
- Using Sec_Relevant_Cols
741
- Using Sec_Relevant_Cols_Opt
744
- Exercise Seven
747
