Home  Courses  Custom dept.  Sales desk  Samples  Services  Help   

Phone 01636 893673
International
+44 1636 893673

Fax (0870) 051 7558
International
+44 870 051 7558

enquiries@firstalt.co.uk

Training Centre:
Harwell, Oxon.
Newark, Notts.

Administration office:
High Park Farm
Swinderby Road
Collingham
Nr. Newark
Notts NG23 7NZ.

OA2

Oracle PL/SQL Fundamentals

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.

It is suitable for ORACLE version 10g or 11g of the relational database and the principles learnt may be applied to earlier versions.
Full course notes are provided along with sample database files, example PL/SQL files and free software tools for use in accessing an ORACLE database.
Our courses are currently run using Micosoft Windows as the platform.

Select here for related courses
Select here for a list of all courses

OBJECTIVES OF THE COURSE

To take students with basic SQL knowledge a stage further, and to become proficient with PL/SQL.

COURSE CODE OA2

DURATION 3 days VENUE Newark Training Centre **
PRICE £875 plus VAT * BOOKING CONTACT Mick Hosegood email
EXPERIENCE LEVEL Intermediate TECHNICAL CONTACT Mick Hosegood email
*see note at bottom for special savings! for pricing for a course run especially for your organisation, please use our worksheet **Also available on your site for groups of four to ten.

COURSE DATES:

Mar 2010
Apr
May
Jun
Jul
Aug
Sep
Oct Nov Dec
Jan 2011




26-N
23-N


15-N


INTENSITY: About 40% of the course is practicals, and 60% lectures.

RESOURCE: Each student will have exclusive use of a PC for the duration of the course. Each student will be provided with a full set of training notes relating to the course.

PREREQUISITES: Basic SQL knowledge as taught on the Introduction to Oracle SQL*Plus  course or extensive experience of SQL with another relational database, such as MySQL, Informix or Sybase.

FOLLOW UPS: This course can be followed by the Oracle Database Administration course..

PRACTICALS / TAKEAWAYS: Each student leaves the course with his own set of training notes for the material covered.

SOFTWARE VERSIONS: This course is suitable for ORACLE version 10g or 11g

 

TOPICS COVERED ON THE COURSE

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?

Objectives, What is PL/SQL?, Why Use PL/SQL?, Block Structure, Displaying a Message, Sample Code, Setting SERVEROUTPUT, Update Example, Style Guide

Variables

Objectives, Variables, Datatypes, Setting Variables, Constants, Local and Global Variables, %Type Variables, Substitution Variables, Comments with &, Verify Option, && Variables, Define and Undefine

SELECT Statement

Objectives, SELECT Statement, Populating Variables, %Rowtype Variables, CHR Function, Self Study, PL/SQL Records, Example Declarations

Conditional Statement

Objectives, IF Statement, SELECT Statement, Self Study, Case Statement

Trapping Errors

Objectives, 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

Objectives, Loop Statement, While Statement, For Statement, Goto Statement and Labels

Cursors

Objectives, 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

Objectives, 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

Objectives, 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

Ref Cursors

Objectives, Cursor Variables, Defining REF CURSOR Types, Declaring Cursor Variables, Constrained and Unconstrained, Using Cursor Variables, Cursor Variable Examples

Dynamic SQL

Objectives, 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

Objectives, Using Text Files, UTL_FILE Package, Write/Append Example, Read Example, Trigger Example, DBMS_ALERT Packegs, DBMS_JOB Package

COLLECTIONS

Objectives, %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


 

SPECIAL SAVINGS FOR MULTIPLE AND PACKAGE BOOKINGS

Multiple Bookings
If you book more than one place on our courses on the same order, you are entitled to a 10% discount.

Package Bookings
Further discounts are available for certain packages of courses.

The complete package is available for £2400 for the 10 days of training (individually taken, the price would be £2850)

 
Fiercely inde dent
 

 

© 2010 First Alternative. All rights reserved.