Skip Ribbon Commands
Skip to main content
info@gsatrain.com
(855) 289-4728
Skip Navigation LinksGSAtrain > Course Detail

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

Price:

Duration: 5 Days

Audience:

Level:

Technology:

Delivery Method:

Software Assurance Value:

Microsoft CPE:

Course Information

Course Description

​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.

Course Objectives

​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.

Course 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.

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

Course 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
Course Schedule
This course is not scheduled yet.

Main Navigation

Recent Blog Posts
Testimonials
Great instructor, I learned much more than I expected - also great selection of snacks and drinks.
Monica
Copyright © 2015 GSAtrain LLC Powered By: SharePoint