Course Topics: Introduction Describing the Life Cycle Development Phases Defining a Relational

Course Topics:
Introduction
Describing the Life Cycle Development Phases
Defining a Relational Database
Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
Describing How SQL Is Used in the Oracle Product Set

Writing a Basic SQL Statement
Describing the SQL Select Capabilities
Executing a Basic Select Statement with the Mandatory Clauses
Differentiating Between SQL and iSQL*Plus Commands
Restricting and Sorting Data
Limiting the Rows Retrieved by a Query
Sorting the Rows Retrieved by a Query
Single Row Functions
Describing Various Types of Functions Available in SQL
Using a Variety of Character, Number, and Date Functions in SELECT Statements
Explaining What the Conversion Functions Are and How They Are Used
Using Control Statements
Displaying Data from Multiple Tables
Writing SELECT Statements to Access Data from More Than One Table
Describing the Cartesian Product
Describing and Using the Different Types of Joins
Writing Joins Using the Tips Provided
Aggregating Data by Using Group Functions
Identifying the Different Group Functions Available
Explaining the Use of Group Functions
Grouping Data by Using the GROUP BY Clause
Writing Subqueries
Describing the Types of Problems That Subqueries Can Solve
Describing Subqueries
Listing the Types of Subqueries
Writing Single-Row and Multi-Row Subqueries
Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved
Producing Readable Output with iSQL*Plus
Producing Queries That Require an Input Variable
Customizing the iSQL*Plus Environment
Producing More Readable Output
Creating and Executing Script Files
Manipulating Data
Describing Each Data Manipulation Language (DML) Command
Inserting Rows into a Table
Updating Rows in a Table
Deleting Rows from a Table
Merging Rows into a Table
Controlling Transactions
Describing Transaction Processing
Describing Read Consistency and Implicit and Explicit Locking
Creating and Managing Tables
Describing the Main Database Objects
Creating Tables
Describing the Oracle Data Types
Altering Table Definitions
Dropping, Renaming, and Truncating Tables
Including Constraints
Describing Constraints
Creating and Maintaining Constraints
Creating Views
Describing Views and Their Uses
Creating a View
Retrieving Data by Means of a View
Inserting, Updating, and Deleting Data Through Views
Dropping Views
Altering the Definition of a View
Inline Views
Top 'N' Analysis
Other Database Objects
Creating, Maintaining, and Using Sequences
Creating and Maintaining Indexes
Creating Private and Public Synonyms
Controlling User Access
Understanding the Concepts of Users, Roles, and Privileges
Granting and Revoking Object Privileges
Creating Roles and Granting Privileges to Roles
Creating Synonyms for Ease of Table Access
SQL Workshop
Applying Techniques Learned in This Course
Preparing for Future Oracle Courses
Using Set Operators
Describing the Set Operators
Obeying the Set Operators Rules and Guidelines
Using a Set Operator to Combine Multiple Queries into a Single Subquery
Controlling the Order of Rows Returned
Oracle 9i Datetime Functions
Using DATETIME Functions
Using the NVL2 Function to Handle NULL Values
Enhancements to the GROUP BY Clause
Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
Using the WITH Clause
Advanced Subqueries
Multiple-Column Subqueries
Writing a Subquery in the FROM Clause
Writing and describing Correlated Subquery
Using EXISTS and NOT EXISTS Operators
Updating and Deleting Rows Using Correlated Subqueries
Using Scalar Subqueries in SQL
Hierarchical Retrieval
Discussing the Benefits of the Hierarchical Query
Ordering the Rows Retrieved by a Query in a Hierarchical Manner
Formatting Hierarchical Data so That It Is Easy to Read
Excluding Branches from the Tree Structure
Oracle9i Extensions to DML and DDL Statements
Discussing Multitable Inserts
Creating and Using External Tables
Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint

PL/SQL

Describing PL/SQL
Describing the Use of PL/SQL for the Developer and the Database Administrator
Explaining the Benefits of PL/SQL
PL/SQL program constructs
PL/SQL anonymous block structure
Subprogram block structure
Course objectives and overview
Declaring Variables
Recognizing the Basic PL/SQL Block and Its Sections
Describing the Significance of Variables in PL/SQL
Distinguishing Between PL/SQL and Non-PL/SQL Variables
Declaring Variables and Constants
Executing a PL/SQL Block
Writing Executable Statements
Recognizing the Significance of the Executable Section
Writing Statements Within the Executable Section
Describing the Rules of Nested Blocks
Executing and Testing a PL/SQL Block
Using Coding Conventions
Interacting with the Oracle Server
Writing a Successful SELECT Statement in PL/SQL
Declaring the Data type and Size of a PL/SQL Variable Dynamically
Writing Data Manipulation Language (DML) Statements in PL/SQL
Controlling Transactions in PL/SQL
Determining the Outcome of SQL DML Statements
Writing Control Structures
Identifying the Uses and Types of Control Structures
Constructing an IF Statement
Constructing and Identifying Different Loop Statements
Controlling Block Flow Using Nested Loops and Labels
Using Logic Tables
Working with Composite Data types
Creating User-Defined PL/SQL Records
Creating a PL/SQL Table
Creating a PL/SQL Table of Records
Differentiating Among Records, Tables, and Tables of Records
Writing Explicit Cursors
Using a PL/SQL Record Variable
Distinguishing Between the Implicit and Explicit Cursor
Writing a Cursor FOR Loop
Advanced Explicit Cursor Concepts
Writing a Cursor that Uses Parameters
Determining When a FOR UPDATE Clause in a Cursor Is Required
Using a PL/SQL Table Variable
Using a PL/SQL Table of Records
Handling Exceptions
Defining PL/SQL Exceptions
Recognizing Unhandled Exceptions
Listing and Using Different Types of PL/SQL Exception Handlers
Trapping Unanticipated Errors
Describing the Effect of Exception Propagation in Nested Blocks
Customizing PL/SQL Exception Messages
Creating Procedures
Describe the uses of procedures
Create procedures
Create procedures with arguments
Invoke a procedure
Remove a procedure
Creating Functions
Describe the uses of functions
Create a function
Invoke a function
Remove a function
Differentiate between a procedure and a function
Managing Subprograms
Describe system privilege requirements
Describe object privilege requirements
Query the relevant data dictionary views
Debug subprograms
Creating Packages
Describe packages and list their possible components
Create packages that include public and private subprograms, as well as global and local variables
Invoke objects in a package
Remove packages
More Package Concepts
Write packages that use the overloading feature of PL/SQL
Avoid errors with mutually referential subprograms
Initialize variables with a one-time-only procedure
Specify the purity level of packaged functions
Describe the persistent state of packaged variables, cursors, tables, and records
Query the relevant data dictionary views
Using Oracle-Supplied Packages
Overview of Oracle-supplied packages
View examples of some supplied packages
Write dynamic SQL
Creating Database Triggers
Describe different types of triggers
Describe database triggers and their use
Create database triggers
Describe database trigger firing rules
Drop database triggers
More Trigger Concepts
Create triggers that fire when certain database actions occur
List some of the limitations of database triggers
Determine when to use database triggers or Oracle Server features
Create triggers by using alternative events (notINSERT/UPDATE/DELETE)
Create triggers by using alternative levels (not STATEMENT/ROW)
Query the relevant data dictionary views
Managing Dependencies
Overview of object dependencies
Manage PL/SQL objects for recompilation
Manipulating Large Objects
Compare and contrast LONG/RAW/LONG RAW with large objects (LOBs)
Understand LOBs
Manage binary large file objects (BFILEs)
Use PL/SQL with an LOB
Create a table with LOB columns
Manipulate LOBs
Use DBMS_LOB Oracle-supplied packages
Create a temporary LOB

Introduction to Oracle Forms Developer and Oracle Forms Services
Internet Computing Solutions
What Is the Oracle9i Internet Platform?
Oracle9iAS Architecture and Components
Forms Services Architecture
Oracle9iDS Components
Oracle9i Forms Developer Key Features
Forms Builder Components
Navigating the Forms Builder Interface
Running a Forms Developer Application
Running a Form
Web Form Tester
Starting a Run Time Session
What are the Forms Servlet and Listener Servlet?
What Is the Forms Client?
Defining Forms Environment Variables
What You See at Run Time
Identifying the Data Elements
Working in the Forms Developer Environment
Forms Builder Key Features
Forms Developer Executables
Forms Builder, Forms Compiler
Invoking Forms Executables
Forms Builder Module Types
Forms Builder Module Object Hierarchy
Testing a Form with the Run Form button
Starting OC4J
Creating a Basic Form Module
Creating a New Form Module
Creating and modifying a New Data Block
Modifying the Layout
Template Forms
Saving and Compiling a Form Module
Deploying a Form Module
Moving the Form to the Middle Tier
Generating on the Middle Tier
Working with Data Blocks and Frames
Managing Object Properties
Re-entrant Wizards
Layout Editor
Property Palette
Controlling the Behavior of Data Blocks
Setting Navigation and Database Properties
Controlling the Appearance of Data Blocks
Setting Record, Scrollbar and Frame Properties
Working with Text Items
Creating a Text Item
Modifying Appearance
Controlling the Data of a Text Item
Altering the Navigational Behavior of a Text Item
Enhancing the Relationship between Text Item and Database
Adding Functionality
Displaying Helpful Messages
Creating LOVs and Editors
LOVs and Record Groups
Creating an LOV
LOV Properties
Creating an Editor
Setting Editor Properties
Associating an Editor with a Text Item
Creating Additional Input Items
Creating a Check Box
Setting Check Box Properties
Creating a List Item
Setting List Item Properties
Creating a Radio Group
Setting Radio Group Properties
Creating Noninput Items
Creating a Display Item
Creating an Image Item
Setting Image Item Properties
Creating a Push Button
Setting Button Properties
Creating a Calculated Item
Setting Calculated Item Properties
Creating a Hierarchical Tree Item
Creating Windows and Content Canvases
What Are Windows and Canvases?
Relationship between Windows and Content Canvases
Displaying a Form Module in Multiple Windows
Creating a Window
Setting Window Properties
Displaying a Form Module on Multiple Layouts
Creating a New Content Canvas
Setting Content Canvas Properties
Working with Other Canvas Types
Overview of Canvas Types
Creating a Stacked Canvas
Setting Stacked Canvas Properties
Setting Toolbar Properties
Creating a Toolbar Canvas
Creating a Tab Canvas
Setting Tab Canvas Properties
Introduction to Triggers
Trigger Components
Type, Code and Scope
Trigger Execution Hierarchy
Producing Triggers
Creating Triggers
Setting Trigger Properties
PL/SQL Editor Features
Database Trigger Editor Features
Writing Trigger Code
Adding Functionality with Built-in Subprograms
Using Triggers
Debugging Triggers
The Debugging Process
The Debug Console
Call Stack
Variables
Watch List
Form Values
PL/SQL Packages
Global/System Variables
Adding Functionality to Items
Item Interaction Triggers
Defining Functionality for Input Items
Interacting with Check Boxes
Changing List Items at Run Time
Defining Functionality for Noninput Items
Displaying LOVs from Buttons
Populating Image Items
Populating Hierarchical Trees
Runtime Messages and Alerts
Runtime Messages and Alerts Overview
Built-Ins and Handling Errors
Controlling System Messages
The FORM_TRIGGER_FAILURE Exception
Triggers for Intercepting System Messages
Creating and Controlling Alerts
Handling Errors Raised by the Oracle Database Server
Query Triggers
Query Processing Overview
SELECT Statements Issued During Query Processing
WHERE and ORDER BY clauses
The ONETIME_WHERE property
Writing Query Triggers
Query Array Processing
Coding Triggers for Enter-Query Mode
Overriding Default Query Processing
Validation
Validation Process
Controlling Validation Using Properties
Form-level Properties
Block-level Properties
Item-level Properties
Controlling Validation Using Triggers
When-Validate-Item Trigger
When-Validate-Record Trigger
Navigation
Navigation Overview
Understanding Internal Navigation
Controlling Navigation Using Object Properties
Writing Navigation Triggers
Using Pre- and Post- Triggers
Using When-New-<object>-Instance Triggers
The Navigation Trap
Using Navigation Built-Ins in Triggers
Transaction Processing
Transaction Processing Overview
The Commit Sequence of Events
Characteristics of Commit Triggers
Common Uses of Commit Triggers
The Life of an Update
Delete Validation
Assigning Sequence Numbers
Keeping an Audit Trail
Writing Flexible Code
Using System Variables for Flexible Coding
Using Built-in Subprograms for Flexible Coding
Referencing Objects by Internal ID
Referencing Items Indirectly
Sharing Objects and Code
Benefits of Reusable Objects and Code
What Are Property Classes?
Creating a Property Class
Inheriting from a Property Classes
What Are Object Groups?
Creating and Using Object Groups
Copying and Subclassing Objects and Code
What is an Object Library