TruTek

Give your IT staff the knowledge of using advanced features of Oracle’s procedural language: PL/SQL. 

The focus of this course is to advance attendee knowledge of the PL/SQL programming language.  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 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. 

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. 

Workshop Prerequisites: 

A working knowledge of SQL and PL/SQL is required.

Topics covered:

• Oracle Architecture
• Introduction to PL/SQL
• Syntax and language structure review
• PL/SQL Topics
• Package Usage (both User Defined and Oracle Defined Packages)
• 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

Attendees Receive:

• Study guide and relevant white papers
• Diskette full of useful SQL and SQL*Plus scripts
• A hands-on opportunity to learn more about Oracle, TOAD, and SQL Developer

Course Outline:

• Day 1: Advanced Oracle PL/SQL

-          PL/SQL Overview/Review

-          PL/SQL Compiler Options

-          Code Encryption

-          Conditional Compilation

-          Definer/Invoker Rights

-          Executing and Exception handling

-          Autonomous Transactions

-          Package Options

-          Overloading

-          Database Triggers

• DML, Instead of, ServerError, Startup, Shutdown, Logon     

• Day 2: Advanced Oracle PL/SQL

-          Working with Cursors

• User Defined Datatypes
• Cursor Sharing
• Cursor Variables
• Strong vs Weak Cursors

-         Working with Collections

• Associate Arrays
• Nested Tables
• Varrays
• Bulk Collect/Forall Bulk Binding
• Using Collections for Reference Table

-          Oracle Provided Packages

• DBMS_Alert
• DBMS_Job
• DBMS_Output
• DBMS_Scheduler
• UTL_File
• UTL_Mail
• Other Provided Packages 

• Day 3: Advanced Oracle PL/SQL

-          DBMS_SQL & Dynamic SQL

-          Working with Large Objects

-          Debugging PL/SQL Routines

-          PL/SQL Profile