Oracle Advanced PL/SQL Course Overview This course,designed for Oracle database administrators and software development personnel who need to gain practical experience of the advanced features of PL/SQL such as program design,packages,cursors,large objects and collections. It also introduces some of the Oracle-supplied packages. Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered. Versions supported 12cR2,18c,19c.
COURSE OBJECTIVE:
Course Objectives By the end of this Oracle Advanced PL/SQL course delegates will be able to effectively tune PL/SQL code; create subtypes; create and use collections; execute external C programs and Java programs from PL/SQL; use fine-grained access control; use LOB data types and the DBMS_LOB package; use SecureFile LOBs; effectively design cursors; improve memory usage with PL/SQL result caching and SQL result sets and apply coding standards to avoid SQL injection attacks.
TARGET AUDIENCE:
Who will the Course Benefit? Software development personnel and Oracle database administrators who need to take advantage of the advanced features of PL/SQL in Oracle Database. Practical experience of Oracle SQL or SQL Developer and a good working knowledge of the fundamentals of the PL/SQL programming language are required. Versions supported 12cR2,18c and 19c.
COURSE PREREQUISITES:
Requirements Practical experience of Oracle SQL and the PL/SQL programming language is required. This can be gained by attendance on the pre-requisite courses. Familiarity with the Oracle Database is also required.
COURSE CONTENT:
Oracle Advanced PL/SQL Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: DESIGNING PL/SQL CODE FOR PERFORMANCE • Oracle Predefined Data Types • Create Subtypes Based on Existing Types for an Application • Guidelines for Cursor Design • Use Cursor Variables • Pass Cursor Variables as Program Parameters • Compare Cursor Variables to Static Cursors Session 2: USE COLLECTIONS • Overview of Collections • Associative Arrays • Nested Tables • Varrays • Select the Appropriate Collection Type • Create PL/SQL Programs that use Collections • Use Collections Effectively Session 3: ADVANCED INTERFACE METHODS • The Benefits of External Routines • External Routine Components • Define the Library to Oracle • Register the External Function • Networking Files • The Shared Library • Call an Operating System Supplied DLL from PL/SQL • Create Secure External Procedures • Call C Program from PL/SQL • Call Java from PL/SQL Session 4: VPD AND FINE-GRAINED ACCESS CONTROL • Overview of Fine-Grained Access Control • The Features of Fine-Grained Access Control • Overview of an Application Context • Create an Application Context • Set an Application Context • Describe the Package DBMS_RLS • Implement a Policy • Query the Data Dictionary for Fine-Grained Access Control Information Oracle Advanced PL/SQL Training Course Course Contents – DAY 2 Session 5: MANIPULATE LARGE OBJECTS • Overview of a LOB Object • Manage Internal LOBs • Character Large Objects • Binary Large Objects • External Large Objects – BFILEs • Create and Use the DIRECTORY Object to Access and Use BFILEs • The DBMS_LOB Package • Delete LOBs • Create a Temporary LOB using the Package DBMS_LOB Session 6: ADMINISTER SECUREFILE LOBS • Overview of SecureFile LOBs • Configure the Environment for SecureFile LOBs • Store Documents using SecureFile LOBs • Convert BasicFile LOBS to SecureFile LOBs • Evaluate the Performance of SecureFile LOBs • Establish Space Utilization of a LOB File • Set up Encryption • Enable Compression and Deduplication Session 7: TUNING PL/SQL CODE FOR PERFORMANCE • Understand the PL/SQL Compiler • Influence the Compiler • Tuning PL/SQL Programs • The DBMS_UTILITY.GET_TIME Function • Tuning SQL • Reduce the Number of SQL Calls • PL/SQL Tuning • The FETCH FIRST Clause and Bulk Collections • The DBMS_EXECUTE_PARALLEL Package • PL/SQL Functions that Run Faster in SQL • PL/SQL Native Compilation Session 8: USE SQL AND PL/SQL CACHING TO IMPROVE PERFORMANCE • The Importance of the Shared Pool • Library Cache • The Data Dictionary Cache • Pinning Code in the Shared Pool • SQL and PL/SQL Caching • PL/SQL Function Cache • Invoker's Right Function Result Caching • The Invalidation of Cache Results • The DBMS_RESULTS_CACHE Package Oracle Advanced PL/SQL Training Course Course Contents – DAY 3 Session 9: ANALYZE PL/SQL CODE • Use Data Dictionary Views to View Coding Information • Use Supplied Packages to View Coding Information • Use the DBMS_METADATA Package to View Metadata as XML • Use the DBMS_METADATA Package to Create DDL that can be used to Re-create Objects • Determine Identifier Types and Usages with PL/Scope • The UTL_CALL_STACK Package • Compile Time Warnings • Conditional Compilation Session 10: PROFILE AND TRACE PL/SQL CODE • Trace PL/SQL Program Execution • Profile PL/SQL Applications • Access the Trace Data • DBMS_PROFILER • The Hierarchical Profiler • PL/SQL Predefined Enquiry Directives Session 11: SECURE CODE • Grant Roles to Standalone Programs and PL/SQL Packages • BEQUEATH CURRENT_USER Views • Enable INHERENT PRIVILEGES and INHERIT ANY PRIVILEGES Privileges • White Lists using the ACCESSIBLE BY Clause Session 12: SAFEGUARD CODE • Overview of SQL Injections • Detecting Attacks • Review of Dynamic SQL • The Attack Surface • Typical SQL Attacks • Reduce Attack Surfaces • Use the DBMS_ASSERT Package • Design Immune Code • Test Code for SQL Injection Flaws Session 13: MISCELLANEOUS NEW 12c NEW FEATURES • Edition Based Redefinition • Invisible Columns and the %ROWTYPE Attribute
FOLLOW ON COURSES:
Further Learning Oracle 19c SQL Performance Tuning Oracle Database 19c Administration