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) is used. PREREQUISITES:   Delegates should be computer literate with a good knowledge of Oracle SQL and SQL*Plus and the standard editor available on their target hardware platform. Attendance of our course Oracle SQL Part 1 is highly recommended. DURATION:   3 days. Hands on. 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 the SQL*Plus environment in order to write Stored Procedures and Stored Functions. This course is suitable for Oracle 9i & 10G users. On completion, delegates will be able to: - Write PL/SQL Control Structures. - Edit, store & recall PL/SQL blocks. COURSE CONTENT:   PL/SQL Overview SQL*Plus Client/server architecture Internet/intranet architecture ISO ( Incorporating ANSI and BSI) standards Quiz Programming Local variables Database derived variables User defined records User defined variables Variable assignment If ... Else Oracle9i/10G PL/SQL case statements Oracle9i/10G PL/SQL case expressions Oracle9i/10G searched case statements General statement considerations For loop While (repeat until) loop Goto and labels Quiz and practical session on programming Exception Handling User defined Oracle predefined Oracle defined Sqlcode and sqlerrm Nested blocks Quiz practical session on exception handling Cursors Introduction Cursor Open Fetch Close Cursor for loop Data modifications Implicit cursors Cursor variables Quiz and practical session on cursors Stored Procedures and Stored Functions Advantages Stored procedures Input parameters Output parameters I/O parameters Stored functions Local procedures and functions Compilation User defined errors External programs Quiz and practical session on stored procedures Database DML Triggers Trigger roles Trigger restrictions Trigger execution Trigger creation Old and new data & update trigger A delete trigger An insert trigger Multiple event triggers An instead of trigger Quiz and practical session on database DML triggers PI07/01