Oracle

Objective
To provide the knowledge and skills required to make effective use of Oracle 10g.

Target audience
Analysts, designers, programmers and database administrators who work with Oracle databases.

Prerequisites
Conceptual data modeling (Course BD101) and knowledge of a programming language.

Topics covered

• Introduction to the Oracle database (physical structure, memory structures)
• Basic techniques of data modeling
• Introduction to the SQL*Plus environment
• Differences between SQL and SQL*Plus commands
• Queries, display and formatting of table data
• Basic formatting functions (e.g. TO_CHAR, TO_DATE, etc...)
• Overview of the various join types
• Introduction to complex queries
• Nested subqueries within SQL statements
• Inserting, updating and deleting table data
• Creating, updating and dropping Oracle tables
• Controlling user access to tables
• Granting and revoking privileges on tables
• Creating and displaying a view on one or more tables

Note: Even though the course is presented using Oracle Express 10g Edition, the concepts and techniques covered apply equally well to the Standard and Business Editions.

Objective
To provide the knowledge and skills required to configure and manage an Oracle 10g database.

Target audience
Oracle database administrators and technical personnel.

Prerequisites
Knowledge of Oracle 10g (Course OR101) and system administration (Windows Server 2003 or UNIX)

Topics covered

• Memory structures, physical and logical database structure
• Product installation and configuration
• Creating, starting up and shutting down a database
• Control files and redo log files
• The logical structure of a database: segments, extents, blocks
• Rollback and undo segments
• Table types: regular, partitioned, external, temporary
• Index types: B*tree, bitmap, function-based, reverse-key, partitioned
• Managing privileges and roles
• Backup and restore
• Administrative and monitoring tools

Objective
To provide the knowledge and skills required to develop PL/SQL programs with Oracle 10g.

Target audience
Oracle analysts, designers and programmers.

Prerequisites
Knowledge of Oracle (Course OR101).

Topics covered

• Introduction to PL/SQL concepts: anonymous blocks, procedures, functions, packages and triggers
• Structure of a PL/SQL block
• Creating an anonymous block
• Variable types
• Conditional execution: IF, ELSIF and ELSE
• Loop constructs: FOR LOOP and LOOP
• Parameter types: IN, OUT and IN OUT
• Handling errors with the EXCEPTION clause
• Using SQL commands in a PL/SQL block
• Implicit and explicit cursors
• Creating procedures and functions
• Creating the body and the specification for a package
• Introduction to encapsulation techniques

Objective
To provide the knowledge and skills required to denormalize a transactional environment into a data warehouse.

Target audience
Oracle database architects and conceptual analysts.

Prerequisites
Knowledge of Oracle (Course OR101) or of Oracle administration (Course OR201).

Topics covered

• The evolution of a data warehouse
• Identifying the objects of a data warehouse
• Description of the Warehouse Builder tools
• Introduction to the structure of Warehouse Builder - Design Center
• Overview of ETL techniques (extraction, transformation and load)
• Validation, generation and deployment of a mapping