• Home
• Course Catalogue
• Certifications
• Onsite Training
• Scheduled Training
• E-Learning
• Managed Services
• Consulting
• Company
• Special Offers
• News
• Links
• Enquiry And Feedback Form

Training Course Outline

IT Vendor Courses   Oracle DBA Training Courses

Oracle 9i: Program with PL/SQL

View course dates

Course Duration: 5 days

Course Code: OC9IPPLS

Course Description:

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create procedures, functions, packages, and database triggers.

 

Students use iSQL*Plus to develop these program units. Students also learn to manage PL/SQL program units and database triggers, to manage dependencies, to manipulate large objects, and to use some of the Oracle-supplied packages.

This course counts towards the Hands-on course requirement for the Oracle 9i Database Administrator and Oracle Database 10g Administrator Certification. Only instructor-led inclass or instructor-led online formats of this course will meet the Certification Hands-on Requirement. Self Study CD-Rom and Knowledge Center courses DO NOT meet the Hands-on Requirement

 

Audience:
Database Administrators
System Analysts

 

Course Objectives:
- Create, execute, and maintain procedures, functions, packages, and triggers
- Manage dependencies
- Manipulate large objects
- Conditionally control code flow (loops, control structures, and explicit cursors)
- Describe Oracle-supplied packages
- Handle runtime errors
- Manage program constructs
- Describe the features and syntax of PL/SQL

Prerequisites:

Working with iSQL*Plus
Introduction to Oracle9i: SQL

Prerequisite courses:

Introduction to Oracle 9i: SQL

Relevant Certifications:

Oracle9i Database Administrator

Oracle Database 10g Administrator

This course includes the following modules:

Introduction

• Describing PL/SQL
• Describing the Use of PL/SQL for the Developer and the Database Administrator
• Explaining the Benefits of PL/SQL
• PL/SQL program constructs
• PL/SQL anonymous block structure
• Subprogram block structure
• Course objectives and overview

Declaring Variables

• Recognizing the Basic PL/SQL Block and Its Sections
• Describing the Significance of Variables in PL/SQL
• Distinguishing Between PL/SQL and Non-PL/SQL Variables
• Declaring Variables and Constants
• Executing a PL/SQL Block

Writing Executable Statements

• Recognizing the Significance of the Executable Section
• Writing Statements Within the Executable Section
• Describing the Rules of Nested Blocks
• Executing and Testing a PL/SQL Block
• Using Coding Conventions

Interacting with the Oracle Server

• Writing a Successful SELECT Statement in PL/SQL
• Declaring the Data type and Size of a PL/SQL Variable Dynamically
• Writing Data Manipulation Language (DML) Statements in PL/SQL
• Controlling Transactions in PL/SQL
• Determining the Outcome of SQL DML Statements

Writing Control Structures

• Identifying the Uses and Types of Control Structures
• Constructing an IF Statement
• Constructing and Identifying Different Loop Statements
• Controlling Block Flow Using Nested Loops and Labels
• Using Logic Tables

Working with Composite Data types

• Creating User-Defined PL/SQL Records
• Creating a PL/SQL Table
• Creating a PL/SQL Table of Records
• Differentiating Among Records
• Tables, and Tables of Records

Writing Explicit Cursors

• Using a PL/SQL Record Variable
• Distinguishing Between the Implicit and Explicit Cursor
• Writing a Cursor FOR Loop

Advanced Explicit Cursor Concepts

• Writing a Cursor that Uses Parameters
• Determining When a FOR UPDATE Clause in a Cursor Is Required
• Using a PL/SQL Table Variable
• Using a PL/SQL Table of Records

Handling Exceptions

• Defining PL/SQL Exceptions
• Recognizing Unhandled Exceptions
• Listing and Using Different Types of PL/SQL Exception Handlers
• Trapping Unanticipated Errors
• Describing the Effect of Exception Propagation in Nested Blocks
• Customizing PL/SQL Exception Messages

Creating Procedures

• Describe the uses of procedures
• Create procedures
• Create procedures with arguments
• Invoke a procedure
• Remove a procedure

 

To make an enquiry for this course to be run ONSITE at your location, click here or call Sales on +44 (0) 20 7620 0033.

Perpetual Solutions are a global provider of onsite training solutions throughout London, UK and the rest of the world. For scheduled training our UK partner network provides an unrivalled choice of courses.

Save as PDF

Print this course