back to Course Categories | back | print
Oracle10g: A Comprehensive Hands-On Intro (SQL, PL/SQL, tuning, troubleshooting)          

Duration: 5 days

Description 
This class focuses on key development skills necessary to work in Oracle10g environments. Topics will include the Oracle architecture, Oracle SQL programming and features, PL/SQL programming features, SQL tuning features and concepts as well as key Oracle tools used in development. This class will develop expertise and skills with SQL, PL/SQL and SQL tuning in Oracle10g as well as troubleshooting and diagnostics capability.

Topics

  • Oracle overview of database, Apps Server and developer tools
  • Relational and object-relational designs
  • Review relational database design
  • Data models
  • Using SQL*Plus, iSQL*Plus
  • SQL Developer
  • SQL*Plus commands
  • Changing your password
  • Differences between SQL*Plus and iSQL*Plus
  • SQL*Plus Worksheet
  • SELECT statements
  • Components of SQL statements
  • Creating SQL scripts
  • Filtering records
  • Comparison conditions
  • LIKE operator
  • Working with NULL
  • Logical conditions (AND, OR, NOT)
  • Sorting data with ORDER BY
  • Building SQL statements
  • Techniques with SELECT statements
  • Working with DUAL
  • Using the data dictionary
  • The GROUP BY clause
  • Aggregate functions
  • HAVING clause
  • Different ways to group data
  • What to watch out for with GROUP BY
  • INSERT, UPDATE and DELETE statements
  • Understanding read consistency
  • Transactions
  • The UNION, INTERSECT, and MINUS operators
  • Techniques for SET operators
  • SAMPLE clause, GROUPING function, CUBE and ROLLUP operators
  • Introduction to Analytical functions
  • The WITH clause
  • Advanced subqueries
  • Regular expressions
  • Dealing with case sensitivity with SQL
  • Techniques for writing joins
  • Complex data retrieval using equi-joins, Cartesian-joins, self-joins, outer-joins
  • Hierarchical and tree-structured queries
  • Regular and correlated subqueries
  • Single and multiple row subqueries
  • Group functions in subqueries
  • Guidelines for subqueries
  • Oracle native and ANSI 99 SQL
  • Character functions
  • Date functions
  • Numeric functions
  • Conversion functions
  • Miscellaneous functions
  • SQL*Plus application and report writing using parameters, substitution variables, calculations, groups, breaks, summaries, headers and footers, Web reports
  • Working with schema objects
  • Sequences
  • Synonyms
  • Database links
  • Creating, altering and deleting tables
  • Renaming tables
  • Managing tables
  • Working with constraints
  • Primary and foreign keys
  • Unique keys
  • NOT NULL constraints
  • Check constraints
  • DEFAULT values
  • Creating and accessing external tables
  • Different types of indexes
    • PL/SQL in the Oracle10g environment
    • 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
    • Triggers
    • Purpose of Triggers
    • Trigger Guidelines
    • Table Triggers
    • Firing Order of Table Triggers
    • Table Trigger Syntax
    • ALTER and DROP Trigger Commands
    • Boolean Functions
    • Reference Variables for ROW Triggers
    • ROW Trigger Examples
    • Information about Triggers
    • WHEN Clause
    • Restrictions
    • Database Triggers
    • Event Functions
    • Resource Manager Events
    • Client Events
    • Schema Triggers
    • INSTEAD-OF Triggers
    • object-relational types
    • Autonomous Transactions
    • Packages and Triggers
    • SQL tuning fundamentals
    • Understanding the Oracle10g tuning model
    • Understanding execution plans
    • Explain plan
    • Oracle join-optimization
    • Working with hints

    Prerequisites

    • A background in data processing, relational database concepts and programming is strongly recommended.


 
 
 

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