Home About Courses Schedule Services Webinars Contact Search

Introduction to Oracle for Db2 Developers and DBAs

SEE SCHEDULE

Duration: 4.0 days

Method: Instructor led, Hands-on workshops

Price: $2450.00

Course Code: OR5455


Audience

Experienced Db2 or SQL Server developers

Description

This course provides students that have a background with SQL and/or another programming language in a non-Oracle environment with an introduction to Oracle. A discussion of the Oracle architecture will provide the foundation for students to understand both SQL statement and PL/SQL program processing. Students will add to their SQL knowledge Oracle’s proprietary extensions to the SQL language.Students will also learn how to write PL/SQL stored procedures, functions, packages and triggers.Hands-on workshops provide students with a solid understanding of the concepts presented in the lectures.This course can be taught for any Oracle version.

Topics

  • I. Oracle Architecture for Developers
    • Architecture Components
      • Instance
      • Database
      • Tablespace
      • Segment
      • Extent
      • Block
      • Memory Structures
    • SQL Behind the Scenes
    • SQL Performance Considerations
    • External Tables
    • Development Tools
  • II. Oracle SQL - Proprietary
    • Inner Join Options
    • Outer Join Options
    • Row Functions
      • Types
      • Character
      • Numeric
      • Date / Time
      • Conversion
      • Miscellaneous
    • Multi-table INSERT Command
    • Sequences
    • Oracle View Options
  • III. Introduction to Oracle PL/SQL
    • Types of PL/SQL Programs
      • Anonymous Blocks
      • Stored Procedures
      • Functions
      • Packages
      • Triggers
    • Program Sections
      • DECLARE
      • BEGIN
      • EXCEPTION
  • IV. The PL/SQL DECLARE Section
    • Declaring Variables
    • Data Types
    • Initialization
    • PL/SQL Records
    • PL/SQL Arrays
  • V. The PL/SQL BEGIN Section
    • Decision Constructs
      • IF-THEN-ELSE
      • IF-THEN-ELSIF
      • CASE
      • Simple CASE
      • Searched CASE
    • Loop Constructs
      • Simple / Basic Loop
      • FOR Loop
      • While Loop
  • VI. Embedded SQL in PL/SQL Programs
    • The SELECT Statement
      • Restrictions
      • The INTO Clause
      • Exceptions Raised
    • INSERTS, UPDATES, DELETES
    • COMMIT, ROLLBACK
    • Using SQL Attributes to test SQL results
      • SQL%FOUND
      • SQL%NOTFOUND
      • SQL%ROWCOUNT
  • VII. Processing Multiple Records in PL/SQL Programs
    • Explicit Cursors
    • Basic Loop Cursor
    • FOR Loop Cursor
    • Using Cursor Attributes
  • VIII. Declaring, Raising and Handling Program Exceptions in PL/SQL Programs
    • Predefined Exceptions
    • User defined Exceptions
    • The EXCEPTION Section
    • Other types of Exceptions
  • IX. PL/SQL Stored Procedures and Functions
    • Creating / Compiling a Stored Procedure / Function
    • Stored Procedure / Function Benefits
    • Fixing Compilation Errors
    • Viewing Stored Procedure / Function Code
    • Input / Output Parameters
    • Executing Stored Procedures / Functions
  • X. PL/SQL Packages
    • Benefits of Packages
    • Package Structure
    • The Package Specification
    • The Package Body
    • Creating / Compiling a Package
    • Fixing Compilation Errors
    • Viewing Package Code
    • Executing Programs in a Package
  • XI. PL/SQL Triggers
    • Trigger Uses
    • Trigger Structure
    • Trigger Events
    • Row Level vs. Statement Level Triggers
    • Creating / Compiling a Trigger
    • Fixing Compilation Errors
    • Viewing Trigger Code
    • Autonomous Triggers