Download our latest
Android and iPhone apps ...
At a glance ...
Pages
293
Exercises
5
Sections
20
Public
On-Site
Level
2
Days
2

Course Name: PLSQL II

Duration:
Minimum skill level required:
On-Site course:
Public course:
Sections:
No of exercises:
No of pages in the manual:
Pre-Requisite course(s):
Suggested further course(s):
Price guide:
2 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
20
5
293
Oracle Essential SQL 12c Oracle Essential PLSQL 12c
None
Contact us for the latest pricing

Synopsis for course

This is the second part of the PLSQL courses and deals with very advanced techniques.
Emphasis is also given to enhancements to PLSQL introduced in later versions of the Oracle Database.
This course is only suitable to Developers who have already attended the Essential PLSQL course or are experienced Developers wishing to update their skills.

Exercise highlights

Database triggers
Overloading
Case statements in PLSQL
PLSQL tables

Course Contents

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