Index of Essential Oracle 11g PLSQL 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-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
