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

Course Name: PLSQL 9i

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
38
18
647
Oracle Essential SQL 9i
None
Contact us for the latest pricing

Synopsis for course

A complete guide to PLSQL in a 9i 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

Section
Section Name
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 (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 9i
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

Nineteen
Encrypting Code
308

 
- Introduction
309

 
- Using Wrap.exe to encrypt processes in Oracle
310

 
- Example of using Wrap.exe to encrypt processes
313

Twenty
Invoker Rights in PLSQL
315

 
- The need to use Invoker Rights
316

 
- Example of Invoker Rights
317

 
- Using AUTHID in PLSQL
320

Twenty One
Forward Declaration
323

Twenty Two
Autonomous Transactions in PLSQL
326

 
- Introduction
327

 
- Example of an Autonomous Transaction
329

 
- Rollback in Autonomous Transactions
336

 
- Using Autonomous Transactions in Triggers
337

Twenty Three
Advanced Exceptions
339

 
- Handling Exceptions in Sub-Processes in PLSQL
340

 
- Using Nocopy when Declaring Processes
342

 
- Exercise Eleven
346

Twenty Four
Packaging Programmer Defined Programs
349

 
- Syntax of Packages in Oracle
350

 
- Compiling of Packages in Oracle
355

 
- Private Processes in PLSQL Packages
356

 
- Executing Packages in Oracle
358

 
- Handling Exceptions in Packages in PLSQL
359

Twenty Five
Overloading Modules in PLSQL
361

 
- Introduction
362

 
- Example
363

 
- User_Procedures
367

 
- Exercise Twelve
368

Twenty Six
DML Triggers in Oracle
375

 
- Oracle Triggers Introduction
376

 
- DML Triggers Introduction
377

 
- DML Triggers Timing Points
380

 
- DML Triggers When Conditions
381

 
- DML Triggers When Conditions Examples
382

 
- DML Triggers Suggested Naming Conventions
384

 
- Syntax of DML Triggers in PLSQL
386

 
- Referencing Values within Triggers
387

 
- Checking the Event which fired the DML Trigger
391

 
- Enabling/Disabling Triggers in an Oracle Database
393

 
- Metadata for Triggers (User_Triggers)
395

 
- Mutating DML Triggers in Oracle
396

 
- Exercise Thirteen
400

Twenty Seven
Instead Of Triggers
407

 
- Introduction to Instead Of Triggers
408

 
- Syntax of an Instead Of Trigger in Oracle
411

 
- Example of an Instead Of Trigger
413

 
- Deleting Records in Oracle using an Instead Of
421

Twenty Eight
System Triggers
422

 
- Introduction to System Triggers
423

 
- Syntax for System Triggers in Oracle
424

 
- System Event Triggers
425

 
- User Event Triggers
426

 
- Schema Event Triggers
430

 
- Database Level Attributes for System Triggers
436

 
- Exercise Fourteen
437

Twenty Nine
File I/O
441

 
- Reading and Writing Files in PLSQL
442

 
- Simple Write using UTL_FILE.PUT_LINE
444

 
- Simple Read Using UTL_FILE.GET_LINE
445

 
- Additional Functions in the UTL_FILE Package
446

 
- Using UTL_FILE.PUTF in PLSQL
447

 
- UTL_FILE Exceptions
449

 
- Using UTL_FILE to perform Operating System Commands
451

 
- Using UTL_FILE.Fcopy in PLSQL
452

 
- Using UTL_FILE.Fgetattr in PLSQL
453

 
- Using UTL_FILE.Fremove in PLSQL
454

 
- Using UTL_FILE.Frename in PLSQL
455

Thirty
Working with Clobs and Blobs in PLSQL
456

 
- Introduction
457

 
- Creation of Clobs in Tables
458

 
- Using Clobs in PLSQL
459

 
- Inserting Clobs into the Database using PLSQL
460

 
- Writing Clobs to the Filesystem using PLSQL
464

 
- Introduction to Blobs
465

 
- Inserting Blobs into the Database using PLSQL
466

 
- Writing Blobs into the Filesystem using PLSQL
467

 
- DBMS_LOB exceptions
470

Thirty One
DBMS_METADATA
472

 
- Generating Database Object Scripts
473

 
- List of Object Types
474

 
- Example of Using Dbms_Metadata
475

 
- Exercise Fifteen
477

Thirty Two
Using Contexts
479

 
- Introduction
480

 
- Attributes
481

 
- User Defined
489

 
- Creating Contexts
490

 
- Creating a Package for a Context
491

 
- Creating a Context
492

 
- Populating a Context
493

 
- Accessing a Context
494

 
- Listing Contents of a Context
495

 
- Deleting Contents of a Context
497

 
- Changing Context Behaviour
499

Thirty Three
Timestamps in PLSQL
500

 
- Timestamps and Intervals in PLSQL
501

 
- Timestamp Variables in PLSQL
502

 
- Timestamp in PLSQL
504

 
- Populating Timestamps in PLSQL
507

 
- Using Extract in PLSQL
509

 
- Timestamp with Time Zone
510

 
- Timestamp Time Zone and Extract
513

 
- Using sys_extract_utc to Normalise Timestamps
514

 
- Using the To_Timestamp_TZ function
515

 
- Using the From_TZ function
517

 
- Timestamp with Local Time Zone
519

 
- Intervals
521

 
- Populating Year to Month Intervals
524

 
- Populating Day to Second Intervals
525

 
- Using Intervals in PLSQL
526

 
- Using Intervals with Extract in PLSQL
531

 
- Populating Intervals using Timestamps
532

 
- Exercise Sixteen
533

Thirty Four
PLSQL Data Structures and Collections
536

 
- Programmer Defined Records
537

 
- Nested Programmer Defined Records
541

 
- Varrays in PLSQL
543

 
- Using Varrays in Loops
546

 
- Populating Varrays
547

 
- Extending Varrays
548

 
- Deleting from Varrays
550

 
- Varrays and Tables
551

 
- Multilevel Varrays
554

Thirty Five
Associate Arrays (Index by Tables)
557

 
- Introducing Associate Arrays in PLSQL
558

 
- Declaring PLSQL Tables
560

 
- Populating PLSQL Tables
561

 
- Using Count with PLSQL Tables
563

 
- Using Delete with PLSQL Tables
564

 
- Using Exists with PLSQL Tables
565

 
- Using First/Last with PLSQL Tables
566

 
- Using Next/Prior with PLSQL Tables
567

 
- Using Varchar2 as an index with a PLSQL Table
568

 
- Multi-level PLSQL Table
572

 
- Nested PLSQL Table
573

 
- Trimming Nested PLSQL Tables
576

 
- Multi-Level Nested PLSQL Tables
578

 
- Exercise Seventeen
579

Thirty Six
Bulk SQL in PLSQL
581

 
- Introduction to Bulk Processing
582

 
- Bulk Binding in PLSQL
583

 
- Bulk Binding in PLSQL using Associate Arrays
584

 
- Bulk Binding in PLSQL using Varrays
585

 
- Bulk Binding in PLSQL using Nested Tables
586

 
- Forall with Save Exceptions
587

 
- Forall with SQL%BULKEXCEPTIONS
589

 
- Bulk Collect with Implicit Cursors (Varray)
592

 
- Bulk Collect with Implicit Cursors (Associative Array)
593

 
- Bulk Collect with Implicit Cursors (Nested Table)
594

 
- Bulk Collect with Explicit Cursors
595

 
- Bulk Collect and Returning in PLSQL
596

 
- Problems with Bulk Processing in PLSQL
599

 
- Bulk Processing and Limit in PLSQL
600

Thirty Seven
Advanced Use of Packages
601

 
- Introduction
602

 
- Defining Explicit Cursors in Packages
603

 
- Flexible Explicit Cursors in Packages
607

 
- Passing Parameters Using Packages
610

 
- Using PLSQL Tables as Parameters
613

 
- Global Variables using Packages
617

 
- Public and Private Variables in Packages
623

Forty Three
Dynamic SQL
625

 
- Introduction to Dynamic SQL
626

 
- Native Dynamic SQL (NDS) with Execute Immediate
627

 
- NDS Error Handling
630

 
- NDS with Inputs
631

 
- NDS with Output
633

 
- Dynamic Cursors and Sys_Refcursor
634

 
- NDS with Outputs
636

 
- Introduction to DBMS_SQL
637

 
- DBMS_SQL Example
638

 
- Exercise Eighteen
639