StayAhead Oracle Program with PL/SQL Training Course :: Oracle Program with PL/SQL Training UK

Print Friendly

Back

Oracle

Oracle e-Business Suite 11i

Oracle e-Business Suite R12

MySQL & PHP

UNIX

Solaris

AIX

HP-UX

LINUX

Red Hat Linux

SUSE Linux

Java & Web Development

Databases

Business Analysis & Systems Development

ITIL® & Prince2®

Request Information

Let Us Call You

Request a Brochure

Course Information Request

On-Site Request

Email a Friend

Training Centres

Oracle Program with PL/SQL Course Overview

The Oracle Program with PL/SQL training course introduces the delegate to Oracle's PL/SQL programming language and provides practical experience in writing stand-alone programs, developing database triggers and implementing stored functions, procedures and packages. It also introduces some of the Oracle-supplied packages.

Skills Gained

The delegates will practise:

Writing and running PL/SQL programs from SQL*Plus or SQL Developer
Declaring and using variables and constants
Assigning and manipulating values
Using SQL statements within PL/SQL
Implementing conditional execution and loop constructs
Declaring and using cursors with and without parameters
Updating data with cursors
Trapping and handling errors and exceptions in code
Implementing nested and labelled loops and blocks
Raising exceptions
Handling PL/SQL records and Index-By tables
Using PL/SQL Index-By table built-in functions
Implementing various types of trigger
Creating, managing and invoking stored procedures
Creating, managing and executing stored functions
Developing and using packages
Using package features and constructs effectively
Wrapping packages
Using Cursor Variables
Using records and Index-By tables or associative arrays in packages
Implementing Bulk Binding and Bulk Collecting techniques
Managing program unit dependencies
Implementing Native Compilation and Compile-time Warnings
Implementing Native Dynamic SQL
Using a variety of Oracle-supplied packages

Who will the Course Benefit?

Oracle database administrators, software development personnel and database support staff who need to write PL/SQL scripts and implement or maintain database triggers, program units and packages.

Course Objectives

To provide the skills needed to develop, write and maintain PL/SQL scripts, triggers, stored program units and packages.

Examinations

The Oracle Program with PL/SQL course is suitable for delegates intending to sit the Oracle examination #1Z0-144 - Oracle Database 11g: Program with PL/SQL & the Oracle PL/SQL Developer Certified Associate examination #1Z0-147 - Program with PL/SQL Oracle examination.

Before taking any exam, ensure you have the recommended experience. The Oracle website lists all exam requirements and these are updated regularly.

Exams are not included as part of the course.

Requirements

A working knowledge of Oracle SQL or SQL Developer is required required along with practical experience in writing SQL statements. This can be obtained by attendance on the pre-requisite courses. Some previous programming experience will also prove advantageous as well as practical experience in writing SQL statements.

Pre-Requisite Courses

Oracle SQL

Follow-On Courses

NOTE: Course technical content is subject to change without notice.

Oracle Program with PL/SQL Training Course

Course Contents - DAY 1

Course Introduction

Administration and Course Materials
Course Structure and Agenda
Delegate and Trainer Introductions

Session 1: PL/SQL FUNDAMENTALS

What is PL/SQL?
Basic Elements
Variables and Constants
Data Types
Initializing Variables and Assigning Values
Using SQL Statements in Code
Generating Output to SQL or SQL Developer

Session 2: PROGRAM LOGIC

IF THEN ELSE Statements
CASE Expressions
The Basic Loop Construct
WHILE and FOR Loops
Nested and Labelled Loops
The GOTO Statement
The CONTINUE Statement

Session 3: USING CURSORS

What is a Cursor?
Implicit and Explicit Cursors
Cursor Operations
Declaring, Opening and Closing Cursors
Fetching Rows
Status Checking
Using Cursors FOR UPDATE
The Cursor FOR Loop
Parameterised Cursors

Oracle Program with PL/SQL Training Course

Course Contents - DAY 2

Session 4: EXCEPTIONS AND NESTED BLOCKS

The EXCEPTION Section
Types of Exception
Handling Named System-Raised Exceptions
Handling Unnamed System-Raised Exceptions
User-Declared Exceptions and Application Errors
When others then Null
Nested and Labelled Blocks
Propagation of Exceptions
Scope of Variables and Cursors

Session 5: PL/SQL RECORDS AND INDEX-BY TABLES

Declaring Record Types
Handling PL/SQL Records
Nested Records
Declaring PL/SQL Index-By Tables or Associative Arrays
PL/SQL Table Built In Functions
Manipulating PL/SQL Tables or Associative Arrays

Oracle Program with PL/SQL Training Course

Course Contents - DAY 3

Session 6: TRIGGERS

DML Triggers
The CREATE TRIGGER Statement
Writing Trigger Code
INSTEAD OF Triggers
Calling Procedures from Triggers
Coding Restrictions
System Event and DDL Triggers
Attribute Functions
Compound Triggers
Create Trigger Follows Clause
Managing Triggers
Privileges required for Triggers
Dictionary Information Concerning triggers

Session 7: PROCEDURES

What is a Procedure?
The CREATE PROCEDURE Statement
Procedure Parameters
Invoking Procedures
Local Subprograms
Named Association Parameter Passing
Definer's Right and Invoker's Rights
Autonomous Transactions
Managing Procedures
Privileges Required for Procedures
Dictionary Information Concerning Procedures
The Call Statement

Session 8: FUNCTIONS

What is a Function?
The CREATE FUNCTION Statement
Executing Functions
Invoker's Rights
Autonomous Transactions
DBMS_OUTPUT
Using Functions in SQL Statements
Deterministic and Parallel-Enabled Functions
Function Result Cache
Managing Functions
Privileges Required for Functions
Dictionary Information Concerning Functions

Oracle Program with PL/SQL Training Course

Course Contents - DAY 4

Session 9: PACKAGES

What is a Package?
Public and Private Components
Creating a Package
Example Package
Persistent States
One-time-only Procedures
Overloading
Purity Level Checking
Forward Declarations
Wrapping Packages
Managing Packages
Privileges Required for Packages
Dictionary Information Concerning Packages Features

Session 10: USING PL/SQL RECORDS AND TABLES IN PACKAGES

Overview of PL/SQL Records, Index-by Tables and Associative Arrays
Using PL/SQL Records and Tables in Packages
Table Built-In Functions
The NOCOPY Hint
Bulk Collection
Bulk Binding DML Statements

Session 11: CURSOR VARIABLES (REF CURSORS)

Declare Cursor Variables
Use Cursor Variables
Open and Close Cursor Variables
Fetch Rows
Cursor Variable Attributes
Pass Cursor Variables as Parameters
Open and Close Cursor Variables
Refcursor Datatype

Session 12: MANAGING DEPENDENCIES

Dependent and Referenced Objects
Invalidation and Recompilation
Local and Remote Dependencies
Recompilation Considerations

Oracle Program with PL/SQL Training Course

Course Contents - DAY 5

Session 13: NATIVE COMPILATION AND COMPILE-TIME WARNINGS

Introduction
Native Compilation
Automatic Recompilation
Automatic program Sublining
WHEN OTHERS ... THEN NULL
Data Dictionary Information
Compiler Warning Categories
Using the DBMS_WARNING Package

Session 14: ORACLE-SUPPLIED PACKAGES

Overview of Oracle-Supplied Packages
Using the DBMS_SQL Package
Using Native Dynamic SQL
The DBMS_METADATA Package
The UTL_MAIL Package
The DBMS_APPLICATION_INFO Package
The DBMS_UTILITY Package
Scheduling Jobs using the DBMS_SCHEDULER Package

Pre-Requisite Courses

Oracle SQL

Follow-On Courses

Combination of the PL/SQL and PL/SQL Stored Program Units courses. Delegates may book this course as a one 5 Day course starting on a Monday or as two courses, the dates to be selected from the PL/SQL and PL/SQL Stored Program Units scheduled dates.

Home Let Us Call You Enquiry Make Booking Top