Advanced PL/SQL Tips and Techniques

Course Description

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 up to and including Oracle21c. 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 contains additional white papers on Java and PL/SQL and executing external procedures. 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.
3 days
Contact us for pricing
 

Prerequisites

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

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
Useful SQL and SQL*Plus scripts
A hands-on opportunity to learn more about Oracle, TOAD, and SQL Developer

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 and Dynamic SQL
- Lecture on SQL Injection
- Working with Large Objects
- Debugging PL/SQL Routines
- PL/SQL Profile

About Dan Hotka

Dan Hotka is a Training Specialist and an Oracle ACE Director Alumni (a recognized outside expert/evangelist on Oracle Products) who has over 10 years in the computer industry, over 34 years of experience with Oracle products. His experience with the Oracle RDBMS dates back to the Oracle V4.0 days. Dan enjoys sharing his knowledge of the Oracle RDBMS. Dan is well published with 14 Oracle books and well over 200 published articles. He is frequently published in Oracle trade journals, regularly blogs, and speaks at Oracle conferences and user groups around the world.
OracleDan HotkaOracle 12cOracle Database 12cOracle PL/SQLOracle PL/SQL TuningOracle 19c19cOracle ExpertOracle 21cOracle 21c Tuning