ORACLE PL/SQL Fundamentals Training Course

Primary tabs

Duration Duration

21 hours (usually 3 days including breaks)

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

Overview Overview

This 3 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 lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.

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

Course Outline 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

Bookings, Prices and Enquiries

Guaranteed to run even with a single delegate!
Public Classroom Public Classroom
From 5206PLN
(69)
Public Classroom
Participants from multiple organisations. Topics usually cannot be customised
Private Classroom
Participants are from one organisation only. No external participants are allowed. Usually customised to a specific group, course topics are agreed between the client and the trainer.
Private Remote
The instructor and the participants are in two different physical locations and communicate via the Internet. More Information

The more delegates, the greater the savings per delegate. Table reflects price per delegate and is used for illustration purposes only, actual prices may differ.

Number of Delegates Public Classroom Private Remote
1 5206PLN 15200PLN
2 4258PLN 9255PLN
3 3942PLN 7273PLN
4 3784PLN 6283PLN
Cannot find a suitable date? Choose Your Course Date >>
Too expensive? Suggest your price

Related Categories

Related Courses


Upcoming Courses

VenueCourse DateCourse Price [Remote / Classroom]
Częstochowa, ul. Wały Dwernickiego 117/121Tue, 2017-06-13 09:0015200PLN / 5656PLN
Poznan, Garbary 100/63Tue, 2017-06-13 09:0015200PLN / 5206PLN
Rzeszów, Plac Wolności 13Tue, 2017-06-13 09:0015200PLN / 5206PLN
Bielsko-Biała, Al. Armii Krajowej 220Wed, 2017-06-14 09:0015200PLN / 5656PLN
Opole, Wladyslawa Reymonta 29Wed, 2017-06-14 09:0015200PLN / 5956PLN

Course Discounts

Course Venue Course Date Course Price [Remote / Classroom]
Python Programming Szczecin, ul. Sienna 9 Mon, 2017-05-29 09:00 10000PLN / 4448PLN
Facebook in advertising and marketing Lublin, ul. Spadochroniarzy 9 Fri, 2017-06-02 09:00 1881PLN / 1002PLN
MongoDB for Administrators Kraków, ul. Rzemieślnicza 1 Tue, 2017-06-06 09:00 3861PLN / 2087PLN
Oracle 11g - Programming with PL / SQL I - Workshops Wroclaw, ul.Ludwika Rydygiera 2a/22 Tue, 2017-06-06 09:00 5990PLN / 2939PLN
Adobe Photoshop Elements Gdynia, ul. Ejsmonda 2 Wed, 2017-06-07 09:00 1881PLN / 1127PLN
Microsoft Office Excel - moduł Business Intelligence Gdynia, ul. Ejsmonda 2 Wed, 2017-06-07 09:00 2673PLN / 1391PLN
Adobe Photoshop Elements Gdańsk, ul. Powstańców Warszawskich 45 Wed, 2017-06-07 09:00 1881PLN / 1127PLN
Adobe InDesign Poznan, Garbary 100/63 Thu, 2017-06-08 09:00 1881PLN / 1027PLN
Design Patterns in C# Poznan, Garbary 100/63 Thu, 2017-06-08 09:00 3861PLN / 1830PLN
SQL Fundamentals Gdańsk, ul. Powstańców Warszawskich 45 Thu, 2017-06-08 09:00 3663PLN / 1610PLN
Visual Basic for Applications (VBA) in Excel - Advanced Warszawa, ul. Złota 3/11 Mon, 2017-06-12 09:00 3069PLN / 1623PLN
Visual Basic for Applications (VBA) in Excel - Introduction to programming Gdynia, ul. Ejsmonda 2 Mon, 2017-06-12 09:00 3564PLN / 1891PLN
DTP (InDesign, Photoshop, Illustrator, Acrobat) Opole, Wladyslawa Reymonta 29 Mon, 2017-06-12 09:00 5940PLN / 4230PLN
Spring and Hibernate in Java Applications Poznan, Garbary 100/63 Tue, 2017-06-13 09:00 7722PLN / 3358PLN
Drools Rules Administration Wroclaw, ul.Ludwika Rydygiera 2a/22 Wed, 2017-06-14 09:00 21196PLN / 7023PLN
Adobe LiveCycle Designer Poznan, Garbary 100/63 Mon, 2017-06-19 09:00 2970PLN / 1885PLN
Build applications with Oracle Application Express (APEX) Katowice ul. Opolska 22 Mon, 2017-06-19 09:00 9801PLN / 4720PLN
Front End Developer Rzeszów, Plac Wolności 13 Mon, 2017-06-19 09:00 23000PLN / 7970PLN
Creating and managing Web sites Poznan, Garbary 100/63 Mon, 2017-06-19 09:00 5841PLN / 2298PLN
Introduction to Selenium Warszawa, ul. Złota 3/11 Thu, 2017-06-22 09:00 1871PLN / 824PLN
Introduction to Programming Gdańsk, ul. Powstańców Warszawskich 45 Mon, 2017-06-26 09:00 5742PLN / 4121PLN
Javascript And Ajax Rzeszów, Plac Wolności 13 Mon, 2017-06-26 09:00 5841PLN / 3655PLN
Implementation and Administration of Elasticsearch Wroclaw, ul.Ludwika Rydygiera 2a/22 Wed, 2017-06-28 09:00 20800PLN / 6903PLN
Effective interpersonal communication with elements of assertiveness Wroclaw, ul.Ludwika Rydygiera 2a/22 Thu, 2017-06-29 09:00 5148PLN / 1430PLN
Elasticsearch Advanced Administration, Monitoring and Maintenance Gdańsk, ul. Powstańców Warszawskich 45 Tue, 2017-07-04 09:00 17741PLN / 5876PLN
Nginx Setup, Configuration and Administration Bydgoszcz, ul. Dworcowa 94 Wed, 2017-07-05 09:00 6930PLN / 2850PLN
SQL Fundamentals Warszawa, ul. Złota 3/11 Mon, 2017-07-10 09:00 3663PLN / 1510PLN
SIP protocol in VoIP Poznan, Garbary 100/63 Mon, 2017-07-17 09:00 15929PLN / 5427PLN
Excel VBA Introduction Wroclaw, ul.Ludwika Rydygiera 2a/22 Wed, 2017-08-02 09:00 2376PLN / 1192PLN
Programming in WPF 4.5 Lublin, ul. Spadochroniarzy 9 Wed, 2017-08-16 09:00 6435PLN / 2443PLN
Creating and managing Web sites Poznan, Garbary 100/63 Mon, 2017-09-25 09:00 5841PLN / 2298PLN

Course Discounts Newsletter

We respect the privacy of your email address. We will not pass on or sell your address to others.
You can always change your preferences or unsubscribe completely.

Some of our clients