Oracle Developer 6i  
Course outline

Introduction to Oracle: SQL and PL/SQL

Overview of Relational Databases, SQL and PL/SQL

• Discuss the theoretical and physical aspects of a relational database
• Describe the Oracle implementation of the RDBMS and ORDBMS
• Describe the use and benefits of PL/SQL

Writing Basic SQL Statements

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement
• Differentiate between SQL statements and SQL*Plus commands

Restricting and Sorting Data

• Limit the rows retrieved by a query
• Sort the rows retrieved by a query

Single Row Functions

• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements
• Describe the use of conversion functions

Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using quality and none quality joins
• View data that generally does not meet a join condition by using outer joins
• Join a table to itself

Aggregating Data Using Group Functions

• Identify the available group functions
• Describe the use of group functions
• Group data using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Sub queries

• Describe the types of problems that sub queries can solve
• Define sub queries List the types of sub queries
• Write single-row and multiple-row sub queries

Multiple-Column Sub queries

• Write multiple-column sub queries
• Describe and explain the behavior of sub queries when null values are retrieved
• Write sub queries in a FROM clause

Producing Readable Output with SQL*Plus

• Produce queries that require an input variable
• Customize the SQL*Plus environment
• Produce more readable output
• Create and execute script files
• Save customizations

Manipulating Data

• Describe each DML statement
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

Creating and Managing Tables

• Describe the main database objects
• Create tables
• Describe the datatypes that can be used when specifying column definition
• Alter table definitions
• Drop, rename, and truncate tables

Including Constraints

• Describe constraints
• Create and maintain constraints

Creating Views

• Describe a view
• Create a view
• Retrieve data through a view
• Insert, update, and delete data through a view
• Drop a view

Oracle Data Dictionary

• Describe the data dictionary views a user may access
• Query data from the data dictionary
• Other Database Objects
• Describe database objects and their uses
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Controlling User Access

• Create users
• Create roles to ease setup and maintenance of the security model
• Use the GRANT and REVOKE statements to grant and revoke object privileges

Declaring Variables

• List the benefits of PL/SQL
• Describe the basic PL/SQL block and its sections
• Describe the significance of variables in PL/SQL
• Declare PL/SQL variables
• Execute a PL/SQL block

Writing Executable Statements

• Describe the significance of the executable section*
• Write statements in the executable section
• Describe the rules of nested blocks
• Execute and test a PL/SQL block
• Use coding conventions

Interacting with the Oracle Server

• Write successful SELECT statements in PL/SQL
• Declare the datatype and size of a PL/SQL variable dynamically
• Write DML statements in PL/SQL
• Control transactions in PL/SQL
• Determine the outcome of SQL DML statements

Writing Control Structures

• Identify the uses and types of control structures
• Construct an IF statement
• Construct and identify different loop statements
• Use logic tables
• Control block flow using nested loops and labels

Working with Composite Datatypes

• Create user-defined PL/SQL records
• Create a record with the %ROWTYPE attribute
• Create a PL/SQL table
• Create a PL/SQL table of records
• Describe the difference between records, tables, and tables of records

Writing Explicit Cursors

• Distinguish between an implicit and an explicit cursor
• Use a PL/SQL record variable
• Write a cursor FOR loop

Advanced Explicit Cursor Concepts

• Write a cursor that uses parameters
• Determine when a FOR UPDATE clause in a cursor is required
• Determine when to use the WHERE CURRENT OF clause
• Write a cursor that uses a sub query

Handling Exceptions

• Define PL/SQL exceptions
• Recognize unhandled exceptions
• List and use different types of PL/SQL exception handlers
• Trap unanticipated errors
• Describe the effect of exception propagation in nested blocks
• Customize PL/SQL exception messages

Develop PL/SQL Program Units                                                  

Description: In this course participants will learn to write PL/SQL procedures, functions and packages. Working in both the Procedure Builder and SQL*Plus environments, participants will learn how to create and manage PL/SQL program units and database triggers. Participants will also learn how to use some of the Oracle supplied packages and manipulate large objects.

Overview of PL/SQL

• Distinguish between anonymous PL/SQL blocks and PL/SQL subprograms
• Describe the PL/SQL development environment

Work with Procedure Builder

• Describe the features of Procedure Builder
• Manage program units using the Object Navigator
• Create and compile program units using the Program Unit Editor
• Invoke program units using the PL/SQL interpreter

Create Procedures

• Describe the uses of procedures
• Create client-side and server-side procedures
• Create procedures with arguments
• Invoke a procedure
• Remove a procedure

Create Functions

• Describe the uses of functions
• Create client-side and server-side functions
• Invoke a function
• Remove a function
• Differentiate between a procedure and a function

Create Packages

• Describe packages and list their possible components
• Create a package to group together related variables, cursors, constants, exceptions, procedures and functions
• Make a package construct either public or private
• Invoke a package construct
• Write packages that make use of the overloading feature of PL/SQL
• Avoid errors with mutually referential subprograms
• Initialize public or private variables with an automatic one time only procedure
• Declare ref cursors in a package
• Control the extent to which functions permit database operations
• Describe the use and application of the Oracle supplied packages

Database Triggers

• Describe database triggers and their uses
• Create database triggers
• Describe database trigger firing rules
• Understand database trigger restrictions
• Drop database triggers

Manage Subprograms

• Describe system privilege requirements
• Track procedural dependencies
• Predict the effect of changing a database object upon stored procedures and functions
• Describe object dependencies
• Debug client-side and server-side subprograms

Manage Large Objects

• Compare and contrast LONG and LOB (large object) datatypes
• Create and maintain LOB datatypes
• Differentiate between internal and external LOBs
• Utilize the DBMS_LOB PL/SQL packages

Oracle iDS Forms: Build Internet Applications I                                                  

Description: In this course students build and test interactive Internet applications. Working in a graphical user interface (GUI) environment, students learn to customize Forms with user input items such as check boxes, list items, and radio groups. They also learn to modify data access by creating event-related triggers.

The Learning Reinforcement Package is the best way to accelerate your learning curve. In the IT world, you not only have to learn more, you have to learn more quickly. The Technology Based Training will give special focus to key parts of the course.

Topics:

Introduction to Oracle Forms Developer and Oracle Forms Server

• Describe the Features and Benefits of Oracle Forms Developer and Oracle Forms Server
• Navigate Around the Form Builder Interface
• Use the Online Help Facilities
• Describe The Course Application

Run a Form Builder Application

• Understand The Runtime Environment
• Navigate a Forms Application
• Retrieve Both Restricted and Unrestricted Data from the Database into a Forms Application
• Identify the Two Modes of Operation
• Insert, Update, and Delete Records
• Display Database Errors

Work in the Form Builder Environment

• Identify the Main Forms Executables
• Identify the Main Components of Form Builder
• Identify the Main Objects in a Form

Create a Basic Form Module

• Create a Form Module
• Create a Data Block
• Modify the Layout
• Save and Run a Form Module
• Identify Form File Formats and Their Characteristics
• Create Data Blocks with Relationships
• Run a Master-Detail Form Module

Work with Data Blocks and Frames

• Manage Object Properties
• Control the Behavior and Appearance of Data Blocks
• Create Data Blocks that Do Not Directly Correspond to Database Tables
• Delete Redundant Data Blocks and Their Components

Work with Text Items

• Describe Text Items
• Create a Text Item
• Modify the Appearance of a Text Item
• Control the Data in a Text Item
• Alter the Navigational Behavior of a Text Item
• Enhance the Relationship Between the Text Item and the Database
• Add Functionality to a Text Item
• Include Helpful Messages

Create Lists of Values (LOVs) and Editors

• Describe LOVs and Editors
• Design, Create, and Associate LOVs with Text Items
• Create an LOV Using the LOV Wizard
• Create Editors and Associate Them with Text Items

Create Additional Input Items

• Identify the Item Types that Allow Input
• Create a Check Box
• Create a List Item
• Create a Radio Group

Create Noninput Items

• Identify Item Types that Do Not Allow Input
• Create a Display Item
• Create an Image Item
• Create a Sound Item
• Create a Button
• Include Tooltips
• Create a Calculated Field
• Include a Hierarchical Tree Control

Create Windows and Content Canvases

• Describe the Relationship Between Windows and Content Canvases
• Display a Form Module in Multiple Windows
• Display a Form Module on Multiple Layouts

Work with Other Canvas-Views

• Describe the Different Types of Canvases
• Identify the Appropriate Canvas Types for Different Scenarios
• Create an Overlay Effect on the Interface
• Create a Toolbar
• Create a Tabbed Interface

Understand Triggers

• Identify the Different Trigger Categories
• Plan the Type and Scope of Triggers in a Form
• Describe the Properties that Affect Trigger Behavior

Produce Triggers

• Write Trigger Code
• Explain the Use of Built-In Subprograms in Oracle Developer Applications
• Identify the When-Button-Pressed Trigger and Know When to Use It
• Identify the When-Window-Closed Trigger and Know When to Use It
• Debug Triggers and Program Units

Add Functionality to GUI Items

• Supplement the Functionality of Input Items Using Triggers
• Supplement the Functionality of Noninput Items Using Triggers
• Handle Errors Using Built-In Subprograms

Discuss Runform Messages and Alerts

• Describe the Default Messaging Behavior of a Form
• Identify the Different Types of Forms Messages
• Control System Messages
• Create and Control Alerts

Describe Query Triggers

• Explain the Processes Involved in Querying a Data Block
• Identify Where Triggers Can Be Coded to Control the Query Process
• Write Triggers that Screen Query Conditions
• Write Triggers to Supplement Query Results
• Control Trigger Action Based Upon the Query Status of the Form

Perform Validation

• Understand the Effects of the Validation Unit upon a Form
• Employ Additional Forms Features for Validation
• Control Validation Using Triggers
• Supplement Default Validation

Control Navigation

• Distinguish Between Internal and External Navigation
• Describe and Use the Navigation Triggers
• Write Navigation in Triggers

Explain Transaction Processing

• Explain the Process Used by Oracle Forms Developer to Apply Users’ Changes to the Database
• Describe the Commit Sequence of Events
• Supplement Transaction Processing
• Allocate Sequence Numbers to Records as They Are Applied to Tables

Write Flexible Code

• Define Flexible Code
• Identify The Advantages of Using System Variables
• Identify Built-In Subprograms that Assist Flexible Coding
• Reference Objects by Internal ID
• Reference Items Indirectly

Oracle iDS Forms: Build Internet Applications II                                                  

Description: In this course students broaden their application building skills using Oracle Forms Developer, a productive development environment for building enterprise-class Internet database applications. Students create multiple-form Internet applications and learn to manage multiple transactions across modules. By adding custom menus, reports, and charts they also learn to enhance their applications. Finally, the course teaches students to enhance the user interface for Web-deployed forms using Java Beans.  

The Learning Reinforcement Package is the best way to accelerate your learning curve. In the IT world, you not only have to learn more, you have to learn more quickly. The Technology Based Training will give special focus to key parts of the course.
Topics:

Create a Menu Document

• Identify Components of the Menu Module
• Create a Menu Module
• Outline Menu Properties
• Implement Menu Toolbars
• Store and Attach the Menu Module

Manage Menu Documents

• Use PL/SQL in Menu Item Code
• Manage Menu Security
• Define Security Roles
• Assign Access to Menu Items

Program Function Keys

• Define Key Triggers
• Classify Key Triggers
• Use Key Triggers
• Associate Key Triggers with Other Interface Controls

Respond to Mouse Events

• Define Mouse Events
• Respond to Mouse Movement
• Respond to Mouse Button Actions

Control Windows and Canvases Programmatically

• Use Window-Interaction Triggers
• Identify the Built-In for Manipulating Windows and Canvases
• Work with Tab-Style Canvases
• Manipulate Window Properties Programmatically
• Manipulate Windows in Web-Deployed Forms
• Size Windows for Web-Deployed Forms
• Display Large Data Blocks in a Window
• Manage Images for Web-Deployed Forms

Define Data Sources

• Identify Data Source Types
• Base a Data Block on a FROM Clause Query
• Base a Data Block on a Stored Procedure
• Decide Whether to use a REF Cursor or a Table of Records
• Use the Data Block Wizard
• Discuss Data Block Properties

Work with Oracle8i Objects in Form Builder

• Review and Identify the Object Types
• Understand how Oracle Forms Developer Treats Objects
• Create Data Blocks Based on Oracle8i Features
• Select Object Table Columns
• Select Object Column Attributes
• Create Blocks with REF Lookups
• Create LOVs for REFs

Control Data Block Relationships

• Create a Relation
• Coordinate Blocks Using REFs
• Discuss the Characteristics of Relation-Handling Triggers
• Implement a Coordination-Type Toggle
• Force a Commit Per Master

Build Multiple-Form Applications

• Navigate Between Forms
• Discuss Transaction Processing for Open and Called Forms
• Identify Different Ways of Invoking Forms
• Create and Manipulate Parameter Lists
• Pass Data Between Forms

Work with Record Groups

• Define Record Groups at Design Time
• Learn the Built-In Functions for Controlling Record Groups
• Define Query and Nonquery Record Groups Programmatically
• Define Global Record Groups
• Manipulate List Items Programmatically
• Implement Dynamic List Items
• Add Values to Combo Boxes

Understand the Reusable Components

• Outline the Reusable Components
• Understand the Calendar Class
• Use Built-Ins for Manipulating the Calendar

Use Java in Web-Deployed Forms

• Understand Java in Web-Deployed Forms
• Learn the Pluggable Java Components
• Communicate with the JavaBean
• Create JavaBeans
• Exploit Oracle8i Features

Use Server Features in Forms

• Use Oracle Database Server Functionality in Forms
• Deal with PL/SQL Code
• Understand the PL/SQL8 Support
• Handle Errors Raised by the Oracle Database Server
• Perform DDL and FORMS_DDL

Apply Timers

• Define a Timer
• Handle Timer Expiration
• Create, Modify and Delete a Timer

Include Charts and Reports

• Use the Chart Wizard
• Understand Reporting within Form Builder
• Work with Reports

Oracle iDS Reports: Build Internet Reports                                                  

Description: In this course students build a variety of standard and custom reports using Oracle Reports 6i and run the reports both in client-server and on the Web. Working in a graphical user interface environment, students learn to retrieve, display, and format data in numerous reporting styles. They also learn to customize more complex reports, embed graphical charts, and use the Reports Server. The course covers building reports for the Web, calling Java applets, using reports administration and security  

The Learning Reinforcement Package is the best way to accelerate your learning curve. In the IT world, you not only have to learn more, you have to learn more quickly. The Technology Based Training will give special focus to key parts of the course. 
Topics:

Introduction to Oracle Reports

• Understand the Concept of Business Intelligence
• Discuss Enterprise Reporting
• Understand where Oracle Developer Fits In
• Identify Report Builder Components
• Get Started in the Oracle Reports Interface
• Customize your Oracle Reports Session
• Introduce the Course Application

Design and Run a Report

• Design Reports
• Understand the User Requirements
• Choose the Report Style
• Retrieve and Share Data
• Run a Report
• View the Report Output

Discuss Report Builder Concepts

• Identify Reports Executables
• Learn the Different Types of Report Builder Modules
• Identify Report Builder Components
• Identify Object Categories

Create Reports Using the Report Wizard

• Invoke the Report Builder
• Create a Tabular Report Use the Report Wizard
• Modify a Report Using the Report Wizard
• Add Summaries
• Create Mailing Labels
• Create Letters
• Web-Enable a Report Using the Web Wizard
• Publish and View a Report on the Web

Enhance a Report in the Live Previewer

• Understand the Live Previewer
• Modify a Report in the Live Previewer
• Align Columns
• Set Format Masks
• Manipulate Objects
• Change Text and Modify Visual Attributes
• Create Format Exceptions
• Insert Page Numbers, Current Date, and Time

Manage Report Templates

• Use the Reports Template Editor
• Modify a Predefined Template
• Add Web Links to a Template for HTML Output
• Register a Customized Template

Understand Reports Storage Methods

• Understand Storage Methods and Requirements
• Share Reports Among Developers
• Convert Between Storage Types
• Document Reports
• Understand File Types and Portability

Enhance Reports Using the Data Model: Create Queries and Groups

• Identify the Data Model Objects
• Change the Group Structure
• Order Data in a Group Using the Break Order Property
• Eliminate Data Using Group Filters
• Add New Data
• Link Data from Different Queries Using a Data Link

Enhance Reports Using the Data Model: Create Columns

• Maintain Database Columns
• Use a Database Column
• Create Summary Columns
• Create Formula Columns
• Create Placeholder Columns

Enhance Reports Using the Layout Model

• Identify the Three-Report Sections
• Use Report Bursting
• Create a Multipanel Report for Large Sets of Data
• Identify the Layout Model Objects
• Create Variable-Length Vertical Lines
• Display a Video Clip Using a Button
• Define Implicit and Explicit Anchors
• Position One Object Relative to Another Using an Explicit Anchor

Modify Common Layout Properties

• Use Web Support for Layout Objects
• Use Format Triggers
• Modify Web Setting Properties

Modify Specific Layout Properties

• Control the Print Direction of a Repeating Frame
• Control Pagination of a Repeating Frame
• Control Spacing Between Records
• Create New Fields in the Layout and Modify the Properties
• Create a Link File to Display File Contents s

Use Report Parameters and Customize a Parameter Form

• Create User Parameters
• Reference User Parameters
• Create Lists of Values for Parameters
• Reference System Parameters
• Create a Parameter Form Layout
• Customize the Parameter Form Layout
• Create Parameter HTML Extensions

Embedding a Chart in a Report

• Create and Embed a Graphics Chart Using the Chart Wizard
• Embed an Existing Graphics Chart in a Report
• Restrict Chart Data with a User Parameter
• Pass Report Data to the Chart
• Create Chart Hyperlinks

Enhance Matrix Reports

• Understand the Matrix Data Model
• Understand the Matrix Layout Model
• Create Matrix Summaries
• Create a Nested Matrix Report Using the Wizards

Code PL/SQL Triggers in Reports

• Differentiate Trigger Types
• Create Report Triggers
• Create Data Model Triggers
• Create Layout Model Triggers
• Write Common Code

Build Reports for the Web

• Use Static Web Reporting
• Use Dynamic Web Reporting
• Build Web Drill-Down Reports

Use the Report Builder Built-In Package

• Identify the Contents of the Report Builder Built-in Package
• Output Runtime Messages
• Manage Temporary Tables
• Modify Visual Attributes Dynamically
• Control the Printer Output Dynamically
• Suppress Buttons in the Previewer Window Dynamically

Customize Reports at Runtime

• Build Reports Use XML
• Apply Runtime Customizations
• Apply Batch Updates

Use the Reports Server

• Understand Why to Use a Remote Server
• Learn the Reports Server Architecture and Configuration
• View and Schedule Reports in the Queue Manager
• Control User Access