Oracle PL/SQL Stored Program Units
- Course Code OSPU
- Duration 3 days
Course Delivery
Additional Payment Options
-
GTC 22 inc. VAT
GTC, Global Knowledge Training Credit, please contact Global Knowledge for more details
Jump to:
Course Delivery
This course is available in the following formats:
-
Company Event
Event at company
-
Public Classroom
Traditional Classroom Learning
-
Virtual Learning
Learning that is virtual
Request this course in a different delivery format.
Course Overview
TopOracle 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.
Exercises and examples are used throughout the course to give practical hands-on experience with the techniques covered.
Versions supported 12cR2,18c and 19c.
Course Schedule
Top-
- Delivery Format: Virtual Learning
- Date: 22-24 May, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 24-26 July, 2024
- Location: Virtual
-
- Delivery Format: Virtual Learning
- Date: 18-20 September, 2024
- Location: Virtual
Target Audience
TopWho 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 Objectives
TopCourse Objectives
To provide the skills needed to develop,write and maintain triggers,stored program units and packages.
Course Content
TopOracle 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 Prerequisites
TopRequirements
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.
Follow on Courses
TopFurther Learning
- Oracle Advanced PL/SQL
- Oracle Database 19c Administration
- STA_OSPU
- Oracle PL/SQL Stored Program Units
- Database Management & Development
- STA_OSPU | Oracle PL/SQL Stored Program Units | Training Course | Oracle.
- Oracle