Index of Oracle PLSQL II training course
Section
Contents
Page
One
Introduction
2
- Introduction
3
Two
Advanced Techniques
4
- Loop Labels in PLSQL
5
- Loop Continue & Continue When in PLSQL
7
- Defining Constants in PLSQL
11
Three
Autonomous Transactions in PLSQL
13
- Introduction
14
- Example of an Autonomous Transaction
16
- Using Autonomous Transactions in Triggers
23
Four
Invoker Rights in PLSQL
25
- The need to use Invoker Rights
26
- Example of Invoker Rights
27
- Using AUTHID in PLSQL
30
Five
Overloading Modules in PLSQL
33
- Introduction
34
- Example
35
- User_Procedures
39
Six
Using Nocopy in PLSQL
40
- Introduction
41
- Using Nocopy When Declaring Processes
42
- Exercise One
47
Seven
Forward Declaration
54
Eight
Advanced Cursors in PLSQL
57
- Using Embedded Cursors in Oracle
58
- Using Correlated Embedded Cursors in Oracle
65
- Using Rowtype for DML
66
Nine
Advanced Error Handling in PLSQL
68
- Introduction
69
- Re-Raising Exceptions in PLSQL
70
- Handling Exceptions in Sub-Processes in Oracle
72
- Using DBMS_UTILITY Functions
75
- Using DBMS_UTILITY.FORMAT_ERROR_STACK
76
- Using DBMS_UTILITY.FORMAT_BACKTRACE
77
Ten
Private Processing in Packages
80
- Introduction
81
- Private Processes in PLSQL Packages
82
- Handling Exceptions in Packages in PLSQL
84
- Exercise Two
86
Eleven
Instead of Triggers
93
- Introduction
94
- Syntax of an Instead of Trigger in Oracle
97
- Example of an Instead of Trigger
99
- Deleting Records in Oracle using an Instead Of
107
Twelve
System Triggers
108
- Introduction
109
- Syntax for System Triggers in Oracle
110
- System Event Triggers
111
- User Event Triggers
112
- Schema Event Triggers
116
- Database Level Attributes for System Triggers
122
- Exercise Three
123
Thirteen
PLSQL Compiler
128
- Introduction
129
- Warning Levels
130
- Enabling/Disabling Warning Levels
131
- Using the Alter Session
132
- Using the Error Option
135
- Using DBMS_WARNING Package
137
- Using DBMS_WARNING.GET_CATEGORY
138
- Severe Category Errors
139
- Informational Category Errors
141
- Performance Category Errors
143
- Oracle Error Messages
144
- Using Dbms_Warning.Set_Warning_Setting_String
145
- Using Dbms_Warning.Set_Warning_Setting_Num
146
- Using Dbms_Warning.Set_Warning_Setting_Cat
146
- User_PLSQL_Object_Settings
148
- Re-Compiling with same Warning settings
150
- PLSQL_OPTIMIZE_LEVEL
151
- Optimize Levels
152
- Timing Optimization with Dbms_utility,get_cpu_time
153
- Optimize Levels in User_plsql_object_settings
159
- Re-Compiling with same Optimize settings
160
- High Level Optimization without Pragma Inline
161
Fourteen
Working with Clobs and Blobs in PLSQL
163
- Accessing the File System using Directories
164
- Creating Directories
165
- Introduction
167
- Creation of Clobs in Tables
168
- Using Clobs in PLSQL
169
- Inserting Clobs into the Database using PLSQL
170
- Writing Clobs to the Filesystem using PLSQL
174
- Introduction to Blobs
175
- Inserting Blobs into the Database using PLSQL
176
- Writing Blobs into the Filesystem using PLSQL
177
- DBMS_LOB exceptions
180
Fifteen
DBMS_METADATA
182
- Generating Database Object Scripts
183
- List of Object Types
184
- Example of Using DBMS_METADATA
185
Sixteen
Encrypting Code
187
- Introduction
188
- Using Wrap.exe to encrypt processes in Oracle
189
- Example of using Wrap.exe to encrypt processes
192
- Using Dbms_DDL.Create_Wrapped Function
194
Seventeen
PLSQL Data Structures and Collections
198
- Programmer Defined Records
199
- Nested Programmer Defined Records
203
- Varrays in PLSQL
205
- Using Varrays in Loops
208
- Populating Vassrays
209
- Extending Varrays
210
- Deleting from Varrays
212
- Varrays and Tables
213
- Multilevel Varrays
216
- Exercise Four
219
Eighteen
Associate Arrays (Index by Tables)
221
- Introducing Associate Arrays in PLSQL
222
- Declaring PLSQL Tables
224
- Populating PLSQL Tables
225
- Using Count with PLSQL Tables
227
- Using Delete with PLSQL Tables
228
- Using Exists with PLSQL Tables
229
- Using First/Last with PLSQL Tables
230
- Using Next/Prior with PLSQL Tables
231
- Using Varchar2 as an index with PLSQL Tables
232
- Multi-Level PLSQL Table
236
- Nested PLSQL Table
237
- Cardinality with Nested Table
240
- Tidying Nested PLSQL Tables using Set
241
- Nested PLSQL Tables using Set
242
- Trimming Nested PLSQL Tables
244
- Multi-Level Nested PLSQL Tables
246
Nineteen
Associate Arrays (Index by Tables)
247
- Introduction
248
- Check for Equality
249
- Using IN with a Nested Table
252
- Using Member Of with a Nested Table
252
- Using Is Empty with a Nested Table
252
- Multiset Union
255
- Multiset Union Distinct
257
- Multiset Intersect
258
- Multiset Except
259
- Submultiset
260
- Not Submultiset
261
- Not Submultiset Alternative
262
Twenty
Bulk SQL in PLSQL
263
- Introduction to Bulk Processing
264
- Bulk Binding in PLSQL
265
- Bulk Binding in PLSQL using Associate Arrays
266
- Bulk Binding in PLSQL using Varrays
267
- Bulk Binding in PLSQL using Nested Tables
268
- Forall with Save Exceptions
269
- Forall with SQL%BULKEXCEPTIONS
271
- Forall with Indices Of
274
- Forall with Values Of
276
- Bulk Collect with Implicit Cursors (Varray)
277
- Bulk Collect with Implicit Cursors (Associative Array)
278
- Bulk Collect with Implicit Cursors (Nested Table)
279
- Bulk Collect with Explicit Cursors
280
- Bulk Collect and Returning in PLSQL
281
- Problems with Bulk Processing in PLSQL
284
- Bulk Processing and Limit in PLSQL
285
- Exercise Five
286
