PDF Print E-mail
Oracle 10g PL/SQL Training

Oracle10g PL/SQL Programming Workshop

The course has been prepared by Oracle Partner company Skillbuilders (U.S.) (www.skillbuilders.com)
Duration of the course: 3+2 days (3 days of theory and 2 days of practice)
Dates of the first training: March 30, 2009 - April 03,2009
Trainer: Igor Duca, OCP (http://duca.at)
Price: 1375 US Dollars w/o VAT per student, including books and lunch
Method of Payment - prepayment in MDL - wire transfer - exchange rate to be fixed by BNM for the day of transfer
Location of the course : Training room of UniORA: Alba Iulia 190/1 of 34, 35 (New KVINT building: across the street - Orange Moldova)
Language - Russian, terminology - English

Class closed.

Especially for developers and analysts with strong programming skills, this class covers PL/SQL fundamentals and how to create database-intensive PL/SQL applications by writing efficient, scalable programs. Introduces PL/SQL including structure, concepts and language fundamentals; using PL/SQL and SQL including Cursors and Bulk Processing; Stored Procedures; Functions; Error handling; Packages; DML Triggers; Trace and Tune; file input/output using UTL_FILE and sending email with UTL_MAIL.

This class will teach you how to write efficient and scalable PL/SQL programs to create database-intensive PL/SQL applications. You will learn the critical and fundamental aspects of PL/SQL language.

Hands-on workshops constitute approximately 50% of the class. Overviews of relational

concepts and Oracle architecture are also provided. As with all of our courses, this class is highly customizable to your specific training requirements.

Audience: Developers and Analysts. Database Administrators who know a programming language will also benefit.

Prerequisites: You must possess strong programming skills to benefit from this class. (We will /not /teach you the basics of programming such as logic flow and conditional logic. We will, however, teach you how to do it efficiently in PL/SQL.) Experience with a procedural language (e.g. Java, C or COBOL), SQL and SQL*Plus is required.

Next Courses: *Advanced PL/SQL Programming, Advanced Queries for Oracle10g Databases, Oracle10g SQL Tuning for Developers and DBA's, Oracle10g Database Administration.

Objectives: After successfully completing this course, you will be able to:

Code efficient, scalable PL/SQL programs that include common programming constructs such as data-typing, variable assignment, flow control, cursor handling, bulk processing, array processing and error handling

Create PL/SQL stored procedures that accept and return values or sets of values

Manage stored procedure dependencies and privileges

Create PL/SQL functions, including powerful table functions and pipelined table functions

reate database triggers for auditing, complex business rule support, simple replication and more

Create and maintain simple Oracle packages

Use the UTL_FILE package to read and write to operating system files

Use the UTL_MAIL package to send email from an Oracle database

Use the DBMS_PROFILER package to profile and tune PL/SQL programs

Do simple benchmarking of PL/SQL code with the DBMS_UTITLITY package

Topic Summary

Introduction to PL/SQL

  • What is PL/SQL?
  • Why Use PL /SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Compile Errors
  • Sending Output to SQL*Plus
  • Introduction to Procedures
  • Procedure Compile Errors
  • Procedure Compile Warnings (10g)
  • Introduction to Functions
  • Introduction to Packages
  • Querying the Data Dictionary
  • Introduction to Triggers
  • Tools for PL/SQL Development
  • Working in SQL*Plus

Language Fundamentals

  • PL/SQL Statements
  • PL/SQL Symbols
  • Quoting Mechanism (10g)
  • Common PL/SQL Datatypes (10g Enhancements)
  • Declaring Variables
  • %TYPE Attribute
  • PL/SQL Records
  • %ROWTYPE Attribute
  • Programmer Defined Records
  • Variable Scope
  • Nested Blocks
  • Functions
  • Regular Expressions (10g)
  • IF Statement
  • CASE Statement and Expression
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods

PL/SQL and SQL, Part I Basics

  • SELECT INTO Statement
  • Implicit Cursor Loops
  • DML in PL/SQL
  • Cursor Attributes
  • Embedding DDL

PL/SQL and SQL, Part II Cursors

  • What is a Cursor?
  • Explicit Cursor Processing
  • Cursor Attributes
  • Dynamic Cursors
  • Dynamic Cursor Example
  • Variable Scope & Cursors
  • Cursor Parameters
  • Cursor Records
  • Cursor FOR LOOP
  • Referencing the Current Row
  • FOR UPDATE Example

PL/SQL and SQL, Part III Bulk Processing

  • Bulk Processing
  • Bulk Collect
  • Bulk DML - FORALL (10g Enhancements)
  • Bulk DELETE
  • Bulk INSERT
  • Bulk UPDATE
  • Returning into Arrays (10g Enhancements)

Stored Procedures

  • What is a Stored Procedure?
  • CREATE PROCEDURE Syntax
  • Simple Example
  • Review: Compile Errors
  • Procedure Signatures
  • Calling Procedures
  • Procedure Synonyms
  • Referencing Parameters by Name
  • Returning Sets: REF CURSOR
  • Dropping Stored Procedures
  • Procedure Dependencies
  • ALTER COMPILE Statement
  • PL/SQL Procedure Privileges
  • Granting Execute Privilege
  • The Data Dictionary

PL/SQL Functions

  • What is a Function?
  • CREATE FUNCTION Syntax
  • Simple Example
  • Calling Functions
  • The Data Dictionary
  • Table Functions
  • Building a Table Function
  • Using a Table Function
  • Pipelined Functions
  • Pipelined Example
  • Test Data Generator Example

Error Handling

  • Types of Errors
  • Runtime Errors
  • Exception Concepts
  • Predefined Named Exceptions
  • EXCEPTION Block Syntax
  • Handling Exceptions
  • Recovering from Errors
  • Logging Exceptions
  • RAISE_APPLICATION_ERROR Statement
  • Raising Exceptions
  • Raising User Exceptions
  • EXCEPTION_INIT Pragma
  • Using FORMAT_ERROR_BACKTRACE (10g)

PL/SQL Packages

  • Concepts
  • Package Benefits
  • Package Benefits Diagram
  • Package Contents
  • Package Spécification
  • Package Body
  • Package Privileges
  • Calling Packaged Objects
  • Initialization Code
  • Session Variables
  • Package Body Variables
  • Package Overloading
  • Compiling Packages
  • DROP PACKAGE Statement
  • Sample of Oracle Supplied Packages

DML Triggers

  • DML Trigger Concepts
  • Trigger Execution
  • CREATE TRIGGER Syntax
  • Row Triggers
  • :OLD and :NEW Reference Variables
  • Trigger Attributes
  • Audit Trigger Example
  • Derived Value Trigger Example
  • Trigger Restrictions
  • Table Triggers
  • Autonomous Transactions
  • Autonomous Trigger Example
  • INSTEAD OF Triggers

Trace and Tune

  • Introduction to Tuning PL/SQL
  • DBMS_PROFILER (10g Enhancements)
  • Profiler Setup
  • Using Profiler
  • START_PROFILER and STOP_PROFILER
  • Reporting with PROFSUM
  • Effective Use of Bind Variables
  • Benchmarking with GET_TIME and GET_CPU_TIME (10g)

File I/O Using UTL_FILE

  • UTL_FILE Concepts
  • Setup for UTL_FILE
  • Unix File Permissions
  • Opening Files
  • Closing Files
  • Reading Files
  • Writing Files - PUT
  • Writing Files - PUT_LINE
  • Writing Files - NEW_LINE
  • Writing Files - PUTF

Sending Email with UTL_MAIL (10g)

  • Introduction to UTL_MAIL
  • Setup for Emailing from the Database
  • UTL_MAIL Example
 

Your are currently browsing this site with Internet Explorer 6 (IE6).

Your current web browser must be updated to version 7 of Internet Explorer (IE7) to take advantage of all of template's capabilities.

Why should I upgrade to Internet Explorer 7? Microsoft has redesigned Internet Explorer from the ground up, with better security, new capabilities, and a whole new interface. Many changes resulted from the feedback of millions of users who tested prerelease versions of the new browser. The most compelling reason to upgrade is the improved security. The Internet of today is not the Internet of five years ago. There are dangers that simply didn't exist back in 2001, when Internet Explorer 6 was released to the world. Internet Explorer 7 makes surfing the web fundamentally safer by offering greater protection against viruses, spyware, and other online risks.

Get free downloads for Internet Explorer 7, including recommended updates as they become available. To download Internet Explorer 7 in the language of your choice, please visit the Internet Explorer 7 worldwide page.