OVERVIEW
The focus of this course is to advance attendee knowledge of the PL/SQL programming language used for procedures, functions and triggers.. This is an excellent course for those people that need a better knowledge of the PL/SQL language. This course covers all the latest features of PL/SQL using databases Oracle9i, Oracle10g, and Oracle11g.
The attendees have the opportunity to learn how to find the poorly-performing components of PL/SQL routines, use Oracle’s newer DEBUG routines to find problems in PL/SQL logic and how to use the latest features of the PL/SQL language such as the new optimizing compiler and collections. Some of the topics include Dynamic SQL, user defined objects, external procedures, object methods, bulk processing, intersession communication, UTIL_FILE and UTIL_MAIL.
The course utilizes a variety of current tools. Students will have the opportunity to learn more about TOAD, SQL Developer, as well as the Oracle tools like SQL*Plus.
DURATION: 3 Days
OBJECTIVES
Upon successfully completing the course, participants will be able to:
- Package Usage (both User Defined and Oracle Defined Packages)
- PL/SQL Compiler Options
- Code Encryption
- Conditional Compilation
- Cursor Sharing/Cursor Variables
- Collections
- Triggers
- Dynamic SQL
- Autonomous Transactions
- PL/SQL Coding Tips
- Debugging PL/SQL
- PL/SQL Profiling
AUDIENCE
Application developers or Database Administrators who will develop applications in an Oracle environment.
PREREQUISITES: A working knowledge of PL/SQL.
FORMAT/MATERIALS
Lecture and discussion followed by hands-on labs to reinforce the material. Each participant receives an extensive manual of lecture notes, examples and workshop information.
1. PL/SQL Course Overview
- Course Objectives
- TOAD
- PL/SQL Documentation
2. PL/SQL Review
- PL/SQL Review
- PL/SQL Basic Syntax
- PL/SQL Variables
- Lab 2-1
- Lab 2-1 Solutions
- PL/SQL Logic Flow
- PL/SQL Logic Flow
- PL/SQL Procedures & Functions
- PL/SQL Procedures & Functions
- Lab 2-4
- Lab 2-4 Solutions
- PL/SQL Packages
- Lab 2-5
- Lab 2-5 Solutions
3. PL/SQL Options
- Function/Procedure Syntax
- Result Cache Tuning (11g)
- PL/SQL Limits
- Definer Rights/Invoker Rights
- Autonomous Transactions
- Purity Lab 3-1
- Lab 3-1 Solutions
- PL/SQL Compilers
- Oracle Native Compilation
- PL/SQL Packages
- Forward Declarations
- Lab 3-2
- Lab 3-2 Solutions
- Source Code Encryption
- Conditional Compilation
- Package Overloading
- Bodiless Packages
- Oracle11g New Features
- Lab 3-3
- Lab 3-3 Solutions
4. Database Triggers
- Trigger Overview
- Trigger Syntax
- Trigger Restrictions
- Trigger Processing Flow
- DML Trigger Examples
- Lab 4-1
- Lab 4-1 Solutions
- DDL Triggers
- SERVERERROR Triggers
- Lab 4-2
- Lab 4-2 Lab Solutions
5. Working with Cursors
- Cursor Variables
- Strong versus Weak Cursors
- Ref Cursor Example
- CURSOR_SHARING
- Lab 5-1
- Lab 5 Solutions.
6. PL/SQL Collections
- Collections Overview
- Record/Object Types
- Collections
- Associative ArraysNested Tables
- VARRY
- Lab 6-1
- Lab 6-1 Solutions
- Bulk Binding
- Forall Bulk Binding
- Lab 6-2
- Lab 6-2 Solutions
- Pipelined Table Functions
7. Oracle Provided Packages
- Oracle Package OverviewUseful Information
- PL/SQL Coding Stuff
- DBMS_OUTPUT
- UTL_FILE
- DBMS_UTILITY
- Lab 7-1
- Lab 7-1 Solutions
- PL/SQL Communications
- DBMS_ALERT
- DBMS_PIPE
- UTL_TCP
- Lab 7-2
- Lab 7-2 Solutions
- Useful Utilities
- DBMS_JOB
- DBMS_SCHEDULER
- DBMS_LOCK
- DBMS_RANDOM
- Lab 7-3
- Lab 7-3 Solutions
- DBA Stuff
- DBMS_STATS
- DBMS_UTILITY.EXECUTE_DDL_STATMENT
8. Dynamic SQL
- Dynamic SQL Overview
- DBMS _SQL
- Lab 8-1
-
Lab 8-1 Solutions
-
Execute Immediate
-
Lab 8-2
-
Lab 8-2 Solutions
9. Working with Large Objects
10. PL/SQL Tuning and Debugging
-
PL/SQL Coding Tips
-
PL/SQL Debugging
-
Lab 10-1
-
Lab 10-1 Solutions
-
PL/SQL Profiling
-
Understanding the Profiler Process
-
Using the PL/SQL Profiler
-
PL/SQL Profiling using TOAD
-
Formal End of Course.
11. White Papers