back to Course Categories | back | print
Oracle10g Developers Workshop          

Duration: 4 days

Description 
This course is designed to teach attendees the skills necessary to write Oracle database applications. Topics include the Oracle database server architecture for developers, Advanced SQL programming, SQL tuning fundamentals, creating tables and indexes and storage issues, managing constraints, Oracle tools for loading and extracting database from the database and the fundamentals of PL/SQL programming.

Topics

  • Understanding the Oracle Architecture
  • The UNION, INTERSECT, and MINUS operators
  • SAMPLE clause, GROUPING function
  • CUBE and ROLLUP operators
  • Introduction to Analytical functions
  • The WITH clause
  • Complex data retrieval using equi-joins,
  • Cartesian-joins, self-joins, outer-joins
  • Subqueries and correlated subqueries
  • Hierarchical and tree-structured queries
  • Oracle character, date, numeric and conversion functions
  • SQL*Plus application and report writing
  • SQL*Plus parameters, substitution variables,
  • Calculations, groups, breaks, summaries
  • Headers and footers
  • SQL*Plus Web reports
  • Fundamentals of SQL tuning
  • Rule versus cost-based optimzer
  • Generating statistics using DBMS_STATS
  • Generating statistics using ANALYZE command
  • Explain plan
  • Evaluating execution plans
  • Autotrace
  • SQL Trace
  • Tkprof
  • Evaluating runtime statistics
  • Join optimization methods
  • Tuning joins and subqueries
  • Tuning using hints
  • Creating tables
  • Different types of tables
  • Creating indexes
  • Different types of indexes
  • Working with constraints
  • Features of PL/SQL
  • The Block Structure
  • Language Rules
  • Executing PL/SQL from SQL*Plus
  • Variables and Constants
  • Identifiers
  • Customizing SQL*Plus for PL/SQL Scripts
  • SELECT INTO Statement
  • Using Bind Variables
  • Interactive PL/SQL Programs
  • Oracle Datatypes are supported in PL/SQL
  • Numeric Datatypes
  • Datatype Conversion
  • Implicit and Explicit Conversion
  • %TYPE and %ROWTYPE
  • Subtypes
  • Subtype Declarations
  • Labels
  • Variable Scope and Visibility Rules
  • SQL and PL/SQL Reserved Words
  • PL/SQL Reserved Words
  • Direction of PL/SQL
  • External Routines and Java Stored Procedures
  • Control Flow
  • Operators
  • Built-In Functions
  • Handling Exceptions
  • Working with Exceptions
  • SQLCODE and SQLERRM Functions
  • Predefined PL/SQL Exceptions
  • User-Defined Exceptions
  • Working with Exceptions
  • The Procedure Raise_Application_Error
  • IF Statements
  • Generic LOOP
  • Working with NULL
  • LOOP Labels
  • FOR LOOP
  • WHILE LOOP
  • GOTO Statement
  • Working with Cursors
  • Working with Cursors
  • Implicit Cursors and Attributes
  • Explicit Cursors
  • Explicit Cursor Attributes
  • Using Subqueries with Cursors
  • Using Cursor FOR LOOPs
  • Using %ROWTYPE with Cursors
  • Using Aliases and Subqueries
  • Using Aliases and Subqueries (FOR LOOP)
  • Using Parameters with Explicit Cursors
  • Modifying Data using Explicit Cursors
  • Managing Transactions in PL/SQL Programs
  • Procedures and Functions
  • Working with Subprograms
  • Named Subprograms
  • Software Evolution
  • Stored Procedures
  • Parameters
  • NOCOPY Compiler Directive
  • Parameter Default Values
  • Positional and Named Notation
  • Executing a Stored Procedure
  • Compiling and Debugging Stored Procedures
  • Compiling a Stored Procedure
  • Stored Function Syntax
  • Executing a Function from SQL*Plus
  • The RETURN Statement
  • Advantages of Packages
  • Packages
  • What can a Package Contain?
  • Components of a Package
  • Package Specification
  • Package Body
  • Defining Package Components
  • Package Scope
  • Public Scope
  • Private Scope
  • Accessing Package Components
  • Privileges
  • Managing Side Effects
  • Defining Subprograms within another Subprogram
  • Forward Declarations
  • Overloading
  • Restrictions on Overloading
  • Restrictions on Packaged Variables
  • Using Data Pump
  • Using Export/Import
  • Using SQL*Loader

Prerequisites

  • A fundamental understanding of relational database concepts.
  • A fundamental of SQL Programming.

This class can be taught for Oracle 9i or Oracle10g. The course contents are different between Oracle9i and Oracle10g.

 
 
 

Trubix Inc. P.O. Box 2235 Littleton, CO 80161 | 1.866.4-TRUBIX • 303.730.6800
email: info@trubix.com
....................................................................................................................................................................

Training Service | Consulting / Mentoring | Public Schedule | Course Listings | Tru iLearning | About Us | Contact Us
Oracle DBA Classes | Oracle Dev Classes | Oracle App Server Classes | Linux / Unix Classes
SQL Server / .NET / Tools / DB2 Classes / Public Classes

 

 
all content property © 2006, 2007Trubix Inc.| site design by Art Z design