Advanced Oracle PL/SQL Tips and Techniques - ORA730
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.
- 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
Students Receive
- Study guide and relevant white papers
- Zip file full of useful SQL and SQL*Plus scripts
- Hands-on opportunity to learn more about Oracle, Toad and SQL Developer
Course Outline
Module 1: Advanced Oracle PL/SQL
- PL/SQL overview / review
- PL/SQL compiler options
- Code encryption
- Conditional compilation
- Definer / invoking rights
- Executing and exception handling
- Autonomous transactions
- Package Options
- Overloading
- Database Triggers
- DML, instead of, servererror, startup, shutdown, logon
Module 2: Working with cursor
- User defined data types
- Cursor Sharing
- Cursor variables
- Strong vs. weak cursors
- Associate Arrays
- Nested tables
- Varrays
- Bulk collect/foral bulk binding
- Using collections for reference table
- DBMS_alert
- DBMS_job
- DBMS_output
- DBMS_scheduler
- UTL_file
- UTL_mail
- Other provided package
- DBMS_SQL and Dynamic SQL
- Working with large objects
- Debugging PL/SQL Routines
- PL/SQL Profile