Index of Oracle PLSQL 19c training course
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
34
- Compiling PLSQL in SQL*Plus
36
- Common Compilation Problems
40
- Commenting PLSQL
42
- Documenting PLSQL
44
Three
The SQL*Plus Interface
45
- Introduction
46
- Anatomy of SQL*Plus
51
- ORA-01017: invalid username/password; logon denied
52
- ORA-12154: TNS:could not resolve the connect identifier
53
- Exiting an SQL*Plus Session
54
- Using the DOS Window
55
- Copying and Pasting
56
- Using the Function Keys
59
- Using the Line Editor in SQL*Plus
60
- Using the Editor in SQL*Plus
61
- SQL*Plus Environment Settings
62
- File Manipulation in SQL*Plus
63
- Spooling Results in SQL*Plus
64
- Changing Passwords / Clearing the Screen in SQL
65
- File Manipulation in SQL*Plus
63
- Exercise One
66
Four
Performing Simple DML in PLSQL
69
- Introduction
70
- Using Local Variables
71
- Declaring Variables Using %Type
74
- Using Substitution Values in PLSQL
78
- Exercise Two
81
Five
Performing Conditional Statements in PLSQL
83
- If .. Then Statements
84
- If .. Then Else Statements
87
- If .. Then Elsif Statements
89
- Combining Tests
90
- Nesting Conditions
91
- Boolean Conditions
92
Six
Communicating from PLSQL
93
- Messaging Via PLSQL
94
- Exercise Three
99
Seven
Exception Handling
101
- Introduction
102
- The When Clause
105
- The When Others Clause
106
- Raise_Application_Error
110
- Oracle Defined Exceptions
114
- Access_into_Null (ORA-06530)
115
- Case_Not_Found (ORA-06592 )
117
- Collection_Is_Null (ORA-06531)
118
- Cursor_Already_Open (ORA-06511)
120
- Dup_Val_On_Index (ORA-00001)
121
- Invalid_Cursor (ORA-01001)
122
- Invalid_Number (ORA-01722)
123
- Login_Denied (ORA-01017)
124
- No_Data_Found (ORA-01403)
126
- Not_Logged_On (ORA-01012)
127
- Program_Error (ORA-06501)
128
- Rowtype_Mismatch (ORA-06504)
129
- Self_is_Null (ORA-30625)
131
- Storage_Error (ORA-06500)
134
- Subscript_Beyond_Count (ORA-06533)
135
- Subscript_outside_limit (ORA-06532)
136
- Sys_Invalid_Rowid (ORA-01410)
138
- Timeout_On_Resource (ORA-00051)
139
- Too_Many_Rows (ORA-01422)
140
- Value_Error (ORA-06502)
141
- Zero_Divide (ORA-01476)
142
- Raising User Defined Exceptions
143
- Pragma Exceptions in PLSQL
146
- Grouping Exceptions in PLSQL
150
- Alternative Way to Handle SQL Errors in PLSQL
151
- Handling SQL Errors in PLSQL
152
- Advanced SQLErrm
154
- Exercise Four
155
Eight
Implicit Cursors
158
- Using Implicit Cursors in PLSQL
159
- Using Implicit Cursors to Select Records
161
- Exceptions in PLSQL
162
- Attributes of Implicit Cursors
164
- SQL%FOUND
165
- SQL%NOTFOUND
166
- SQL%ROWCOUNT
167
Nine
Sub-Blocking in PLSQL
168
- Introduction
169
- Defining
170
- Re-Raising Exceptions in PLSQL
172
- Exercise Five
175
Ten
Explicit Cursors
180
- Using Explicit Cursors in PLSQL
181
- How Explicit Cursors Work
183
- Attributes of an Explicit Cursor
186
- Passing Parameters to Explicit Cursors
189
- Explicit Cursors Versus Implicit Cursors
192
- Exercise Six
193
Eleven
Using Sequences in PLSQL 19c
196
Twelve
Loop Processing
199
- Iterative Processing in PLSQL
200
- Basic Looping in PLSQL
201
- While Looping in PLSQL
205
- For Looping in PLSQL
208
- Using Dates in For Loops in PLSQL
212
- Using Characters in For Loops in PLSQL
213
- Implicit Cursor Looping in PLSQL
214
- Explicit Cursor Looping in PLSQL
217
- Using other Loops with Explicit Cursors
219
- Loop Labels in PLSQL
222
- Loop Continue and Continue When
224
- Exercise Seven
228
Thirteen
PL/SQL Database Objects
234
- PLSQL Objects
235
- Compiling PLSQL Objects
236
- Metadata For PLSQL Objects
237
- Re-Compiling PLSQL Objects
238
- Dropping PLSQL Objects
240
- Passing Parameters in PLSQL
241
- Passing Parameters in PLSQL using Positional Notation
242
- Passing Parameters in PLSQL using Named Notation
244
- Using Mixed Notation in SQL
245
- Metadata (User Source)
247
- Describing PLSQL Objects in SQL*Plus
248
Fourteen
Programmer Defined Functions
249
- Syntax of Functions in Oracle
250
- Suggested Naming Convention of Functions in Oracle
253
- Function Example in PLSQL
254
- Running Functions in SQL and PLSQL
256
- Running Functions in Oracle
257
- Exercise Eight
258
Fifteen
Programmer Defined Procedures
267
- Syntax of Procedures in Oracle
268
- Example of Procedures in Oracle
270
- Executing Procedures in Oracle within SQL Plus
271
- Executing Procedures in Oracle within PLSQL
272
Sixteen
Sub-programming Techniques
273
- Introduction
274
- Example
275
- Exercise Nine
277
Seventeen
Autonomous Transactions in PLSQL
280
- Introduction
281
- Example of an Autonomous Transaction
283
- Rollback in Autonomous Transactions
291
- Using Autonomous Transactions in Triggers
292
Eighteen
Packaging Programmer Defined Programs
295
- Syntax of Packages in Oracle
296
- Compiling of Packages in Oracle
301
- Private Processes in PLSQL Packages
302
- Executing Packages in Oracle
304
- Handling Exceptions in Packages in PLSQL
305
- Exercise Ten
307
Nineteen
DML Triggers in Oracle
308
- Oracle Triggers Introduction
309
- DML Triggers Introduction
310
- DML Triggers Timing Points
312
- DML Triggers When Conditions
313
- DML Triggers When Conditions Examples
314
- DML Triggers Suggested Naming Conventions
316
- Syntax of DML Triggers in PLSQL
318
- Referencing Values within Triggers
319
- Checking the Event which fired the DML Trigger
323
- Using the Follows syntax to dictate Trigger Firing
325
- ORA-25022: cannot reference a trigger of different type
329
- Using the Disable syntax when Compiling Triggers
330
- Enabling/Disabling Triggers in an Oracle Database
331
- Metadata for Triggers (User_Triggers)
333
- Mutating DML Triggers in Oracle
334
- Exercise Eleven
338
Twenty
Instead Of Triggers
345
- Introduction to Instead Of Triggers
346
- Syntax of an Instead Of Trigger in Oracle
349
- Example of an Instead Of Trigger
351
- Deleting Records in Oracle using an Instead Of
359
- Using Follows in an Instead Of Trigger
360
Twenty One
Compound Triggers
361
- Introduction to Compound Triggers
362
- Syntax for Table Compound Triggers
364
- Declaration Section
365
- Timing Points
366
- Before Statement
367
- Before Each Row
368
- After Each Row
369
- After Statement
370
- Compound Triggers for Views
371
Twenty Two
System Triggers
372
- Introduction to System Triggers
373
- Syntax for System Triggers in Oracle
374
- System Event Triggers
375
- User Event Triggers
376
- Schema Event Triggers
380
- Database Level Attributes for System Triggers
386
- Exercise Twelve
387
Twenty Three
Rowtypes in PLSQL
396
- Using %Rowtype in PLSQL
397
- Declaring a Variable as a %Rowtype
398
- Referencing a %Rowtype Variable
399
- Populating a Variable which is a %Rowtype
400
- Passing a %Rowtype between Processes
403
- Declaring a Cursor %Rowtype
405
- Using Rowtype for DML
408
Twenty Four
Advanced Cursors in Oracle 19c
410
- For Update with Explicit Cursors
411
- Where Current of Explicit Cursors
412
- Example of Updating with Explicit Cursors
413
- Using Nowait with Explicit Cursors
414
- Using Skip Locked with Explicit Cursors
415
- Using Wait with an Explicit Cursors
416
- Using Rowid with Explicit Cursors
417
- Using Returning Into in PLSQL
418
- Ref Cursors in PLSQL
424
- Strongly Typed Ref Cursors in PLSQL
425
- Weakly Typed Ref Cursors in PLSQL
427
- Sys_refcursor in PLSQL
428
- Using Sys_Refcursors with SQL
430
Twenty Five
Encrypting Code
434
- Introduction
435
- Using Wrap.exe to encrypt processes in Oracle
436
- Example of using Wrap.exe to encrypt processes
439
- Using DBMS_DDL.CREATE_WRAPPED Function
441
Twenty Six
Savepoints in PLSQL
445
- Using Savepoints in a PLSQL process
446
- Example of Savepoints in PLSQL process
447
Twenty Seven
Case Statements and Case Expressions
448
- Introduction
449
- Using Searched Case in PLSQL
450
- Using Case in PLSQL
451
- Using Nested Case in PLSQL
452
- Using Case Expressions in PLSQL
454
- Using Nullif in PLSQL
458
- Using Coalesce in PLSQL
459
- Exercise Thirteen
460
Twenty Eight
Object Orientated Programming
465
- Introduction
466
- Definition of Objects
467
Twenty Nine
Row Objects
472
- Introduction
473
- Creating
474
- Metadata
475
- Data
476
- Indexes
477
- Views
479
- Removing
480
- OIDs
482
Thirty
Column Objects
490
- Introduction
491
- Describing
492
- Inserting Into
495
- Selecting from
497
- Updating
503
- Object Views
504
Thirty One
Defining Processes within Objects
506
- Introduction
507
- Defining Methods
508
- Member Methods
510
- Map Methods
517
- Member Methods
510
- Order Methods
519
- Constructor Methods
525
Thirty Two
PLSQL Data Structures and Collections
529
- Programmer Defined Records
530
- Nested Programmer Defined Records
534
- Varrays in PLSQL
537
- Using Varrays in Loops
540
- Populating Varrays
541
- Extending Varrays
542
- Deleting from Varrays
544
- Varrays and Tables
545
- Multilevel Varrays
548
Thirty Three
Associate Arrays (Index by Tables)
551
- Introducing Associate Arrays in PLSQL
552
- Declaring PLSQL Tables
554
- Populating PLSQL Tables
555
- Using Count with PLSQL Tables
557
- Using Delete with PLSQL Tables
558
- Using Exists with PLSQL Tables
559
- Using First/Last with PLSQL Tables
560
- Using Next/Prior with PLSQL Tables
561
- Using Varchar2 as an index with a PLSQL Table
562
- Multi-level PLSQL Table
566
- Nested PLSQL Table
567
- Cardinality with Nested Table
570
- Tidying Nested PLSQL Tables using Set
571
- Nested PLSQL Tables using Set
572
- Trimming Nested PLSQL Tables
574
- Multi-Level Nested PLSQL Tables
576
Thirty Four
Collection Comparisons
577
- Introduction
578
- Check for Equality
579
- Using IN with a Nested Table
582
- Using Member Of with a Nested Table
583
- Using Is Empty with a Nested Table
584
- Multiset Union
585
- Multiset Union Distinct
587
- Multiset Intersect
588
- Multiset Except
589
- Submultiset
590
- Not Submultiset
591
- Not Submultiset Alternative
592
- Exercise Fourteen
593
Thirty Five
Bulk SQL in PLSQL
596
- Introduction to Bulk Processing
597
- Bulk Binding in PLSQL
598
- Bulk Binding in PLSQL using Associate Arrays
599
- Bulk Binding in PLSQL using Varrays
600
- Bulk Binding in PLSQL using Nested Tables
601
- Forall with Save Exceptions
603
- Forall with SQL%BULKEXCEPTIONS
604
- Forall with Indices Of
607
- Forall with Values Of
609
- Bulk Collect with Implicit Cursors (Varray)
610
- Bulk Collect with Implicit Cursors (Associative Array)
611
- Bulk Collect with Implicit Cursors (Nested Table)
612
- Bulk Collect with Explicit Cursors
613
- Bulk Collect and Returning in PLSQL
614
- Problems with Bulk Processing in PLSQL
617
- Bulk Processing and Limit in PLSQL
618
Thirty Six
Invoker Rights in PLSQL
619
- The need to use Invoker Rights
620
- Example of Invoker Rights
621
- Using AUTHID in PLSQL
624
Thirty Seven
Forward Declaration
627
Thirty Eight
Advanced Exceptions
630
- Handling Exceptions in Sub-Processes in PLSQL
631
- Using Nocopy when Declaring Processes
633
- Using DBMS_UTILITY Functions
637
- DBMS_UTILITY.FORMAT_ERROR_STACK
638
- DBMS_UTILITY.FORMAT_BACKTRACE
639
- Exercise Fifteen
642
Thirty Nine
Advanced Use of Packages
647
- Introduction
648
- Defining Explicit Cursors in Packages
649
- Flexible Explicit Cursors in Packages
653
- Passing Parameters Using Packages
656
- Using PLSQL Tables as Parameters
659
- Global Variables using Packages
663
- Public and Private Variables in Packages
669
Forty
Overloading Modules in PLSQL
671
- Introduction
672
- Example
673
- User_Procedures
677
- Exercise Sixteen
678
Forty One
Regular Expressions in PLSQL (Regex)
684
- Introduction
685
- Using REGEXP_LIKE in Where Clauses
686
- Case Sensitivity
688
- Line Anchors
689
- Using the Dot (Any Character)
691
- Counting Characters {Intervals}
694
- Character Lists
698
- Excluding Character Lists
702
- Alternatives to Character Lists
703
- Class Shorthands
706
- Or Patterns (Alternatives)
707
- Or Patterns with Character Lists
708
- Using the Question Mark (Optional)
709
- Using the Plus sign (Mandatory)
712
- Using the Star sign (Optional)
714
- Backreferencing
715
- POSIX
717
- Using POSIX in Oracle – [:upper:] example
719
- Using POSIX in Oracle – [:digit:] example
720
- Using POSIX in Oracle – Character equivalents
721
- Using Regexp_like in PLSQL
722
- Using Regexp_count in PLSQL
725
- Using Regexp_replace in PLSQL
729
- Using Regexp_instr in PLSQL
731
- Using Regexp_substr in PLSQL
733
- Using Regular Expressions in Oracle
735
Forty Two
Using Alternative Quoting Mechanism in PLSQL
736
- Introduction
737
- Using Alternative Quoting Mechanism
738
- Exercise Seventeen
739
Forty Three
File I/O
743
- Reading and Writing Files in PLSQL
744
- Accessing the File System using Directories
745
- Creating Directories
746
- Simple Write using UTL_FILE.PUT_LINE
748
- Simple Read Using UTL_FILE.GET_LINE
749
- Additional Functions in the UTL_FILE Package
750
- Using UTL_FILE.PUTF in PLSQL
751
- UTL_FILE Exceptions
753
- Using UTL_FILE to perform Operating System Commands
755
- Using UTL_FILE.Fcopy in PLSQL
756
- Using UTL_FILE.Fgetattr in PLSQL
757
- Using UTL_FILE.Fremove in PLSQL
758
- Using UTL_FILE.Frename in PLSQL
759
Forty Four
Working with Clobs and Blobs in PLSQL
760
- Introduction
761
- Creation of Clobs in Tables
762
- Using Clobs in PLSQL
763
- Inserting Clobs into the Database using PLSQL
764
- Writing Clobs to the Filesystem using PLSQL
768
- Introduction to Blobs
769
- Inserting Blobs into the Database using PLSQL
770
- Writing Blobs into the Filesystem using PLSQL
771
- DBMS_LOB exceptions
774
Forty Five
DBMS_METADATA
776
- Generating Database Object Scripts
777
- List of Object Types
778
- Example of Using Dbms_Metadata
779
- Exercise Eighteen
781
Forty Six
Using Contexts
783
- Introduction
784
- Attributes
785
- User Defined
793
- Creating Contexts
794
- Creating a Package for a Context
795
- Creating a Context
796
- Populating a Context
797
- Accessing a Context
798
- Listing Contents of a Context
799
- Deleting Contents of a Context
801
- Changing Context Behaviour
803
Forty Seven
Subprogram Inlining
804
- Introduction
805
- Pragma Inline
806
- Example of Inlining
807
- Inlining Usage
812
- Conclusion
814
Forty Eight
PLSQL Compiler
815
- Introduction
816
- Warning Levels
817
- Enabling/Disabling Warning Levels
818
- Using the Alter Session
819
- Using the Error option
822
- Using the Dbms_warning Package
824
- Using the Dbms_warning.get_category
825
- Severe Category Errors
826
- Informational Category Errors
828
- Performance Category Errors
830
- Using Dbms_warning.set_warning_setting_string
831
- Using Dbms_warning.set_warning_setting_num
832
- Using Dbms_warning.set_warning_setting_cat
833
- User_plsql_object_settings
834
- Re-Compiling with same Warning settings
836
- PLSQL_Optimize_level
837
- Optimize Levels
838
- Timing Optimization with Dbms_utility.get_cpu_time
839
- Optimize Levels in User_plsql_object_settings
845
- Re-Compiling with same Optimize settings
846
- High Level Optimization without Pragma Inline
847
Forty Nine
Conditional Compilation in PLSQL
849
- Introduction
850
- Dbms_preprocessor
852
- Compiling using PLSQL_CCFLAGS
853
- PLSQL_CCFLAGS in PLSQL
855
- Using $error Directive
863
- Using Constants with CCFlags
865
- $$PLSQL_CODE_TYPE
866
- Oracle Compilation Modes
867
- $$PLSQL_OPTIMIZE_LEVEL
868
- $$PLSQL_LINE
869
- $$PLSQL_UNIT
870
- DBMS_DB_VERSION
871
- Setting Session CCFLags
873
- PLSQL_ccflags in user_plsql_object_settings
874
- PLSQL_ccflags in Triggers
875
Fifty
Timestamps in PLSQL
877
- Timestamps and Intervals in PLSQL
878
- Timestamp Variables in PLSQL
879
- Timestamp in PLSQL
881
- Populating Timestamps in PLSQL
884
- Using Extract in PLSQL
886
- Timestamp with Time Zone
887
- Timestamp Time Zone and Extract
890
- Using sys_extract_utc to Normalise Timestamps
891
- Using the To_Timestamp_TZ function
892
- Using the From_TZ function
894
- Timestamp with Local Time Zone
896
- Intervals
898
- Populating Year to Month Intervals
901
- Populating Day to Second Intervals
902
- Using Intervals in PLSQL
903
- Using Intervals with Extract in PLSQL
908
- Populating Intervals using Timestamps
909
- Exercise Nineteen
910
Fifty One
Dynamic SQL
914
- Introduction to Dynamic SQL
915
- Native Dynamic SQL (NDS) with Execute Immediate
916
- NDS Error Handling
919
- NDS with Inputs
920
- NDS with Output
922
- Dynamic Cursors and Sys_Refcursor
923
- NDS with Outputs
925
- Introduction to DBMS_SQL
926
- DBMS_SQL Example
927
- Exercise Twenty
928