This course provides in-depth training on advanced Oracle database administration techniques. Participants will learn how to optimize database performance, ensure high availability

PL/SQL Basics

• The HR Schema
• What is PL/SQL?
• Blocks
• Outputting Information
• Variables and Constants
• Constants
• Data Types
• Naming Variables and Other Elements
• Embedding SQL in PL/SQL
• SELECT…INTO and RETURNING…INTO
• Using Variables (Exercise)
• PL/SQL Features

Subprograms

• Introduction to Subprograms
• Procedures
• Variable Declarations
• Parameters
• Parameters with Default Values
• Parameter Modes
• IN Mode
• OUT Mode
• IN OUT Mode
• Named Notation
• Using SQL in a Subprogram
• %TYPE
• Creating a Procedure (Exercise)
• Functions
• Creating a Function (Exercise)
• Using PL/SQL Functions in SQL Queries
• Dropping a Subprogram

Conditional Processing

• Conditions and Booleans
• IF-ELSIF-ELSE Conditions
• Creating a get_age() Function (Exercise)
• ELSIF
• Creating a check_rights() Procedure (Exercise)
• Creating an is_manager() Function (Exercise)
• BOOLEAN Values and Standard SQL
• The CASE Statement
• CASE Expressions
• Replacing the Head Honcho (Exercise)

Exceptions

• Introduction to Exceptions
• Predefined Exceptions
• The EXCEPTION Part of the Block
• Catching NO_DATA_FOUND Exception (Exercise)
• User-defined Exceptions
• User-defined Exceptions in Subprograms
• Re-raising Exceptions
• Replacing the Head Honcho (revisited) (Exercise)
• Adding Exceptions to update_employee_manager() (Exercise)
• Naming Unnamed Predefined Exceptions
• WHILE Loops
• When to Use Exceptions

Cursors

• Implicit Cursors
• Using Implicit Cursor Attributes (Exercise)
• Explicit Cursors
• %ROWTYPE
• Explicit Cursor Use Case
• Cursor FOR LOOP
• Using an Explicit Cursor (Exercise)
• Cursor Parameters

Packages

• Package Basics
• The Package Specification
• The Package Body
• Modifying the Package (Exercise)
• Building an Employee Package
• Adding a get_manager() Function (Exercise)
• Overloading Subprograms
• Adding Overloaded Functions to the Package (Exercise)
• Auditing
• Validation Procedures
• Adding a Validation Procedure (Exercise)
• Package Cursors
• Adding a Cursor to the Package (Exercise)
• Benefits of Packages

Triggers

• What are Triggers?
• Trigger Parts
• Validation Triggers
• Creating a Trigger on the jobs Table (Exercise)
• The WHEN Clause
• Using the WHEN Clause (Exercise)
• Audit Triggers
• Statement-level Triggers
• Compound Triggers
• Trigger Warning

• Oracle Database 11g: Introduction to SQL (combination of Oracle Database 11g: SQL Fundamentals I and Oracle Database 11g: SQL Fundamentals II)

Suggested Prerequisites:

• Previous programming experience