IT manager

Training Course: ORACLE PL/SQL Fundamentals Training Course


Register Now
Quick Inquiry
Discount Group Download Brochure  

IT234661

23 - 27 May 2021

Cairo (Egypt)

Hotel : InterContinental Cairo Semiramis

Cost : 2500 €

Introduction

This 5-day course gives an introduction to ORACLE PL/SQL, an application development environment that enables the writing of stored procedures, functions, and triggers using both SQL and PL/SQL commands.

The course takes the format of a workshop, with a mix of lectures, working examples, and practical exercises. Although the content may be customized, at least 2 days are needed to cover the core elements.

Full course notes are provided along with sample database files, for example, SQL files, and free software tools for use in accessing an ORACLE database.

Target Audience of ORACLE PL/SQL Fundamentals Training Course

  • 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.

Course Outline of ORACLE PL/SQL Fundamentals Training Course

Day 1

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

Day 2

What is PL/SQL?

  • What is PL/SQL?

  • Why Use PL/SQL?

  • Block Structure

  • Displaying a Message

  • Sample Code

  • Setting SERVER OUTPUT

  • 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

Day 3

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

Day 4

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

Day 5

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

IT manager

Training Course: ORACLE PL/SQL Fundamentals Training Course


Register Now
Quick Inquiry
Discount Group Download Brochure  

IT234661

23 - 27 May 2021

Cairo (Egypt) - InterContinental Cairo Semiramis

Hotel : InterContinental Cairo Semiramis

Cost: 2500 €


  About Cairo

Cairo is a mesmerizing assortment of iconic monuments, bustling markets, and ancient sites encircled by a vast and sprawling metropolis. At its core lies a maze-like medieval quarter. A world away from the modern side of the city, it boasts a seductive blend of intricate mosques, jostling crowds, madrasas, and traditional hammams. No visit could be complete without visiting the awe-inspiring Giza Pyramids that preside over the city's horizon. Whether you come to delve into the city's ancient past, dive into colorful souqs, sip mint tea and contemplate thousands of years of history or explore the city's cosmopolitan side, you'll be left with memories to last a lifetime.


  Things to do and places to visit in Cairo

The multi-layered capital of Egypt offers its visitors lively markets, authentic restaurants, and a multitude of ancient sights and archeological findings. Gaze in wonder at the Pyramids, take an awe-inspiring look at Tutankhamen's burial mask, shop for handmade souvenirs in the bazaars or hop on a sunset cruise down the Nile and embrace the sensory overload that is Cairo.

Take a vacation in Cairo and you can:

  • Visit the Great Sphinx of Giza, one of Ancient Egypt's great mysteries.
  • Explore the ramparts of the Salah El-Din Citadel.
  • Join locals drinking coffee at Fishawi Café, one of Cairo's first cafes and most important meeting spots.
  • Marvel at the seemingly endless historical artifacts on display at the Egyptian Museum.
  • Learn about the Ummayad, Ottoman, and Ayyubbid dynasties and more at the Museum of Islamic Art.
  • Haggle for souvenirs and gifts at the Khan el-Khalili souk.
  • See one of the world's oldest universities at the Al-Azhar Mosque, one of the city's original mosques.
  • Take a tour of the showstopping Giza pyramids.
  • Savor local cuisine such as mezze in the old city.
  • Wander through the labyrinth-like streets of Coptic Cairo, the city's ancient quarter, and spot Roman relics.
 22 Portman Square, Marylebone, London W1H 7BG, UK
 3 Oudai street, Aldouki, Giza, Giza Governorate, Egypt
 0020233379764
 00201095004484
 00201102960555
 00201102960666
 19 Mayıs Mahallesi, 19 Mayis Street No 2 Sisli, 34360 Istanbul/Turkey
 00905357839460
 Australia Street, Raouche Beirut, Lebanon .، Beirut, Lebanon
 0096181746278
 811 Massachusetts Avenue, Boston, Massachusetts, 02118, USA
 6 Beirut Street - Fifth Circle Abdoun, P.O. Box 831370, 11183 Amman, Jordan
Copyright Global Horizon Training Center © 2019