Index of Essential Oracle 21c PL/SQL training course
Section
Contents
Page
One
PL/SQL Introduction
2
- What is PLSQL used for ?
3
Two
PL/SQL Coding Structure
5
- The Begin and End of PLSQL
6
- Declaring Variables in PLSQL
7
- Local Variables in PLSQL
8
- Naming Local Variables in PLSQL
10
- Using Quoted Identifier Variables (QIV)
12
- Declaring Varchar2 in PLSQL
16
- Declaring Numbers in PLSQL
17
- Declaring Decimals in PLSQL
21
- Declaring Integers in PLSQL
22
- Declaring PLSQL_Integer in PLSQL
23
- Declaring Binary_Integer in PLSQL
24
- Declaring Natural in PLSQL
25
- Declaring Naturaln in PLSQL
26
- Declaring Positive in PLSQL
27
- Declaring Positiven in PLSQL
28
- Declaring Signtype in PLSQL
29
- Declaring Simple_Integer in PLSQL
30
- Declaring Boolean in PLSQL
31
- Declaring Constants in PLSQL
32
- How to run a PLSQL program in SQLPlus
34
- Compiling PLSQL in SQL*Plus
36
- Common PLSQL Compilation Problems
39
- Commenting PLSQL Scripts
41
- Suggested documentation within PLSQL
43
Three
The Oracle SQL Plus Interface
44
- How to logon to SQLPlus
45
- What do I need to login into SQLPlus
46
- The initial details shown in SQL Plus
50
- ORA-01017 Error when logging into SQLPlus
51
- ORA-12154 Error when logging into SQLPlus
52
- How do I exit an SQLPlus session
53
- Using Dos commands in SQLPlus
54
- SQLPlus Function Keys
55
- Using the Line Editor in SQL Plus
56
- Using the Editor in SQL Plus
57
- SQL Plus Environment Settings
58
- Saving and Reading in SQLPlus
59
- Spooling Results to a file in SQL Plus
60
- Changing Passwords / Clearing the Screen in SQL
61
- Exercise One
62
Four
Performing Simple DML in PLSQL
64
- Using PLSQL to issue SQL statements
65
- SQL statements and PLSQL local variables
66
- Declaring Variables Using %Type
69
- Using Substitution Values in PLSQL (Ampersands)
73
- Exercise Two
76
Five
Performing Conditional Statements in PLSQL
78
- If .. Then Statements
79
- If .. Then Else Decision Statements
82
- If .. Then Elsif Decision Statements
84
- Multiple Conditions in PLSQL
85
- Nesting If statements in PLSQL
86
- Testing Booleans in PLSQL conditions
87
Six
Messaging with PLSQL
88
- How to use the DBMS_OUTPUT package
89
- Enabling and Disabling Messaging
92
- Exercise Three
94
Seven
Exception Handling
96
- The Exception clause in PLSQL
97
- Using When within Exceptions
100
- Using Others within Exceptions
103
- Killing a PLSQL process with Raise_Application_Error
105
- Testing for Oracle Defined Exceptions
109
- Raising Developer Defined Exceptions
110
- Assigning Pragma Exceptions in PLSQL
113
- Grouping Exceptions in PLSQL
117
- Alternative Way to Handle SQL Errors in PLSQL
118
- Handling SQL Errors in PLSQL
122
- Advanced techniques using SQLErrm
121
- Exercise Four
122
Eight
Implicit Cursors
125
- Using Implicit Cursors in PLSQL
126
- Using Implicit Cursors to Select Records
128
- Exception Handling in Implicit Cursors
129
- Attributes of Implicit Cursors
131
- Example of SQL%FOUND
132
- Example of SQL%NOTFOUND
133
- Example of SQL%ROWCOUNT
134
Nine
Sub-Blocking in PLSQL
135
- PLSQL blocks within PLSQL blocks
136
- Defining a Sub-Block
137
- Re-Raising Exceptions in Sub-Blocks
139
- Exercise Five
142
Ten
Explicit Cursors
146
- Using Explicit Cursors in PLSQL
147
- How Explicit Cursors Work
149
- Attributes of an Explicit Cursor
152
- Passing Parameters to Explicit Cursors
155
- Explicit Cursors Versus Implicit Cursors
158
- Exercise Six
159
Eleven
Using Sequences in PLSQL 21c
162
Twelve
Looping in PLSQL
165
- Repetitive Processing in PLSQL
166
- Basic Looping in PLSQL
167
- While Looping in PLSQL
171
- For Looping in PLSQL
174
- Looping Dates in PLSQL
178
- Using Characters in For Loops in PLSQL
179
- Implicit Cursor For Looping in PLSQL
180
- Explicit Cursor For Looping in PLSQL
183
- Using other Loops with Explicit Cursors
185
- Loop Labels in PLSQL
188
- Loop Continue and Continue When
190
- Exercise Seven
194
Thirteen
PL/SQL Database Objects
200
- List of PLSQL Objects
201
- Compiling PLSQL Objects
202
- Metadata For PLSQL Objects
203
- Re-Compiling PLSQL Objects
204
- Dropping PLSQL Objects from a Database
206
- Passing Parameters to PLSQL
207
- Passing Parameters using Positional Notation
208
- Passing Parameters Using Named Notation
210
- Using Mixed Notation in SQL
211
- User_Source Metadata
213
- Describing PLSQL Objects in SQL*PLUS
214
Fourteen
Programmer Defined Functions
215
- Syntax of Functions in Oracle
216
- Suggested Naming Convention of Functions in Oracle
219
- Function Example in PLSQL
220
- Running Functions in SQL and PLSQL
222
- Running Functions in Oracle
223
- Exercise Eight
224
Fifteen
Programmer Defined Procedures
228
- Syntax of Procedures in Oracle
229
- Example of Procedures in Oracle
231
- Executing Procedures in Oracle within SQL Plus
232
- Executing Procedures in Oracle within PLSQL
233
Sixteen
Sub-programming Techniques
234
- Declaring PLSQL within PLSQL
235
- Example of a sub-program
236
- Exercise Nine
238
Seventeen
Autonomous Transactions in PLSQL
241
- Selective Committing in PLSQL processing
242
- Example of an Autonomous Transaction
244
- Using Autonomous Transactions in Triggers
251
Eighteen
Packaging Programmer Defined Programs
254
- Packaging PLSQL in Oracle
255
- Syntax of Packages in Oracle
257
- Compiling of Packages in Oracle
260
- Private Processes in PLSQL Packages
261
- Executing Packages in Oracle
263
- Handling Exceptions in Packages in PLSQL
264
- Exercise Ten
266
Nineteen
DML Triggers in Oracle
267
- Types of Oracle Triggers
268
- What are DML Triggers
269
- DML Triggers Timing Points
271
- DML Triggers When Conditions
272
- DML Triggers When Conditions Examples
273
- DML Triggers Suggested Naming Conventions
275
- Syntax of DML Triggers in PLSQL
277
- Referencing Values within Triggers
278
- Checking the Event which fired the DML Trigger
282
- Using the Follows syntax to dictate Trigger Firing
284
- ORA-25022: cannot reference a trigger of different type
288
- Using the Disable syntax when Compiling Triggers
289
- Enabling/Disabling Triggers in an Oracle Database
290
- Metadata for Triggers (User_Triggers)
292
- Mutating DML Triggers in Oracle
293
- Exercise Eleven
297
Twenty
Rowtypes in PLSQL
304
- Using %Rowtype in PLSQL
305
- Declaring a Variable as a %Rowtype
306
- Referencing a %Rowtype Variable
307
- Populating a Variable which is a %Rowtype
308
- Passing a %Rowtype between Processes
311
- Declaring a Cursor %Rowtype
313
Twenty One
Advanced Cursors in Oracle 21c
316
- For Update with Explicit Cursors
317
- Where Current Of Explicit Cursors
318
- Example of Updating with Explicit Cursors
319
- Using Nowait with Explicit Cursors
320
- Using Skip Locked with Explicit Cursors
321
- Using Wait with an Explicit Cursor
322
- Using Rowid with Explicit Cursors
323
- Using Returning Into in PLSQL
324
- Using Rowtype for DML
327
Twenty Two
Savepoints in PLSQL
329
- Marking progress in a PLSQL process
330
- Example of Savepoints in a PLSQL process
331
Twenty Three
Case Statements and Case Expressions
332
- Alternative Conditional Testing
333
- Using Searched Case in PLSQL
334
- Using Case in PLSQL
335
- Using Nested Case in PLSQL
336
- Using Case Expressions in PLSQL
338
- Using Nullif in PLSQL
342
- Using Coalesce in PLSQL
343
- Exercise Twelve
344
Twenty Four
File I/O
347
- Reading and Writing from the Operating System
348
- Accessing the File System using Oracle Directories
349
- Creating Oracle Directories
350
- Simple Write using UTL_FILE.PUT_LINE
352
- Simple Read Using UTL_FILE.GET_LINE
353
- Additional Functions in the UTL_FILE Package
354
- Using UTL_FILE.PUTF in PLSQL
355
- UTL_FILE Exceptions
357
- Using UTL_FILE to perform Operating System Commands
359
- Using UTL_FILE.Fcopy in PLSQL
360
- Using UTL_FILE.Fgetattr in PLSQL
361
- Using UTL_FILE.Fremove in PLSQL
362
- Using UTL_FILE.Frename in PLSQL
363
Twenty Five
Dynamic SQL
364
- Introduction to Dynamic SQL
365
- Native Dynamic SQL (NDS) with Execute Immediate
366
- NDS Error Handling
369
- NDS with Inputs
370
- NDS with Output
372
- Dynamic Cursors and Sys_Refcursor
373
- NDS with Outputs
375
- Introduction to DBMS_SQL
376
- DBMS_SQL Example
377
- Exercise Thirteen
378
