Course Overview
In this Oracle Database 12c: PL/SQL II – Intermediate: Develop Program Units course, students will apply the basics of the Oracle PL/SQL database programming language and its syntax and employ the language in the development of database applications. In particular, the focus will be on the use of database-resident stored program units such as procedures, functions, packages, and database triggers. New features introduced with the Oracle 12c release of the database are also explained and demonstrated.
This course considers subjects applicable to certification as both an Oracle PL/SQL Developer Certified Associate (OCA) and an Oracle Advanced PL/SQL Developer Certified Professional (OCP). The topics considered are included within ‘Exam 1Z0-144: Program With PL/SQL.’
What you’ll learn
Upon completion of the Oracle Database 12c: PL/SQL II – Intermediate: Develop Program Units course, students will be able to:
- Understand how database-resident program units can be used as part of the overall database application architecture
- Apply knowledge of database-resident program units to the development of PL/SQL packages
- Package PL/SQL program units
- Use advanced database programming capabilities
- Use and understand database triggers in advanced database application design
Requirements
- DBOR-952 Oracle Database 12c: PL-SQL I - Introduction, or equivalent knowledge
Target audiences
- Application designers and database developers
- Database administrators
- Web server administrators
Curriculum
- 1 Section
- 13 Lessons
- 3 Days
- Topics13
- 1.1Introducing Database-Resident Program Units About Database-Resident Programs Physical Storage & Execution Types Of Stored Program Units Stored Program Unit Advantages Modular Design Principles
- 1.2Creating Stored Procedures & Functions Stored Procedures & Functions CREATE Procedure / CREATE Function Creating Procedures & Functions RAISE_SALARY() Procedure SALARY_VALID() Function The Parameter Specification DEFAULT Clause SYSTEM & OBJECT Privileges Using The Development Tools
- 1.3Executing Stored Procedures & Functions Calling Procedures & Functions Unit Testing With EXECUTE ANONYMOUS BLOCK Unit Testing Specifying A Parameter Notation SQL Worksheet Unit Testing Calling Functions From SQL
- 1.4Maintaining Stored Program Units Recompiling Programs Mass Recompilation Using UTL_RECOMP() Dropping Procedures & Functions DROP Procedure / Function Data Dictionary Metadata Using USER_OBJECTS Using USER_SOURCE Using USER_ERRORS Using USER_OBJECT_SIZE Using USER_DEPENDENCIES
- 1.5Managing Dependencies DEPENDENCY INTERNALS TRACKING DEPENDENCIES The DEPENDENCY TRACKING Utility SQL Developer Dependency Info Dependency Strategy Checklists
- 1.6Creating & Maintaining Packages About Packages Creating Packages Maintaining Packages Performance Considerations
- 1.7Advanced Package Capabilities Definer & Invoker Rights White Lists & Accessible By Persistent Global Objects Defining Initialization Logic Object Orientation Support
- 1.8Advanced Cursor Techniques USING CURSOR VARIABLES Using SYS_REFCURSOR Using CURSOR Expressions
- 1.9Using System-Supplied Packages DBMS_OUTPUT() UTL_FILE() FOPEN() Example
- 1.10Database Trigger Concepts About Database Triggers DML EVENT TRIGGER Sub-Types DATABASE TRIGGER Scenario TRIGGER Execution Mechanisms TRIGGERS Within SQL Worksheet
- 1.11Creating Database Triggers STATEMENT-LEVEL TRIGGERS Using RAISE_APPLICATION_ERROR() ROW-LEVEL TRIGGERS EXAMPLES OF TRIGGERS EMPLOYEE_SALARY_CHECK Example EMPLOYEE_JOURNAL Example BUDGET_EVENT Example INSTEAD OF TRIGGERS Triggers Within An Application
- 1.12Maintaining Database Triggers CALL Syntax Trigger Maintenance Tasks SHOW ERRORS Trigger DROP Trigger ALTER Trigger Multiple Triggers For A Table Handling Mutating Table Issues
- 1.13Implementing System Event Triggers What Are System Event Triggers? Defining The Scope Available System Events System Event Attributes
Instructor
