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.
Course Outline
Introduction
- Aims and Objectives
- Course Schedule
- Introductions
- Pre-requisites
- Responsibilities
SQL Tools
- Objectives
- SQL Developer
- SQL Developer - Connection
- Viewing Table Information
- Using SQL, SQL Developer - Query
- SQL*Plus Login
- Direct Connection
- Using SQL*Plus
- Ending the Session
- SQL*Plus Commands
- SQL*Plus Environment
- SQL*Plus Prompt
- Finding Information about Tables
- Getting Help
- Using SQL Files
- iSQL*Plus, Entity Models
- The ORDERS Tables
- The FILM Tables
- Course Tables Handout
- SQL Statement Syntax
- SQL*Plus Commands
What is PL/SQL?
- What is PL/SQL?
- Why Use PL/SQL?
- Block Structure
- Displaying a Message
- Sample Code
- Setting SERVEROUTPUT
- Update Example, Style Guide
Variables
- Variables
- Datatypes
- Setting Variables
- Constants
- Local and Global Variables
- %Type Variables
- Substitution Variables
- Comments with &
- Verify Option
- && Variables
- Define and Undefine
SELECT Statement
- SELECT Statement
- Populating Variables
- %Rowtype Variables
- CHR Function
- Self Study
- PL/SQL Records
- Example Declarations
Conditional Statement
- IF Statement
- SELECT Statement
- Self Study
- Case Statement
Trapping Errors
- Exception
- Internal Errors
- Error Code and Message
- Using No Data Found
- User Exceptions
- Raise Application Error
- Trapping Non-defined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback
- Self Study
- Nested Blocks
- Workshop
Iteration - Looping
- Loop Statement
- While Statement
- For Statement
- Goto Statement and Labels
Cursors
- Cursors
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Example
- Declaring the Cursor
- Declaring the Variable
- Open, Fetching the First Row
- Fetching the Next Row
- Exit When %Notfound
- Close
- For Loop I
- For Loop II
- Update Example
- FOR UPDATE
- FOR UPDATE OF
- WHERE CURRENT OF
- Commit with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters,
- Workshop
- Workshop Solution
Procedures, Functions and Packages
- Create Statement
- Parameters
- Procedure Body
- Showing Errors
- Describe a Procedure
- Calling Procedures
- Calling Procedures in SQL*Plus
- Using Output Parameters
- Calling with Output Parameters
- Creating Functions
- Example Function
- Showing Errors
- Describe a Function
- Calling Functions
- Calling Functions in SQL*Plus
- Modular Programming
- Example Procedure
- Calling Functions
- Calling Functions In An IF Statement
- Creating Packages
- Package Example
- Reasons for Packages
- Public and Private Sub-programs
- Showing Errors
- Describe a Package
- Calling Packages in SQL*Plus
- Calling Packages From Sub-Programs
- Dropping a Sub-Program
- Finding Sub-programs
- Creating a Debug Package
- Calling the Debug Package
- Positional and Named Notation
- Parameter Default Values
- Recompiling Procedures and Functions
- Workshop
Triggers
- Creating Triggers
- Statement Triggers
- Row Level Triggers
- WHEN Restriction
- Selective Triggers - IF
- Showing Errors
- Commit in Triggers
- Restrictions
- Mutating Triggers
- Finding Triggers
- Dropping a Trigger
- Generating an Auto-number
- Disabling Triggers
- Enabling Triggers
- Trigger Names
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
Dynamic SQL
- SQL in PL/SQL
- Binding
- Dynamic SQL
- Native Dynamic SQL
- DDL and DML
- DBMS_SQL Package
- Dynamic SQL - SELECT
- Dynamic SQL - SELECT Procedure
Using Files
- Using Text Files
- UTL_FILE Package
- Write/Append Example
- Read Example
- Trigger Example
- DBMS_ALERT Packages
- DBMS_JOB Package
COLLECTIONS
- %Type Variables
- Record Variables
- Collection Types
- Index-By Tables
- Setting Values
- Nonexistent Elements
- Nested Tables
- Nested Table Initialisation
- Using the Constructor
- Adding to a Nested Table
- Varrays
- Varray Initialization
- Adding Elements to a Varray
- Multilevel Collections
- Bulk Bind
- Bulk Bind Example
- Transactional Issues
- BULK COLLECT Clause
- RETURNING INTO
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained and Unconstrained
- Using Cursor Variables
- Cursor Variable Examples
Requirements
The course is only suitable for those who have some knowledge of SQL.
Previous experience with an interactive computer system is desirable but not essential.
21 Hours
Delivery Options
Private Group Training
Our identity is rooted in delivering exactly what our clients need.
- Pre-course call with your trainer
- Customisation of the learning experience to achieve your goals -
- Bespoke outlines
- Practical hands-on exercises containing data / scenarios recognisable to the learners
- Training scheduled on a date of your choice
- Delivered online, onsite/classroom or hybrid by experts sharing real world experience
Private Group Prices RRP from £5700 online delivery, based on a group of 2 delegates, £1800 per additional delegate (excludes any certification / exam costs). We recommend a maximum group size of 12 for most learning events.
Contact us for an exact quote and to hear our latest promotions
Public Training
Please see our public courses
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.
