Top
Email
Bottom
PLSQL 9i
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Download our latest
Android and iPhone apps ...
Click on whichever one of our Apps you are interested in

PLSQL 9i course at a glance

Pages
647No of slides in the Manual
Exercises
18Total No of Exercises
Sections
38No of main topics, see index below
Public
NoPublic course in the UK and Ireland
On-Site
YesWe can come to your venue
Level
21 - Beginners
2 - Intermediate
3 - Advanced
Days
5Week days but we can put on weekend courses
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

Index of Oracle 9i PLSQL training course

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

Index of Oracle 9i PLSQL training course

Section
Section Name
One
PLSQL Introduction
Two
PLSQL Structure
Three
Performing Simple DML in PLSQL
Four
Performing Conditional Statements in PLSQL
Five
Communicating from PLSQL
Six
Exception Handling
Seven
Implicit Cursors
Eight
Sub-Blocking in PLSQL
Nine
Explicit Cursors
Ten
Loop Processing
Eleven
Case Statements and Case Expressions
Twelve
Rowtypes in PLSQL
Thirteen
Savepoints in PLSQL
Fourteen
PL/SQL Database Objects
Fifteen
Programmer Defined Functions
Sixteen
Programmer Defined Procedures
Seventeen
Sub-programming Techniques
Eighteen
Advanced Cursors in Oracle 9i
Nineteen
Encrypting Code
Twenty
Invoker Rights in PLSQL
Twenty One
Forward Declaration
Twenty Two
Autonomous Transactions in PLSQL
Twenty Three
Advanced Exceptions
Twenty Four
Packaging Programmer Defined Programs
Twenty Five
Overloading Modules in PLSQL
Twenty Six
DML Triggers in Oracle
Twenty Seven
Instead Of Triggers
Twenty Eight
System Triggers
Twenty Nine
File I/O
Thirty
Working with Clobs and Blobs in PLSQL
Thirty One
DBMS_METADATA
Thirty Two
Using Contexts
Thirty Three
Timestamps in PLSQL
Thirty Four
PLSQL Data Structures and Collections
Thirty Five
Associate Arrays (Index by Tables)
Thirty Six
Bulk SQL in PLSQL
Thirty Seven
Advanced Use of Packages
Forty Three
Dynamic SQL

Synopsis for Oracle 9i PLSQL training 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.
Request for detailsClick for the contact form for the PLSQL 9i training course
Public Courses
Oracle Essential SQL 21c
Days
2
Pages
340
Exercises
8
Sections
15
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 21c
Days
3
Pages
384
Exercises
13
Sections
25
Public
Yes
On-Site
Yes
Public Courses
Using SQL with Oracle SQL Developer 19.2
Days
4
Pages
884
Exercises
18
Sections
37
Public
Yes
On-Site
Yes
Public Courses
Essential JavaScript
Days
4
Pages
660
Exercises
16
Sections
31
Public
Yes
On-Site
Yes
Public Courses
Essential CSS 3
Days
4
Pages
800
Exercises
15
Sections
30
Public
Yes
On-Site
Yes
Public Courses
Essential SQLite 3
Days
3
Pages
550
Exercises
12
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential MySQL 8
Days
4
Pages
533
Exercises
16
Sections
24
Public
Yes
On-Site
Yes
Public Courses
Essential Python 3
Days
5
Pages
955
Exercises
17
Sections
41
Public
Yes
On-Site
Yes
Public Courses
Oracle Application Express 20
Days
0
Pages
0
Exercises
0
Sections
0
Public
Yes
On-Site
Yes
Public Courses
PHP 8 and MySQL 8
Days
5
Pages
870
Exercises
23
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 17 Programming with Eclipse 2021-12
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 17 Swing with Eclipse 2021-12
Days
5
Pages
756
Exercises
19
Sections
35
Public
Yes
On-Site
Yes
Click on the course name to see full details, left and right to view other courses