Oracle PL/SQL Programming Part 2

Audience

This course is for programmers and support staff who wish to gain a comprehensive understanding of how the Oracle Procedural Language / Structured Query Language (PL/SQL) can be used in order to utilise Packages, Triggers and Native Dynamic SQL (NDS).

Prerequisites

Delegates should be computer literate with a good knowledge of Oracle SQL and the standard editor available on their target hardware platform. Understanding of Oracle PL/SQL programming concepts and techniques is also required. Previous attendance of our course Oracle PL/SQL Programming Part 1 is highly recommended.

Duration

2 days. Hands on.

Course Objectives

This course is highly interactive and consists of theory coupled with plenty of hands on practical sessions. It provides an in-depth look at PL/SQL in order to create and use user defined packages, as well as be able to use Oracle supplied packages. The course also covers creation of Database Triggers and use of the Native Dynamic SQL (NDS). After completing the course, delegates will be able to:

• Write user defined Packages and Triggers.
• Produce PL/SQL code that uses Oracle supplied Packages.
• Create re-usable code with NDS.

Course Content

Review of Packages
Package structure
Package creation

Built-In Packages
STANDARD and DBMS_STANDARD
DBMS_ALERT
DBMS_DDL
DBMS_JOB
DBMS_LOCK
DBMS_PIPE
DBMS_SESSION
DBMS_TRANSACTION
DBMS_UTILITY
DBMS_STATS
DBMS_RANDOM
UTL_FILE
ORACLE Packages
QUIZ and practical session on built-in packages

Native Dynamic SQL (NDS)
DBMS_SQL Vs. NDS
NDS Commands
EXECUTE IMMEDIATE
OPEN FOR (CURSOR VARIABLES)
Using BIND variables with NDS
QUIZ and practical sessionon Native Dynamic SQL (NDS)

DDL and Database Level Triggers
DDL Triggers
Database level triggers
A LOGON trigger
Quiz and practical session on triggers

Collections
Declaring nested tables and VARRAYs
Initialising collections
Manipulating elements
Collection methods
Bulk BINDS
QUIZ and practical session on collections

This course is suitable for all recent versions of Oracle, up to and including 11G.