×
Oracle Essential PLSQL 19c
 
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
List of Public Courses
Select the location for this training course

Oracle Essential PLSQL 19c course at a glance

Pages
384
No of slides in the Manual
Exercises
13
Total No of Exercises
Sections
25
No of main topics, see index below
Public
Yes
Public course in the UK and Ireland
On-Site
Yes
We can come to your venue
Level
2
1 - Beginners
2 - Intermediate
3 - Advanced
Days
3
Week days but we can put on weekend courses

Course Name: Oracle Essential PLSQL 19c

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:
3 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
Yes
25
13
384
Oracle Essential SQL 19c
None
Contact us for the latest pricing
PDF - Course IndexClick to view the PDF index for the course

PDF - Exercise ExampleClick to view the PDF sample exercise

PDF - Sample PagesClick to view the PDF sample course pages

Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course
Top
Email
Bottom

Synopsis for Essential Oracle 19c PL/SQL training course

What's the point of learning Oracle PLSQL ? isn’t Oracle SQL enough to keep the Database running sweet and providing all the information we painstakingly entered into it ? I think you already know the answer to that … a big fat NO !!

SQL is a very important command language in a lot of different Database systems (most Databases of which we have training course for) but whilst it is the fundamental language for creating objects, altering and updating these objects it can fall short when Developers try to make bulk changes, interrogate the Database with complex rules and protect the Database from wilful and accidental data incidents. Here at Seer Computing we love SQL in all of its dialects and versions but we adore PLSQL for helping us keep many Databases working sweetly and effectively.

So do you need Oracle PLSQL as a skill? yes if you are responsible for anything other than extracting data from an Oracle Database, if you are a high end User manipulating the contents of the Oracle tables and views and producing mission critical reports then you probably will never need to develop in PLSQL, however if you create tables, have important business rules governing the data within the Database, need to extract data for use in other Databases, convert incoming Datasets and need to protect the information from being trashed you might wish to give learning PLSQL a thought.

Here is a non-exhaustive list of what PLSQL is good for, in no particular order of importance …

Data integrity checks using Database triggers
Functions based on in-house business rules
Integration of comma separated files into the Database enforcing Database constraints
Centralisation and packaging of key processing
Merging complex data structures into aggregated values and records
Enforcing security access
Extraction of records for export
Error handling
Bulk processing of data in batch
Database maintenance with scripts
Retrieval and insertion of Long Objects into the Oracle Database

What isn’t PLSQL good at?


For a start, PLSQL can't produce webpages directly, it can be invoked by JavaScript and PHP (both of which we have courses for) to produce dynamic webpages, but it cannot be embedded in HTML
PLSQL cannot be used directly as a gatherer of information in a GUI environment, that role falls to other products including Oracle Forms and Java etc (guess what, we do courses in Forms and Java too !)
PLSQL cannot be run outside the confines of an Oracle Database, it is specifically a language embedded in Oracle

This course teaches Delegates the structure and usage of PLSQL, moving onto to applying the code to embedded objects such as Functions and Procedures, then the Delegates will learn how to package the individual objects together. Moving on, the use of Table triggers will be examined and practiced with further amendment to include audit logs.

This is a three day course, it is essential that the Delegates already have Oracle SQL training or experience (we offer a range of excellent SQL courses), the clue is in the last three characters, PLSQL is an extension of SQL therefore you'll need to know that before we can expand your knowledge.

Both the SQL and PLSQL courses are available as public courses which we have sensibly run back to back at a variety of locations in the UK and Ireland at various times of the year, so start on a Monday with SQL and by the end of Friday afternoon you will be trained in both Oracle SQL and PLSQL.

An alternative is to learn onsite, this is particularly cost effective because the Delegates will be at their normal place of work, no travelling to a conference centre, it also means that we can schedule the course when it suits you (in other words, doesn’t have to start on a Wednesday), plus Delegates can be pulled out of the course if needed to fire-fight, the Training Consultant will wait until they return.

Contact our training advisors to discuss your requirement. We give no obligation quotations which are valid for six months and we can also prepare a number of training scenarios to your specification

Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course

Essential Oracle 19c PL/SQL training course
Exercise highlights
(some of the many things you get to practice)

Looping through records from the Database
Creating variables in PLSQL
Writing to the filesystem
Using Execute Immediate
DML Trigger creation

Index of Essential Oracle 19c PL/SQL training course

Section
Contents
Page

One
PL/SQL Introduction
2

 
- Procedural Language for SQL
3

Two
PL/SQL 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 (simple_integer)
30

 
- Variable Datatypes (boolean)
31

 
- 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

Three
The SQL Plus Interface
44

 
- Introduction
45

 
- Anatomy of SQL Plus
50

 
- ORA-01017: invalid username/password; logon denied
51

 
- ORA-12154: TNS:could not resolve the connect identifier
52

 
- Exiting an SQL Plus Session
53

 
- Using the DOS Window
54

 
- Copying and Pasting
55

 
- Using the Function Keys
58

 
- Using the Line Editor in SQL Plus
59

 
- Using the Editor in SQL Plus
60

 
- SQL Plus Environment Settings
61

 
- File Manipulation in SQL Plus
62

 
- Spooling Results in SQL Plus
63

 
- Changing Passwords / Clearing the Screen in SQL
64

 
- Exercise One
65

Four
Performing Simple DML in PLSQL
67

 
- Introduction
68

 
- Using Local Variables
69

 
- Declaring Variables Using %Type
72

 
- Using Substitution Values in PLSQL
76

 
- Exercise Two
79

Five
Performing Conditional Statements in PLSQL
81

 
- If .. Then Statements
82

 
- If .. Then Else Statements
85

 
- If .. Then Elsif Statements
87

 
- Combining Tests
88

 
- Nesting Conditions
89

 
- Boolean Conditions
90

Six
- Communicating from PLSQL
91

 
- Messaging Via PLSQL
92

 
- Exercise Three
97

Seven
Exception Handling
99

 
- Introduction
100

 
- The When clause
103

 
- The When Others clause
106

 
- Raise_Application_Error
108

 
- Oracle Defined Exceptions
112

 
- Raising User Defined Exceptions
113

 
- Pragma Exceptions in PLSQL
116

 
- Grouping Exceptions in PLSQL
120

 
- Alternative Way to Handle SQL Errors in PLSQL
121

 
- Handling SQL Errors in PLSQL
122

 
- Advanced SQLErrm
124

 
- Exercise Four
125

Eight
Implicit Cursors
128

 
- Using Implicit Cursors in PLSQL
129

 
- Using Implicit Cursors to Select Records
131

 
- Exceptions in Implicit Cursors
132

 
- Attributes of Implicit Cursors
134

 
- SQL%FOUND
135

 
- SQL%NOTFOUND
136

 
- SQL%ROWCOUNT
137

Nine
Sub-Blocking in PLSQL
138

 
- Introduction
139

 
- Defining
140

 
- Re-Raising Exceptions in PLSQL
142

 
- Exercise Five
145

Ten
Explicit Cursors
149

 
- Using Explicit Cursors in PLSQL
150

 
- How Explicit Cursors Work
152

 
- Attributes of an Explicit Cursor
155

 
- Passing Parameters to Explicit Cursors
158

 
- Explicit Cursors Versus Implicit Cursors
161

 
- Exercise Six
162

Eleven
Using Sequences in PLSQL 19c
165

 
- Using Sequences in PLSQL 19c
166

 
- Using Sequences in PLSQL 19c
167

Twelve
Loop Processing
168

 
- Iterative Processing in PLSQL
169

 
- Basic Looping in PLSQL
170

 
- While Looping in PLSQL
174

 
- For Looping in PLSQL
177

 
- Using Dates in For Loops in PLSQL
181

 
- Using Characters in For Loops in PLSQL
182

 
- Implicit Cursor For Looping in PLSQL
183

 
- Explicit Cursor For Looping in PLSQL
186

 
- Using other Loops with Explicit Cursors
188

 
- Loop Labels in PLSQL
191

 
- Loop Continue and Continue When
193

 
- Exercise Seven
197

Thirteen
PL/SQL Database Objects
203

 
- PLSQL Objects
204

 
- Compiling PLSQL Objects
205

 
- Metadata For PLSQL Objects
206

 
- Re-Compiling PLSQL Objects
207

 
- Dropping PLSQL Objects
209

 
- Passing Parameters in PLSQL
210

 
- Passing Parameters using Positional Notation
211

 
- Passing Parameters Using Named Notation
213

 
- Using Mixed Notation in SQL
214

 
- Metadata (User_Source)
216

 
- Describing PLSQL Objects in SQL*PLUS
217

Fourteen
Programmer Defined Functions
218

 
- Syntax of Functions in Oracle
219

 
- Suggested Naming Convention of Functions in Oracle
222

 
- Function Example in PLSQL
223

 
- Running Functions in SQL and PLSQL
225

 
- Running Functions in Oracle
226

 
- Exercise Eight
227

Fifteen
Programmer Defined Procedures
231

 
- Syntax of Procedures in Oracle
232

 
- Example of Procedures in Oracle
234

 
- Executing Procedures in Oracle within SQL Plus
235

 
- Executing Procedures in Oracle within PLSQL
236

Sixteen
Sub-programming Techniques
237

 
- Introduction
238

 
- Example
239

 
- Exercise Nine
241

Seventeen
Autonomous Transactions in PLSQL
244

 
- Introduction
245

 
- Example of an Autonomous Transaction
247

 
- Rollback in Autonomous Transactions
254

 
- Using Autonomous Transactions in Triggers
255

Eighteen
Packaging Programmer Defined Programs
258

 
- Syntax of Packages in Oracle
259

 
- Compiling of Packages in Oracle
264

 
- Private Processes in PLSQL Packages
265

 
- Executing Packages in Oracle
267

 
- Handling Exceptions in Packages in PLSQL
268

 
- Exercise Ten
270

Nineteen
DML Triggers in Oracle
271

 
- Oracle Triggers Introduction
272

 
- DML Triggers Introduction
273

 
- DML Triggers Timing Points
275

 
- DML Triggers When Conditions
276

 
- DML Triggers When Conditions Examples
277

 
- DML Triggers Suggested Naming Conventions
279

 
- Syntax of DML Triggers in PLSQL
281

 
- Referencing Values within Triggers
282

 
- Checking the Event which fired the DML Trigger
286

 
- Checking the Event which fired the DML Trigger
287

 
- Using the Follows syntax to dictate Trigger Firing
288

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

 
- Using the Disable syntax when Compiling Triggers
293

 
- Enabling/Disabling Triggers in an Oracle Database
294

 
- Metadata for Triggers (User_Triggers)
296

 
- Mutating DML Triggers in Oracle
297

 
- Exercise Eleven
301

Twenty
Rowtypes in PLSQL
308

 
- Using %Rowtype in PLSQL
309

 
- Declaring a Variable as a %Rowtype
310

 
- Referencing a %Rowtype Variable
311

 
- Populating a Variable which is a %Rowtype
312

 
- Passing a %Rowtype between Processes
315

 
- Declaring a Cursor %Rowtype
317

Twenty One
Advanced Cursors in Oracle 19c
320

 
- For Update with Explicit Cursors
321

 
- Where Current Of Explicit Cursors
322

 
- Example of Updating with Explicit Cursors
323

 
- Using Nowait with Explicit Cursors
324

 
- Using Skip Locked with Explicit Cursors
325

 
- Using Wait with an Explicit Cursor
326

 
- Using Rowid with Explicit Cursors
327

 
- Using Returning Into in PLSQL
328

 
- Using Rowtype for DML
331

Twenty Two
Savepoints in PLSQL
333

 
- Using Savepoints in a PLSQL process
334

 
- Example of Savepoints in a PLSQL process
335

Twenty Three
Case Statements and Case Expressions
336

 
- Introduction
337

 
- Using Searched Case in PLSQL
338

 
- Using Case in PLSQL
339

 
- Using Nested Case in PLSQL
340

 
- Using Case Expressions in PLSQL
342

 
- Using Nullif in PLSQL
346

 
- Using Coalesce in PLSQL
347

 
- Exercise Twelve
348

Twenty Four
File I/O
351

 
- Reading and Writing from the Operating System
352

 
- Accessing the File System using Directories
353

 
- Creating Directories
354

 
- Simple Write using UTL_FILE.PUT_LINE
356

 
- Simple Read Using UTL_FILE.GET_LINE
357

 
- Additional Functions in the UTL_FILE Package
358

 
- Using UTL_FILE.PUTF in PLSQL
359

 
- UTL_FILE Exceptions
361

 
- Using UTL_FILE to perform OS Commands
363

 
- Using UTL_FILE.Fcopy in PLSQL
364

 
- Using UTL_FILE.Fgetattr in PLSQL
365

 
- Using UTL_FILE.Fremove in PLSQL
366

 
- Using UTL_FILE.Frename in PLSQL
367

Twenty Five
Dynamic SQL
368

 
- Introduction to Dynamic SQL
369

 
- Native Dynamic SQL (NDS) with Execute Immediate
370

 
- NDS Error Handling
373

 
- NDS with Inputs
374

 
- NDS with Output
376

 
- Dynamic Cursors and Sys_Refcursor
377

 
- NDS with Outputs
379

 
- Introduction to DBMS_SQL
380

 
- DBMS_SQL Example
381

 
- Exercise Thirteen
382

 
 
 

Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course


Essential Oracle 19c PL/SQL training course

List of Public Courses
Select the location for this training course
×
Oracle Essential PLSQL 19c
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Oracle Essential PLSQL 19c course at a glance

Duration: 3 days
Minimum skill level required: 2
(1 - Beginner, 2 - Experienced
3 - Advanced)
On-Site course:
Yes

Public course: Yes
No of pages in the manual: 384
Sections: 25
No of exercises: 13
Pre-Requisite course(s):
Oracle Essential SQL 19c
Suggested further course(s):
None

Price guide: Contact us for the latest pricing

enquiries@seercomputing.com
or call us on +44 (0) 800 157 7519.

PDF - Course Index

PDF - Exercise Example

PDF - Sample Pages

Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course

Synopsis for Essential Oracle 19c PL/SQL training course

What's the point of learning Oracle PLSQL ? isn’t Oracle SQL enough to keep the Database running sweet and providing all the information we painstakingly entered into it ? I think you already know the answer to that … a big fat NO !!

SQL is a very important command language in a lot of different Database systems (most Databases of which we have training course for) but whilst it is the fundamental language for creating objects, altering and updating these objects it can fall short when Developers try to make bulk changes, interrogate the Database with complex rules and protect the Database from wilful and accidental data incidents. Here at Seer Computing we love SQL in all of its dialects and versions but we adore PLSQL for helping us keep many Databases working sweetly and effectively.

So do you need Oracle PLSQL as a skill? yes if you are responsible for anything other than extracting data from an Oracle Database, if you are a high end User manipulating the contents of the Oracle tables and views and producing mission critical reports then you probably will never need to develop in PLSQL, however if you create tables, have important business rules governing the data within the Database, need to extract data for use in other Databases, convert incoming Datasets and need to protect the information from being trashed you might wish to give learning PLSQL a thought.

Here is a non-exhaustive list of what PLSQL is good for, in no particular order of importance …

Data integrity checks using Database triggers
Functions based on in-house business rules
Integration of comma separated files into the Database enforcing Database constraints
Centralisation and packaging of key processing
Merging complex data structures into aggregated values and records
Enforcing security access
Extraction of records for export
Error handling
Bulk processing of data in batch
Database maintenance with scripts
Retrieval and insertion of Long Objects into the Oracle Database

What isn’t PLSQL good at?


For a start, PLSQL can't produce webpages directly, it can be invoked by JavaScript and PHP (both of which we have courses for) to produce dynamic webpages, but it cannot be embedded in HTML
PLSQL cannot be used directly as a gatherer of information in a GUI environment, that role falls to other products including Oracle Forms and Java etc (guess what, we do courses in Forms and Java too !)
PLSQL cannot be run outside the confines of an Oracle Database, it is specifically a language embedded in Oracle

This course teaches Delegates the structure and usage of PLSQL, moving onto to applying the code to embedded objects such as Functions and Procedures, then the Delegates will learn how to package the individual objects together. Moving on, the use of Table triggers will be examined and practiced with further amendment to include audit logs.

This is a three day course, it is essential that the Delegates already have Oracle SQL training or experience (we offer a range of excellent SQL courses), the clue is in the last three characters, PLSQL is an extension of SQL therefore you'll need to know that before we can expand your knowledge.

Both the SQL and PLSQL courses are available as public courses which we have sensibly run back to back at a variety of locations in the UK and Ireland at various times of the year, so start on a Monday with SQL and by the end of Friday afternoon you will be trained in both Oracle SQL and PLSQL.

An alternative is to learn onsite, this is particularly cost effective because the Delegates will be at their normal place of work, no travelling to a conference centre, it also means that we can schedule the course when it suits you (in other words, doesn’t have to start on a Wednesday), plus Delegates can be pulled out of the course if needed to fire-fight, the Training Consultant will wait until they return.

Contact our training advisors to discuss your requirement. We give no obligation quotations which are valid for six months and we can also prepare a number of training scenarios to your specification

Scroll through these locations and find the one nearest to you.

Click on the location of your choice and that will take you to the dates for each course

Select the location and then click on the image to view the dates

Sections of Essential PLSQL Oracle 19c

1
PL/SQL Introduction

2
PL/SQL Structure

3
The SQL Plus Interface

4
Performing Simple DML in PLSQL

5
Performing Conditional Statements in PLSQL

6
Communicating from PLSQL

7
Exception Handling

8
Implicit Cursors

9
Sub-Blocking in PLSQL

10
Explicit Cursors

11
Using Sequences in PLSQL 19c

12
Loop Processing

13
PL/SQL Database Objects

14
Programmer Defined Functions

15
Programmer Defined Procedures

16
Sub-programming Techniques

17
Autonomous Transactions in PLSQL

18
Packaging Programmer Defined Programs

19
DML Triggers in Oracle

20
Rowtypes in PLSQL

21
Advanced Cursors in Oracle 19c

22
Savepoints in PLSQL

23
Case Statements and Case Expressions

24
File I/O

25
Dynamic SQL



Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course
×
Oracle Essential PLSQL 19c
 
Name:
Company:
Email:
Telephone:
Training type:
Delegates:


Additional Information
 

Oracle Essential PLSQL 19c course at a glance

Duration: 3 days
Minimum skill level required: 2
(1 - Beginner, 2 - Experienced
3 - Advanced)
On-Site course:
Yes

Public course: Yes
No of pages in the manual: 384
Sections: 25
No of exercises: 13
Pre-Requisite course(s):
Oracle Essential SQL 19c
Suggested further course(s):
None

Price guide: Contact us for the latest pricing

enquiries@seercomputing.com
or call us on +44 (0) 800 157 7519.

PDF - Course Index

PDF - Exercise Example

PDF - Sample Pages

Request for details
Click for the contact form for the Oracle Essential PLSQL 19c training course

Scroll left/right and then click on the course to view the details