Oracle PL/SQL Stored Program Units Course Overview The Oracle PL/SQL Stored Program Units course provides practical experience in developing and writing triggers,functions,procedures and packages. It also introduces some of the Oracle-supplied packages. Similarly,the course is targeted to closely follow the official Oracle Database curriculum for certification. Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered. Versions supported 12cR2,18c and 19c.
TARGET AUDIENCE:
Who will the Course Benefit? Oracle database administrators and software development personnel who need to write new or maintain existing,PL/SQL triggers,program units and packages. Practical experience of Oracle SQL and of using SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language are required.
COURSE PREREQUISITES:
Requirements Practical experience of Oracle SQL*Plus or SQL Developer and a good working knowledge of the fundamentals of the Oracle PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses.
COURSE CONTENT:
Oracle PL/SQL Stored Program Units Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: TRIGGERS • DML Triggers • The CREATE TRIGGER Statement • Writing Trigger Code • INSTEAD OF Triggers • Calling Procedures from Triggers • Coding Restrictions • System Event and DDL Triggers • Attribute Functions • Compound Triggers • Create Trigger Follows Clause • Managing Triggers • Privileges Required for Triggers • Dictionary Information Concerning Triggers Session 2: PROCEDURES • What is a Procedure? • The CREATE PROCEDURE Statement • Procedure Parameters • Invoking Procedures • Local Subprograms • Named Association Parameter Passing • Definer's Rights and Invoker's Rights • Autonomous Transactions • Managing Procedures • Privileges Required for Procedures • Dictionary Information Concerning Procedures • The Call Statement Session 3: FUNCTIONS • What is a Function? • The CREATE FUNCTION Statement • Executing Functions • Invoker's Rights • Autonomous Transactions • DBMS_OUTPUT • Using Functions in SQL Statements • Deterministic and Parallel-Enabled Functions • Function Result Cache • Managing Functions • Privileges Required for Functions • Dictionary Information Concerning Functions Oracle PL/SQL Stored Program Units Training Course Course Contents – DAY 2 Session 4: PACKAGES • What is a Package? • Public and Private Components • Creating a Package • Example Package • Persistent States • One-time-only Procedures • Overloading • Purity Level Checking • Forward Declarations • Wrapping Packages • Managing Packages • Privileges Required for Packages • Dictionary Information Concerning Packages Session 5: USING PL/SQL RECORDS AND TABLES IN PACKAGES • Overview of PL/SQL Records,Index-by Tables and Associative Arrays • Using PL/SQL Records and Tables in Packages • Table Built-in Functions • The NOCOPY Hint • Bulk Collection • Bulk Binding DML Statements Session 6: CURSOR VARIABLES (REF CURSORS) • Declare Cursor Variables • Use Cursor Variables • Open and Close Cursor Variables • Fetch Rows • Cursor Variable Attributes • Pass Cursor Variables as Parameters • Refcursor Datatype Session 7: MANAGING DEPENDENCIES • Dependent and Referenced Objects • Invalidation and Recompilation • Local and Remote Dependencies • Recompilation Considerations Oracle PL/SQL Stored Program Units Training Course Course Contents – DAY 3 Session 8: NATIVE COMPILATION AND COMPILE-TIME WARNINGS • Introduction • Native Compilation • Automatic Recompilation • Automatic Program Sublining • WHEN OTHERS …THEN NULL • Data Dictionary Information • Compiler Warning Categories • Using the DBMS_WARNING Package Session 9: ORACLE-SUPPLIED PACKAGES • Overview of Oracle-Supplied Packages • Using the DBMS_SQL Package • Using Native Dynamic SQL • The DBMS_METADATA Package • The UTL_MAIL Package • The DBMS_APPLICATION_INFO Package • The DBMS_UTILITY Package • Scheduling Jobs using the DBMS_SCHEDULER Package Session 10: PL/SQL DESIGN CONSIDERATIONS • Invoker versus Definer™ Rights • Grant Roles to PL/SQL Packages and Programs • Programming Standards for Variables,Parameters and Constants • Standardise Constants with a Package • Standardise Exceptions with a Package • Write PL/SQL Code using Local Subprograms • Use NOCOPY Compiler Hint • Optimise Code with the PARALLEL ENABLE hint • Use the AUTONOMOUS TRANSACTION Pragma
COURSE OBJECTIVE:
Course Objectives To provide the skills needed to develop,write and maintain triggers,stored program units and packages.
FOLLOW ON COURSES:
Further Learning Oracle Advanced PL/SQL Oracle Database 19c Administration