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

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

Synopsis for course

A complete guide to PLSQL in a 10g Database, 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

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

 
- Variable Datatypes - Constants
32

 
- Running PLSQL in SQL*Plus
34

 
- Compiling PLSQL in SQL*Plus
36

 
- Common Compilation Problems
39

 
- Commenting PLSQL
41

 
- Documenting PLSQL
43

 
- Exercise One
44

Three
Performing Simple DML in PLSQL
46

 
- Introduction
47

 
- Using Local Variables
48

 
- Declaring Variables Using %Type
51

 
- Using Substitution Values in PLSQL
55

 
- Exercise Two
58

Four
Performing Conditional Statements in PLSQL
60

 
- If .. Then Statements
61

 
- If .. Then Else Statements
64

 
- If .. Then Elsif Statements
66

 
- Combining Tests
67

 
- Nesting Conditions
68

 
- Boolean Conditions
69

Five
Communicating from PLSQL
70

 
- Messaging Via PLSQL
71

 
- Exercise Three
76

Six
Exception Handling
80

 
- Introduction
81

 
- The When Clause
84

 
- The When Others Clause
87

 
- Raise_Application_Error
89

 
- Oracle Defined Exceptions
93

 
- Access_into_Null (ORA-06530)
94

 
- Case_Not_Found (ORA-06592 )
96

 
- Collection_Is_Null (ORA-06531)
97

 
- Cursor_Already_Open (ORA-06511)
99

 
- Dup_Val_On_Index (ORA-00001)
100

 
- Invalid_Cursor (ORA-01001)
101

 
- Invalid_Number (ORA-01722)
102

 
- Login_Denied (ORA-01017)
103

 
- No_Data_Found (ORA-01403)
105

 
- Not_Logged_On (ORA-01012)
106

 
- Program_Error (ORA-06501)
107

 
- Rowtype_Mismatch (ORA-06504)
108

 
- Self_is_Null (ORA-30625)
110

 
- Storage_Error (ORA-06500)
113

 
- Subscript_Beyond_Count (ORA-06533)
114

 
- Subscript_outside_limit (ORA-06532)
115

 
- Sys_Invalid_Rowid (ORA-01410)
117

 
- Timeout_On_Resource (ORA-00051)
118

 
- Too_Many_Rows (ORA-01422)
119

 
- Value_Error (ORA-06502)
120

 
- Zero_divide (ORA-01476)
121

 
- Raising User Defined Exceptions
122

 
- Pragma Exceptions in PLSQL
125

 
- Grouping Exceptions in PLSQL
129

 
- Alternative Way to Handle SQL Errors in PLSQL
130

 
- Handling SQL Errors in PLSQL
131

 
- Advanced SQLErrm
133

 
- Exercise Four
134

Seven
Implicit Cursors
137

 
- Using Implicit Cursors in PLSQL
138

 
- Using Implicit Cursors to Select Records
140

 
- Exceptions in PLSQL
141

 
- Attributes of Implicit Cursors
143

 
- SQL%FOUND
144

 
- SQL%NOTFOUND
145

 
- SQL%ROWCOUNT
146

Eight
Sub-Blocking in PLSQL
147

 
- Introduction
148

 
- Defining
149

 
- Re-Raising Exceptions in PLSQL
151

 
- Exercise Five
154

Nine
Explicit Cursors
158

 
- Using Explicit Cursors in PLSQL
159

 
- How Explicit Cursors Work
161

 
- Attributes of an Explicit Cursor
164

 
- Passing Parameters to Explicit Cursors
167

 
- Explicit Cursors Versus Implicit Cursors
170

 
- Exercise Six
171

Ten
Loop Processing
174

 
- Iterative Processing in PLSQL
175

 
- Basic Looping in PLSQL
176

 
- While Looping in PLSQL
180

 
- For Looping in PLSQL
183

 
- Using Dates in For Loops in PLSQL
187

 
- Using Characters in For Loops in PLSQL
188

 
- Implicit Cursor Looping in PLSQL
189

 
- Explicit Cursor Looping in PLSQL
192

 
- Using other Loops with Explicit Cursors
194

 
- Loop Labels in PLSQL
197

 
- Exercise Seven
199

Eleven
Case Statements and Case Expressions
205

 
- Introduction
206

 
- Using Searched Case in PLSQL
207

 
- Using Case in PLSQL
208

 
- Using Nested Case in PLSQL
209

 
- Using Case Expressions in PLSQL
211

 
- Using Nullif in PLSQL
215

 
- Using Coalesce in PLSQL
216

Twelve
Rowtypes in PLSQL
217

 
- Using %Rowtype in PLSQL
218

 
- Declaring a Variable as a %Rowtype
219

 
- Referencing a %Rowtype Variable
220

 
- Populating a Variable which is a %Rowtype
221

 
- Passing a %Rowtype between Processes
224

 
- Declaring a Cursor %Rowtype
226

Thirteen
Savepoints in PLSQL
229

 
- Using Savepoints in a PLSQL process
230

 
- Example of Savepoints in PLSQL process
231

 
- Exercise Eight
232

Fourteen
PL/SQL Database Objects
236

 
- PLSQL Objects
237

 
- Compiling PLSQL Objects
238

 
- Metadata For PLSQL Objects
239

 
- Re-Compiling PLSQL Objects
240

 
- Dropping PLSQL Objects
242

 
- Passing Parameters in PLSQL
243

 
- Passing Parameters in PLSQL using Positional Notation
244

 
- Passing Parameters in PLSQL using Named Notation
246

 
- Using Mixed Notation in SQL
247

 
- Metadata (User Source)
249

 
- Describing PLSQL Objects in SQL*Plus
250

Fifteen
Programmer Defined Functions
251

 
- Syntax of Functions in Oracle
252

 
- Suggested Naming Convention of Functions in Oracle
255

 
- Function Example in PLSQL
256

 
- Running Functions in SQL and PLSQL
258

 
- Running Functions in Oracle
259

 
- Exercise Nine
260

Sixteen
Programmer Defined Procedures
269

 
- Syntax of Procedures in Oracle
270

 
- Example of Procedures in Oracle
272

 
- Executing Procedures in Oracle within SQL Plus
273

 
- Executing Procedures in Oracle within PLSQL
274

Seventeen
Sub-programming Techniques
275

 
- Introduction
276

 
- Example
277

 
- Exercise Ten
279

Eighteen
Advanced Cursors in Oracle 10g
287

 
- For Update with Explicit Cursors
288

 
- Where Current of Explicit Cursors
289

 
- Example of Updating with Explicit Cursors
290

 
- Using Nowait with Explicit Cursors
291

 
- Using Skip Locked with Explicit Cursors
292

 
- Using Wait with an Explicit Cursors
293

 
- Using Rowid with Explicit Cursors
294

 
- Using Returning Into in PLSQL
295

 
- Ref Cursors in PLSQL
298

 
- Strongly Typed Ref Cursors in PLSQL
299

 
- Weakly Typed Ref Cursors in PLSQL
301

 
- Sys_refcursor in PLSQL
302

 
- Using Sys_Refcursors with SQL
304

 
- Using Rowtype for DML
308

Nineteen
Encrypting Code
310

 
- Introduction
311

 
- Using Wrap.exe to encrypt processes in Oracle
312

 
- Example of using Wrap.exe to encrypt processes
315

 
- Using DBMS_DDL.CREATE_WRAPPED Function
317

Twenty
Invoker Rights in PLSQL
321

 
- The need to use Invoker Rights
322

 
- Example of Invoker Rights
323

 
- Using AUTHID in PLSQL
326

Twenty One
Forward Declaration
329

Twenty Two
Autonomous Transactions in PLSQL
332

 
- Introduction
333

 
- Example of an Autonomous Transaction
335

 
- Rollback in Autonomous Transactions
342

 
- Using Autonomous Transactions in Triggers
343

Twenty Three
Advanced Exceptions
345

 
- Handling Exceptions in Sub-Processes in PLSQL
346

 
- Using Nocopy when Declaring Processes
348

 
- Using DBMS_UTILITY Functions
352

 
- DBMS_UTILITY.FORMAT_ERROR_STACK
353

 
- DBMS_UTILITY.FORMAT_BACKTRACE
354

 
- Exercise Eleven
357

Twenty Four
Packaging Programmer Defined Programs
360

 
- Syntax of Packages in Oracle
361

 
- Compiling of Packages in Oracle
366

 
- Private Processes in PLSQL Packages
367

 
- Executing Packages in Oracle
369

 
- Handling Exceptions in Packages in PLSQL
370

Twenty Five
Overloading Modules in PLSQL
372

 
- Introduction
373

 
- Example
374

 
- User_Procedures
378

 
- Exercise Twelve
379

Twenty Six
DML Triggers in Oracle
386

 
- Oracle Triggers Introduction
387

 
- DML Triggers Introduction
388

 
- DML Triggers Timing Points
391

 
- DML Triggers When Conditions
392

 
- DML Triggers When Conditions Examples
393

 
- DML Triggers Suggested Naming Conventions
395

 
- Syntax of DML Triggers in PLSQL
397

 
- Referencing Values within Triggers
398

 
- Checking the Event which fired the DML Trigger
402

 
- Enabling/Disabling Triggers in an Oracle Database
404

 
- Metadata for Triggers (User_Triggers)
406

 
- Mutating DML Triggers in Oracle
407

 
- Exercise Thirteen
411

Twenty Seven
Instead Of Triggers
418

 
- Introduction to Instead Of Triggers
419

 
- Syntax of an Instead Of Trigger in Oracle
422

 
- Example of an Instead Of Trigger
424

 
- Deleting Records in Oracle using an Instead Of
432

Twenty Eight
System Triggers
433

 
- Introduction to System Triggers
434

 
- Syntax for System Triggers in Oracle
435

 
- System Event Triggers
436

 
- User Event Triggers
437

 
- Schema Event Triggers
441

 
- Database Level Attributes for System Triggers
447

 
- Exercise Fourteen
448

Twenty Nine
Regular Expressions in PLSQL (Regex)
452

 
- Introduction
453

 
- Using REGEXP_LIKE in Where Clauses
454

 
- Case Sensitivity
456

 
- Line Anchors
457

 
- Using the Dot (Any Character)
459

 
- Counting Characters {Intervals}
462

 
- Character Lists
466

 
- Excluding Character Lists
470

 
- Alternatives to Character Lists
471

 
- Class Shorthands
474

 
- Or Patterns (Alternatives)
475

 
- Or Patterns with Character Lists
476

 
- Using the Question Mark (Optional)
477

 
- Using the Plus sign (Mandatory)
480

 
- Using the Star sign (Optional)
482

 
- Backreferencing
483

 
- POSIX
485

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

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

 
- Using POSIX in Oracle - Character equivalents
489

 
- Using Regexp_like in PLSQL
490

 
- Using Regexp_count in PLSQL
493

 
- Using Regexp_replace in PLSQL
497

 
- Using Regexp_instr in PLSQL
499

 
- Using Regexp_substr in PLSQL
501

 
- Using Regular Expressions in Oracle
503

Thirty
Using Alternative Quoting Mechanism in PLSQL
504

 
- Introduction
505

 
- Using Alternative Quoting Mechanism
506

 
- Exercise Fifteen
507

Thirty One
File I/O
511

 
- Reading and Writing Files in PLSQL
512

 
- Accessing the File System using Directories
513

 
- Creating Directories
514

 
- Simple Write using UTL_FILE.PUT_LINE
516

 
- Simple Read Using UTL_FILE.GET_LINE
517

 
- Additional Functions in the UTL_FILE Package
518

 
- Using UTL_FILE.PUTF in PLSQL
519

 
- UTL_FILE Exceptions
521

 
- Using UTL_FILE to perform Operating System Commands
523

 
- Using UTL_FILE.Fcopy in PLSQL
524

 
- Using UTL_FILE.Fgetattr in PLSQL
525

 
- Using UTL_FILE.Fremove in PLSQL
526

 
- Using UTL_FILE.Frename in PLSQL
527

Thirty Two
Working with Clobs and Blobs in PLSQL
528

 
- Introduction
529

 
- Creation of Clobs in Tables
530

 
- Using Clobs in PLSQL
531

 
- Inserting Clobs into the Database using PLSQL
532

 
- Writing Clobs to the Filesystem using PLSQL
536

 
- Introduction to Blobs
537

 
- Inserting Blobs into the Database using PLSQL
538

 
- Writing Blobs into the Filesystem using PLSQL
539

 
- DBMS_LOB exceptions
542

Thirty Three
DBMS_METADATA
544

 
- Generating Database Object Scripts
545

 
- List of Object Types
546

 
- Example of Using Dbms_Metadata
547

 
- Exercise Sixteen
549

Thirty Four
Using Contexts
551

 
- Introduction
552

 
- Attributes
553

 
- User Defined
561

 
- Creating Contexts
562

 
- Creating a Package for a Context
563

 
- Creating a Context
564

 
- Populating a Context
565

 
- Accessing a Context
566

 
- Listing Contents of a Context
567

 
- Deleting Contents of a Context
569

 
- Changing Context Behaviour
571

Thirty Five
PLSQL Compiler
572

 
- Introduction
573

 
- Warning Levels
574

 
- Enabling/Disabling Warning Levels
575

 
- Using the Alter Session
576

 
- Using the Error option
579

 
- Using the Dbms_warning Package
580

 
- Using the Dbms_warning.get_category
581

 
- Severe Category Errors
582

 
- Informational Category Errors
584

 
- Performance Category Errors
586

 
- Using Dbms_warning.set_warning_setting_string
587

 
- Using Dbms_warning.set_warning_setting_num
588

 
- Using Dbms_warning.set_warning_setting_cat
589

 
- User_plsql_object_settings
590

 
- Re-Compiling with same Warning settings
592

 
- PLSQL_Optimize_level
593

 
- Optimize Levels
594

 
- Timing Optimization with Dbms_utility.get_cpu_time
595

 
- Optimize Levels in User_plsql_object_settings
601

 
- Re-Compiling with same Optimize settings
602

Thirty Six
Conditional Compilation in PLSQL
603

 
- Introduction
604

 
- Dbms_preprocessor
606

 
- Compiling using PLSQL_CCFLAGS
607

 
- PLSQL_CCFLAGS in PLSQL
609

 
- Using $error Directive
617

 
- Using Constants with CCFlags
619

 
- $$PLSQL_CODE_TYPE
620

 
- $$PLSQL_OPTIMIZE_LEVEL
621

 
- $$PLSQL_LINE
622

 
- $$PLSQL_UNIT
623

 
- DBMS_DB_VERSION
624

 
- Setting Session CCFLags
626

 
- PLSQL_ccflags in user_plsql_object_settings
627

 
- PLSQL_ccflags in Triggers
628

Thirty Seven
Timestamps in PLSQL
630

 
- Timestamps and Intervals in PLSQL
631

 
- Timestamp Variables in PLSQL
632

 
- Timestamp in PLSQL
634

 
- Populating Timestamps in PLSQL
637

 
- Using Extract in PLSQL
639

 
- Timestamp with Time Zone
640

 
- Timestamp Time Zone and Extract
643

 
- Using sys_extract_utc to Normalise Timestamps
644

 
- Using the To_Timestamp_TZ function
645

 
- Using the From_TZ function
647

 
- Timestamp with Local Time Zone
649

 
- Intervals
651

 
- Populating Year to Month Intervals
654

 
- Populating Day to Second Intervals
655

 
- Using Intervals in PLSQL
656

 
- Using Intervals with Extract in PLSQL
661

 
- Populating Intervals using Timestamps
662

 
- Exercise Seventeen
663

Thirty Eight
PLSQL Data Structures and Collections
667

 
- Programmer Defined Records
668

 
- Nested Programmer Defined Records
672

 
- Varrays in PLSQL
674

 
- Using Varrays in Loops
677

 
- Populating Varrays
678

 
- Extending Varrays
679

 
- Deleting from Varrays
681

 
- Varrays and Tables
682

 
- Multilevel Varrays
685

Thirty Nine
Associate Arrays (Index by Tables)
688

 
- Introducing Associate Arrays in PLSQL
689

 
- Declaring PLSQL Tables
691

 
- Populating PLSQL Tables
692

 
- Using Count with PLSQL Tables
694

 
- Using Delete with PLSQL Tables
695

 
- Using Exists with PLSQL Tables
696

 
- Using First/Last with PLSQL Tables
697

 
- Using Next/Prior with PLSQL Tables
698

 
- Using Varchar2 as an index with a PLSQL Table
699

 
- Multi-level PLSQL Table
703

 
- Nested PLSQL Table
704

 
- Cardinality with Nested Table
707

 
- Tidying Nested PLSQL Tables using Set
708

 
- Nested PLSQL Tables using Set
709

 
- Trimming Nested PLSQL Tables
711

 
- Multi-Level Nested PLSQL Tables
713

Forty
Collection Comparisons
714

 
- Introduction
715

 
- Check for Equality
716

 
- Using IN with a Nested Table
719

 
- Using Member Of with a Nested Table
720

 
- Using Is Empty with a Nested Table
721

 
- Multiset Union
722

 
- Multiset Union Distinct
724

 
- Multiset Intersect
725

 
- Multiset Except
726

 
- Submultiset
727

 
- Not Submultiset
728

 
- Not Submultiset Alternative
729

 
- Exercise Eighteen
730

Forty One
Bulk SQL in PLSQL
732

 
- Introduction to Bulk Processing
733

 
- Bulk Binding in PLSQL
734

 
- Bulk Binding in PLSQL using Associate Arrays
735

 
- Bulk Binding in PLSQL using Varrays
736

 
- Bulk Binding in PLSQL using Nested Tables
737

 
- Forall with Save Exceptions
738

 
- Forall with SQL%BULKEXCEPTIONS
740

 
- Forall with Indices Of
743

 
- Forall with Values Of
745

 
- Bulk Collect with Implicit Cursors (Varray)
746

 
- Bulk Collect with Implicit Cursors (Associative Array)
747

 
- Bulk Collect with Implicit Cursors (Nested Table)
748

 
- Bulk Collect with Explicit Cursors
749

 
- Bulk Collect and Returning in PLSQL
750

 
- Problems with Bulk Processing in PLSQL
753

 
- Bulk Processing and Limit in PLSQL
754

Forty Two
Advanced Use of Packages
755

 
- Introduction
756

 
- Defining Explicit Cursors in Packages
757

 
- Flexible Explicit Cursors in Packages
761

 
- Passing Parameters Using Packages
764

 
- Using PLSQL Tables as Parameters
767

 
- Global Variables using Packages
771

 
- Public and Private Variables in Packages
777

Forty Three
Dynamic SQL
779

 
- Introduction to Dynamic SQL
780

 
- Native Dynamic SQL (NDS) with Execute Immediate
781

 
- NDS Error Handling
784

 
- NDS with Inputs
785

 
- NDS with Output
787

 
- Dynamic Cursors and Sys_Refcursor
788

 
- NDS with Outputs
790

 
- Introduction to DBMS_SQL
791

 
- DBMS_SQL Example
792

 
- Exercise Nineteen
793