Oracle PL/SQL Programming Workshop
Duration: 3 Days
This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.
Learning how to create efficient, maintainable, and scalable PL/SQL programs are important objectives of this course. To meet these objectives, the course includes instruction on such things as the proper use of stored procedures and functions, cursors, bind variables, bulk processing, packages, triggers, error handling and utilities. Hands-on workshops constitute approximately 50% of the course.
Enrollment includes the highly rated O’Reilly text book “Oracle PL/SQL Best Practices“.
Hands-on workshops constitute approximately 50% of the class.
For customer-site training, as with all of our courses, this class is highly customizable to your specific training requirements.
Also see these free online tutorials:
- What is PL/SQL?
- Why Use PL/SQL?
- PL/SQL Program Structure
- Anonymous Blocks
- Using SQL Developer
- Compile Errors
- Server Output
- Introduction to Procedures
- Procedure Compile Errors
- Procedure Compile Warnings
- Introduction to Functions
- Introduction to Packages
- Querying the Data Dictionary
- Introduction to Triggers
- Tools for PL/SQL Development
- Working in SQL*Plus
- Workshop
- PL/SQL Statements
- PL/SQL Symbols
- Quoting Mechanism
- Common PL/SQL Datatypes
- Declaring Variables
- Assignment
- %TYPE Attribute
- PL/SQL Records
- %ROWTYPE Attribute
- Programmer Defined Records
- Workshop
- Variable Scope
- Nested Blocks
- Functions
- Regular Expressions
- IF Statement
- CASE Statement and Expression
- Simple CASE
- Searched CASE
- Simple Loops
- Nested Loops
- Numeric FOR Loop
- PL/SQL Arrays
- Simple Array Example
- Array Methods
- Using Arrays for Processing Speed
- Workshop
- SELECT INTO Statement
- Implicit Cursor Loops
- Workshop
- DML in PL/SQL
- Cursor Attributes
- Embedding DDL
- Workshop
- What is a Cursor?
- Explicit Cursor Processing
- Cursor Attributes
- Workshop
- Dynamic Cursors
- Dynamic Cursor Example
- Variable Scope & Cursors
- Cursor Parameters
- Cursor Records
- Cursor FOR LOOP
- Workshop
- Referencing the Current Row
- FOR UPDATE Example
- Workshop
- Bulk Processing
- Bulk Collect
- Bulk Collect: Fetch with Limit
- Workshop
- Bulk Delete: FORALL
- Bulk Insert – FORALL
- Advanced FORALL: IN INDICIES OF; IN VALUES OF
- Returning into Arrays
- Workshop
- What is a Stored Procedure?
- CREATE PROCEDURE Syntax
- Compile Errors
- Compiler Warnings
- Optimizing Compiler
- Native Compilation
- Conditional Compilation
- Procedure Signatures
- Calling Procedures
- Procedure Synonyms
- Referencing Parameters by Name
- Returning Sets: REF CURSOR
- Dropping Stored Procedures
- Procedure Dependencies
- ALTER COMPILE Statement
- PL/SQL Procedure Privileges
- Granting Execute Privilege
- The Data Dictionary
- Workshop
- What is a Function?
- CREATE FUNCTION Syntax
- Calling Functions
- The Data Dictionary
- Workshop
- Table Functions
- Building a Table Function
- Using a Table Function
- Pipelined Functions
- Pipelined Example
- Test Data Generator Example
- Workshop
- Types of Errors
- Runtime Errors
- Exception Concepts
- Predefined Named Exceptions
- EXCEPTION Block Syntax
- Handling Exceptions
- Workshop
- Recovering from Errors
- Logging Exceptions
- Debugging with FORMAT_ERROR_BACKTRACE
- RAISE_APPLICATION_ERROR Statement
- Workshop
- Raising Exceptions
- Raising User Exceptions
- EXCEPTION_INIT Pragma
- Concepts
- Package Benefits
- Package Contents
- Package Specification
- Package Body
- Package Privileges
- Calling Packaged Objects
- Initialization Code
- Session Variables
- Package Body Variables
- Workshop
- Package Overloading
- Workshop
- Compiling Packages
- DROP PACKAGE Statement
- Workshop
- Sample of Oracle Supplied Packages
- DML Trigger Concepts
- Trigger Execution
- Trigger Ordering
- CREATE TRIGGER Syntax
- Row Triggers
- :OLD and :NEW Reference Variables
- Trigger Attributes
- Audit Trigger
- Derived Value Trigger
- Trigger Restrictions
- Security Privileges
- Workshop
- Statement Level Triggers
- Workshop
- Autonomous Transactions
- Autonomous Triggers
- Workshop
- INSTEAD OF Triggers
- Workshop
- UTL_FILE Concepts
- Setup for UTL_FILE
- Permissions
- Opening Files
- Closing Files
- Reading Files
- Writing Files - PUT
- Writing Files - PUT_LINE
- Writing Files - NEW_LINE
- Writing Files – PUTF
- Workshop
- Introduction to UTL_MAIL
- Setup for Emailing from the Database
- UTL_MAIL Coding
- Other Oracle Supplied Packages
