Oracle 9i PL/SQL Foundations Professional Training Solutions Ltd

See all Oracle Courses Start a new search

Oracle 9i PL/SQL Foundations

Professional Training Solutions Ltd

Price: TBA

Location: Westland Square Pearse St Dublin 2 Ireland
Duration: 5 Days
Call: +971 4 5490356

Course details

This course will cover the Oracle 9i PL/SQL programming language. You will be able to control data sharing and locking, develop an understanding of multi-user and concurrent transactions and develop triggers, procedures, function and packages.

Prerequisites:

Oracle 9i Foundation: SQL Basics & SQL *Plus or equivalent experience.

Introduction to PL/SQL:

History of PL/SQL
Features and benefits of PL/SQL
Relationship of PL/SQL to SQL
PL/SQL development tools
Native Compilation

PL/SQL Basics:

PL/SQL anonymous block structure, Lexical units, Variable declarations, types, and Records
SQL *Plus development environment
Displaying messages with DBMS_OUTPUT
Object naming rules, and PL/SQL stype guide and coding conventions

Working with Database Data:

Selecting single rows
Declaring variable datatypes dynamically
Modifying database data (DML)
Transaction control statements

Selecting Multiple Rows Using Cursors:

Declaring explicit cursors
Implicit cursor attributes
Using the cursor FOR LOOP

Exception Handling:

Writing an exception handler section
Handling predefined exceptions, Controlling exception processing - exception propagation
Using RAISE_APPLICATOIN_ERROR
Preventing unhandled exceptions
Exception propagations
Using PRAGMA EXCEPTION_INIT

Advanced Cursors:

Cursor parameters
Taking advantage of a weak cursor variable
OPEN FOR, FETCH and CLOSE
Using the FOR UPDATE clause
Using PL/SQL collections and nested collections

Introduction to Procedures and Functions:

Creating stored PL/SQL objects, procedures, functions

Creating Packages:

Creating package specifications and bodies

Creating DML Triggers:

Triggering events, Trigger behavior
Correlation identifiers, Multi-statement triggers
Trigger firing behavior, Enabling/Disabling triggers

Advanced Packages:

Initialising variables, Module Overloading, Recursion, Purity levels, Using the "Persistent State" to advantage, and One Time Only Procedures

Advanced Triggers:

Triggerlimitations, Mutating and Constraining Tables
Using CALL, and client triggers
DDL Triggers
Using SERVERERROR event
Schema vs. Database triggers
Using alternative events and levels
INSTEAD OF triggers on views

PL/SQL Composite Datatypes and Collections:

PL/SQL records, PL/SQL associative arrays, and arrays of records
Using PL/SQL record variables
PL/SQL collections

Bulk-Bind Data Loading Using PL/SQL:

Defining bulk binds
Error handling with bulk binds

Using Oracle Supplied Packages:

DBMS_OUTPUT package
UTL_FILE package (file i/o)
DBMS_ALERT Package
DBMS_PIPE Package DBMS_JOB Package
DBMS_STATS Package
DBMS_UTILITY Package
UTL_SMTP Package
UTL_SMTP Package
DBMS_SQL Package

Writing Native Dynamic SQL:

PL/SQL Wrapper:

PL/SQL wrapper (source code encryption)
Let`s wrap

Understanding Dependencies:

Viewing dependencies
Effect of breaking dependency chain

Large Object Management in PL/SQL:

Differences between LONG/LONG RAW and LOBs
Creating and using BFILEs, and tables with LOBs
LOBs and PL/SQL
DBMS_LOB capabilities
Temporary LOBs

Objects:

Basic Objects, Object Inheritance

Updated on 08 November, 2015

Course Location

Show map Location Westland Square Pearse St Dublin 2 Ireland

About Professional Training Solutions Ltd

Professional Training is a group of experienced specialists providing specific training and consultancy solutions, within Management Skills, I.T. systems, Human Restheirce Development, Credit Management, Sales, Finance and Engineering.

they believe in using a combination of tailored, flexible approaches and real-life business knowledge to develop and provide techniques and solutions for immediate implementation.

their recent projects include supporting major initiatives for clients in utilities, telecoms, manufacturing and software, covering:

restructuring for competitiveness

productivity improvements through updating work processes to match new technology

managing outstheircing

strategic financial planning and engineering

their methodology is to:

Work closely with clients to understand and help them prioritise their needs

Design and deliver the best mix of training activities and consultancy to establish the competencies required

Provide ongoing coaching to sustain the improved practices and processes

they create relevant, targeted, enjoyable, and interactive, training sessions which are designed to improve the retention of information by participants and create a clear link from the training to the workplace. This creates a planned, visible improvement in key business indicators such as productivity, motivation, loyalty, employee retention, and ultimately, profit.

their collaborative approach builds long-term relationships with clients. Together, they can deal effectively with the inevitable challenges all businesses face.

See all Professional Training Solutions Ltd courses

View more Virtual classroom training for Oracle online courses See all Oracle Courses

See all current SQL Server and Oracle jobs in UAE