Top
Email
Bottom
Apex PLSQL
 
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

Apex PLSQL course at a glance

Pages
496No of slides in the Manual
Exercises
14Total No of Exercises
Sections
26No 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
4Week 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:
4 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
26
14
496
Apex SQL
None
Contact us for the latest pricing

Index of Essential Oracle 11g PLSQL training course with APEX 4.2

Section
Contents
Page

One
PLSQL Introduction
2

 
- Procedural Language for SQL
3

 
- Application Express
5

Two
Using Application Express
6

 
- Introduction
7

 
- Logging in
9

 
- The Workspace
12

 
- The Schema
13

 
- Dashboard Topography
14

 
- SQL Workshop
15

 
- Administration
16

 
- Changing the Password
18

 
- About Administration
19

 
- Exiting the Session
20

 
- Invalid Login Credentials
21

 
- Timeout and Session Expiry
22

Three
SQL Scripts
23

 
- Introduction
24

 
- Interface
25

 
- Actions
27

 
- Actions (Select Columns)
28

 
- Actions (Filter)
29

 
- Actions (Rows Per Page)
33

 
- Actions (Format)
34

 
- Actions (Sort)
35

 
- Actions (Control Break)
36

 
- Actions (Highlight)
37

 
- Actions (Compute)
38

 
- Actions (Aggregate)
40

 
- Actions (Chart)
41

 
- Actions (Group By)
42

 
- Actions (Flashback)
43

 
- Actions (Save Report)
45

 
- Actions (Reset)
46

 
- Actions (Help)
47

 
- Actions (Download)
48

 
- Actions (Download CSV)
49

 
- Actions (Download HTML)
51

 
- Actions (Download Email)
53

 
- Actions (Subscription)
55

 
- Alternative View of Scripts (View Icons)
56

 
- Running Scripts
57

 
- Manage Script Results
59

 
- Other Options
64

 
- Upload
65

 
- Create
68

 
- Show Quotas
69

 
- Exporting SQL Scripts from Application Express
70

 
- Importing SQL into Application Express
73

 
- Exercise One
75

Four
PLSQL Structure
78

 
- Basic Structure of PLSQL
79

 
- The Declaration Section in PLSQL
80

 
- Local Variables in PLSQL
81

 
- Naming Local Variables in PLSQL
83

 
- Quoted Identifier Variables
85

 
- Variable Datatypes (varchar2)
89

 
- Variable Datatypes (numbers)
90

 
- Variable Datatypes (number)
91

 
- Variable Datatypes (decimal)
94

 
- Variable Datatypes (integer)
95

 
- Variable Datatypes (pls_integer)
96

 
- Variable Datatypes (binary_integer)
97

 
- Variable Datatypes (natural)
98

 
- Variable Datatypes (naturaln)
99

 
- Variable Datatypes (positive)
100

 
- Variable Datatypes (positiven)
101

 
- Variable Datatypes (signtype)
102

 
- Variable Datatypes (simple_integer)
103

 
- Variable Datatypes (boolean)
104

 
- Variable Datatypes - Constants
105

 
- Running PLSQL in Application Express
107

 
- Compiling PLSQL
108

 
- Common Compilation Problems
109

 
- Commenting PLSQL
111

 
- Documenting PLSQL
113

 
- Exercise Two
114

Five
SQL Commands
115

 
- Introduction
116

 
- Topography
117

 
- Command Window
118

 
- Autocommit
119

 
- Rows
120

 
- Clear Command
121

 
- Find Tables
122

 
- Find Tables (Rows)
124

 
- Find Tables (Views)
125

 
- Find Tables (Columns)
126

 
- Save
128

 
- Results
130

 
- Explain
131

 
- Describe
132

 
- History
133

 
- Commenting PLSQL
134

Six
Performing Simple DML in PLSQL
135

 
- Introduction
136

 
- Using Local Variables
137

 
- Declaring Variables Using %Type
140

 
- Using Bind Variables
144

 
- Exercise Three
146

Seven
Performing Conditional Statements in PLSQL
149

 
- If .. Then Statements
150

 
- If .. Then Else Statements
153

 
- If .. Then Elsif Statements
155

 
- Combining Tests
156

 
- Nesting Conditions
157

 
- Boolean Conditions
158

Eight
Communicating from Application Express
159

 
- Messaging Using DBMS_OUTPUT
160

 
- Exercise Four
163

Nine
Exception Handling
166

 
- Introduction
167

 
- The When Clause
170

 
- The When Others Clause
173

 
- Raise_Application_Error
175

 
- Oracle Defined Exceptions
178

 
- Raising User Defined Exceptions
179

 
- Pragma Exceptions in PLSQL
182

 
- Grouping Exceptions in PLSQL
186

 
- Alternative Way to Handle SQL Errors in PLSQL
187

 
- Handling SQL Errors in PLSQL
188

 
- Advanced SQLErrm
190

 
- Exercise Five
191

Ten
Implicit Cursors
194

 
- Using Implicit Cursors in PLSQL
195

 
- Using Implicit Cursors to Select Records
197

 
- Exceptions in PLSQL
198

 
- Attributes of Implicit Cursors
200

 
- SQL%FOUND
201

 
- SQL%NOTFOUND
202

 
- SQL%ROWCOUNT
203

Eleven
Sub-Blocking in PLSQL
204

 
- Introduction
205

 
- Defining
206

 
- Re-Raising Exceptions in PLSQL
208

 
- Exercise Six
211

Twelve
Explicit Cursors
216

 
- Using Explicit Cursors in PLSQL
217

 
- How Explicit Cursors Work
219

 
- Attributes of an Explicit Cursor
222

 
- Passing Parameters to Explicit Cursors
225

 
- Explicit Cursors Versus Implicit Cursors
228

 
- Exercise Seven
229

Thirteen
Using Sequences in PLSQL 11g
231

Fourteen
Loop Processing
234

 
- Iterative Processing in PLSQL
235

 
- Basic Looping in PLSQL
236

 
- While Looping in PLSQL
240

 
- For Looping in PLSQL
243

 
- Using Dates in For Loops in PLSQL
247

 
- Using Characters in For Loops in PLSQL
248

 
- Implicit Cursor Looping in PLSQL
249

 
- Explicit Cursor Looping in PLSQL
252

 
- Using other Loops with Explicit Cursors
254

 
- Loop Labels in PLSQL
257

 
- Loop Continue and Continue When
259

 
- Exercise Eight
263

Fifteen
PL/SQL Database Objects
269

 
- PLSQL Objects
270

 
- Functions
271

 
- Procedures
273

 
- Packages
274

 
- Triggers
275

Sixteen
Object Browser in Application Express
276

 
- Introduction
277

 
- Topography
278

 
- Creating Functions
281

 
- Naming Functions
282

 
- Arguments
284

 
- Passing Arguments Using Named Notation
289

 
- Passing Arguments Using Mixed Notation
290

 
- Arguments using %type
291

 
- Function Body
292

 
- Save & Compile
296

 
- Editing the Function
298

 
- Dependencies
302

 
- Errors
303

 
- Grants
304

 
- Syntax for Creating Functions
307

 
- Syntax for Running Functions
310

 
- Object Maintenance
312

 
- Describing PLSQL Objects
316

 
- Metadata (User Source)
317

 
- Exercise Nine
318

Seventeen
Creating PLSQL Procedures in Object Browser
322

 
- Introduction
323

 
- Creating Procedures
324

 
- Naming Procedures
325

 
- Arguments
326

 
- Define
327

 
- Confirm
329

 
- Save & Compile
330

 
- Define
327

 
- Syntax of Procedures in Oracle
332

 
- Executing Procedures in SQL Commands
333

Eighteen
Sub-programming Techniques
334

 
- Introduction
335

 
- Example
336

 
- Autonomous Transaction
338

 
- Exercise Ten
340

Nineteen
Creating Packages in Object Browser
344

 
- Introduction
345

 
- Creating Packages in Object Browser
347

 
- Arguments
349

 
- Define
350

 
- Defining a Specification
351

 
- Object Browser
352

 
- Creating the Package Body
353

 
- Defining the Body
354

 
- Syntax of Packages
355

 
- Private Processes in PLSQL Packages
357

 
- Executing Packages in Oracle
359

 
- Handling Exceptions in Packages
361

 
- Package with methods on database table(s)
363

 
- Exercise Eleven
368

Twenty
DML Triggers in Oracle
370

 
- Oracle Triggers Introduction
371

 
- DML Triggers Introduction
372

 
- DML Triggers Timing Points
374

 
- Creating Triggers in Object Browser
375

 
- Define
376

 
- DML Triggers Suggested Naming Conventions
377

 
- Firing Point for Triggers
378

 
- Triggering Options
379

 
- For Each Row
380

 
- When Conditions
381

 
- DML Triggers When Conditions Examples
382

 
- ORA-04077: When clause cannot be used with table
383

 
- Declaration Area
384

 
- Referencing Values within Triggers
386

 
- Checking the Event which fired the DML Trigger
389

 
- Mutating DML Triggers in Oracle
391

 
- Advanced DML Trigger Settings
395

 
- Syntax of DML Triggers in PLSQL
396

 
- Using the Follows syntax to dictate Trigger Firing
397

 
- ORA-25022: cannot reference a trigger of different type
401

 
- Referencing Values within Triggers
402

 
- Using the Disable syntax when Compiling Triggers
403

 
- Enabling/Disabling Triggers in an Oracle Database
404

 
- Metadata for Triggers (User_Triggers)
406

 
- Exercise Twelve
407

Twenty One
Rowtypes in PLSQL
414

 
- Using %Rowtype in PLSQL
415

 
- Declaring a Variable as a %Rowtype
416

 
- Referencing a %Rowtype Variable
417

 
- Populating a Variable which is a %Rowtype
418

 
- Passing a %Rowtype between Processes
421

 
- Declaring a Cursor %Rowtype
423

Twenty Two
Advanced Cursors in Oracle 11g
426

 
- For Update with Explicit Cursors
427

 
- Where Current of Explicit Cursors
428

 
- Example of Updating with Explicit Cursors
429

 
- Using Nowait with Explicit Cursors
430

 
- Using Skip Locked with Explicit Cursors
431

 
- Using Wait with an Explicit Cursor
432

 
- Using Rowid with Explicit Cursors
433

 
- Using Returning Into in PLSQL
434

 
- Using Rowtype for DML
437

Twenty Three
Savepoints in PLSQL
439

 
- Using Savepoints in a PLSQL process
440

 
- Example of Savepoints in PLSQL process
441

Twenty Four
Case Statements and Case Expressions
442

 
- Introduction
443

 
- Using Searched Case in PLSQL
444

 
- Using Case in PLSQL
445

 
- Using Nested Case in PLSQL
446

 
- Using Case Expressions in PLSQL
448

 
- Using Nullif in PLSQL
452

 
- Using Coalesce in PLSQL
453

 
- Exercise Thirteen
454

Twenty Five
File I/O
457

 
- Reading and Writing Files in PLSQL
458

 
- Accessing the File System using Directories
459

 
- Creating Directories
460

 
- Simple Write using UTL_FILE.PUT_LINE
462

 
- Simple Read Using UTL_FILE.GET_LINE
463

 
- Additional Functions in the UTL_FILE Package
464

 
- Using UTL_FILE.PUTF in PLSQL
465

 
- UTL_FILE Exceptions
467

 
- Using UTL_FILE to perform Operating System Commands
469

 
- Using UTL_FILE.Fcopy in PLSQL
470

 
- Using UTL_FILE.Fgetattr in PLSQL
471

 
- Using UTL_FILE.Fremove in PLSQL
472

 
- Using UTL_FILE.Frename in PLSQL
473

Twenty Six
Dynamic SQL
474

 
- Introduction to Dynamic SQL
475

 
- Native Dynamic SQL (NDS) with Execute Immediate
476

 
- NDS Error Handling
479

 
- NDS with Inputs
480

 
- NDS with Output
482

 
- Dynamic Cursors and Sys_Refcursor
483

 
- NDS with Outputs
485

 
- Introduction to DBMS_SQL
486

 
- DBMS_SQL Example
487

 
- Exercise Thirteen
488

 
 
 

Sections of Essential PLSQL

1
PLSQL Introduction

2
Using Application Express

3
SQL Scripts

4
PLSQL Structure

5
SQL Commands

6
Performing Simple DML in PLSQL

7
Performing Conditional Statements in PLSQL

8
Communicating from Application Express

9
Exception Handling

10
Implicit Cursors

11
Sub-Blocking in PLSQL

12
Explicit Cursors

13
Using Sequences in PLSQL 11g

14
Loop Processing

15
PL/SQL Database Objects

16
Object Browser in Application Express

17
Creating PLSQL Procedures in Object Browser

18
Sub-programming Techniques

19
Creating Packages in Object Browser

20
DML Triggers in Oracle

21
Rowtypes in PLSQL

22
Advanced Cursors in Oracle 11g

23
Savepoints in PLSQL

24
Case Statements and Case Expressions

25
File I/O

26
Dynamic SQL


Synopsis for Essential Oracle 11g PLSQL training course with APEX 4.2

This is one of our previous training courses which uses Application Express (Apex) to teach delegates the procedural language PLSQL, we love PLSQL it is such a versatile language for use against an Oracle Database, whichever version you currently have installed.

PLSQL is effectively an extension of SQL, it picks up where SQL runs out of steam, although we are very experienced practitioners of Oracle SQL (and other flavours) we do accept that it has weaknesses, for example the Database table constraints in SQL are pretty primitive and not straight forward to maintain, of course, you can add indexing and limited ranges via SQL but what if you need to compare a column value with another? For example when a date of birth, although valid is not within the acceptable value range for the business rules, what if someone adds a date in the future for date of birth, it’s a valid date in its structure but you can't allow births in the future!! The secret here is to place an before insert trigger on the table, this is written in PLSQL and using raise_application_error the PLSQL Developer can prevent the record spoiling the integrity of the data (or as an alternative the date could be changed to today's date – this is a matter of business policy).

In addition to maintaining data integrity, PLSQL can be used to create processes to perform bulk actions on the Database overnight or indeed during transactional events, Developers can also create additional Functions to standardise business rules within the Database, an example might be to check whether the date entered is a bank holiday in the UK or not, Oracle does not come with a built in function to determine this but any Company dealing with the UK either within the country or outside would need to know if on the date specified the business is open 'for business'.

The course webpage you are currently looking at is not the most current we can supply for you and your Delegates, the Database has moved on a few versions in the last years and we have also moved with it, therefore you might wish to use our menu system to view what our current offering of training courses in PLSQL is. We are however, perfectly happy to come to you to perform this course on site, within this page you will find the essential details of the course, its duration, its minimum requirements for attendance (SQL knowledge for example) and we also publish the breakdown of the course content with PDFs for download for review.

When we perform the course onsite, we don’t use your equipment, all we need is a room with tables and chairs and an electricity supply! We provide the rest, Oracle Database, an individual machine for each Delegate and our comprehensive and high quality manuals, our Consultants are people who walk the walk not just stand and repeat what's in the manual, they are seasoned practitioners of PLSQL and know what they are talking about.

There are plenty of ways to contact us, these details are at the top and bottom of the page, plus there is an enquiry form if you wish to fill that out, any quotations we give are valid for 6 months and not subject to minimum numbers for booking, get in touch and find out how we can help you.

Request for detailsClick for the contact form for the Apex PLSQL 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