Download our latest
Android and iPhone apps ...
At a glance ...
Pages
370
Exercises
13
Sections
24
Public
On-Site
Level
2
Days
3

Course Name: Oracle Essential PLSQL 11g

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
370
Oracle Essential SQL 11g
Advanced PLSQL 11g
Contact us for the latest pricing

Synopsis for course

PLSQL language is Oracle's versatile procedural extension to SQL, which capitalises on SQL's functionality and can increase database efficiency, effectiveness, scalability, portability and security.

This course is run on the Oracle 11g database 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

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-01005:null password given; logon denied
50

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

 
- ORA-01017:invalid username/password
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 PLSQL
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 11g
165

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 Looping in PLSQL
183

 
- Explicit Cursor 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 in PLSQL using Positional Notation
211

 
- Passing Parameters in PLSQL 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
222

 
- 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
Packaging Programmer Defined Programs
244

 
- Syntax of Packages in Oracle
245

 
- Compiling of Packages in Oracle
250

 
- Private Processes in PLSQL Packages
251

 
- Executing Packages in Oracle
253

 
- Handling Exceptions in Packages in PLSQL
254

 
- Exercise Ten
256

Eighteen
DML Triggers in Oracle
257

 
- Oracle Triggers Introduction
258

 
- DML Triggers Introduction
259

 
- DML Triggers Timing Points
261

 
- DML Triggers When Conditions
262

 
- DML Triggers When Conditions Examples
263

 
- DML Triggers Suggested Naming Conventions
265

 
- Syntax of DML Triggers in PLSQL
267

 
- Referencing Values within Triggers
268

 
- Checking the Event which fired the DML Trigger
272

 
- Using the Follows syntax to dictate Trigger Firing
274

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

 
- Using the Disable syntax when Compiling Triggers
279

 
- Enabling/Disabling Triggers in an Oracle Database
280

 
- Metadata for Triggers (User_Triggers)
282

 
- Mutating DML Triggers in Oracle
283

 
- Exercise Eleven
287

Nineteen
Rowtypes in PLSQL
294

 
- Using %Rowtype in PLSQL
295

 
- Declaring a Variable as a %Rowtype
296

 
- Referencing a %Rowtype Variable
297

 
- Populating a Variable which is a %Rowtype
298

 
- Passing a %Rowtype between Processes
301

 
- Declaring a Cursor %Rowtype
303

Twenty
Advanced Cursors in Oracle 11g
306

 
- For Update with Explicit Cursors
307

 
- Where Current of Explicit Cursors
308

 
- Example of Updating with Explicit Cursors
309

 
- Using Nowait with Explicit Cursors
310

 
- Using Skip Locked with Explicit Cursors
311

 
- Using Wait with an Explicit Cursor
312

 
- Using Rowid with Explicit Cursors
313

 
- Using Returning Into in PLSQL
314

 
- Using Rowtype for DML
317

Twenty One
Savepoints in PLSQL
319

 
- Using Savepoints in a PLSQL process
320

 
- Example of Savepoints in PLSQL process
321

Twenty Two
Case Statements and Case Expressions
322

 
- Introduction
323

 
- Using Searched Case in PLSQL
324

 
- Using Case in PLSQL
325

 
- Using Nested Case in PLSQL
326

 
- Using Case Expressions in PLSQL
328

 
- Using Nullif in PLSQL
332

 
- Using Coalesce in PLSQL
333

 
- Exercise Twelve
334

Twenty Three
File I/O
337

 
- Reading and Writing Files in PLSQL
338

 
- Accessing the File System using Directories
339

 
- Creating Directories
340

 
- Simple Write using UTL_FILE.PUT_LINE
342

 
- Simple Read Using UTL_FILE.GET_LINE
343

 
- Additional Functions in the UTL_FILE Package
344

 
- Using UTL_FILE.PUTF in PLSQL
345

 
- UTL_FILE Exceptions
347

 
- Using UTL_FILE to perform Operating System Commands
349

 
- Using UTL_FILE.Fcopy in PLSQL
350

 
- Using UTL_FILE.Fgetattr in PLSQL
351

 
- Using UTL_FILE.Fremove in PLSQL
352

 
- Using UTL_FILE.Frename in PLSQL
353

Twenty Four
Dynamic SQL
354

 
- Introduction to Dynamic SQL
355

 
- Native Dynamic SQL (NDS) with Execute Immediate
356

 
- NDS Error Handling
359

 
- NDS with Inputs
360

 
- NDS with Output
362

 
- Dynamic Cursors and Sys_Refcursor
363

 
- NDS with Outputs
365

 
- Introduction to DBMS_SQL
366

 
- DBMS_SQL Example
367

 
- Exercise Thirteen
368