Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Duration 21 hours
Course Outline
Introduction
- Purpose and Goals
- Schedule Overview
- Participant Introduction
- Prerequisites
- Role Responsibilities
SQL Development Environment
- Learning Objectives
- SQL Developer Interface
- Establishing Connections in SQL Developer
- Examining Table Metadata
- Executing Queries in SQL Developer
- SQL*Plus Authentication
- Direct Database Connection
- Operating SQL*Plus
- Terminating Sessions
- SQL*Plus Command Reference
- SQL*Plus Runtime Environment
- SQL*Plus Prompt Indicators
- Retrieving Table Information
- Accessing Help Resources
- Executing SQL Scripts
- iSQL*Plus and Entity Models
- The ORDERS Table Structure
- The FILM Table Structure
- Reference Materials for Course Tables
- SQL Syntax Standards
- SQL*Plus Command Review
Overview of PL/SQL
- Definition of PL/SQL
- Benefits of PL/SQL Adoption
- Program Block Structure
- Outputting Messages
- Illustrative Code Samples
- Configuring SERVEROUTPUT
- Update Logic and Coding Standards
Variable Management
- Variable Concepts
- Data Type Specifications
- Variable Assignment
- Constant Definitions
- Scope: Local vs. Global Variables
- %Type Variable Usage
- Substitution Variables
- Including Comments with Ampersand
- Validation Options
- Persistent & Variables
- Managing Definitions: Define and Undefine
Implementing the SELECT Statement
- Constructing SELECT Statements
- Assigning Query Results to Variables
- Utilizing %Rowtype Variables
- Applying the CHR Function
- Independent Practice Module
- Defining PL/SQL Records
- Record Declaration Examples
Conditional Logic
- IF Statement Implementation
- SELECT Logic Integration
- Independent Practice Module
- CASE Statement Usage
Error Handling Mechanisms
- Exception Concepts
- System-Generated Errors
- Retrieving Error Codes and Messages
- Handling No Data Found Scenarios
- Defining User Exceptions
- Raising Application Errors
- Handling Undefined Exceptions
- Using PRAGMA EXCEPTION_INIT
- Managing Transactions: Commit and Rollback
- Independent Practice Module
- Nested Block Structures
- Practical Exercise
Looping Constructs
- BASIC LOOP Implementation
- WHILE LOOP Logic
- FOR LOOP Iteration
- Control Flow with GOTO and Labels
Cursor Management
- Cursor Fundamentals
- Cursor Attribute Access
- Explicit Cursor Definitions
- Explicit Cursor Usage Example
- Cursor Declaration Syntax
- Variable Declaration for Cursors
- Opening Cursors and Fetching Initial Rows
- Iterative Row Fetching
- Termination Logic with %NOTFOUND
- Closing Cursors
- FOR Loop Integration Part I
- FOR Loop Integration Part II
- Update Logic Demonstration
- Row Locking with FOR UPDATE
- Column-Specific Locking with FOR UPDATE OF
- Positional Updates with WHERE CURRENT OF
- Transaction Control with Cursors
- Validation Scenario I
- Validation Scenario II
- Parameterized Cursors
- Practical Exercise
- Exercise Solution Key
Subprograms and Modular Code
- CREATE Statement Syntax
- Parameter Definitions
- Procedure Logic Implementation
- Error Reporting in Procedures
- Procedure Metadata Description
- Invoking Procedures
- Calling Procedures via SQL*Plus
- Managing Output Parameters
- Passing Output Parameters in Calls
- Function Development
- Function Implementation Example
- Error Reporting in Functions
- Function Metadata Description
- Invoking Functions
- Calling Functions via SQL*Plus
- Principles of Modular Programming
- Procedure Implementation Example
- Function Invocation
- Integrating Functions in Conditional Logic
- Package Development
- Package Implementation Example
- Advantages of Package Usage
- Access Control: Public and Private Subprograms
- Error Reporting in Packages
- Package Metadata Description
- Calling Packages via SQL*Plus
- Invoking Packages from Subprograms
- Removing Subprograms
- Locating Existing Subprograms
- Developing Debugging Packages
- Utilizing Debugging Packages
- Parameter Passing: Positional and Named Notation
- Setting Default Parameter Values
- Recompilation of Subprograms
- Practical Exercise
Database Triggers
- Trigger Creation Process
- Statement-Level Triggers
- Row-Level Triggers
- Conditional Logic with WHEN Clause
- Selective Trigger Execution
- Error Handling in Triggers
- Transaction Control in Triggers
- Trigger Limitations
- Mutating Table Restrictions
- Identifying Existing Triggers
- Removing Triggers
- Automatic Number Generation
- Disabling Triggers
- Enabling Triggers
- Naming Conventions for Triggers
Reference Data
- ORDER Table Structures
- FILM Table Structures
- EMPLOYEE Table Structures
Dynamic SQL Execution
- Embedding SQL in PL/SQL
- Variable Binding Techniques
- Dynamic SQL Concepts
- Native Dynamic SQL (NDS) Methods
- Executing DDL and DML Dynamically
- Utilizing the DBMS_SQL Package
- Dynamic SELECT Statements
- Dynamic SELECT Procedures
File I/O Operations
- Text File Processing
- UTL_FILE Package Functions
- Write and Append Operations
- Read Operations Example
- Trigger-Based File Operations
- DBMS_ALERT Package Utilization
- DBMS_JOB Package Utilization
Collection Data Types
- %Type Variable Collections
- Record Variable Collections
- Collection Type Overview
- Associative Arrays (Index-By Tables)
- Assigning Collection Values
- Handling Missing Elements
- Nested Table Structures
- Nested Table Initialization
- Constructor Function Usage
- Populating Nested Tables
- VARRAY Implementations
- VARRAY Initialization
- Populating VARRAYs
- Multilevel Collection Structures
- Bulk Binding Operations
- Bulk Binding Example
- Transaction Considerations
- BULK COLLECT Clause Usage
- RETURNING INTO Clause
Reifiable Cursors
- Cursor Variable Concepts
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained vs. Unconstrained Cursors
- Cursor Variable Operations
- Cursor Variable Implementation Examples
Requirements
Participants are expected to possess foundational knowledge of SQL to successfully engage with the course content.
Prior experience with interactive computing systems is recommended but is not a mandatory requirement for admission.
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.
