Download our latest
Android and iPhone apps ...
At a glance ...
Pages
754
Exercises
7
Sections
28
Public
On-Site
Level
2
Days
3

Course Name: Advanced PLSQL 10g

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:
3 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
28
7
754
Oracle Essential SQL 10g Oracle Essential PLSQL 10g
None
Contact us for the latest pricing

Synopsis for course

A three day course developed particularly for Developers/Analysts who have experience in the fundamentals of PLSQL but require additional knowledge particularly in the area of new developments since Oracle 9i, 10g and 11g, please refer to the index for this course for full details.

PLSQL language is Oracle's versatile procedural extension to SQL, which capitalises on SQL's functionality and can increase database efficiency, effectiveness, scalability, portability and security.

Course Contents

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