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

Course Name: Oracle Essential PLSQL 9i

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
23
13
344
Oracle Essential SQL 9i
None
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 9i database, please check that this is the version of the Database you wish to be trained in

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-12154:TNS:could not resolve the connect identifier
51

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

 
- Exiting an SQL Plus Session
53

 
- Using the DOS Window
53

 
- Using the Line Editor in SQL Plus
54

 
- Using the Editor in SQL Plus
55

 
- SQL Plus Environment Settings
56

 
- File Manipulation in SQL Plus
58

 
- Spooling Results in SQL Plus
59

 
- Changing Passwords/Clearing the Screen in SQL
60

 
- Exercise One
61

Four
Performing Simple DML in PLSQL
63

 
- Introduction
64

 
- Using Local Variables
65

 
- Declaring Variables Using %Type
68

 
- Using Substitution Values in PLSQL
72

 
- Exercise Two
75

Five
Performing Conditional Statements in PLSQL
77

 
- If .. Then Statements
78

 
- If .. Then Else Statements
81

 
- If .. Then Elsif Statements
83

 
- Combining Tests
84

 
- Nesting Conditions
85

 
- Boolean Conditions
86

Six
Communicating from PLSQL
87

 
- Messaging Via PLSQL
88

 
- Exercise Three
93

Seven
Exception Handling
95

 
- Introduction
96

 
- The When Clause
99

 
- The When Others Clause
102

 
- Raise_Application_Error
104

 
- Oracle Defined Exceptions
108

 
- Raising User Defined Exceptions
109

 
- Pragma Exceptions in PLSQL
112

 
- Grouping Exceptions in PLSQL
116

 
- Alternative Way to Handle SQL Errors in PLSQL
117

 
- Handling SQL Errors in PLSQL
118

 
- Advanced SQLErrm
120

 
- Exercise Four
121

Eight
Implicit Cursors
124

 
- Using Implicit Cursors in PLSQL
125

 
- Using Implicit Cursors to Select Records
127

 
- Exceptions in PLSQL
128

 
- Attributes of Implicit Cursors
130

 
- SQL%FOUND
131

 
- SQL%NOTFOUND
132

 
- SQL%ROWCOUNT
133

Nine
Sub-Blocking in PLSQL
134

 
- Introduction
135

 
- Defining
136

 
- Re-Raising Exceptions in PLSQL
138

 
- Exercise Five
141

Ten
Explicit Cursors
145

 
- Using Explicit Cursors in PLSQL
146

 
- How Explicit Cursors Work
148

 
- Attributes of an Explicit Cursor
151

 
- Passing Parameters to Explicit Cursors
154

 
- Explicit Cursors Versus Implicit Cursors
157

 
- Exercise Six
158

Eleven
Loop Processing
161

 
- Iterative Processing in PLSQL
162

 
- Basic Looping in PLSQL
163

 
- While Looping in PLSQL
167

 
- For Looping in PLSQL
170

 
- Using Dates in For Loops in PLSQL
174

 
- Using Characters in For Loops in PLSQL
175

 
- Implicit Cursor Looping in PLSQL
176

 
- Explicit Cursor Looping in PLSQL
179

 
- Using other Loops with Explicit Cursors
181

 
- Loop Labels in PLSQL
184

 
- Exercise Seven
186

Twelve
PL/SQL Database Objects
190

 
- PLSQL Objects
191

 
- Compiling PLSQL Objects
192

 
- Metadata For PLSQL Objects
193

 
- Re-Compiling PLSQL Objects
194

 
- Dropping PLSQL Objects
196

 
- Passing Parameters in PLSQL
197

 
- Passing Parameters in PLSQL using Positional Notation
198

 
- Passing Parameters in PLSQL using Named Notation
200

 
- Using Mixed Notation in SQL
201

 
- Metadata (User Source)
203

 
- Describing PLSQL Objects in SQL*Plus
204

Thirteen
Programmer Defined Functions
205

 
- Syntax of Functions in Oracle
206

 
- Suggested Naming Convention of Functions in Oracle
209

 
- Function Example in PLSQL
210

 
- Running Functions in SQL and PLSQL
212

 
- Running Functions in Oracle
213

 
- Exercise Eight
214

Fourteen
Programmer Defined Procedures
218

 
- Syntax of Procedures in Oracle
219

 
- Example of Procedures in Oracle
221

 
- Executing Procedures in Oracle within SQL Plus
222

 
- Executing Procedures in Oracle within PLSQL
223

Fifteen
Sub-programming Techniques
224

 
- Introduction
225

 
- Example
226

 
- Exercise Nine
228

Sixteen
Packaging Programmer Defined Programs
231

 
- Syntax of Packages in Oracle
232

 
- Compiling of Packages in Oracle
237

 
- Private Processes in PLSQL Packages
238

 
- Executing Packages in Oracle
240

 
- Handling Exceptions in Packages in PLSQL
241

 
- Exercise Ten
243

Seventeen
DML Triggers in Oracle
244

 
- Oracle Triggers Introduction
245

 
- DML Triggers Introduction
246

 
- DML Triggers Timing Points
248

 
- DML Triggers When Conditions
249

 
- DML Triggers When Conditions Examples
250

 
- DML Triggers Suggested Naming Conventions
252

 
- Syntax of DML Triggers in PLSQL
254

 
- Referencing Values within Triggers
255

 
- Checking the Event which fired the DML Trigger
259

 
- Enabling/Disabling Triggers in an Oracle Database
261

 
- Metadata for Triggers (User_Triggers)
263

 
- Mutating DML Triggers in Oracle
264

 
- Exercise Eleven
268

Eighteen
Rowtypes in PLSQL
275

 
- Using %Rowtype in PLSQL
276

 
- Declaring a Variable as a %Rowtype
277

 
- Referencing a %Rowtype Variable
278

 
- Populating a Variable which is a %Rowtype
279

 
- Passing a %Rowtype between Processes
282

 
- Declaring a Cursor %Rowtype
284

Nineteen
Advanced Cursors in Oracle 9i
287

 
- For Update with Explicit Cursors
288

 
- Where Current of Explicit Cursors
289

 
- Example of Updating with Explicit Cursors
290

 
- Using Nowait with Explicit Cursors
291

 
- Using Skip Locked with Explicit Cursors
292

 
- Using Wait with an Explicit Cursor
293

 
- Using Rowid with Explicit Cursors
294

 
- Using Returning Into in PLSQL
295

 
- Using Rowtype for DML
298

Twenty
Savepoints in PLSQL
300

 
- Using Savepoints in a PLSQL process
301

 
- Example of Savepoints in PLSQL process
302

Twenty One
Case Statements and Case Expressions
303

 
- Introduction
304

 
- Using Searched Case in PLSQL
305

 
- Using Case in PLSQL
306

 
- Using Nested Case in PLSQL
307

 
- Using Case Expressions in PLSQL
310

 
- Using Nullif in PLSQL
313

 
- Using Coalesce in PLSQL
314

 
- Exercise Twelve
315

Twenty Two
File I/O
318

 
- Reading and Writing Files in PLSQL
319

 
- Simple Write using UTL_FILE.PUT_LINE
321

 
- Simple Read Using UTL_FILE.GET_LINE
322

 
- Additional Functions in the UTL_FILE Package
323

 
- Using UTL_FILE.PUTF in PLSQL
324

 
- UTL_FILE Exceptions
326

Twenty Three
Dynamic SQL
328

 
- Introduction to Dynamic SQL
329

 
- Native Dynamic SQL (NDS) with Execute Immediate
330

 
- NDS Error Handling
333

 
- NDS with Inputs
334

 
- NDS with Output
336

 
- Dynamic Cursors and Sys_Refcursor
337

 
- NDS with Outputs
339

 
- Introduction to DBMS_SQL
340

 
- DBMS_SQL Example
341

 
- Exercise Thirteen
342