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

Course Name: Advanced PLSQL 11g

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
39
7
798
Oracle Essential SQL 11g Oracle Essential PLSQL 11g
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 below 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 11g 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

 
- Loop Continue and Continue When in PLSQL
29

 
- Exercise One
33

Three
Advanced PLSQL 11g Techniques
37

 
- Defining Sub-Blocks in PLSQL
38

 
- Variables in PLSQL
44

 
- Quoted Identifier Variables in PLSQL
45

 
- Numeric Variables in PLSQL
48

 
- Number Variables in PLSQL
49

 
- Decimal Variables in PLSQL
52

 
- Integer Variables in PLSQL
53

 
- PLS_Integer Variables in PLSQL
54

 
- Binary_Integer Variables in PLSQL
55

 
- Natural Variables in PLSQL
56

 
- Naturaln Variables in PLSQL
57

 
- Positive Variables in PLSQL
58

 
- Positiven Variables in PLSQL
59

 
- Signtype Variables in PLSQL
60

 
- Simple_Integer Variables in PLSQL
61

 
- Defining Constants in PLSQL
62

 
- Using %Rowtype in PLSQL
64

 
- Declaring a Variable as a %Rowtype
65

 
- Referencing a %Rowtype Variable
66

 
- Populating a Variable which is a %Rowtype
67

 
- Passing a %Rowtype between Processes
70

 
- Declaring a Cursor %Rowtype
72

 
- Using Sequences in PLSQL 11g
75

 
- Using Savepoints in a PLSQL process
76

 
- Example of Savepoints in a PLSQL process
77

 
- Timestamps and Intervals in PLSQL
78

 
- Timestamp Variables in PLSQL
79

 
- Timestamp in PLSQL
81

 
- Populating Timestamps in PLSQL
84

 
- Using Extract in PLSQL
86

 
- Timestamp with Time Zone
87

 
- Timestamp Time Zone and Extract
90

 
- Using sys_extract_utc to Normalise Timestamps
91

 
- Using the To_Timestamp_TZ function
92

 
- Using the From_TZ function
94

 
- Timestamp with Local Time Zone
96

 
- Intervals
98

 
- Populating Year to Month Intervals
101

 
- Populating Day to Second Intervals
102

 
- Using Intervals in PLSQL
103

 
- Using Intervals with Extract in PLSQL
108

 
- Populating Intervals using Timestamps
109

Four
Case Statements and Case Expressions
110

 
- Introduction
111

 
- Using Searched Case in PLSQL
112

 
- Using Case in PLSQL
113

 
- Using Nested Case in PLSQL
114

 
- Using Case Expressions in PLSQL
116

 
- Using Nullif in PLSQL
120

 
- Using Coalesce in PLSQL
121

 
- Exercise Two
122

Five
Advanced Cursors in Oracle 11g
125

 
- For Update with Explicit Cursors
126

 
- Where Current Of Explicit Cursors
127

 
- Example of Updating with Explicit Cursors
128

 
- Using Nowait with Explicit Cursors
129

 
- Using Skip Locked with Explicit Cursors
130

 
- Using Wait with an Explicit Cursor
131

 
- Using Rowid with Explicit Cursors
132

 
- Using Returning Into in PLSQL
133

 
- Ref Cursors in PLSQL
136

 
- Strongly Typed Ref Cursors in PLSQL
137

 
- Weakly Typed Ref Cursors in PLSQL
139

 
- Sys_refcursor in PLSQL
140

 
- Using Sys_Refcursors with SQL
142

 
- Using Rowtype for DML
146

Six
Error Handling in PLSQL 11g
148

 
- Exceptions in PLSQL
149

 
- Testing for Exceptions in PLSQL
150

 
- Informing the User of the Error
152

 
- Raise_Application_Error Parameters
154

 
- Oracle Defined Exceptions
155

 
- Access_into_Null (ORA-06530 )
156

 
- Case_Not_Found (ORA-06592 )
158

 
- Collection_Is_Null (ORA-06531 )
159

 
- Cursor_Already_Open (ORA-06511 )
161

 
- Dup_Val_On_Index (ORA-00001 )
162

 
- Invalid_Cursor (ORA-01001 )
163

 
- Invalid_Number (ORA-01722 )
164

 
- Login_Denied (ORA-01017 )
165

 
- No_Data_Found (ORA-01403 )
167

 
- Not_Logged_On (ORA-01012 )
168

 
- Program_Error (ORA-06501 )
169

 
- Rowtype_Mismatch (ORA-06504 )
170

 
- Self_is_Null (ORA-30625 )
172

 
- Storage_Error (ORA-06500 )
175

 
- Subscript_Beyond_Count (ORA-06533 )
176

 
- Subscript_outside_limit (ORA-06532)
177

 
- Sys_Invalid_Rowid (ORA-01410)
179

 
- Timeout_On_Resource (ORA-00051)
180

 
- Too_Many_Rows (ORA-01422 )
181

 
- Value_Error (ORA-06502 )
182

 
- Zero_divide (ORA-01476 )
183

 
- Others
184

 
- Raising User Defined Exceptions in PLSQL
185

 
- Pragma Exceptions in PLSQL
188

 
- Alternative Way to Handle SQL Errors in PLSQL
190

 
- Handling SQL Errors in PLSQL
191

 
- Advanced SQLErrm
193

 
- Nesting Exceptions in PLSQL
194

 
- Re-Raising Exceptions in PLSQL
197

 
- Handling Exceptions in Sub-Processes in PLSQL
199

 
- Using Nocopy When Declaring Processes
203

 
- Using DBMS_UTILITY Functions
207

 
- Using DBMS_UTILITY.FORMAT_ERROR_STACK
208

 
- Using DBMS_UTILITY.FORMAT_BACKTRACE
209

Seven
PL/SQL Database Objects
212

 
- PLSQL Objects
213

 
- Compiling PLSQL Objects
214

 
- Metadata For PLSQL Objects
215

 
- Re-Compiling PLSQL Objects
216

 
- Dropping PLSQL Objects
218

 
- Passing Parameters in PLSQL
219

 
- Passing Parameters using Positional Notation
220

 
- Passing Parameters Using Named Notation
222

 
- Using Mixed Notation in SQL
223

 
- Metadata (User_Source)
225

 
- Describing PLSQL Objects in SQL PLUS
226

Eight
Programmer Defined Functions
227

 
- Syntax of Functions in Oracle
228

 
- Suggested Naming Convention of Functions in Oracle
231

 
- Function Example in PLSQL
232

 
- Running Functions in Oracle
233

Nine
Programmer Defined Procedures
235

 
- Syntax of Procedures in Oracle
236

 
- Example of Procedures in Oracle
238

 
- Executing Procedures in Oracle within SQLPLUS
239

 
- Executing Procedures in Oracle within PLSQL
240

 
- Exercise Three
241

Ten
Packaging Programmer Defined Programs
247

 
- Syntax of Packages in Oracle
248

 
- Compiling of Packages in Oracle
253

 
- Private Processes in PLSQL Packages
254

 
- Executing Packages in Oracle
256

 
- Handling Exceptions in Packages in PLSQL
257

Eleven
DML Triggers in Oracle
259

 
- Oracle Triggers Introduction
260

 
- DML Triggers Introduction
261

 
- DML Triggers Timing Points
264

 
- DML Triggers When Conditions
265

 
- DML Triggers When Conditions Examples
266

 
- DML Triggers Suggested Naming Conventions
268

 
- Syntax of DML Triggers in PLSQL
270

 
- Referencing Values within Triggers
271

 
- Checking the Event which fired the DML Trigger
275

 
- Using the Follows syntax to dictate Trigger Firing
277

 
- Using the Disable syntax when Compiling Triggers
282

 
- Enabling/Disabling Triggers in an Oracle Database
283

 
- Metadata for Triggers (User_Triggers)
285

 
- Mutating DML Triggers in Oracle
286

Twelve
Instead Of Triggers
290

 
- Introduction to Instead Of Triggers
291

 
- Syntax of an Instead Of Trigger in Oracle
294

 
- Example of an Instead Of Trigger
296

 
- Deleting Records in Oracle using an Instead Of
304

 
- Using Follows in an Instead Of Trigger
305

Thirteen
Compound Triggers
306

 
- Introduction to Compound Triggers
307

 
- Syntax for Table Compound Triggers
309

 
- Declaration Section
310

 
- Timing Points
311

 
- BEFORE STATEMENT
312

 
- BEFORE EACH ROW
313

 
- AFTER EACH ROW
314

 
- AFTER STATEMENT
315

 
- Compound Triggers for Views
316

Fourteen
System Triggers
317

 
- Introduction to System Triggers
318

 
- Syntax for System Triggers in Oracle
319

 
- System Event Triggers
320

 
- User Event Triggers
321

 
- Schema Event Triggers
325

 
- Database Level Attributes for System Triggers
331

 
- Exercise Four
332

Fifteen
Using Contexts
341

 
- Introduction
342

 
- Attributes
343

 
- User Defined
351

 
- Creating Contexts
352

 
- Creating a Package for a Context
353

 
- Creating a Context
354

 
- Populating a Context
355

 
- Accessing a Context
356

 
- Listing Contents of a Context
357

 
- Deleting Contents of a Context
359

 
- Changing Context Behaviour
361

Sixteen
Subprogram Inlining
362

 
- Introduction
363

 
- Pragma Inline
364

 
- Example of Inlining
365

 
- Inlining Usage
370

 
- Conclusion
372

Seventeen
PLSQL Compiler
373

 
- Introduction
374

 
- Warning Levels
375

 
- Enabling/Disabling Warning Levels
376

 
- Using the Alter Session
377

 
- Using the Error option
380

 
- Using DBMS_WARNING Package
382

 
- Using DBMS_WARNING.GET_CATEGORY
383

 
- Severe Category Errors
384

 
- Informational Category Errors
386

 
- Performance Category Errors
388

 
- Oracle Error Messages
389

 
- Using dbms_warning.set_warning_setting_string
390

 
- Using dbms_warning.set_warning_setting_num
391

 
- Using dbms_warning.set_warning_setting_cat
392

 
- USER_PLSQL_OBJECT_SETTINGS
393

 
- Re-Compiling with same Warning settings
395

 
- PLSQL_OPTIMIZE_LEVEL
396

 
- Optimize Levels
397

 
- Timing Optimization with dbms_utility.get_cpu_time
398

 
- Optimize Levels in user_plsql_object_settings
404

 
- Re-Compiling with same Optimize settings
405

 
- High Level Optimization without Pragma Inline
406

Eighteen
Conditional Compilation in PLSQL
408

 
- Introduction
409

 
- Dbms_preprocessor
411

 
- Compiling using PLSQL_CCFLAGS
412

 
- PLSQL_CCFLAGS in PLSQL
414

 
- Using $error Directive
422

 
- Using Constants with CCFlags
424

 
- $$PLSQL_CODE_TYPE
425

 
- Oracle Compilation Modes
426

 
- $$PLSQL_OPTIMIZE_LEVEL
427

 
- $$PLSQL_LINE
428

 
- $$PLSQL_UNIT
429

 
- DBMS_DB_VERSION
430

 
- Setting Session CCFlags
432

 
- Plsql_ccflags in user_plsql_object_settings
433

 
- Plsql_ccflags in Triggers
434

Nineteen
Regular Expressions in PLSQL (Regex)
436

 
- Introduction
437

 
- Using REGEXP_LIKE in Where Clauses
439

 
- Case Sensitivity
441

 
- Line Anchors
442

 
- Using the Dot (Any Character)
444

 
- Counting Characters {Intervals}
447

 
- Character Lists
451

 
- Excluding Character Lists
455

 
- Alternatives to Character Lists
456

 
- Class Shorthands
459

 
- Or Patterns (Alternatives)
460

 
- Or Patterns with Character Lists
461

 
- Using the Question Mark (Optional)
462

 
- Using the Plus sign (Mandatory)
465

 
- Using the Star sign (Optional)
467

 
- Backreferencing
468

 
- POSIX
470

 
- Using POSIX in Oracle - [:upper] example
472

 
- Using POSIX in Oracle - [:digit] example
473

 
- Using POSIX in Oracle - Character equivalents
474

 
- Using Regexp_like in PLSQL
475

 
- Using Regexp_count in PLSQL
478

 
- Using Regexp_replace in PLSQL
482

 
- Using Regexp_instr in PLSQL
484

 
- Using Regexp_substr in PLSQL
486

 
- Using Regular Expressions in Oracle
488

Twenty
Using Alternative Quoting Mechanism in PLSQL
489

 
- Introduction
490

 
- Using Alternative Quoting Mechanism
491

 
- Exercise Five
492

Twenty One
File I/O
497

 
- Reading and Writing from the Operating System in PLSQL
498

 
- Accessing the File System using Directories
499

 
- Creating Directories
500

 
- Simple Write using UTL_FILE.PUT_LINE
502

 
- Simple Read using UTL_FILE.GET_LINE
503

 
- Additional Functions in the UTL_FILE Package
504

 
- Using UTL_FILE.PUTF in PLSQL
505

 
- UTL_FILE Exceptions
507

 
- Using UTL_FILE to perform Operating System Commands
509

 
- Using UTL_FILE.Fcopy in PLSQL
510

 
- Using UTL_FILE.Fgetattr in PLSQL
511

 
- Using UTL_FILE.Fremove in PLSQL
512

 
- Using UTL_FILE.Frename in PLSQL
513

Twenty Two
Working with Clobs and Blobs in PLSQL
514

 
- Introduction
515

 
- Creation of Clobs in Tables
516

 
- Using Clobs in PLSQL
517

 
- Inserting Clobs into the Database using PLSQL
518

 
- Writing Clobs to the Filesystem using PLSQL
522

 
- Introduction to Blobs
523

 
- Inserting Blobs into the Database using PLSQL
524

 
- Writing Blobs into the Filesystem using PLSQL
525

 
- DBMS_LOB exceptions
528

Twenty Three
DBMS_Metadata
530

 
- Generating Database Object Scripts
531

 
- List of Object Types
532

 
- Example of Using DBMS_Metadata
533

Twenty Four
Sub-programming Techniques
535

 
- Introduction
536

 
- Example
537

Twenty Five
Encrypting Code
539

 
- Introduction
540

 
- Using Wrap.exe to encrypt processes in Oracle
541

 
- Example of using Wrap.exe to encrypt processes
544

 
- Using DBMS_DDL.CREATE_WRAPPED Function
546

Twenty Six
Object Orientated Programming
550

 
- Introduction
551

 
- Definition of Objects
552

Twenty Seven
Row Objects
556

 
- Introduction
557

 
- Creating
558

 
- Metadata
559

 
- Data
560

 
- Indexes
561

 
- Views
563

 
- Removing
564

 
- OIDs
566

Twenty Eight
Column Objects
573

 
- Introduction
574

 
- Describing
575

 
- Inserting into
578

 
- Selecting from
580

 
- Updating
585

 
- Object Views
586

Twenty Nine
Defining Processes within Objects
588

 
- Introduction
589

 
- Defining Methods
590

 
- Member Methods
592

 
- Map Methods
599

 
- Order Methods
601

 
- Constructor Methods
607

Thirty
PLSQL Data Structures and Collections
611

 
- Programmer Defined Records
612

 
- Nested Programmer Defined Records
616

 
- Varrays in PLSQL
618

 
- Using Varrays in Loops
621

 
- Populating Varrays
622

 
- Extending Varrays
623

 
- Deleting from Varrays
625

 
- Varrays and Tables
626

 
- Multilevel Varrays
629

Thirty One
Associate Arrays (Index by Tables)
632

 
- Introducing Associate Arrays in PLSQL
633

 
- Declaring PLSQL Tables
635

 
- Populating PLSQL Tables
636

 
- Using Count with PLSQL Tables
638

 
- Using Delete with PLSQL Tables
639

 
- Using Exists with PLSQL Tables
640

 
- Using First/Last with PLSQL Tables
641

 
- Using Next/Prior with PLSQL Tables
642

 
- Using Varchar2 as an index with a PLSQL Table
643

 
- Multi-Level PLSQL Table
647

 
- Nested PLSQL Table
648

 
- Cardinality with Nested Table
651

 
- Tidying Nested PLSQL Tables using Set
652

 
- Nested PLSQL Tables using Set
653

 
- Trimming Nested PLSQL Tables
655

 
- Multi-Level Nested PLSQL Tables
657

Thirty Two
Collection Comparisons
658

 
- Introduction
659

 
- Check for Equality
660

 
- Using IN with a Nested Table
663

 
- Using Member Of with a Nested Table
664

 
- Using Is Empty with a Nested Table
665

 
- Multiset Union
666

 
- Multiset Union Distinct
668

 
- Multiset Intersect
669

 
- Multiset Except
670

 
- Submultiset
671

 
- Not Submultiset
672

 
- Not Submultiset Alternative
673

 
- Exercise Six
674

Thirty Three
Bulk SQL in PLSQL
677

 
- Introduction to Bulk Processing
678

 
- Bulk Binding in PLSQL
679

 
- Bulk Binding in PLSQL Using Associate Arrays
680

 
- Bulk Binding in PLSQL Using Varrays
681

 
- Bulk Binding in PLSQL Using Nested Tables
682

 
- Forall with Save Exceptions
683

 
- Forall with SQL%BULKEXCEPTIONS
685

 
- Forall with Indices Of
688

 
- Forall with Values Of
690

 
- Bulk Collect with Implicit Cursors (Varray)
691

 
- Bulk Collect with Implicit Cursors (Associative Array)
692

 
- Bulk Collect with Implicit Cursors (Nested Table)
693

 
- Bulk Collect with Explicit Cursors
694

 
- Bulk Collect and Returning in PLSQL
695

 
- Problems with Bulk Processing in PLSQL
698

 
- Bulk Processing and Limit in PLSQL
699

Thirty Four
Invoker Rights in PLSQL
700

 
- The need to use Invoker Rights
701

 
- Example of Invoker Rights
702

 
- Using AUTHID in PLSQL
705

Thirty Five
Overloading Modules in PLSQL
708

 
- Introduction
709

 
- Example
710

 
- User_Procedures
714

Thirty Six
Forward Declaration
715

Thirty Seven
Autonomous Transactions in PLSQL
718

 
- Introduction
719

 
- Example of an Autonomous Transaction
721

 
- Rollback in Autonomous Transactions
728

 
- Using Autonomous Transactions in Triggers
729

Thirty Eight
Advanced Use of Packages
731

 
- Introduction
732

 
- Defining Explicit Cursors in Packages
733

 
- Flexible Explicit Cursors in Packages
737

 
- Passing Parameters Using Packages
740

 
- Using PLSQL Tables as Parameters
743

 
- Global Variables using Packages
747

 
- Public and Private Variables in Packages
753

Thirty Nine
Dynamic SQL
755

 
- Introduction to Dynamic SQL
756

 
- Native Dynamic SQL (NDS) with Execute Immediate
757

 
- NDS Error Handling
760

 
- NDS with Inputs
761

 
- NDS with Output
763

 
- Dynamic Cursors and Sys_Refcursor
764

 
- NDS with Outputs
766

 
- Introduction to DBMS_SQL
767

 
- DBMS_SQL.Example
768

 
- Exercise Seven
769