ORA10g101 - Oracle Database 10g: Program with PL/SQL

Course ORA10g101

Table of Contents

Introduction
Audience
At Course Completion
Prerequisites
Certified Professional Exams
Student Materials
Course Outline

Introduction

The Oracle PL/SQL language provides for programming logic features not contained within SQL. The grammar, syntax and benefits of using the PL/SQL language within an Oracle database environment are considered.

Return to Top

Audience

Target audience for this course is all Oracle professionals. Among the specific groups for whom this course will be helpful are:

• Application designers and developers.
• Database administrators.

Return to Top

At Course Completion

Part I Objectives: to provide an introduction to the PL/SQL database programming language. Major subject areas to be explored are:

• Understand the environment and context in which PL/SQL operates.
• Consider the advantages and benefits of PL/SQL within a database environment.
• Declaring program variables and complex data types.
• Developing logic within PL/SQL program blocks.
• Fetching data from the database into program variables.
• Returning program output to users.
• Handling program exceptions.
• Using explicit and implicit database cursors.
• Using the advanced features of nested blocks and subprograms.
• How to take advantage of new features such as regular expression support and handling of string literals.

Part II Objectives: to equip students to develop database server-side PL/SQL program units within an Oracle database. Major subject areas to be explored and secondary objectives are:

• Understanding application partitioning within a client/server or multi-tiered web-based architecture.
• Understanding the basic form and structure of program units stored within the database.
• Building and maintaining database stored program units.
• Encapsulating stored units within packages and taking advantage of accompanying advanced programming techniques.
• Building and maintaining DML-event and system-event database triggers.
• Discuss the storage and execution model for database programs and how one can write efficient programs to maximize performance.
• Using system-supplied packages to extend the power of your SQL statements and PL/SQL applications.

Return to Top

Prerequisites

Before attending this course, students must have:

• Completed ora10g008, Oracle Database 10g: Introduction to SQL I, or equivalent.
• Completed ora10g009, Oracle Database 10g: Introduction to SQL II or equivalent.

Suggested Next Course

• ora10g204, Oracle Database 10g: New Features For Administrators
• ora10g401, Oracle 10g Forms I: Build Internet Applications

Return to Top

Certified Professional Exams

This course will help the student prepare for the following Certified Professional exams:

• Exam 1Z0-147 Oracle9i: Program with PL/SQL

Following the training sessions, Oracle Corporation recommends six-months production experience with the technology prior to taking the certification examinations. While this is good practice, it may not be necessary for all individuals.

Return to Top

Student Materials

The student kit includes a comprehensive workbook and other necessary materials for this class.

Return to Top

Course Outline

Course Outline - Part I

LANGUAGE FEATURES

• UNDERSTANDING PL/SQL
• STRUCTURE OF A PL/SQL PROGRAM BLOCK
• PL/SQL FROM INTERACTIVE TOOLS
• GENERATING OUTPUT WITHIN SQL*PLUS

DECLARE CLAUSE

• ABOUT THE DECLARE CLAUSE
• DECLARE SIMPLE TYPES
• COMPLEX TYPES

BEGIN CLAUSE

• ABOUT THE BEGIN CLAUSE
• PERFORMING DATA MANIPULATION
• LOGIC CONTROL & BRANCHING

EXCEPTION CLAUSE

• ABOUT THE EXCEPTION CLAUSE
• ISOLATING THE SPECIFIC EXCEPTION

EXPLICIT CURSORS

• ABOUT EXPLICIT CURSORS
• ADVANCED CURSOR TECHNIQUES

ADVANCED PROGRAMMING: NESTED BLOCKS

• ABOUT THE ADVANCED FEATURES
• USING NESTED BLOCKS

ADVANCED PROGRAMMING: DECLARED SUBPROGRAMS

Course Outline - Part II

INTRODUCING DATABASE PROGRAM UNITS

• TYPES OF PL/SQL PROGRAM UNITS
• TYPES OF STORED PROGRAM UNITS
• ADVANTAGES OF USING STORED PROGRAM UNITS
• DEVELOPMENT TOOLS

CREATING STORED PROCEDURES & FUNCTIONS

• CREATING PROCEDURES & FUNCTIONS
• EXECUTING PROCEDURES & FUNCTIONS
• HANDLING COMPILATION ERRORS

MAINTAINING STORED PROCEDURES & FUNCTIONS

• ABOUT APPLICATION MAINTENANCE
• RECOMPILING & DROPPING PROGRAMS
• DATA DICTIONARY STORAGE
• MANAGING DEPENDENCIES

CREATING & MAINTAINING PACKAGES

• ABOUT PACKAGES
• CREATING PACKAGES
• ADVANCED PROGRAMMING TECHNIQUES
• MAINTAINING PACKAGES

ADVANCED CURSOR TECHNIQUES

• USING CURSOR VARIABLES

USING SYSTEM-SUPPLIED PACKAGES

• DBMS_OUTPUT()
• UTL_FILE()

CREATING DATABASE TRIGGERS

• ABOUT DATABASE TRIGGERS
• STATEMENT-LEVEL TRIGGERS
• ROW-LEVEL TRIGGERS
• EXAMPLES OF TRIGGERS
• INSTEAD OF TRIGGERS
• EMPLOYING TRIGGERS WITH AN APPLICATION

MAINTAINING DATABASE TRIGGERS

• ABOUT TRIGGER MAINTENANCE
• CALL SYNTAX
• MULTIPLE TRIGGERS PER EVENT
• TRIGGER MAINTENANCE TASKS
• HANDLING MUTATING TABLE ISSUES

IMPLEMENTING SYSTEM EVENT TRIGGERS

• WHAT ARE SYSTEM EVENT TRIGGERS?
• DEFINING THE SCOPE
• AVAILABLE SYSTEM EVENTS
• SYSTEM EVENT ATTRIBUTES

Return to Top