Index of Oracle 10g Essential 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-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 10g
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
- Accessing the File System using Directories
320
- Creating Directories
321
- Simple Write using UTL_FILE.PUT_LINE
323
- Simple Read Using UTL_FILE.GET_LINE
324
- Additional Functions in the UTL_FILE Package
325
- Using UTL_FILE.PUTF in PLSQL
326
- UTL_FILE Exceptions
328
- Using UTL_FILE to perform Operating System Commands
330
- Using UTL_FILE.Fcopy in PLSQL
331
- Using UTL_FILE.Fgetattr in PLSQL
332
- Using UTL_FILE.Fremove in PLSQL
333
- Using UTL_FILE.Frename in PLSQL
334
Twenty Three
Dynamic SQL
335
- Introduction to Dynamic SQL
336
- Native Dynamic SQL (NDS) with Execute Immediate
337
- NDS Error Handling
340
- NDS with Inputs
341
- NDS with Output
343
- Dynamic Cursors and Sys_Refcursor
344
- NDS with Outputs
346
- Introduction to DBMS_SQL
347
- DBMS_SQL Example
348
- Exercise Thirteen
349
