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.