COURSE DESCRIPTION: This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.
Attendees will learn tips and techniques on how to advance their expertise when programming in PL/SQL. This course covers all of the latest features of PL/SQL using databases Oracle 10g, Oracle 11g and Oracle 12c. This course utilizes a variety of tools, one of which is Quest Software's Toad.
This class could easily be named "New Features for Developers."
Lectures and topics are enhanced with live illustrations and hands-on exercises.
Review of PL/SQL syntax and language structure.
Review of cursor management.
Review of PL/SQL Profiling.
Review of Package Usage (User Defined and Oracle Defined.
Introduction to advanced PL/SQL topics including compiler options, code encryption, conditional compilation, collections, triggers, dynamic SQL and much more.
AUDIENCE: Students who wish to learn advanced tips and techniques of PL/SQL programming who already have a working knowledge of SQL and PL/SQL.
PREREQUISITES: Working knowledge of SQL and PL/SQL.
ATTENDEES RECEIVE: o Study Guide with presentations o PL/SQL Reference pocket guide o Example files with SQL and SQL*Plus scripts o Information on Toad, SQL Developer, using UTL_MAIL and executing external procedures
COURSE OUTLINE: Day 1: Review of PL/SQL Options and Compiling o Overview o Review o PL/SQL Compiler Options o Code Encryption o Conditional Compilation o Definer/Invoker Rights o Executing and Exception handling o Autonomous Transactions o Package Options o Overloading
Day 2: Triggers, Cursors and Collections o Database Triggers - DML (Data Manipulation Language) - Instead of - ServerError - Startup - Shutdown - Logon o Cursors - User Defined Datatypes - Cursor Sharing - Cursor Variables - Strong vs. Weak Cursors o Collections - Associate Arrays - Nested Tables - VArrays - Bulk Collect/Forall Bulk Binding - Using Collections for Reference Table
Day 3: Oracle Provided Packages and Miscellaneous Topics o DBMS_ALERT o DBMS_JOB o DBMS_OUTPUT o DBMS_SCHEDULER o UTL_FILE o UTL_MAIL o Other Provided Packages o DBMS_SQL and Dynamic SQL o Working with Large Objects o Debugging PL/SQL Routines o PL/SQL Profile