Top
Email
Bottom
Oracle Essential PLSQL 12c
 
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

Oracle Essential PLSQL 12c course at a glance

Pages
369No of slides in the Manual
Exercises
13Total No of Exercises
Sections
24No 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
3Week 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:
3 days
2 (1 - Beginner, 2 - Experienced 3 - Advanced)
Yes
No
24
13
369
Oracle Essential SQL 12c
Advanced PLSQL 12c
Contact us for the latest pricing

Essential Oracle 12c 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 12c PL/SQL training course

Section
Contents
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 (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
49

 
- ORA-12560:TNS:protocol adapter error
50

 
- ORA-12545:Connect failed
51

 
- Exiting an SQL Plus Session
52

 
- Using the DOS Window
53

 
- Copying and Pasting
54

 
- Using the Function Keys
57

 
- Using the Line Editor in SQL Plus
58

 
- Using the Editor in SQL Plus
59

 
- SQL Plus Environment Settings
60

 
- File Manipulation in SQL Plus
57

 
- Spooling Results in SQL Plus
62

 
- Changing Passwords/Clearing the Screen in SQL
63

 
- Exercise One
64

Four
Performing Simple DML in PLSQL
66

 
- Introduction
67

 
- Using Local Variables
68

 
- Declaring Variables Using %Type
71

 
- Using Substitution Values in PLSQL
75

 
- Exercise Two
78

Five
Performing Conditional Statements in PLSQL
80

 
- If .. Then Statements
81

 
- If .. Then Else Statements
84

 
- If .. Then Elsif Statements
86

 
- Combining Tests
87

 
- Nesting Conditions
88

 
- Boolean Conditions
89

Six
Communicating from PLSQL
90

 
- Messaging Via PLSQL
91

 
- Exercise Three
96

Seven
Exception Handling
98

 
- Introduction
99

 
- The When Clause
102

 
- The When Others Clause
105

 
- Raise_Application_Error
107

 
- Oracle Defined Exceptions
111

 
- Raising User Defined Exceptions
112

 
- Pragma Exceptions in PLSQL
115

 
- Grouping Exceptions in PLSQL
119

 
- Alternative Way to Handle SQL Errors in PLSQL
120

 
- Handling SQL Errors in PLSQL
121

 
- Advanced SQLErrm
123

 
- Exercise Four
124

Eight
Implicit Cursors
127

 
- Using Implicit Cursors in PLSQL
128

 
- Using Implicit Cursors to Select Records
130

 
- Exceptions in PLSQL
131

 
- Attributes of Implicit Cursors
133

 
- SQL%FOUND
134

 
- SQL%NOTFOUND
135

 
- SQL%ROWCOUNT
136

Nine
Sub-Blocking in PLSQL
137

 
- Introduction
138

 
- Defining
139

 
- Re-Raising Exceptions in PLSQL
141

 
- Exercise Five
144

Ten
Explicit Cursors
148

 
- Using Explicit Cursors in PLSQL
149

 
- How Explicit Cursors Work
151

 
- Attributes of an Explicit Cursor
154

 
- Passing Parameters to Explicit Cursors
157

 
- Explicit Cursors Versus Implicit Cursors
160

 
- Exercise Six
161

Eleven
Using Sequences in PLSQL 12c
164

Twelve
Loop Processing
167

 
- Iterative Processing in PLSQL
168

 
- Basic Looping in PLSQL
169

 
- While Looping in PLSQL
173

 
- For Looping in PLSQL
176

 
- Using Dates in For Loops in PLSQL
180

 
- Using Characters in For Loops in PLSQL
181

 
- Implicit Cursor Looping in PLSQL
182

 
- Explicit Cursor Looping in PLSQL
185

 
- Using other Loops with Explicit Cursors
187

 
- Loop Labels in PLSQL
190

 
- Loop Continue and Continue When
192

 
- Exercise Seven
196

Thirteen
PL/SQL Database Objects
202

 
- PLSQL Objects
203

 
- Compiling PLSQL Objects
204

 
- Metadata For PLSQL Objects
205

 
- Re-Compiling PLSQL Objects
206

 
- Dropping PLSQL Objects
208

 
- Passing Parameters in PLSQL
209

 
- Passing Parameters in PLSQL using Positional Notation
210

 
- Passing Parameters in PLSQL using Named Notation
212

 
- Using Mixed Notation in SQL
213

 
- Metadata (User Source)
215

 
- Describing PLSQL Objects in SQL*Plus
216

Fourteen
Programmer Defined Functions
217

 
- Syntax of Functions in Oracle
218

 
- Suggested Naming Convention of Functions in Oracle
221

 
- Function Example in PLSQL
222

 
- Running Functions in SQL and PLSQL
224

 
- Running Functions in Oracle
225

 
- Exercise Eight
226

Fifteen
Programmer Defined Procedures
230

 
- Syntax of Procedures in Oracle
231

 
- Example of Procedures in Oracle
233

 
- Executing Procedures in Oracle within SQL Plus
234

 
- Executing Procedures in Oracle within PLSQL
235

Sixteen
Sub-programming Techniques
236

 
- Introduction
237

 
- Example
238

 
- Exercise Nine
240

Seventeen
Packaging Programmer Defined Programs
243

 
- Syntax of Packages in Oracle
244

 
- Compiling of Packages in Oracle
249

 
- Private Processes in PLSQL Packages
250

 
- Executing Packages in Oracle
252

 
- Handling Exceptions in Packages in PLSQL
253

 
- Exercise Ten
255

Eighteen
DML Triggers in Oracle
256

 
- Oracle Triggers Introduction
257

 
- DML Triggers Introduction
258

 
- DML Triggers Timing Points
260

 
- DML Triggers When Conditions
261

 
- DML Triggers When Conditions Examples
262

 
- DML Triggers Suggested Naming Conventions
264

 
- Syntax of DML Triggers in PLSQL
266

 
- Referencing Values within Triggers
267

 
- Checking the Event which fired the DML Trigger
271

 
- Using the Follows syntax to dictate Trigger Firing
273

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

 
- Using the Disable syntax when Compiling Triggers
278

 
- Enabling/Disabling Triggers in an Oracle Database
279

 
- Metadata for Triggers (User_Triggers)
281

 
- Mutating DML Triggers in Oracle
282

 
- Exercise Eleven
286

Nineteen
Rowtypes in PLSQL
293

 
- Using %Rowtype in PLSQL
294

 
- Declaring a Variable as a %Rowtype
295

 
- Referencing a %Rowtype Variable
296

 
- Populating a Variable which is a %Rowtype
297

 
- Passing a %Rowtype between Processes
300

 
- Declaring a Cursor %Rowtype
302

Twenty
Advanced Cursors in Oracle 12c
305

 
- For Update with Explicit Cursors
306

 
- Where Current of Explicit Cursors
307

 
- Example of Updating with Explicit Cursors
308

 
- Using Nowait with Explicit Cursors
309

 
- Using Skip Locked with Explicit Cursors
310

 
- Using Wait with an Explicit Cursor
311

 
- Using Rowid with Explicit Cursors
312

 
- Using Returning Into in PLSQL
313

 
- Using Rowtype for DML
316

Twenty One
Savepoints in PLSQL
318

 
- Using Savepoints in a PLSQL process
319

 
- Example of Savepoints in PLSQL process
320

Twenty Two
Case Statements and Case Expressions
321

 
- Introduction
322

 
- Using Searched Case in PLSQL
323

 
- Using Case in PLSQL
324

 
- Using Nested Case in PLSQL
325

 
- Using Case Expressions in PLSQL
327

 
- Using Nullif in PLSQL
331

 
- Using Coalesce in PLSQL
332

 
- Exercise Twelve
333

Twenty Three
File I/O
336

 
- Reading and Writing Files in PLSQL
337

 
- Accessing the File System using Directories
338

 
- Creating Directories
339

 
- Simple Write using UTL_FILE.PUT_LINE
341

 
- Simple Read Using UTL_FILE.GET_LINE
342

 
- Additional Functions in the UTL_FILE Package
343

 
- Using UTL_FILE.PUTF in PLSQL
344

 
- UTL_FILE Exceptions
346

 
- Using UTL_FILE to perform Operating System Commands
348

 
- Using UTL_FILE.Fcopy in PLSQL
349

 
- Using UTL_FILE.Fgetattr in PLSQL
350

 
- Using UTL_FILE.Fremove in PLSQL
351

 
- Using UTL_FILE.Frename in PLSQL
352

Twenty Four
Dynamic SQL
353

 
- Introduction to Dynamic SQL
354

 
- Native Dynamic SQL (NDS) with Execute Immediate
355

 
- NDS Error Handling
358

 
- NDS with Inputs
359

 
- NDS with Output
361

 
- Dynamic Cursors and Sys_Refcursor
352

 
- NDS with Outputs
364

 
- Introduction to DBMS_SQL
365

 
- DBMS_SQL Example
366

 
- Exercise Thirteen
367

 
 
 

Sections of Essential Oracle 12c

One
PLSQL Introduction

Two
PLSQL Structure

Three
The SQL Plus Interface

Four
Performing Simple DML in PLSQL

Five
Performing Conditional Statements in PLSQL

Six
Communicating from PLSQL

Seven
Exception Handling

Eight
Implicit Cursors

Nine
Sub-Blocking in PLSQL

Ten
Explicit Cursors

Eleven
Using Sequences in PLSQL 12c

Twelve
Loop Processing

Thirteen
PL/SQL Database Objects

Fourteen
Programmer Defined Functions

Fifteen
Programmer Defined Procedures

Sixteen
Sub-programming Techniques

Seventeen
Packaging Programmer Defined Programs

Eighteen
DML Triggers in Oracle

Nineteen
Rowtypes in PLSQL

Twenty
Advanced Cursors in Oracle 12c

Twenty One
Savepoints in PLSQL

Twenty Two
Case Statements and Case Expressions

Twenty Three
File I/O

Twenty Four
Dynamic SQL


Synopsis for Essential Oracle 12c PL/SQL training course

So you are a wizard with Oracle SQL? There's nothing you can't do with SQL, outer joins, embedded SQL, merging tables, seen it, done it, got the badge, well you've either been on one of our Essential Oracle SQL courses or you have been working with SQL for some time. But once you get proficient with SQL you'll realise it does have its limitations, sure it's fast, sure its efficient and easy to read, but the constraints you can place on the tables is rudimentary at best and maintaining them is pretty clunky, and what about those business rules you need to enforce regarding data? Oracle don’t cater for anything other than generalised functions, if you want to make something lowercase, there's a function for that but if you want to check that the Visa or MasterCard number that has been entered is valid, there is nothing in Oracle SQL that you can run to check it.

This is where Oracle PLSQL comes it, it gives Developers the ability to define scripts and Database Objects which don’t replace SQL in an Oracle Database, PL/SQL enhances it. PLSQL is a structured language, and it's not for everyone to learn, if all you want to do is extract data for Management reports then this is probably not the course for you, however if you are creating or maintaining a complex Database then PLSQL is an essential tool for your skillset.

This Oracle PLSQL 12c course assumes that you have recently attended our Oracle SQL training course, if you know SQL but from another Database manufacturer let our training analyst know and they will advise you about what needs to be done before you take this course.

This PLSQL course is three days duration, it does not use the latest Oracle Database, that version has a different PLSQL course available, that Essential 18c PLSQL course is available as both onsite and as a public course throughout the UK and Ireland at various centres at regular intervals. This course because it's no longer the current version is only available as an onsite course, well we say that but you can come to us at our offices in Swansea or if you really want this version we can arrange training close to your location, there is no minimum numbers so even if you only have two people to train let us know we know we can help you.

Each course we give, and there are over 150 to choose from, have been written in-house, we've been around for twenty years so we know what we are doing. When we perform onsite courses we come to you with individual workstations for each Delegate, all we need is a room large enough for the number of people on the course … and of course … electricity … we don’t bring that with us, we bring everything else.

Did we tell you about the manuals? They are given to each individual, they can use them for notes, scribble on, heck they can even make origami figures with them, but that would be a mistake because our manuals are full of information and example pieces of code, great for reference after the training course and essential during the course when the Delegates are working on the exercises, oh yes there are plenty of practical work during the course, supported by the training Consultant the Delegates get to write professional Functions, Triggers and Procedures and learn all of the techniques needed for PLSQL.

Get in touch, our contact details are all over this website, we give no obligation quotations and don’t nag you after you have, satisfy your curiosity about us today.

Request for detailsClick for the contact form for the Oracle Essential PLSQL 12c 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