Course Objectives:
Deploy Form modules on the Web
Display Form modules in multiple windows and use a variety of layout styles
Link one form module to another
Create Form modules, including components for database interaction and GUI controls
Reuse objects and code

Course Topics:
Introduction
List the Oracle Database 10g main features
Provide an overview of: components, internet platform, apps server and developer suite
Describe relational and object relational database designs
Review the system development life cycle
Describe different means of storing data
Review the relational database concept
Define the term data models
Show how multiple tables can be related

Retrieving Data Using the SQL SELECT Statement
Define projection, selection, and join terminology
Review the syntaxes for the basic SQL SELECT statements
Use Arithmetic and Concatenation operators in SQL statements
List the differences between SQL and iSQL*Plus
Log into the database using iSQL*Plus
Explain the iSQL*Plus interface
Categorize the different types of iSQL*Plus commands
Save SQL statements to script files

Restricting and Sorting Data
Limit rows using a selection
Using the WHERE clause to retrieve specific rows
Using the comparison conditions in the WHERE clause
Use the LIKE condition to compare literal values
List the logical conditions AND, OR, NOT
Describe the rules of precedence for the conditions shown in this lesson
Sort rows with the ORDER BY clause
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single Row Functions to Customize Reports
Show the differences between single row and multiple row SQL functions
Categorize the character functions into case manipulation and character manipulation types
Use the character manipulation functions in the SELECT and WHERE clauses
Explain and use the DATE and numeric functions
Use the SYSDATE function to retrieve the current date in the default format
Introduce the DUAL table as a means to view function results
List the rules for applying the arithmetic operators on dates
Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions
Describe and categorize the group functions
Use the group functions
Utilize the DISTINCT keyword with the group functions
Describe how nulls are handled with the group functions
Create groups of data with the GROUP BY clause
Group data by more than one column
Avoid illegal queries with the group functions
Exclude groups of data with the HAVING clause

Displaying Data From Multiple Tables
Show the join tables syntax using SQL 99 syntax
Use table aliases to write shorter code and explicitly identify columns from multiple tables
Issue a SQL CROSS JOIN statement to produce a cartesian product
Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
Create a join with the USING clause to identify specific columns between tables
Create a three way join with the ON clause to retrieve information from 3 tables
List the types of outer joins LEFT, RIGHT, and FULL
Add additional conditions when joining tables with the AND clause

Using Sub queries to Solve Queries
List the syntax for sub queries in a SELECT statements WHERE clause
List the guidelines for using sub queries
Describe the types of sub queries
Execute single row sub queries and use the group functions in a sub query
Identify illegal statements with sub queries
Execute multiple row sub queries
Analyze how the ANY and ALL operators work in multiple row sub queries
Explain how null values are handled in sub queries

Using the SET Operators
Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
Use the UNION ALL operator to return all rows from multiple tables
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator

Manipulating Data
Write INSERT statements to add rows to a table
Copy rows from another table
Create UPDATE statements to change data in a table
Generate DELETE statements to remove rows from a table
Use a script to manipulate data
Save and discard changes to a table through transaction processing
Show how read consistency works
Describe the TRUNCATE statement

Using DDL Statements to Create and Manage Tables
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement and Rename a table

Creating Other Schema Objects
List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query and remove a table with the DROP statement
Describe the ALTERTABLE functionality
Rename a table

Managing Objects with Data Dictionary Views
Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema objects

Controlling User Access
Controlling user access
System versus objects privileges
Creating user sessions and granting system privileges
Using roles to define user groups
Creating and granting privileges to a role
Granting and revoking object privileges
Changing your password
Using Database Links

Manage Schema Objects
Creating directories
Creating and querying external tables
Creating Index Organized Tables
Creating Function based indexes
Dropping Columns
Altering the structure of tables and adding constraints
Performing FLASHBACK Statement
Materialized Views overview

Manipulating Large Data Sets
Using the MERGE Statement
Performing DML with Subqueries
Performing DML with a RETURNING Clause
Overview of Multitable INSERT Statements
Tracking Changes in DML

Generating Reports by Grouping Related Data
Overview of GROUP BY and Having Clause
Aggregating data with ROLLUP and CUBE Operators
Determine subtotal groups using GROUPING Functions
Compute multiple groupings with GROUPING SETS
Define levels of aggregation with Composite Columns
Create combinations with Concatenated Groupings

Managing Data in Different Time Zones
TIME ZONES
Oracle9i Date time Support
Conversion operations

Searching Data Using Advanced Sub queries
Subquery Overview
Using a Sub query
Comparing several columns using Multiple-Column Sub queries
Defining a Data source Using a Sub query in the FROM Clause
Returning one Value using Scalar Sub query Expressions
Performing ROW by-row processing with Correlated Sub queries
Reusing query blocks using the WITH Clause

Hierarchical Data Retrieval
Sample Data from the EMPLOYEES Table
The Tree Structure of Employee data
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses

Performing Regular Expression Support and Case Insensitive
Regular Expression Support Overview
Describing simple and complex patterns for searching and manipulating data

Introduction to Oracle Forms Developer and Oracle Forms Services
Features and Benefits of Oracle Forms Developer and Oracle Forms Services
Navigating Around the Form Builder Interface
Using the Online Help Facilities
The Course Application

Running a Form Builder Application
The Runtime Environment
Navigating a Forms Application
Retrieving Both Restricted and Unrestricted Data from the Database into a Forms Application
Identifying the Two Modes of Operation
Inserting, Updating, and Deleting Records
Displaying Database Errors

Working in the Form Builder Environment
Identifying the Main Forms Executables
Identifying the Main Components of Form Builder
Identifying the Main Objects in a Form

Creating a Basic Form Module
Creating a Form Module
Creating a Data Block
Modifying the Layout
Saving and Running a Form Module
Identifying Form File Formats and Their Characteristics
Creating Data Blocks with Relationships
Running a Master-Detail Form Module

Working with Data Blocks and Frames
Managing Object Properties
Controlling the Behavior and Appearance of Data Blocks
Creating Data Blocks that Do Not Directly Correspond to Database Tables
Deleting Redundant Data Blocks and Their Components

Working with Text Items
Describing Text Items
Creating a Text Item
Modifying the Appearance of a Text Item
Controlling the Data in a Text Item
Altering the Navigational Behavior of a Text Item
Enhancing the Relationship Between the Text Item and the Database
Adding Functionality to a Text Item
Including Helpful Messages

Creating Lists of Values (LOVs) and Editors
Describing LOVs and Editors
Designing, Creating, and Associating LOVs with Text Items
Creating an LOV Using the LOV Wizard
Creating Editors and Associating Them with Text Items

Creating Additional Input Items
Identifying the Item Types that Allow Input
Creating a Check Box
Creating a List Item
Creating a Radio Group

Creating Noninput Items
Identifying Item Types that Do Not Allow Input
Creating a Display Item
Creating an Image Item
Creating a Sound Item
Creating a Button
Including Tooltips
Creating a Calculated Field
Including a Hierarchical Tree Control

Creating Windows and Content Canvases
Describing the Relationship Between Windows and Content Canvas-Views
Displaying a Form Module in Multiple Windows
Displaying a Form Module on Multiple Layouts

Working with Other Canvas-Views
Describing the Different Types of Canvas-View
Identifying the Appropriate Canvas-View Type for Different Scenarios
Creating an Overlay Effect on the Interface
Creating a Toolbar
Creating a Tabbed Interface

Introduction to Triggers
Identifying the Different Trigger Categories
Planning the Type and Scope of Triggers in a Form
Describing the Properties that Affect Trigger Behavior

Producing Triggers
Writing Trigger Code
Explaining the Use of Built-In Subprograms in Oracle Developer Applications
Identifying the When-Button-Pressed Trigger and Knowing When to Use It
Identifying the When-Window-Closed Trigger and Knowing When to Use It
Debugging Triggers and Program Units

Adding Functionality to GUI Items
Supplementing the Functionality of Input Items Using Triggers
Supplementing the Functionality of Noninput Items Using Triggers
Handling Errors Using Built-In Subprograms

Runform Messages and Alerts
Describing the Default Messaging Behavior of a Form
Identifying the Different Types of Forms Messages
Controlling System Messages
Creating and Controlling Alerts

Query Triggers
Explaining the Processes Involved in Querying a Data Block
Identifying Where Triggers Can Be Coded to Control the Query Process
Writing Triggers that Screen Query Conditions
Writing Triggers to Supplement Query Results
Controlling Trigger Action Based Upon the Query Status of the Form

Validation
The Effects of the Validation Unit upon a Form
Employing Additional Forms Features for Validation
Controlling Validation Using Triggers
Supplementing Default Validation

Navigation
Distinguishing Between Internal and External Navigation
Describing and Using the Navigation Triggers
Writing Navigation in Triggers

Transaction Processing
Explaining the Process Used by Oracle Forms Developer to Apply Users¿ Changes to the Database
Describing the Commit Sequence of Events
Supplementing Transaction Processing
Allocating Sequence Numbers to Records as They Are Applied to Tables

Writing Flexible Code
Defining Flexible Code
The Advantages of Using System Variables
Identifying Built-In Subprograms that Assist Flexible Coding
Referencing Objects by Internal ID
Referencing Items Indirectly

Designing and Building Reports
The basic techniques
Setting default options
Using the Report Wizard
Creating the report data model

Designing report layouts
Specifying and developing layout objects
Editing and manipulating layout objects
Creating master-detail and tabular reports

Applying advanced techniques
Using triggers for validating parameters
Manipulating report format dynamically using triggers

Developing Charts
Examining Graphics Builder components
Chart items and their properties
Data queries
Categories and values

Building graphical displays
Editing the layout
Defining chart items and specifying queries used for populating the chart
Testing and executing displays

Creating drill-down charts
Passing parameters between master and detail charts
Using layers to navigate between drill-down chart levels

Building Applications
Integrating Forms and Reports modules
Creating integrated applications
Passing parameters between Form Builder modules