---

Compile PL/SQL programs; update, insert, and delete data; define and execute testing procedures and functions; and gain an understanding of advanced PL/SQL features such as how to control data sharing and locking, debug and use error reporting procedures use Oracle-supplied packages, and develop database triggers, stored procedures, and functions for future reuse.

Highlights

• The PL/SQL environment, PL/SQL program structure, native compilation, update, insert and delete statements, error functions / debugging, defining, executing and testing functions, and procedures
• Creating package specifications and bodies, creating triggers of all types, using Oracle-Supplied packages, understanding advanced PL/SQL features

Course Outline

1. Review of Introduction to PL/SQL

• Features and benefits of PL/SQL, relationship of PL/SQL to SQL, PL/SQL development tools
• Native compilation
• PL/SQL system and session parameters, PL/SQL anonymous block structure
• Variable declarations, eclaring variable datatypes dynamically, declaring explicit cursors
• PL/SQL types and records, modifying database data (DML), transaction control statements, implicit cursor attributes, exception handling, creating procedures, functions, packages, and DML triggers

2. Advanced Cursors

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

3. Advanced Packages

• Initializing variables, module overloading, recursion, purity levels
• Using the "Persistent Stateto advantage
• One time only procedures, forward declarations, using persistent state, code encapsulation, constant and exception standardization

4. Advanced Triggers

• Trigger limitations, Mutating and Constraining Tables
• Using CALL and client triggers, DDL / SERVERERROR triggers, LOGON/LOGOFF, SUSPEND, STARTUP/SHUTDOWN triggers, schema versus database triggers
• Using alternative events and levels, INSTEAD OF triggers on views

5. PL/SQL Composite Datatypes and Collections

• PL/SQL records, associative arrays (INDEX BY), nested tables, VARRAYs
• Built-in type methods, arrays of composite types, using PL/SQL record variables, PL/SQL collections

6. Bulk-Bind Data Loading Using PL/SQL

• Defining bulk binds, BULK COLLECT / FORALL, Error handling with bulk binds - SAVE EXCEPTIONS

7. Using Oracle Supplied Packages

• DBMS_OUTPUT Package, UTL_FILE Package (file i/o)
• DBMS_ALERT Package, DBMS_PIPE Package , DBMS_JOB Package, DBMS_SCHEDULER Package, DBMS_STATS Package, DBMS_UTILITY Package
• UTL_SMTP Package, UTL_MAIL Package, DBMS_SQL Package

8. Writing Native Dynamic SQL

• EXECUTE IMMEDIATE, benefits Of NDS

9. PL/SQL Wrapper

• PL/SQL wrapper (source code encryption)

10. Understanding Dependencies

• Defining dependencies, local versus remote, viewing dependencies, effect of breaking dependency chain

11. Large Object Management in PL/SQLDifferences between LONG/LONG RAW and LOBs

• Creating and using BFILEs, creating tables with LOBs, LOBs and PL/SQL, DBMS_LOB capabilities, Temporary LOBs

12. Objects

• Basic objects, object inheritance

13. Java

• Java in PL/SQL, sample encapsulation

Space is limited. Register today to save your space!