The target audience for this course is all Oracle professionals. Among the specific groups for whom
this course will be helpful are:
- Application designers and database developers
- Database administrators
- Web server administrators
Oracle Training Prerequisites
Either of the course Oracle Database 12c: SQL Fundamentals (Levels I & II) or Oracle
Database 12c: SQL Complete Library (Levels I, II, & III) are recommended prerequisites for this
course.
Oracle Training Certification
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".
Oracle Training Objectives
This course explains how database-resident program units can be used as part of the overall
database application architecture and the benefits realized by doing so. It then builds upon one's
knowledge of database-resident program units and applies these to the development of PL/SQL
packages. In a production environment most PL/SQL program units should be packaged, and these
advanced database programming capabilities along with the benefits of using these are discussed. It
concludes with extensive demonstrations on how a particular type of database-resident program unit
known as a database trigger can be used as part of an advanced database application design.
Course duration
3 Days
Oracle Training Course outline
INTRODUCING DATABASE-RESIDENT PROGRAM UNITS
- ABOUT DATABASE-RESIDENT PROGRAMS
- PHYSICAL STORAGE & EXECUTION
- TYPES OF STORED PROGRAM UNITS
- STORED PROGRAM UNIT ADVANTAGES
- MODULAR DESIGN PRINCIPLES
CREATING 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
EXECUTING 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
MAINTAINING 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
MANAGING DEPENDENCIES
- DEPENDENCY INTERNALS
- TRACKING DEPENDENCIES
- THE DEPENDENCY TRACKING UTILITY
- SQL DEVELOPER DEPENDENCY INFO
- DEPENDENCY STRATEGY CHECKLISTS
CREATING & MAINTAINING PACKAGES
- ABOUT PACKAGES
- CREATING PACKAGES
- MAINTAINING PACKAGES
- PERFORMANCE CONSIDERATIONS
ADVANCED PACKAGE CAPABILITIES
- DEFINER & INVOKER RIGHTS
- WHITE LISTS & ACCESSIBLE BY
- PERSISTENT GLOBAL OBJECTS
- DEFINING INITIALIZATION LOGIC
- OBJECT ORIENTATION SUPPORT
ADVANCED CURSOR TECHNIQUES
- USING CURSOR VARIABLES
- Using SYS_REFCURSOR
- USING CURSOR EXPRESSIONS
USING SYSTEM-SUPPLIED PACKAGES
- DBMS_OUTPUT()
- UTL_FILE()
- FOPEN() Example
DATABASE TRIGGER CONCEPTS
- ABOUT DATABASE TRIGGERS
- DML EVENT TRIGGER SUB-TYPES
- DATABASE TRIGGER SCENARIO
- TRIGGER EXECUTION MECHANISMS
- TRIGGERS WITHIN SQL WORKSHEET
CREATING 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
MAINTAINING DATABASE TRIGGERS
- CALL SYNTAX
- TRIGGER MAINTENANCE TASKS
- SHOW ERRORS TRIGGER
- DROP TRIGGER
- ALTER TRIGGER
- MULTIPLE TRIGGERS FOR A TABLE
- HANDLING MUTATING TABLE ISSUES
IMPLEMENTING SYSTEM EVENT TRIGGERS
- WHAT ARE SYSTEM EVENT TRIGGERS?
- DEFINING THE SCOPE
- AVAILABLE SYSTEM EVENTS
- SYSTEM EVENT ATTRIBUTES
