Course Description
The objective of this Oracle 11g training course is to provide an introduction to develop database server-side PL/SQL program units within an Oracle database. The areas covered include understanding the basic form and structure of program units stored within the database, building and maintaining stored procedures, functions, packaged programs, and database triggers. Additional topics include taking advantage of advanced programming techniques such as cursor variables and cursor expressions. This Oracle 11g course will prepare students for the Oracle certification exams (OCP).
Instructor: Tim Miles
What am I going to get from this course?
- Prepare your environment
- Introducing Database Program Units
- Creating and Maintaining Stored Procedures & Functions
- Creating and Maintaining Packages
- Advanced Cursor Techniques
- Using System-Supplied Packages
- Creating and Maintaining Database Triggers
- Implementing System Event Triggers
Prerequisites and Target Audience
What will students need to know or do before starting this course?
Familiarity with database and programming principles
Who should take this course? Who should not?
- Application designers and developers
- Database administrators
- Business users and non-technical senior end users
Curriculum
Resource 1
Student Data Files
Module 2: Preparing Your Environment
Lecture 2
Installing Oracle XE
Lecture 3
SQL Developer Installation
Lecture 4
Workstation Setup
Module 3: Database Program Units
Lecture 5
Introducing the Database Program Unit
Module 4: Working with Stored Procedures and Functions
Lecture 6
Creating Stored Procedures and Functions
Lecture 7
Understanding the Parameter Specification
Lecture 8
Executing Procedures and Functions
Lecture 9
Calling Functions from within SQL
Lecture 10
Handling Compilation Errors
Module 5: Maintaining Stored Procedures and Functions
Lecture 11
Recompiling and Dropping Programs
Lecture 12
Data Dictionary Storage
Lecture 13
Managing Dependencies
Lecture 14
Tracking Dependencies
Lecture 15
Using the Dependency Tracking Utility
Module 6: Creating and Maintaining Packages
Lecture 16
What is a Package?
Lecture 17
Creating a Package
Lecture 18
Package Definition Information
Lecture 19
Advanced Programming Techniques
Lecture 20
Using Persistent Global Objects
Lecture 21
Including Initialization Logic
Lecture 22
Object Oriented Support within Packages
Lecture 23
Package Maintenance
Module 7: Advanced Cursor Techniques
Lecture 24
Learning to Use Cursor Variables
Lecture 25
Weak Cursor Definitions
Lecture 26
REFCURSOR Cursor Definition
Lecture 27
Using Cursor Expressions
Module 8: Using System Supplied Packages
Lecture 28
Using the DBMS OUTPUT () Packages
Lecture 29
Using the UTL_FILE() Package
Module 9: Creating Database Triggers
Lecture 30
Understanding the Database Trigger
Lecture 31
Statement-Level Triggers
Lecture 32
Row-Level Triggers
Lecture 33
Row Level Trigger Example 1
Lecture 34
Row Level Trigger Example 2
Lecture 35
Row Level Trigger Example 3
Lecture 36
Working with the INSTEAD OF Trigger
Lecture 37
Using Triggers within an Application
Module 10: Trigger Maintenance
Lecture 38
Using the CALL Statement
Lecture 39
Trigger Maintenance Tasks
Lecture 40
Working with Multiple Triggers for a Single Table
Lecture 41
Handling Mutating Table Issues
Lecture 42
Using the Compound Trigger
Module 11: Working with System Event Triggers
Lecture 43
What is a System Event Trigger?
Lecture 44
Defining Scope
Lecture 45
Identifying Available System Events
