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

Course Name: 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:
5 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
46
19
847
Oracle Essential SQL 11g
None
Contact us for the latest pricing

Synopsis for course

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
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 (simple_integer) 
30

 
- Variable Datatypes (boolean) 
31

 
- Variable Datatypes - Constants 
33

 
- Running PLSQL in SQL*Plus 
35

 
- Compiling PLSQL in SQL*Plus 
37

 
- Common Compilation Problems 
40

 
- Commenting PLSQL 
42

 
- Documenting PLSQL 
44

 
- Exercise One 
45

Three
Performing Simple DML in PLSQL 
47

 
- Introduction 
48

 
- Using Local Variables 
49

 
- Declaring Variables Using %Type 
52

 
- Using Substitution Values in PLSQL 
56

 
- Exercise Two 
59

Four
Performing Conditional Statements in PLSQL 
61

 
- If .. Then Statements 
62

 
- If .. Then Else Statements 
65

 
- If .. Then Elsif Statements 
67

 
- Combining Tests 
68

 
- Nesting Conditions 
69

 
- Boolean Conditions 
70

Five
Communicating from PLSQL 
71

 
- Messaging Via PLSQL 
72

 
- Exercise Three 
77

Six
Exception Handling 
81

 
- Introduction 
82

 
- The When Clause 
85

 
- The When Others Clause 
88

 
- Raise_Application_Error 
90

 
- Oracle Defined Exceptions 
94

 
- Access_into_Null (ORA-06530) 
95

 
- Case_Not_Found (ORA-06592 ) 
97

 
- Collection_Is_Null (ORA-06531) 
98

 
- Cursor_Already_Open (ORA-06511) 
100

 
- Dup_Val_On_Index (ORA-00001) 
101

 
- Invalid_Cursor (ORA-01001) 
102

 
- Invalid_Number (ORA-01722) 
103

 
- Login_Denied (ORA-01017) 
104

 
- No_Data_Found (ORA-01403) 
106

 
- Not_Logged_On (ORA-01012) 
107

 
- Program_Error (ORA-06501) 
108

 
- Rowtype_Mismatch (ORA-06504) 
109

 
- Self_is_Null (ORA-30625) 
111

 
- Storage_Error (ORA-06500) 
114

 
- Subscript_Beyond_Count (ORA-06533) 
115

 
- Subscript_outside_limit (ORA-06532) 
116

 
- Sys_Invalid_Rowid (ORA-01410) 
118

 
- Timeout_On_Resource (ORA-00051) 
119

 
- Too_Many_Rows (ORA-01422) 
120

 
- Value_Error (ORA-06502) 
121

 
- Zero_divide (ORA-01476) 
122

 
- Raising User Defined Exceptions 
123

 
- Pragma Exceptions in PLSQL 
126

 
- Grouping Exceptions in PLSQL 
130

 
- Alternative Way to Handle SQL Errors in PLSQL 
131

 
- Handling SQL Errors in PLSQL 
132

 
- Advanced SQLErrm 
134

 
- Exercise Four 
135

Seven
Implicit Cursors 
138

 
- Using Implicit Cursors in PLSQL 
139

 
- Using Implicit Cursors to Select Records 
141

 
- Exceptions in PLSQL 
142

 
- Attributes of Implicit Cursors 
144

 
- SQL%FOUND 
145

 
- SQL%NOTFOUND 
146

 
- SQL%ROWCOUNT 
147

Eight
Sub-Blocking in PLSQL 
148

 
- Introduction 
149

 
- Defining 
150

 
- Re-Raising Exceptions in PLSQL 
152

 
- Exercise Five 
155

Nine
Explicit Cursors 
159

 
- Using Explicit Cursors in PLSQL 
160

 
- How Explicit Cursors Work 
162

 
- Attributes of an Explicit Cursor 
165

 
- Passing Parameters to Explicit Cursors 
168

 
- Explicit Cursors Versus Implicit Cursors 
171

 
- Exercise Six 
172

Ten
Loop Processing 
175

 
- Iterative Processing in PLSQL 
176

 
- Basic Looping in PLSQL 
177

 
- While Looping in PLSQL 
181

 
- For Looping in PLSQL 
184

 
- Using Dates in For Loops in PLSQL 
188

 
- Using Characters in For Loops in PLSQL 
189

 
- Implicit Cursor Looping in PLSQL 
190

 
- Explicit Cursor Looping in PLSQL 
193

 
- Using other Loops with Explicit Cursors 
195

 
- Loop Labels in PLSQL 
198

 
- Loop Continue and Continue When 
200

 
- Exercise Seven 
204

Eleven
Case Statements and Case Expressions 
210

 
- Introduction 
211

 
- Using Searched Case in PLSQL 
212

 
- Using Case in PLSQL 
213

 
- Using Nested Case in PLSQL 
214

 
- Using Case Expressions in PLSQL 
216

 
- Using Nullif in PLSQL 
220

 
- Using Coalesce in PLSQL 
221

Twelve
Rowtypes in PLSQL 
222

 
- Using %Rowtype in PLSQL 
223

 
- Declaring a Variable as a %Rowtype 
224

 
- Referencing a %Rowtype Variable 
225

 
- Populating a Variable which is a %Rowtype 
226

 
- Passing a %Rowtype between Processes 
229

 
- Declaring a Cursor %Rowtype 
231

Thirteen
Using Sequences in PLSQL 11g 
234

Fourteen
Savepoints in PLSQL 
237

 
- Using Savepoints in a PLSQL process 
238

 
- Example of Savepoints in PLSQL process 
239

 
- Exercise Eight 
240

Fifteen
PL/SQL Database Objects 
244

 
- PLSQL Objects 
245

 
- Compiling PLSQL Objects 
246

 
- Metadata For PLSQL Objects 
247

 
- Re-Compiling PLSQL Objects 
248

 
- Dropping PLSQL Objects 
250

 
- Passing Parameters in PLSQL 
251

 
- Passing Parameters in PLSQL using Positional Notation 
252

 
- Passing Parameters in PLSQL using Named Notation 
254

 
- Using Mixed Notation in SQL 
255

 
- Metadata (User Source) 
257

 
- Describing PLSQL Objects in SQL*Plus 
258

Sixteen
Programmer Defined Functions 
259

 
- Syntax of Functions in Oracle 
260

 
- Suggested Naming Convention of Functions in Oracle 
263

 
- Function Example in PLSQL 
264

 
- Running Functions in SQL and PLSQL 
266

 
- Running Functions in Oracle 
267

 
- Exercise Nine 
268

Seventeen
Programmer Defined Procedures 
277

 
- Syntax of Procedures in Oracle 
278

 
- Example of Procedures in Oracle 
280

 
- Executing Procedures in Oracle within SQL Plus 
281

 
- Executing Procedures in Oracle within PLSQL 
282

 
- Exercise Ten 
268

Eighteen
Sub-programming Techniques 
283

 
- Introduction 
284

 
- Example 
285

Nineteen
Advanced Cursors in Oracle 11g 
295

 
- For Update with Explicit Cursors 
296

 
- Where Current of Explicit Cursors 
297

 
- Example of Updating with Explicit Cursors 
298

 
- Using Nowait with Explicit Cursors 
299

 
- Using Skip Locked with Explicit Cursors 
300

 
- Using Wait with an Explicit Cursors 
301

 
- Using Rowid with Explicit Cursors 
302

 
- Using Returning Into in PLSQL 
303

 
- Ref Cursors in PLSQL 
306

 
- Strongly Typed Ref Cursors in PLSQL 
307

 
- Weakly Typed Ref Cursors in PLSQL 
309

 
- Sys_refcursor in PLSQL 
310

 
- Using Sys_Refcursors with SQL 
312

 
- Using Rowtype for DML 
316

Twenty
Encrypting Code 
318

 
- Introduction 
319

 
- Using Wrap.exe to encrypt processes in Oracle 
320

 
- Example of using Wrap.exe to encrypt processes 
323

 
- Using DBMS_DDL.CREATE_WRAPPED Function 
325

Twenty One
Invoker Rights in PLSQL 
329

 
- The need to use Invoker Rights 
330

 
- Example of Invoker Rights 
331

 
- Using AUTHID in PLSQL 
334

Twenty Two
Forward Declaration 
337

Twenty Three
Autonomous Transactions in PLSQL 
340

 
- Introduction 
341

 
- Example of an Autonomous Transaction 
343

 
- Rollback in Autonomous Transactions 
350

 
- Using Autonomous Transactions in Triggers 
351

Twenty Four
Advanced Exceptions 
353

 
- Handling Exceptions in Sub-Processes in PLSQL 
354

 
- Using Nocopy when Declaring Processes 
356

 
- Using DBMS_UTILITY Functions 
360

 
- DBMS_UTILITY.FORMAT_ERROR_STACK 
361

 
- DBMS_UTILITY.FORMAT_BACKTRACE 
362

 
- Exercise Eleven 
365

Twenty Five
Packaging Programmer Defined Programs 
368

 
- Syntax of Packages in Oracle 
369

 
- Compiling of Packages in Oracle 
374

 
- Private Processes in PLSQL Packages 
375

 
- Executing Packages in Oracle 
377

 
- Handling Exceptions in Packages in PLSQL 
378

Twenty Six
Overloading Modules in PLSQL 
380

 
- Introduction 
381

 
- Example 
382

 
- User_Procedures 
386

 
- Exercise Twelve 
387

Twenty Seven
DML Triggers in Oracle 
394

 
- Oracle Triggers Introduction 
395

 
- DML Triggers Introduction 
396

 
- DML Triggers Timing Points  
399

 
- DML Triggers When Conditions 
400

 
- DML Triggers When Conditions Examples 
401

 
- DML Triggers Suggested Naming Conventions 
403

 
- Syntax of DML Triggers in PLSQL 
405

 
- Referencing Values within Triggers 
406

 
- Checking the Event which fired the DML Trigger 
410

 
- Using the Follows syntax to dictate Trigger Firing  
412

 
- Using the Disable syntax when Compiling Triggers  
417

 
- Enabling/Disabling Triggers in an Oracle Database  
418

 
- Metadata for Triggers (User_Triggers)  
420

 
- Mutating DML Triggers in Oracle  
421

 
- Exercise Thirteen 
425

Twenty Eight
Instead Of Triggers 
432

 
- Introduction to Instead Of Triggers 
433

 
- Syntax of an Instead Of Trigger in Oracle 
436

 
- Example of an Instead Of Trigger  
438

 
- Deleting Records in Oracle using an Instead Of 
446

 
- Using Follows in an Instead Of Trigger 
447

Twenty Nine
Compound Triggers 
448

 
- Introduction to Compound Triggers 
449

 
- Syntax for Table Compound Triggers 
451

 
- Declaration Section  
452

 
- Timing Points 
453

 
- Before Statement 
454

 
- Before Each Row 
455

 
- After Each Row 
456

 
- After Statement  
457

 
- Compound Triggers for Views  
458

Thirty
System Triggers 
459

 
- Introduction to System Triggers 
460

 
- Syntax for System Triggers in Oracle 
461

 
- System Event Triggers  
462

 
- User Event Triggers 
463

 
- Schema Event Triggers 
467

 
- Database Level Attributes for System Triggers 
473

 
- Exercise Fourteen 
474

Thirty One
Regular Expressions in PLSQL (Regex) 
482

 
- Introduction 
483

 
- Using REGEXP_LIKE in Where Clauses 
484

 
- Case Sensitivity 
486

 
- Line Anchors 
487

 
- Using the Dot (Any Character) 
489

 
- Counting Characters {Intervals} 
492

 
- Character Lists 
496

 
- Excluding Character Lists 
500

 
- Alternatives to Character Lists 
501

 
- Class Shorthands 
504

 
- Or Patterns (Alternatives) 
505

 
- Or Patterns with Character Lists 
506

 
- Using the Question Mark (Optional) 
507

 
- Using the Plus sign (Mandatory) 
510

 
- Using the Star sign (Optional) 
512

 
- Backreferencing 
513

 
- POSIX 
515

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

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

 
- Using POSIX in Oracle - Character equivalents 
519

 
- Using Regexp_like in PLSQL 
520

 
- Using Regexp_count in PLSQL 
523

 
- Using Regexp_replace in PLSQL 
527

 
- Using Regexp_instr in PLSQL 
529

 
- Using Regexp_substr in PLSQL 
531

 
- Using Regular Expressions in Oracle 
533

Thirty Two
Using Alternative Quoting Mechanism in PLSQL 
534

 
- Introduction 
535

 
- Using Alternative Quoting Mechanism 
536

 
- Exercise Fifteen 
537

Thirty Three
File I/O 
541

 
- Reading and Writing Files in PLSQL 
542

 
- Accessing the File System using Directories 
543

 
- Creating Directories 
544

 
- Simple Write using UTL_FILE.PUT_LINE 
546

 
- Simple Read Using UTL_FILE.GET_LINE 
547

 
- Additional Functions in the UTL_FILE Package 
548

 
- Using UTL_FILE.PUTF in PLSQL 
549

 
- UTL_FILE Exceptions 
551

 
- Using UTL_FILE to perform Operating System Commands 
553

 
- Using UTL_FILE.Fcopy in PLSQL 
554

 
- Using UTL_FILE.Fgetattr in PLSQL 
555

 
- Using UTL_FILE.Fremove in PLSQL 
556

 
- Using UTL_FILE.Frename in PLSQL 
557

Thirty Four
Working with Clobs and Blobs in PLSQL 
558

 
- Introduction 
559

 
- Creation of Clobs in Tables 
560

 
- Using Clobs in PLSQL 
561

 
- Inserting Clobs into the Database using PLSQL 
562

 
- Writing Clobs to the Filesystem using PLSQL 
566

 
- Introduction to Blobs 
567

 
- Inserting Blobs into the Database using PLSQL 
568

 
- Writing Blobs into the Filesystem using PLSQL 
569

 
- DBMS_LOB exceptions 
572

Thirty Five
DBMS_METADATA 
574

 
- Generating Database Object Scripts 
575

 
- List of Object Types 
576

 
- Example of Using Dbms_Metadata 
577

 
- Exercise Sixteen 
579

Thirty Six
Using Contexts 
581

 
- Introduction 
582

 
- Attributes 
583

 
- User Defined  
591

 
- Creating Contexts 
592

 
- Creating a Package for a Context 
593

 
- Creating a Context  
594

 
- Populating a Context  
595

 
- Accessing a Context  
596

 
- Listing Contents of a Context  
597

 
- Deleting Contents of a Context  
599

 
- Changing Context Behaviour  
601

Thirty Seven
Subprogram Inlining 
602

 
- Introduction  
603

 
- Pragma Inline  
604

 
- Example of Inlining  
605

 
- Inlining Usage  
610

 
- Conclusion  
612

Thirty Eight
PLSQL Compiler 
613

 
- Introduction  
614

 
- Warning Levels  
615

 
- Enabling/Disabling Warning Levels  
616

 
- Using the Alter Session  
617

 
- Using the Error option  
620

 
- Using the Dbms_warning Package  
622

 
- Using the Dbms_warning.get_category 
623

 
- Severe Category Errors  
624

 
- Informational Category Errors  
626

 
- Performance Category Errors  
628

 
- Oracle Error Messages  
629

 
- Using Dbms_warning.set_warning_setting_string  
630

 
- Using Dbms_warning.set_warning_setting_num  
631

 
- Using Dbms_warning.set_warning_setting_cat  
632

 
- User_plsql_object_settings  
633

 
- Re-Compiling with same Warning settings  
635

 
- PLSQL_Optimize_level  
636

 
- Optimize Levels  
637

 
- Timing Optimization with Dbms_utility.get_cpu_time  
638

 
- Optimize Levels in User_plsql_object_settings  
644

 
- Re-Compiling with same Optimize settings  
645

 
- High Level Optimization without Pragma Inline  
646

Thirty Nine
Conditional Compilation in PLSQL 
648

 
- Introduction 
649

 
- Dbms_preprocessor 
651

 
- Compiling using PLSQL_CCFLAGS 
652

 
- PLSQL_CCFLAGS in PLSQL 
654

 
- Using $error Directive 
662

 
- Using Constants with CCFlags 
664

 
- $$PLSQL_CODE_TYPE 
665

 
- Oracle Compilation Modes 
666

 
- $$PLSQL_OPTIMIZE_LEVEL 
667

 
- $$PLSQL_LINE 
668

 
- $$PLSQL_UNIT 
669

 
- DBMS_DB_VERSION 
670

 
- Setting Session CCFLags 
672

 
- PLSQL_ccflags in user_plsql_object_settings 
673

 
- PLSQL_ccflags in Triggers 
674

Forty
Timestamps in PLSQL 
676

 
- Timestamps and Intervals in PLSQL 
677

 
- Timestamp Variables in PLSQL 
678

 
- Timestamp in PLSQL 
680

 
- Populating Timestamps in PLSQL 
683

 
- Using Extract in PLSQL 
685

 
- Timestamp with Time Zone 
686

 
- Timestamp Time Zone and Extract 
689

 
- Using sys_extract_utc to Normalise Timestamps 
690

 
- Using the To_Timestamp_TZ function 
691

 
- Using the From_TZ function 
693

 
- Timestamp with Local Time Zone 
695

 
- Intervals 
697

 
- Populating Year to Month Intervals 
700

 
- Populating Day to Second Intervals 
701

 
- Using Intervals in PLSQL 
702

 
- Using Intervals with Extract in PLSQL 
707

 
- Populating Intervals using Timestamps 
708

 
- Exercise Seventeen 
709

Forty One
PLSQL Data Structures and Collections 
713

 
- Programmer Defined Records 
714

 
- Nested Programmer Defined Records 
718

 
- Varrays in PLSQL 
720

 
- Using Varrays in Loops 
723

 
- Populating Varrays 
724

 
- Extending Varrays 
725

 
- Deleting from Varrays 
727

 
- Varrays and Tables 
728

 
- Multilevel Varrays 
731

Forty Two
Associate Arrays (Index by Tables) 
734

 
- Introducing Associate Arrays in PLSQL 
735

 
- Declaring PLSQL Tables 
737

 
- Populating PLSQL Tables 
738

 
- Using Count with PLSQL Tables 
740

 
- Using Delete with PLSQL Tables 
741

 
- Using Exists with PLSQL Tables 
742

 
- Using First/Last with PLSQL Tables 
743

 
- Using Next/Prior with PLSQL Tables 
744

 
- Using Varchar2 as an index with a PLSQL Table 
745

 
- Multi-level PLSQL Table 
749

 
- Nested PLSQL Table 
750

 
- Cardinality with Nested Table 
753

 
- Tidying Nested PLSQL Tables using Set 
754

 
- Nested PLSQL Tables using Set 
755

 
- Trimming Nested PLSQL Tables 
757

 
- Multi-Level Nested PLSQL Tables 
759

Forty Three
Collection Comparisons 
760

 
- Introduction 
761

 
- Check for Equality 
762

 
- Using IN with a Nested Table 
765

 
- Using Member Of with a Nested Table 
766

 
- Using Is Empty with a Nested Table 
767

 
- Multiset Union 
768

 
- Multiset Union Distinct 
770

 
- Multiset Intersect 
771

 
- Multiset Except 
772

 
- Submultiset 
773

 
- Not Submultiset 
774

 
- Not Submultiset Alternative 
775

 
- Exercise Eighteen 
776

Forty Four
Bulk SQL in PLSQL 
778

 
- Introduction to Bulk Processing 
779

 
- Bulk Binding in PLSQL 
780

 
- Bulk Binding in PLSQL using Associate Arrays 
781

 
- Bulk Binding in PLSQL using Varrays 
782

 
- Bulk Binding in PLSQL using Nested Tables 
783

 
- Forall with Save Exceptions 
784

 
- Forall with SQL%BULKEXCEPTIONS 
786

 
- Forall with Indices Of 
789

 
- Forall with Values Of 
791

 
- Bulk Collect with Implicit Cursors (Varray) 
792

 
- Bulk Collect with Implicit Cursors (Associative Array) 
793

 
- Bulk Collect with Implicit Cursors (Nested Table) 
794

 
- Bulk Collect with Explicit Cursors  
795

 
- Bulk Collect and Returning in PLSQL 
796

 
- Problems with Bulk Processing in PLSQL 
799

 
- Bulk Processing and Limit in PLSQL 
800

Forty Five
Advanced Use of Packages 
801

 
- Introduction 
802

 
- Defining Explicit Cursors in Packages 
803

 
- Flexible Explicit Cursors in Packages 
807

 
- Passing Parameters Using Packages 
810

 
- Using PLSQL Tables as Parameters 
813

 
- Global Variables using Packages 
817

 
- Public and Private Variables in Packages 
823

Forty Six
Dynamic SQL 
825

 
- Introduction to Dynamic SQL 
826

 
- Native Dynamic SQL (NDS) with Execute Immediate 
827

 
- NDS Error Handling 
830

 
- NDS with Inputs 
831

 
- NDS with Output 
833

 
- Dynamic Cursors and Sys_Refcursor 
834

 
- NDS with Outputs 
836

 
- Introduction to DBMS_SQL 
837

 
- DBMS_SQL Example 
838

 
- Exercise Nineteen 
839