Top
Email
Bottom
Oracle PLSQL 19c with Apex 20
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Oracle PLSQL 19c with Apex 20 course at a glance

Pages
676No of slides in the Manual
Exercises
17Total No of Exercises
Sections
37No 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
37
17
676
Oracle SQL 19c with Apex 20
None
Contact us for the latest pricing

Apex 20 with Oracle 19c PLSQL 4 day training course
Exercise highlights
Exercises throughout the day(s)

Creating Functions using Application Express
Using table triggers to enforce data integrity
Testing and handling exceptions
Reading and writing to files

Index of Apex 20 with Oracle 19c PLSQL training course

Section
Contents
Page

One
PL/SQL Introduction
2
 
- Procedural Language for SQL
3
 
- Application Express
5
Two
Using Application Express
6
 
- Introduction
7
 
- Logging in
9
 
- Exiting the Session
13
 
- Timeout and Session Expiry
14
 
- Topography
16
Three
SQL Scripts
28
 
- Introduction
29
 
- Topography
32
 
- Upload Script
34
 
- Script Editor
37
 
- Run Script
39
 
- Manage Results
45
 
- Import
49
 
- Export
56
 
- Show Quotas
62
 
- Create
63
Four
PL/SQL Structure
65
 
- Basic Structure of PLSQL
66
 
- The Declaration Section in PLSQL
67
 
- Local Variables in PLSQL
68
 
- Naming Local Variables in PLSQL
70
 
- Quoted Identifier Variables
72
 
- Variable Datatypes (varchar2)
76
 
- Variable Datatypes (numbers)
77
 
- Variable Datatypes (number)
78
 
- Variable Datatypes (decimal)
81
 
- Variable Datatypes (integer)
82
 
- Variable Datatypes (pls_integer)
83
 
- Variable Datatypes (binary_integer)
84
 
- Variable Datatypes (natural)
85
 
- Variable Datatypes (naturaln)
86
 
- Variable Datatypes (positive)
87
 
- Variable Datatypes (positiven)
88
 
- Variable Datatypes (signtype)
89
 
- Variable Datatypes (simple_integer)
90
 
- Variable Datatypes (boolean)
91
 
- Variable Datatypes - Constants
92
 
- Compiling PLSQL
94
 
- Compiling PLSQL in SQL*Plus
95
 
- Common Compilation Problems
96
 
- Commenting PLSQL
98
 
- Documenting PLSQL
100
 
- Exercise One
101
Five
Performing Simple DML in PLSQL
104
 
- Introduction
105
 
- Using Local Variables
106
 
- Declaring Variables Using %Type
109
 
- Using Bind Variables
113
Six
SQL Commands
116
 
- Introduction
117
 
- Find Tables
119
 
- Saving SQL
124
 
- Saved SQL
125
 
- History
126
 
- Download
127
 
- Settings
128
 
- Invalid SQL
129
 
- Number of Rows returned
130
 
- Explain
132
 
- Exercise Two
137
Seven
Performing Conditional Statements in PLSQL
139
 
- If .. Then Statements
140
 
- If .. Then Else Statements
143
 
- If .. Then Elsif Statements
145
 
- Combining Tests
146
 
- Nesting Conditions
147
 
- Boolean Conditions
148
Eight
Communicating from PLSQL
149
 
- Messaging Via PLSQL
150
 
- Exercise Three
153
Nine
Exception Handling
156
 
- Introduction
157
 
- The When clause
160
 
- The When Others clause
163
 
- Raise_Application_Error
165
 
- Oracle Defined Exceptions
168
 
- Raising User Defined Exceptions
169
 
- Pragma Exceptions in PLSQL
172
 
- Grouping Exceptions in PLSQL
176
 
- Alternative Way to Handle SQL Errors in PLSQL
177
 
- Handling SQL Errors in PLSQL
178
 
- Advanced SQLErrm
180
 
- Exercise Four
181
Ten
Implicit Cursors
186
 
- Using Implicit Cursors in PLSQL
187
 
- Using Implicit Cursors to Select Records
189
 
- Exceptions in Implicit Cursors
190
 
- Attributes of Implicit Cursors
192
 
- SQL%FOUND
193
 
- SQL%NOTFOUND
194
 
- SQL%ROWCOUNT
195
Eleven
Sub-Blocking in PLSQL
196
 
- Introduction
197
 
- Defining
198
 
- Re-Raising Exceptions in PLSQL
200
 
- Exercise Five
203
Twelve
Explicit Cursors
210
 
- Using Explicit Cursors in PLSQL
211
 
- How Explicit Cursors Work
213
 
- Attributes of an Explicit Cursor
216
 
- Passing Parameters to Explicit Cursors
219
 
- Explicit Cursors Versus Implicit Cursors
222
 
- Exercise Six
223
Thirteen
Using Sequences in PLSQL 19c
225
 
- Using Sequences in PLSQL 19c
226
Fourteen
Loop Processing
228
 
- Iterative Processing in PLSQL
229
 
- Basic Looping in PLSQL
230
 
- While Looping in PLSQL
234
 
- For Looping in PLSQL
237
 
- Using Dates in For Loops in PLSQL
241
 
- Using Characters in For Loops in PLSQL
242
 
- Implicit Cursor For Looping in PLSQL
243
 
- Explicit Cursor For Looping in PLSQL
246
 
- Using other Loops with Explicit Cursors
248
 
- Loop Labels in PLSQL
251
 
- Loop Continue and Continue When
253
 
- Exercise Seven
257
Fifteen
PL/SQL Database Objects
265
 
- Introduction
266
 
- Compiling PLSQL Objects
267
 
- Re-Compiling PLSQL Objects
269
 
- Dropping PLSQL Objects
271
 
- Passing Parameters in PLSQL
272
 
- Passing Parameters using Positional Notation
273
 
- Passing Parameters Using Named Notation
275
 
- Using Mixed Notation in SQL
276
 
- Metadata (User_Source)
277
 
- Describing PLSQL Objects in SQL*PLUS
279
 
- Describing PLSQL Objects in Apex
280
Sixteen
Programmer Defined Functions
281
 
- Syntax of Functions in Oracle
282
 
- Suggested Naming Convention of Functions in Oracle
285
 
- Function Example in PLSQL
286
 
- Running Functions in SQL and PLSQL
288
 
- Running Functions in Oracle
289
 
- Creating Functions in Apex
290
 
- Object Browser for Functions
298
 
- Object Browser Dependencies
301
 
- Object Browser Errors
302
 
- Object Browser Grants
303
 
- Exercise Eight
304
Seventeen
Programmer Defined Procedures
308
 
- Syntax of Procedures in Oracle
309
 
- Example of Procedures in Oracle
311
 
- Executing Procedures in Oracle within SQL Plus
312
 
- Creating Procedures in Apex
313
 
- Object Browser for Procedures
321
 
- Object Browser Dependencies
324
 
- Object Browser Grants
325
Eighteen
Sub-programming Techniques
326
 
- Introduction
327
 
- Example
328
 
- Exercise Nine
330
Nineteen
Autonomous Transactions in PLSQL
333
 
- Introduction
334
 
- Example of an Autonomous Transaction
336
Twenty
Packaging Programmer Defined Programs
340
 
- Introduction
341
 
- Syntax of Packages in Oracle
343
 
- Compiling of Packages in Oracle
348
 
- Private Processes in PLSQL Packages
349
 
- Executing Packages in Oracle
352
 
- Handling Exceptions in Packages in PLSQL
353
 
- Creating Packages in Apex
356
 
- Creating Package Specification in Apex
358
 
- Object Browser for Packages
361
 
- Creating Package Body in Apex
366
 
- Package with methods on database table(s)
369
 
- Exercise Ten
375
Twenty One
DML Triggers in Oracle
376
 
- Oracle Triggers Introduction
377
 
- DML Triggers Introduction
378
 
- 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
388
 
- Checking the Event which fired the DML Trigger
393
 
- Using the Follows syntax to dictate Trigger Firing
395
 
- ORA-25022: cannot reference a trigger of different type
399
 
- Using the Disable syntax when Compiling Triggers
400
 
- Enabling/Disabling Triggers in an Oracle Database
401
 
- Metadata for Triggers (User_Triggers)
403
 
- Mutating DML Triggers in Oracle
405
 
- Using Autonomous Transactions in Triggers
408
 
- Creating Triggers Using Apex
411
 
- Exercise Eleven
424
Twenty Two
Instead Of Triggers
433
 
- Introduction to Instead Of Triggers
434
 
- Syntax of an Instead Of Trigger in Oracle
437
 
- Example of an Instead Of Trigger
439
 
- Deleting Records in Oracle using an Instead Of
446
 
- Using Follows in an Instead Of Trigger
447
 
- Creating Instead Of Triggers using Apex
448
Twenty Three
Compound Triggers
449
 
- Introduction to Compound Triggers
450
 
- Syntax for Table Compound Triggers
452
 
- Declaration Section
453
 
- Timing Points
454
 
- BEFORE STATEMENT
455
 
- BEFORE EACH ROW
456
 
- AFTER EACH ROW
457
 
- AFTER STATEMENT
458
 
- Compound Triggers for Views
459
 
- Compound Triggers in Apex
460
Twenty Four
System Triggers
461
 
- Introduction to System Triggers
462
 
- Syntax for System Triggers in Oracle
463
 
- System Event Triggers
464
 
- User Event Triggers
465
 
- Schema Event Triggers
468
 
- Database Level Attributes for System Triggers
472
 
- Exercise Twelve
348
Twenty Five
Rowtypes in PLSQL
482
 
- Using %Rowtype in PLSQL
483
 
- Declaring a Variable as a %Rowtype
484
 
- Referencing a %Rowtype Variable
485
 
- Populating a Variable which is a %Rowtype
486
 
- Passing a %Rowtype between Processes
489
 
- Declaring a Cursor %Rowtype
491
Twenty Six
Advanced Cursors in Oracle 19c
494
 
- For Update with Explicit Cursors
495
 
- Where Current Of Explicit Cursors
496
 
- Example of Updating with Explicit Cursors
497
 
- Using Nowait with Explicit Cursors
498
 
- Using Skip Locked with Explicit Cursors
499
 
- Using Wait with an Explicit Cursor
500
 
- Using Rowid with Explicit Cursors
501
 
- Using Returning Into in PLSQL
502
 
- Using Rowtype for DML
505
Twenty Seven
Invoker Rights in PLSQL
507
 
- The need to use Invoker Rights
508
 
- Example of Invoker Rights
509
 
- Using AUTHID in PLSQL
509
Twenty Eight
Forward Declaration
515
Twenty Nine
Savepoints in PLSQL
518
 
- Using Savepoints in a PLSQL process
519
 
- Example of Savepoints in a PLSQL process
520
Thirty
Case Statements and Case Expressions
521
 
- Introduction
522
 
- Using Searched Case in PLSQL
523
 
- Using Case in PLSQL
524
 
- Using Nested Case in PLSQL
525
 
- Using Case Expressions in PLSQL
527
 
- Using Nullif in PLSQL
531
 
- Using Coalesce in PLSQL
532
 
- Exercise Thirteen
533
Thirty One
Advanced Exceptions
535
 
- Handling Exceptions in Sub-Processes in PLSQL
536
 
- Using Nocopy When Declaring Processes
538
 
- Using DBMS_UTILITY Functions
542
 
- DBMS_UTILITY.FORMAT_ERROR_STACK
543
 
- DBMS_UTILITY.FORMAT_BACKTRACE
544
 
- Exercise Fourteen
547
Thirty Two
Advanced Use of Packages
550
 
- Introduction
551
 
- Defining Explicit Cursors in Packages
552
 
- Flexible Explicit Cursors in Packages
556
 
- Passing Parameters Using Packages
559
 
- Using PLSQL Tables as Parameters
562
 
- Global Variables using Packages
565
 
- Public and Private Variables in Packages
570
Thirty Three
Overloading Modules in PLSQL
572
 
- Introduction
573
 
- Example
574
 
- User_Procedures
577
 
- Exercise Fifteen
578
Thirty Four
Regular Expressions in PLSQL (Regex)
584
 
- Introduction
585
 
- Using REGEXP_LIKE in Where Clauses
586
 
- Case Sensitivity
588
 
- Line Anchors
589
 
- Using the Dot (Any Character)
591
 
- Counting Characters {Intervals}
594
 
- Character Lists
598
 
- Excluding Character Lists
602
 
- Alternatives to Character Lists
603
 
- Class Shorthands
606
 
- Or Patterns (Alternatives)
607
 
- Or Patterns with Character Lists
608
 
- Using the Question Mark (Optional)
609
 
- Using the Plus sign (Mandatory)
612
 
- Using the Star sign (Optional)
614
 
- Backreferencing
615
 
- POSIX
617
 
- Using POSIX in Oracle – [:upper:] example
619
 
- Using POSIX in Oracle – [:digit:] example
620
 
- Using POSIX in Oracle – Character equivalents
621
 
- Using Regexp_like in PLSQL
622
 
- Using Regexp_count in PLSQL
625
 
- Using Regexp_replace in PLSQL
629
 
- Using Regexp_instr in PLSQL
631
 
- Using Regexp_substr in PLSQL
633
 
- Using Regular Expressions in Oracle
635
Thirty Five
Using Alternative Quoting Mechanism in PLSQL
636
 
- Introduction
637
 
- Using Alternative Quoting Mechanism
638
 
- Exercise Sixteen
639
Thirty Six
File I/O
643
 
- Reading and Writing from the Operating System
644
 
- Accessing the File System using Directories
645
 
- Creating Directories
646
 
- Simple Write using UTL_FILE.PUT_LINE
648
 
- Simple Read Using UTL_FILE.GET_LINE
649
 
- Additional Functions in the UTL_FILE Package
650
 
- Using UTL_FILE.PUTF in PLSQL
651
 
- UTL_FILE Exceptions
653
 
- Using UTL_FILE to perform OS Commands
655
 
- Using UTL_FILE.Fcopy in PLSQL
656
 
- Using UTL_FILE.Fgetattr in PLSQL
657
 
- Using UTL_FILE.Fremove in PLSQL
658
 
- Using UTL_FILE.Frename in PLSQL
659
Thirty Seven
Dynamic SQL
660
 
- Introduction to Dynamic SQL
661
 
- Native Dynamic SQL (NDS) with Execute Immediate
662
 
- NDS Error Handling
665
 
- NDS with Inputs
666
 
- NDS with Output
668
 
- Dynamic Cursors and Sys_Refcursor
669
 
- NDS with Outputs
671
 
- Introduction to DBMS_SQL
672
 
- DBMS_SQL Example
673
 
- Exercise Seventeen
674

Sections of Apex 20 with Oracle 19c PLSQL

One
PL/SQL Introduction
Two
Using Application Express
Three
SQL Scripts
Four
PL/SQL Structure
Five
Performing Simple DML in PLSQL
Six
SQL Commands
Seven
Performing Conditional Statements in PLSQL
Eight
Communicating from PLSQL
Nine
Exception Handling
Ten
Implicit Cursors
Eleven
Sub-Blocking in PLSQL
Twelve
Explicit Cursors
Thirteen
Using Sequences in PLSQL 19c
Fourteen
Loop Processing
Fifteen
PL/SQL Database Objects
Sixteen
Programmer Defined Functions
Seventeen
Programmer Defined Procedures
Eighteen
Sub-programming Techniques
Nineteen
Autonomous Transactions in PLSQL
Twenty
Packaging Programmer Defined Programs
Twenty One
DML Triggers in Oracle
Twenty Two
Instead Of Triggers
Twenty Three
Compound Triggers
Twenty Four
System Triggers
Twenty Five
Rowtypes in PLSQL
Twenty Six
Advanced Cursors in Oracle 19c
Twenty Seven
Invoker Rights in PLSQL
Twenty Eight
Forward Declaration
Twenty Nine
Savepoints in PLSQL
Thirty
Case Statements and Case Expressions
Thirty One
Advanced Exceptions
Thirty Two
Advanced Use of Packages
Thirty Three
Overloading Modules in PLSQL
Thirty Four
Regular Expressions in PLSQL (Regex)
Thirty Five
Using Alternative Quoting Mechanism in PLSQL
Thirty Six
File I/O
Thirty Seven
Dynamic SQL

Synopsis for Apex 20 with Oracle 19c PLSQL training course

When you need to learn PLSQL to work with an Oracle Database, Seer Computing is the best place to come in the UK and Ireland, we've been giving PLSQL courses for over 20 years and have a variety of different front-ends available to interface with the Oracle Database.

Traditionally Developers would use the interface provided by Oracle in the 1980s which was PLSQL*Plus and we still have a training course in PLSQL using PLSQL*Plus, this is a command line interface, it's not changed much over the years and still gives the Developer the quickest route into the Database, is it User friendly?, well no not really, every command needs to be typed in or copied and pasted in and that includes finding out the structure of tables using the 'describe' command.

Over the years Oracle and other software manufacturers have come out with a number of front end GUI products to use PLSQL against the Oracle Database. Most Oracle Developers will have used the Toad product created by Quest software but for Users of the Database it will prove too expensive to roll out to occasional Users.

In the 1990s a lot of Companies used the Discoverer software to roll out an Oracle Database to Users, this involved limiting the access of the Database using an End User Layer, regardless of the knowledge a person may have in PLSQL they could not influence the values stored in the Database as it the data was read only, Seer Computing still offer Discoverer courses if your legacy system still uses it.

Around the beginning of the 21st century, Oracle brought out two more products other than PLSQL*Plus and Discoverer for running PLSQL against the Database; PLSQL Developer and Application Express.

PLSQL Developer is a free to download product from the Oracle website and we feel is comparable to the Toad product already mentioned. PLSQL Developer gives the User access to all schemas they have access to, listing all Objects and their structures. PLSQL Users would be most interested in Tables, Views and Columns within them for example. PLSQL Developer also gives Users a number of other facilities such as creating Objects, inserting values and of course downloading data into other formats for other products such as Microsoft Excel. Seer Computing offer a number of PLSQL Developer courses.

Let's turn our attention to Application Express. One of the main advantages of using Apex for Users of PLSQL is that the PLSQL*Plus software does not need to be installed on an individual's machine, instead it is reached via a URL of the server. Apex like all front ends to an Oracle Database can never by-pass the Database's security features, therefore a DBA can dictate what access the User has for a particular Schema, in addition Apex has an Administration of its own which can remove a lot of the front end options from specific Users.

This course is designed primarily for Delegates who have little or no experience of PLSQL or Apex, not only does it teach them the main areas of PLSQL but also how to use the commands and techniques within the Apex environment. At this point the Delegates can stop if all they wish to learn is how to use PLSQL in Apex, however we also provide a four day course in developing PLPLSQL within Application Express, as well as a course on the main feature of Application Express; Creating web applications.

We recommend anyone needing to learn how to create Application Express applications to attend both the PLSQL and PLPLSQL courses first so that they will have an iron clad understanding of how Databases work before they tackle the front ends to go onto the Database. Delegates already with PLSQL and PLPLSQL knowledge and training can go directly to learn creating Apps with Seer Computing.

This course as well as every other course that Seer Computing performs is available on-site at your premises, we come to you bringing our complete workshop, which consists of networked workstations, and at no point do we need access to your networks or Databases. We can perform on-site training for one or more delegates up to twelve delegates at a time and in fact the more on a course the more cost effective the charges are.

Contact our training advisers, they will help you decide on the correct training strategy, give options for all courses you are interested in, both dates and prices and ultimately support you through the whole process. If you need more than one quotation, ask away we can cater for any scenario you want to discuss.

Payment for courses and indeed any of our other services is via invoice, all our charges are subject to vat at the current UK rate of 20%, this can be settled by Bacs, cheque or credit card (4% surcharge).
Request for detailsClick for the contact form for the Oracle PLSQL 19c with Apex 20 training course
Public Courses
Oracle Essential SQL 19c
Days
2
Pages
340
Exercises
8
Sections
15
Public
Yes
On-Site
Yes
Public Courses
Oracle Essential PLSQL 19c
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 7.4 and Oracle 19c
Days
5
Pages
917
Exercises
21
Sections
38
Public
Yes
On-Site
Yes
Public Courses
Java 14 Programming with Eclipse 2020-03
Days
5
Pages
788
Exercises
23
Sections
44
Public
Yes
On-Site
Yes
Public Courses
Java 14 Swing with Eclipse 2020-03
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