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