Oracle PL/SQL Training

Delivery Options

• Live Online Training - Public Class (5 days): $2,625.00 (Register)

» Register Now

» Free Demo

» Watch Presentation

» More Info

» Contact Education Consultant

• Customized Onsite Training (5 days): Request a Proposal
• Looking for a self-paced course? Starts at $249.75

Class Date and Time	Price Volume Discounts Students Discounts 2 5% 3-4 10% 5 or more 15% GSA Price: $2,493.75
Jul 8 - 12, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Aug 5 - 9, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Sep 9 - 13, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Sep 30 - Oct 4, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Oct 28 - Nov 1, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Nov 18 - 22, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now
Dec 16 - 20, 2013 10:00 AM - 5:00 PM ET	$2,625.00 or 5 vouchers vouchers	Register Now

Show More Classes

Class Description

Class Overview

Class Goals

• Understand the PL/SQL Development Environment.
• Learn the basics of the PL/SQL language.
• Learn to declare and work with variables.
• Learn to use conditionals and loops in PL/SQL.
• Learn to handle and create user-defined exceptions.
• Learn to use SQL within PL/SQL.
• Learn about nested blocks and variable scope.
• Learn to create Subprograms, Stored Procedures and Functions.
• Learn to work with Packages, Triggers and Cursors.
• Learn to use Oracle Supplied Packages.

Class Outline

1. The Environment for PL/SQL Development
   1. Prerequisites
      1. File Naming Conventions
   2. Connecting to Oracle
      1. SQL*Developer
      2. SQL*Plus
   3. Executing PL/SQL Code
   4. SQL Developer Configuration
   5. The PL/SQL Development Cycle
2. PL/SQL Basics
   1. PL/SQL Block
   2. Anonymous Block Structure
   3. Named Block Structure
   4. Executing Blocks
   5. Calling PL/SQL Functions
   6. Executing PL/SQL Blocks and Functions
3. Declaring Variables
   1. Variable Usage
   2. Variable Data Types
   3. Variable Naming
   4. Variable Assignment
      1. Variable Display
   5. Complex Variable Types
   6. More Information
   7. Variable Declaration, Initialization and Display
4. Within the Block
   1. Conditional Processing
      1. The IF Statement
      2. The CASE Statement
   2. Iterative Processing
      1. The LOOP Statement
      2. The WHILE Statement
      3. The FOR Statement
   3. Salary Classification using Conditional Statements
   4. Salary Increases using Loops
5. Handling Exceptions
   1. Overview of Exceptions
   2. Causing System Generated Exceptions
   3. Handling System Generated Exceptions
   4. Identifying System Generated Exceptions
   5. OTHERS Exception Handler
   6. User Defined Exceptions
   7. Exception Handling for Invalid Salary
6. Use of SQL in PL/SQL
   1. Implicit Cursors
   2. %TYPE and %ROWTYPE Attributes
   3. EXECUTE IMMEDIATE statement
   4. Cursors Attributes
   5. Implicit Cursor
   6. Explicit Cursors
   7. Explicit Cursor
   8. Cursor FOR loop
7. Nested Blocks
   1. Nesting Blocks
   2. Scope of Variables
   3. Scope of Exceptions
   4. Nested Subprograms
   5. Exception Propagation in Nested Blocks
   6. Block Variable Visibility
8. Introducing Subprograms
   1. Anonymous blocks
   2. Types of Subprograms
   3. Finding Subprograms through SQLDeveloper
   4. Finding Subprograms in the Oracle Data Dictionary
   5. Object Dependencies
   6. Subprogram information in the Oracle Data Dictionary
9. Stored Procedures and Functions
   1. Creating Subprograms
   2. Modifying Subprograms
   3. Removing Subprograms
   4. Application Maintenance
   5. Exercise Title
   6. Parameters
   7. Cursors as Parameters
   8. Subprogram Development Techniques
   9. Addressing Compilation Errors
   10. Directives for Debugging
   11. Issues with Booleans
   12. Integrated Development Environments
   13. PL/SQL Subprograms with Parameters
10. Packages
    1. Structure of Packages
    2. Purpose of Packages
    3. Wrap Utility
    4. Positional vs Named Parameter Notation
    5. Subprograms Omitted from Specifications
    6. Using PL/SQL Packages
11. Database Triggers
    1. Purpose of Triggers
    2. Invocation of Triggers
    3. Coding Triggers
    4. Validation Trigger
    5. Modifying Triggers
    6. Viewing Triggers
    7. Enabling/Disabling Triggers
    8. Trigger Errors
    9. System and User Event Triggers
    10. Logon Counter Trigger
12. Data Retrieval Techniques
    1. Cursor Review
    2. Cursor Parameters
    3. Cursor Variables
    4. Dynamic SQL
    5. Employee Report by State/Province
13. Using Oracle Supplied Packages
    1. Oracle Supplied Packages
    2. Identifying Available Packages
    3. Identifying Package Subprograms
    4. Selected Oracle Supplied Package Demos
    5. Packages related to Input/Output
    6. Packages related to Networking
    7. DBMS_SQL: Dynamic SQL
    8. DBMS_URL: Working with URLS
    9. The DBMS_APPLICATION_INFO
14. Specialized Topics
    1. Application Partitioning
    2. Subprograms in other Languages
    3. Oracle Object Features
    4. Statistical Analysis
    5. Data Mining
    6. Use Oracle Object Features

Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following areas is required:

• Oracle SQL as taught in our Introduction to Oracle SQL and Advanced Oracle SQL classes.

Courses that can help you meet these prerequisites:

• Introduction to SQL Training using Oracle
• Advanced Oracle SQL Queries

Technical Requirements

Our computer technical requirements and setup process is easy, with support just a click away.

• Instructor-led Online course technical requirements and setup instructions.
• WebEx system requirements for Instructor-led Online delivery option.
• Onsite course technical requirements and setup instructions.
• Self-paced course technical requirements and setup instructions.