Oracle PL/SQL Programming

THE COURSE
The course will give the delegate an understanding of the Oracle PL/SQL programming language. The course covers control structures, exception handling, anonymous blocks, procedures, functions, triggers and packages. This course will explain and use the features of Oracle 11g SQL. It is also suitable for users of Oracle 10g, Oracle 9i and earlier versions. The course includes practical exercise to help delegates put the features covered into practise.

WHO SHOULD ATTEND
IT professionals who need to know about Oracle PL/SQL. The course is ideal for Oracle developers, database administrators and support technicians.

PRE-REQUISITES
Delegates should have a good knowledge of Oracle SQL. This can be gained from our Oracle SQL course or equivalent.

COURSE TOPICS
PL/SQL OVERVIEW

• Entering PL/SQL
• Introduction to PL/SQL

PROGRAMMING

• Introduction
• Local Variables
• Variable Assignment
• Program Constructs

EXCEPTION HANDLING

• Introduction
• User Defined
• Oracle Predefined
• Oracle Non-predefined
• Nested Blocks
• RAISE_APPLICATION_ERROR

CURSORS

• Introduction
• CURSOR
• OPEN
• FETCH
• CLOSE
• CURSOR FOR LOOP
• Data Modifications
• Implicit Cursors
• Cursors Variables

PROCEDURES & FUNCTIONS

• Introduction
• Advantages
• Stored Procedures
• Stored Functions
• Autonomous Transactions
• Local Procedures and Functions
• Compilation
• External Program Units
• Java Program Units
• PACKAGES
• Introduction
• Package Structure
• Package Creation

BUILT IN PACKAGES

• Introduction
• STANDARD and DBMS_STANDARD
• DBMS_ALERT
• DBMS_DDL
• DBMS_LOB
• DBMS_PIPE
• DBMS_SESSION
• DBMS_SQL
• EXECUTE IMMEDIATE
• DBMS_TRANSACTION
• DBMS_UTILITY
• UTL_FILE
• UTL_HTTP
• UTL_TCP

TRIGGERS

• Introduction
• Table Triggers
• Instead of Triggers
• System Triggers