Oracle Program with PL/SQL Course Overview The Oracle Program with PL/SQL training course introduces the delegate to Oracle's PL/SQL programming language and provides practical experience in writing stand-alone programs,developing database triggers and implementing stored 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,software development personnel and database support staff who need to write PL/SQL scripts and implement or maintain database triggers,program units and packages.
COURSE PREREQUISITES:
Requirements A working knowledge of Oracle SQL or SQL Developer is required along with practical experience in writing SQL statements. This knowledge can be obtained by attendance on the pre-requisite Oracle SQL course. Some previous programming experience will also prove advantageous.
COURSE CONTENT:
Oracle Program with PL/SQL Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: PL/SQL FUNDAMENTALS • What is PL/SQL? • Basic Elements • Variables and Constants • Data Types • Initializing Variables and Assigning Values • Using SQL Statements in Code • Generating Output to SQL or SQL Developer Session 2: PROGRAM LOGIC • IF THEN ELSE Statements • CASE Expressions • The Basic Loop Construct • WHILE and FOR Loops • Nested and Labelled Loops • The GOTO Statement • The CONTINUE Statement Session 3: USING CURSORS • What is a Cursor? • Implicit and Explicit Cursors • Cursor Operations • Declaring,Opening and Closing Cursors • Fetching Rows • Status Checking • Using Cursors FOR UPDATE • The Cursor FOR Loop • Parameterised Cursors Oracle Program with PL/SQL Training Course Course Contents – DAY 2 Session 4: EXCEPTIONS AND NESTED BLOCKS • The EXCEPTION Section • Types of Exception • Handling Named System-Raised Exceptions • Handling Unnamed System-Raised Exceptions • User-Declared Exceptions and Application Errors • When others then Null • Nested and Labelled Blocks • Propagation of Exceptions • Scope of Variables and Cursors Session 5: PL/SQL RECORDS AND INDEX-BY TABLES • Declaring Record Types • Handling PL/SQL Records • Nested Records • Declaring PL/SQL Index-By Tables or Associative Arrays • PL/SQL Table Built In Functions • Manipulating PL/SQL Tables or Associative Arrays Oracle Program with PL/SQL Training Course Course Contents – DAY 3 Session 6: 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 7: PROCEDURES • What is a Procedure? • The CREATE PROCEDURE Statement • Procedure Parameters • Invoking Procedures • Local Subprograms • Named Association Parameter Passing • Definer's Right and Invoker's Rights • Autonomous Transactions • Managing Procedures • Privileges Required for Procedures • Dictionary Information Concerning Procedures • The Call Statement Session 8: 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 Program with PL/SQL Training Course Course Contents – DAY 4 Session 9: 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 Features Session 10: 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 11: 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 • Open and Close Cursor Variables • Refcursor Datatype Session 12: MANAGING DEPENDENCIES • Dependent and Referenced Objects • Invalidation and Recompilation • Local and Remote Dependencies • Recompilation Considerations Oracle Program with PL/SQL Training Course Course Contents – DAY 5 Session 13: 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 14: 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 15: 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 PL/SQL scripts,triggers,stored program units and packages.
FOLLOW ON COURSES:
Further Learning Oracle 19c SQL Performance Tuning Oracle Database 19c Administration