Index of Essential Oracle 23ai PL/SQL training course
Section
Contents
Page
One
PL/SQL Introduction
2
- What is PLSQL used for ?
3
- Notes about the Course
5
Two
PL/SQL Coding Structure
6
- The Begin and End of PLSQL
7
- Declaring Variables in PLSQL
8
- Local Variables in PLSQL
8
- Naming Local Variables in PLSQL
11
- Using Quoted Identifier Variables (QIV)
13
- Declaring Varchar2 in PLSQL
17
- Declaring Numbers in PLSQL
18
- Declaring Decimals in PLSQL
22
- Declaring Integers in PLSQL
23
- Declaring PLSQL_Integer in PLSQL
24
- Declaring Binary_Integer in PLSQL
25
- Declaring Natural in PLSQL
26
- Declaring Naturaln in PLSQL
27
- Declaring Positive in PLSQL
28
- Declaring Positiven in PLSQL
29
- Declaring Signtype in PLSQL
30
- Declaring Simple_Integer in PLSQL
31
- Declaring Boolean in PLSQL
32
- Declaring Constants in PLSQL
33
- How to run a PLSQL program in SQLPlus
35
- Compiling PLSQL in SQL*Plus
37
- PLSQL Compilation Checklist
40
- Commenting PLSQL Scripts
42
- Suggested documentation within PLSQL
44
Three
The Oracle SQLPlus and SQLcl Interfaces
45
- How to logon
46
- What do I need to login
47
- The initial details shown in SQL Plus
51
- ORA-01017 Error when logging into SQLPlus
52
- ORA-12154 Error when logging into SQLPlus
53
- How do I exit a session
54
- Using Dos commands
55
- SQLPlus Function Keys
56
- Using the Line Editor
57
- Using the Buffer Editor
58
- Environment Settings
59
- Saving and Reading SQL
60
- Spooling Results to a file
61
- Changing Passwords / Clearing the Screen in SQL
62
- Exercise One
63
Four
Performing Simple DML in PLSQL
65
- Using PLSQL to issue SQL statements
66
- SQL statements and PLSQL local variables
67
- Declaring Variables Using %Type
70
- Using Substitution Values in PLSQL (Ampersands)
74
- Exercise Two
77
Five
Performing Conditional Statements in PLSQL
79
- If .. Then Decision Statements
80
- If .. Then Else Decision Statements
83
- If .. Then Elsif Decision Statements
85
- Multiple Conditions in PLSQL
86
- Nesting If statements in PLSQL
87
- Testing Booleans in PLSQL conditions
88
Six
- Messaging with PLSQL
89
- How to use the DBMS_OUTPUT package
90
- Enabling and Disabling Messaging
93
- Exercise Three
95
Seven
Exception Handling
97
- The Exception clause in PLSQL
98
- Using When within Exceptions
101
- Using Others within Exceptions
104
- Killing a PLSQL process with Raise_Application_Error
106
- Testing for Oracle Defined Exceptions
110
- Raising Developer Defined Exceptions
111
- Assigning Pragma Exceptions in PLSQL
114
- Grouping Exceptions in PLSQL
119
- Alternative Way to Handle SQL Errors in PLSQL
118
- Advanced techniques using SQLErrm
122
- Exercise Four
123
Eight
Implicit Cursors
126
- Using Implicit Cursors in PLSQL
127
- Using Implicit Cursors to Select Records
129
- Exception Handling in Implicit Cursors
130
- Attributes of Implicit Cursors
132
- Example of SQL%FOUND
133
- Example of SQL%NOTFOUND
134
- Example of SQL%ROWCOUNT
135
Nine
Sub-Blocking in PLSQL
136
- PLSQL blocks within PLSQL blocks
137
- Defining a Sub-Block
138
- Re-Raising Exceptions in Sub-Blocks
140
- Exercise Five
143
Ten
Explicit Cursors
147
- Using Explicit Cursors in PLSQL
148
- How Explicit Cursors Work
150
- Attributes of an Explicit Cursor
153
- Passing Parameters to Explicit Cursors
156
- Explicit Cursors Versus Implicit Cursors
159
- Exercise Six
160
Eleven
Using Sequences in PLSQL 23ai
163
Twelve
Looping in PLSQL
166
- Repetitive Processing in PLSQL
167
- Basic Looping in PLSQL
168
- While Looping in PLSQL
172
- For Looping in PLSQL
175
- Looping Dates in PLSQL
179
- Using Characters in For Loops in PLSQL
180
- Implicit Cursor For Looping in PLSQL
181
- Explicit Cursor For Looping in PLSQL
183
- Using other Loops with Explicit Cursors
186
- Loop Labels in PLSQL
189
- Loop Continue and Continue When
191
- Exercise Seven
195
Thirteen
PL/SQL Database Objects
201
- List of PLSQL Objects
202
- Compiling PLSQL Objects
203
- Metadata For PLSQL Objects
204
- Re-Compiling PLSQL Objects
205
- Dropping PLSQL Objects from a Database
207
- Passing Parameters to PLSQL
208
- Passing Parameters using Positional Notation
209
- Passing Parameters Using Named Notation
211
- Using Mixed Notation in SQL
212
- User_Source Metadata
214
- Describing PLSQL Objects in SQL*PLUS
215
Fourteen
Programmer Defined Functions
216
- Syntax of Functions in Oracle
217
- Suggested Naming Convention of Functions in Oracle
220
- Function Example in PLSQL
221
- Running Functions in SQL and PLSQL
223
- Running Functions in Oracle
224
- Exercise Eight
225
Fifteen
Programmer Defined Procedures
229
- Syntax of Procedures in Oracle
230
- Example of Procedures in Oracle
232
- Executing Procedures in Oracle within SQL Plus
233
- Executing Procedures in Oracle within PLSQL
234
Sixteen
Sub-programming Techniques
235
- Declaring PLSQL within PLSQL
236
- Example of a sub-program
237
- Exercise Nine
239
Seventeen
Autonomous Transactions in PLSQL
242
- Selective Committing in PLSQL processing
243
- Example of an Autonomous Transaction
245
- Using Autonomous Transactions in Triggers
252
Eighteen
Packaging Programmer Defined Programs
255
- Packaging PLSQL in Oracle
256
- Syntax of Packages in Oracle
258
- Compiling of Packages in Oracle
261
- Private Processes in PLSQL Packages
262
- Executing Packages in Oracle
264
- Handling Exceptions in Packages in PLSQL
265
- Exercise Ten
267
Nineteen
DML Triggers in Oracle
268
- Types of Oracle Triggers
269
- What are DML Triggers
270
- DML Triggers Timing Points
272
- DML Triggers When Conditions
273
- DML Triggers When Conditions Examples
274
- DML Triggers Suggested Naming Conventions
276
- Syntax of DML Triggers in PLSQL
278
- Referencing Values within Triggers
279
- Checking the Event which fired the DML Trigger
283
- Using the Follows syntax to dictate Trigger Firing
285
- ORA-25022: cannot reference a trigger of different type
289
- Using the Disable syntax when Compiling Triggers
290
- Enabling/Disabling Triggers in an Oracle Database
291
- Metadata for Triggers (User_Triggers)
293
- Mutating DML Triggers in Oracle
294
- Exercise Eleven
298
Twenty
Rowtypes in PLSQL
305
- Using %Rowtype in PLSQL
306
- Declaring a Variable as a %Rowtype
307
- Referencing a %Rowtype Variable
308
- Populating a Variable which is a %Rowtype
309
- Passing a %Rowtype between Processes
312
- Declaring a Cursor %Rowtype
314
Twenty One
Advanced Cursors
317
- For Update with Explicit Cursors
318
- Where Current Of Explicit Cursors
319
- Example of Updating with Explicit Cursors
320
- Using Nowait with Explicit Cursors
321
- Using Skip Locked with Explicit Cursors
322
- Using Wait with an Explicit Cursor
323
- Using Rowid with Explicit Cursors
324
- Using Returning Into in PLSQL
325
- Using Rowtype for DML
328
Twenty Two
Savepoints in PLSQL
330
- Marking progress in a PLSQL process
331
- Example of Savepoints in a PLSQL process
332
Twenty Three
Case Statements and Case Expressions
333
- Alternative Conditional Testing
334
- Using Searched Case in PLSQL
335
- Using Case in PLSQL
336
- Using Nested Case in PLSQL
337
- Using Case Expressions in PLSQL
339
- Dangling Predicates
343
- Dangling Predicates with multiple choice
344
- Using Nullif in PLSQL
348
- Using Coalesce in PLSQL
349
- Exercise Twelve
350
Twenty Four
File I/O
353
- Reading and Writing from the Operating System
354
- Accessing the File System using Oracle Directories
355
- Creating Oracle Directories
356
- Simple Write using UTL_FILE.PUT_LINE
358
- Simple Read Using UTL_FILE.GET_LINE
359
- Additional Functions in the UTL_FILE Package
360
- Using UTL_FILE.PUTF in PLSQL
361
- UTL_FILE Exceptions
363
- Using UTL_FILE to perform Operating System Commands
365
- Using UTL_FILE.Fcopy in PLSQL
366
- Using UTL_FILE.Fgetattr in PLSQL
367
- Using UTL_FILE.Fremove in PLSQL
368
- Using UTL_FILE.Frename in PLSQL
369
Twenty Five
Dynamic SQL
370
- Introduction to Dynamic SQL
371
- Native Dynamic SQL (NDS) with Execute Immediate
372
- NDS Error Handling
375
- NDS with Inputs
376
- NDS with Output
378
- Dynamic Cursors and Sys_Refcursor
379
- NDS with Outputs
381
- Introduction to DBMS_SQL
382
- DBMS_SQL Example
383
- Exercise Thirteen
384
