Download our latest
Android and iPhone apps ...
At a glance ...
Pages
496
Exercises
14
Sections
26
Public
On-Site
Level
2
Days
4

Course Name: Apex PLSQL

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

Synopsis for course

PLSQL is an essential language for Developers to master in order to work with an Oracle database. Extending the SQL language it gives Developers the ability to validate and process records quickly and efficiently.

This course is run on the Oracle 11g database using Application Express 4.2, please make sure this is the version you require, we supply similar in Oracle 9i, 10g and 12c

Course Contents

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