Index of Oracle 9i PLSQL training course
Section
Section Name
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 (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 9i
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
Nineteen
Encrypting Code
308
- Introduction
309
- Using Wrap.exe to encrypt processes in Oracle
310
- Example of using Wrap.exe to encrypt processes
313
Twenty
Invoker Rights in PLSQL
315
- The need to use Invoker Rights
316
- Example of Invoker Rights
317
- Using AUTHID in PLSQL
320
Twenty One
Forward Declaration
323
Twenty Two
Autonomous Transactions in PLSQL
326
- Introduction
327
- Example of an Autonomous Transaction
329
- Rollback in Autonomous Transactions
336
- Using Autonomous Transactions in Triggers
337
Twenty Three
Advanced Exceptions
339
- Handling Exceptions in Sub-Processes in PLSQL
340
- Using Nocopy when Declaring Processes
342
- Exercise Eleven
346
Twenty Four
Packaging Programmer Defined Programs
349
- Syntax of Packages in Oracle
350
- Compiling of Packages in Oracle
355
- Private Processes in PLSQL Packages
356
- Executing Packages in Oracle
358
- Handling Exceptions in Packages in PLSQL
359
Twenty Five
Overloading Modules in PLSQL
361
- Introduction
362
- Example
363
- User_Procedures
367
- Exercise Twelve
368
Twenty Six
DML Triggers in Oracle
375
- Oracle Triggers Introduction
376
- DML Triggers Introduction
377
- DML Triggers Timing Points
380
- DML Triggers When Conditions
381
- DML Triggers When Conditions Examples
382
- DML Triggers Suggested Naming Conventions
384
- Syntax of DML Triggers in PLSQL
386
- Referencing Values within Triggers
387
- Checking the Event which fired the DML Trigger
391
- Enabling/Disabling Triggers in an Oracle Database
393
- Metadata for Triggers (User_Triggers)
395
- Mutating DML Triggers in Oracle
396
- Exercise Thirteen
400
Twenty Seven
Instead Of Triggers
407
- Introduction to Instead Of Triggers
408
- Syntax of an Instead Of Trigger in Oracle
411
- Example of an Instead Of Trigger
413
- Deleting Records in Oracle using an Instead Of
421
Twenty Eight
System Triggers
422
- Introduction to System Triggers
423
- Syntax for System Triggers in Oracle
424
- System Event Triggers
425
- User Event Triggers
426
- Schema Event Triggers
430
- Database Level Attributes for System Triggers
436
- Exercise Fourteen
437
Twenty Nine
File I/O
441
- Reading and Writing Files in PLSQL
442
- Simple Write using UTL_FILE.PUT_LINE
444
- Simple Read Using UTL_FILE.GET_LINE
445
- Additional Functions in the UTL_FILE Package
446
- Using UTL_FILE.PUTF in PLSQL
447
- UTL_FILE Exceptions
449
- Using UTL_FILE to perform Operating System Commands
451
- Using UTL_FILE.Fcopy in PLSQL
452
- Using UTL_FILE.Fgetattr in PLSQL
453
- Using UTL_FILE.Fremove in PLSQL
454
- Using UTL_FILE.Frename in PLSQL
455
Thirty
Working with Clobs and Blobs in PLSQL
456
- Introduction
457
- Creation of Clobs in Tables
458
- Using Clobs in PLSQL
459
- Inserting Clobs into the Database using PLSQL
460
- Writing Clobs to the Filesystem using PLSQL
464
- Introduction to Blobs
465
- Inserting Blobs into the Database using PLSQL
466
- Writing Blobs into the Filesystem using PLSQL
467
- DBMS_LOB exceptions
470
Thirty One
DBMS_METADATA
472
- Generating Database Object Scripts
473
- List of Object Types
474
- Example of Using Dbms_Metadata
475
- Exercise Fifteen
477
Thirty Two
Using Contexts
479
- Introduction
480
- Attributes
481
- User Defined
489
- Creating Contexts
490
- Creating a Package for a Context
491
- Creating a Context
492
- Populating a Context
493
- Accessing a Context
494
- Listing Contents of a Context
495
- Deleting Contents of a Context
497
- Changing Context Behaviour
499
Thirty Three
Timestamps in PLSQL
500
- Timestamps and Intervals in PLSQL
501
- Timestamp Variables in PLSQL
502
- Timestamp in PLSQL
504
- Populating Timestamps in PLSQL
507
- Using Extract in PLSQL
509
- Timestamp with Time Zone
510
- Timestamp Time Zone and Extract
513
- Using sys_extract_utc to Normalise Timestamps
514
- Using the To_Timestamp_TZ function
515
- Using the From_TZ function
517
- Timestamp with Local Time Zone
519
- Intervals
521
- Populating Year to Month Intervals
524
- Populating Day to Second Intervals
525
- Using Intervals in PLSQL
526
- Using Intervals with Extract in PLSQL
531
- Populating Intervals using Timestamps
532
- Exercise Sixteen
533
Thirty Four
PLSQL Data Structures and Collections
536
- Programmer Defined Records
537
- Nested Programmer Defined Records
541
- Varrays in PLSQL
543
- Using Varrays in Loops
546
- Populating Varrays
547
- Extending Varrays
548
- Deleting from Varrays
550
- Varrays and Tables
551
- Multilevel Varrays
554
Thirty Five
Associate Arrays (Index by Tables)
557
- Introducing Associate Arrays in PLSQL
558
- Declaring PLSQL Tables
560
- Populating PLSQL Tables
561
- Using Count with PLSQL Tables
563
- Using Delete with PLSQL Tables
564
- Using Exists with PLSQL Tables
565
- Using First/Last with PLSQL Tables
566
- Using Next/Prior with PLSQL Tables
567
- Using Varchar2 as an index with a PLSQL Table
568
- Multi-level PLSQL Table
572
- Nested PLSQL Table
573
- Trimming Nested PLSQL Tables
576
- Multi-Level Nested PLSQL Tables
578
- Exercise Seventeen
579
Thirty Six
Bulk SQL in PLSQL
581
- Introduction to Bulk Processing
582
- Bulk Binding in PLSQL
583
- Bulk Binding in PLSQL using Associate Arrays
584
- Bulk Binding in PLSQL using Varrays
585
- Bulk Binding in PLSQL using Nested Tables
586
- Forall with Save Exceptions
587
- Forall with SQL%BULKEXCEPTIONS
589
- Bulk Collect with Implicit Cursors (Varray)
592
- Bulk Collect with Implicit Cursors (Associative Array)
593
- Bulk Collect with Implicit Cursors (Nested Table)
594
- Bulk Collect with Explicit Cursors
595
- Bulk Collect and Returning in PLSQL
596
- Problems with Bulk Processing in PLSQL
599
- Bulk Processing and Limit in PLSQL
600
Thirty Seven
Advanced Use of Packages
601
- Introduction
602
- Defining Explicit Cursors in Packages
603
- Flexible Explicit Cursors in Packages
607
- Passing Parameters Using Packages
610
- Using PLSQL Tables as Parameters
613
- Global Variables using Packages
617
- Public and Private Variables in Packages
623
Forty Three
Dynamic SQL
625
- Introduction to Dynamic SQL
626
- Native Dynamic SQL (NDS) with Execute Immediate
627
- NDS Error Handling
630
- NDS with Inputs
631
- NDS with Output
633
- Dynamic Cursors and Sys_Refcursor
634
- NDS with Outputs
636
- Introduction to DBMS_SQL
637
- DBMS_SQL Example
638
- Exercise Eighteen
639
