Course Outline
Introduction
- Course Aims and Objectives
- Agenda and Schedule
- Participant Introductions
- Prerequisites Review
- Roles and Responsibilities
SQL Tools
- Learning Objectives
- Overview of SQL Developer
- Establishing SQL Developer Connections
- Inspecting Table Information
- Executing Queries via SQL and SQL Developer
- SQL*Plus Login Procedures
- Direct Connection Methods
- Utilizing SQL*Plus
- Terminating Sessions
- Essential SQL*Plus Commands
- Configuring the SQL*Plus Environment
- Navigating the SQL*Plus Prompt
- Retrieving Table Metadata
- Accessing Help Resources
- Working with SQL Files
- iSQL*Plus and Entity Models
- Understanding the ORDERS Tables
- Understanding the FILM Tables
- Course Tables Handout Reference
- SQL Statement Syntax
- Advanced SQL*Plus Commands
Understanding PL/SQL
- Definition and Scope of PL/SQL
- Benefits of Using PL/SQL
- Block Structure Fundamentals
- Outputting Messages
- Sample Code Walkthroughs
- Configuring SERVEROUTPUT
- Update Examples and Style Guide Adherence
Variables
- Variable Concepts
- Data Types Overview
- Initializing Variables
- Working with Constants
- Local vs. Global Variables
- %Type Variable Attributes
- Substitution Variables
- Comments Utilizing &
- Verification Options
- && Variable Usage
- Define and Undefine Commands
The SELECT Statement
- SELECT Statement Fundamentals
- Populating Variables
- %Rowtype Variables
- CHR Function Application
- Self-Study Modules
- PL/SQL Records
- Example Declaration Patterns
Conditional Statements
- IF Statement Logic
- SELECT Statement Integration
- Self-Study Exercises
- Case Statement Structures
Error Handling
- Exception Management
- Handling Internal Errors
- Error Codes and Messages
- Utilizing NO DATA FOUND
- Creating User Exceptions
- Raising Application Errors
- Capturing Undefined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback Operations
- Self-Study Resources
- Nested Blocks
- Workshop Activities
Iteration and Looping
- Loop Statement Mechanics
- While Statement Usage
- For Statement Application
- Goto Statements and Labels
Cursors
- Cursor Fundamentals
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Example
- Declaring the Cursor
- Declaring the Variable
- Opening and Fetching the First Row
- Fetching Subsequent Rows
- Exiting on %Notfound
- Closing Cursors
- For Loop Variant I
- For Loop Variant II
- Update Example
- FOR UPDATE Clause
- FOR UPDATE OF Clause
- WHERE CURRENT OF Usage
- Comitting with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters
- Workshop Exercises
- Workshop Solutions
Procedures, Functions, and Packages
- Create Statement Syntax
- Parameter Definitions
- Procedure Body Construction
- Troubleshooting Errors
- Describing a Procedure
- Invoking Procedures
- Executing Procedures in SQL*Plus
- Leveraging Output Parameters
- Calling with Output Parameters
- Developing Functions
- Function Example
- Troubleshooting Errors
- Describing a Function
- Invoking Functions
- Executing Functions in SQL*Plus
- Principles of Modular Programming
- Procedure Example
- Invoking Functions
- Using Functions in IF Statements
- Creating Packages
- Package Example
- Rationale for Using Packages
- Public and Private Sub-programs
- Troubleshooting Errors
- Describing a Package
- Executing Packages in SQL*Plus
- Calling Packages From Sub-programs
- Dropping a Sub-program
- Locating Sub-programs
- Developing a Debug Package
- Executing the Debug Package
- Positional and Named Notation
- Parameter Default Values
- Recompiling Procedures and Functions
- Workshop Exercises
Triggers
- Creating Triggers
- Statement Triggers
- Row Level Triggers
- WHEN Restriction Logic
- Selective Triggers using IF
- Troubleshooting Errors
- Managing Commits in Triggers
- Trigger Restrictions
- Addressing Mutating Triggers
- Locating Triggers
- Dropping a Trigger
- Generating Auto-numbers
- Disabling Triggers
- Enabling Triggers
- Trigger Naming Conventions
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
Dynamic SQL
- SQL within PL/SQL
- Binding Techniques
- Dynamic SQL Concepts
- Native Dynamic SQL
- DDL and DML Operations
- Utilizing the DBMS_SQL Package
- Dynamic SQL for SELECT
- Dynamic SQL SELECT Procedures
File Management
- Working with Text Files
- Using the UTL_FILE Package
- Write and Append Examples
- Read Examples
- Trigger Integration Examples
- DBMS_ALERT Package
- DBMS_JOB Package
Collections
- %Type Variables
- Record Variables
- Collection Types
- Index-By Tables
- Setting Values
- Handling Nonexistent Elements
- Nested Tables
- Nested Table Initialization
- Using Constructors
- Adding to a Nested Table
- Varrays
- Varray Initialization
- Adding Elements to a Varray
- Multilevel Collections
- Bulk Binding
- Bulk Binding Example
- Transactional Considerations
- BULK COLLECT Clause
- RETURNING INTO Clause
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained and Unconstrained Variants
- Using Cursor Variables
- Cursor Variable Examples
Requirements
This course is intended for individuals who already possess a foundational understanding of SQL.
While prior experience with interactive computer systems is advantageous, it is not a strict requirement.
Testimonials (7)
I liked the hands-on experience and the opportunity to work on actual coding activities
Kristine - Isuzu Philippines Corporation
Course - ORACLE PL/SQL Fundamentals
Relate each topic to a real world application case.
Ferdinand - National Grid Corporation of the Philippines
Course - ORACLE PL/SQL Fundamentals
the practices and the trainer notes
Hamda AlMahri - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
Mr. Khobeib was a great lecturer and trainer. As a beginner to PL/SQL, Khobeib explained the basics and was patient with us while going through the training material. He answered all our questions thoroughly and showed a lot of examples when we asked him to. I definitely learned a lot and can start doing tasks with PL/SQL.
Abdulrahman Alsalami - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
the trainer helpful all the time
Maitha Alselais - Dubai Courts
Course - ORACLE PL/SQL Fundamentals
The trainer was fantastic in all aspects. He was very interactive and engaging. Most importantly, the topics were taught very clearly and at a perfect pace to complete the course. I really appreciate it and would like to give a huge thank you to the trainer.
Vivek Thomas - Estee Lauder BV
Course - ORACLE PL/SQL Fundamentals
It was quite hands-on, not too much theory.
