Oracle Application Developer (D2K) Training

• Introduction to SQL
• Program with PL/SQL
• Build Applications with Oracle Forms
• Build Reports

• Understand the features and basic concepts of relational databases.
• Use data manipulation statements for performing various operations such as sorting, modifying, and retrieving data.
• Customize output using single-row functions, conversion functions and conditional expressions.
• Consolidate and display data from multiple tables using Joins and Group functions.
• Use data definition language to create and manage tables.
• Manage schema objects and control database access to specific objects.
• Create indexes and constraints; and use regular expression.
• Perform data management in different time zones.

• Understand PL/SQL benefits, identifiers, variables, and executable statements.
• Use loops and control statements to control the flow of data.
• Group related constructs, generate screen output, file output, and mail output through PL/SQL packages.
• Create triggers and events to handle business challenges.
• Create anonymous PL/SQL blocks, functions and stored procedures.

• Using Oracle Forms Builder 11g.
• Creating form modules and enhance applications with GUI controls and triggers.
• Troubleshoot applications by using forms debugger
• Use the Forms Debugger to troubleshoot applications.
• Enable user input validation and accordingly display error messages.
• Interact with client computers through WebUtil

• Identify data elements, use report parameters and retrieve data to run a report.
• Familiar with Oracle Reports Developer
• Creating and enhancing paper report using graphs and PL/SQL trigger code
• Embed graphs in a report
• Implement Matrix Data Model
• Monitor and maximize report performance
• Build efficient and tune reports to run at different development and run-time environments
• Manage report templates, create mailing labels and letters

• PL/SQL Developer
• Forms Developer
• Application Developer
• Report Developer
• System Analyst
• Technical Consultant
• Business Intelligence Developer

Oracle Database: Introduction to SQL

Introduction to Oracle Database

• List the features of Oracle Database 12c
• Discuss the basic design, theoretical, and physical aspects of a relational database
• Categorize the different types of SQL statements
• Describe the data set used by the course
• Log on to the database using SQL Developer environment
• Save queries to files and use script files in SQL Developer

Retrieve Data using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Generate a report of data from the output of a basic SELECT statement
• Select All Columns
• Select Specific Columns
• Use Column Heading Defaults
• Use Arithmetic Operators
• Understand Operator Precedence
• Learn the DESCRIBE command to display the table structure

Learn to Restrict and Sort Data

• Write queries that contain a WHERE clause to limit the output retrieved
• List the comparison operators and logical operators that are used in a WHERE clause
• Describe the rules of precedence for comparison and logical operators
• Use character string literals in the WHERE clause
• Write queries that contain an ORDER BY clause to sort the output of a SELECT statement
• Sort output in descending and ascending order

Usage of Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC, and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the DATE functions

Invoke Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

Aggregate Data Using the Group Functions

• Use the aggregation functions to produce meaningful reports
• Divide the retrieved data in groups by using the GROUP BY clause
• Exclude groups of data by using the HAVING clause

Display Data From Multiple Tables Using Joins

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

Use Sub-queries to Solve Queries

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

The SET Operators

• Describe the SET operators
• Use a SET operator to combine multiple queries into a single query
• Control the order of rows returned

Data Manipulation Statements

• Describe each DML statement
• Insert rows into a table
• Change rows in a table by the UPDATE statement
• Delete rows from a table with the DELETE statement
• Save and discard changes with the COMMIT and ROLLBACK statements
• Explain read consistency

Use of DDL Statements to Create and Manage Tables

• Categorize the main database objects
• Review the table structure
• List the data types available for columns
• Create a simple table
• Decipher how constraints can be created at table creation
• Describe how schema objects work

Other Schema Objects

• Create a simple and complex view
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Control User Access

• Differentiate system privileges from object privileges
• Create Users
• Grant System Privileges
• Create and Grant Privileges to a Role
• Change Your Password
• Grant Object Privileges
• How to pass on privileges?
• Revoke Object Privileges

Management of Schema Objects

• Add, Modify, and Drop a Column
• Add, Drop, and Defer a Constraint
• How to enable and Disable a Constraint?
• Create and Remove Indexes
• Create a Function-Based Index
• Perform Flashback Operations
• Create an External Table by Using ORACLE_LOADER and by Using ORACLE_DATAPUMP
• Query External Tables

Manage Objects with Data Dictionary Views

• Explain the data dictionary
• Use the Dictionary Views
• USER_OBJECTS and ALL_OBJECTS Views
• Table and Column Information
• Query the dictionary views for constraint information
• Query the dictionary views for view, sequence, index and synonym information
• Add a comment to a table
• Query the dictionary views for comment information

Manipulate Large Data Sets

• Use Subqueries to Manipulate Data
• Retrieve Data Using a Subquery as Source
• Insert Using a Subquery as a Target
• Usage of the WITH CHECK OPTION Keyword on DML Statements
• List the types of Multitable INSERT Statements
• Use Multitable INSERT Statements
• Merge rows in a table
• Track Changes in Data over a period of time

Data Management in different Time Zones

• Time Zones
• CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Compare Date and Time in a Session’s Time Zone
• DBTIMEZONE and SESSIONTIMEZONE
• Difference between DATE and TIMESTAMP
• INTERVAL Data Types
• Use EXTRACT, TZ_OFFSET and FROM_TZ
• Invoke TO_TIMESTAMP,TO_YMINTERVAL and TO_DSINTERVAL

Retrieve Data Using Sub-queries

• Multiple-Column Subqueries
• Pairwise and Non-pairwise Comparison
• Scalar Subquery Expressions
• Solve problems with Correlated Subqueries
• Update and Delete Rows Using Correlated Subqueries
• The EXISTS and NOT EXISTS operators
• Invoke the WITH clause
• The Recursive WITH clause

Regular Expression Support

• Use the Regular Expressions Functions and Conditions in SQL
• Use Meta Characters with Regular Expressions
• Perform a Basic Search using the REGEXP_LIKE function
• Find patterns using the REGEXP_INSTR function
• Extract Substrings using the REGEXP_SUBSTR function
• Replace Patterns Using the REGEXP_REPLACE function
• Usage of Sub-Expressions with Regular Expression Support
• Implement the REGEXP_COUNT function

Oracle Database: Program with PL/SQL

Introduction to PL/SQL

• Overview of PL/SQL
• Identify the benefits of PL/SQL Subprograms
• Overview of the types of PL/SQL blocks
• Create a Simple Anonymous Block
• How to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

• List the different Types of Identifiers in a PL/SQL subprogram
• Usage of the Declarative Section to Define Identifiers
• Use variables to store data
• Identify Scalar Data Types
• The %TYPE Attribute
• What are Bind Variables?
• Sequences in PL/SQL Expressions

Write Executable Statements

• Describe Basic PL/SQL Block Syntax Guidelines
• Learn to Comment the Code
• Deployment of SQL Functions in PL/SQL
• How to convert Data Types?
• Describe Nested Blocks
• Identify the Operators in PL/SQL

Interaction with the Oracle Server

• Invoke SELECT Statements in PL/SQL
• Retrieve Data in PL/SQL
• SQL Cursor concept
• Avoid Errors by using Naming Conventions when using Retrieval and DML Statements
• Data Manipulation in the Server using PL/SQL
• Understand the SQL Cursor concept
• Use SQL Cursor Attributes to Obtain Feedback on DML
• Save and Discard Transactions

Control Structures

• Conditional processing using IF Statements
• Conditional processing using CASE Statements
• Describe simple Loop Statement
• Describe While Loop Statement
• Describe For Loop Statement
• Use the Continue Statement

Composite Data Types

• Use PL/SQL Records
• The %ROWTYPE Attribute
• Insert and Update with PL/SQL Records
• INDEX BY Tables
• Examine INDEX BY Table Methods
• Use INDEX BY Table of Records

Explicit Cursors

• What are Explicit Cursors?
• Declare the Cursor
• Open the Cursor
• Fetch data from the Cursor
• Close the Cursor
• Cursor FOR loop
• The %NOTFOUND and %ROWCOUNT Attributes
• Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

• Understand Exceptions
• Handle Exceptions with PL/SQL
• Trap Predefined Oracle Server Errors
• Trap Non-Predefined Oracle Server Errors
• Trap User-Defined Exceptions
• Propagate Exceptions
• RAISE_APPLICATION_ERROR Procedure

Stored Procedures

• Create a Modularized and Layered Subprogram Design
• Modularize Development With PL/SQL Blocks
• Understand the PL/SQL Execution Environment
• List the benefits of using PL/SQL Subprograms
• List the differences between Anonymous Blocks and Subprograms
• Create, Call, and Remove Stored Procedures
• Implement Procedures Parameters and Parameters Modes
• View Procedure Information

Stored Functions and Debugging Subprograms

• Create, Call, and Remove a Stored Function
• Identify the advantages of using Stored Functions
• Identify the steps to create a stored function
• Invoke User-Defined Functions in SQL Statements
• Restrictions when calling Functions
• Control side effects when calling Functions
• View Functions Information
• How to debug Functions and Procedures?

Packages

• Listing the advantages of Packages
• Describe Packages
• What are the components of a Package?
• Develop a Package
• How to enable visibility of a Package’s Components?
• Create the Package Specification and Body using the SQL CREATE Statement and SQL Developer
• Invoke the Package Constructs
• View the PL/SQL Source Code using the Data Dictionary

Deploying Packages

• Overloading Subprograms in PL/SQL
• Use the STANDARD Package
• Use Forward Declarations to solve Illegal Procedure Reference
• Implement Package Functions in SQL and Restrictions
• Persistent State of Packages
• Persistent State of a Package Cursor
• Control side effects of PL/SQL Subprograms
• Invoke PL/SQL Tables of Records in Packages

Implement Oracle-Supplied Packages in Application Development

• What are Oracle-Supplied Packages?
• Examples of some of the Oracle-Supplied Packages
• How does the DBMS_OUTPUT Package work?
• Use the UTL_FILE Package to Interact with Operating System Files
• Invoke the UTL_MAIL Package
• Write UTL_MAIL Subprograms

Dynamic SQL

• The Execution Flow of SQL
• What is Dynamic SQL?
• Declare Cursor Variables
• Dynamically Executing a PL/SQL Block
• Configure Native Dynamic SQL to Compile PL/SQL Code
• How to invoke DBMS_SQL Package?
• Implement DBMS_SQL with a Parameterized DML Statement
• Dynamic SQL Functional Completeness

Design Considerations for PL/SQL Code

• Standardize Constants and Exceptions
• Understand Local Subprograms
• Write Autonomous Transactions
• Implement the NOCOPY Compiler Hint
• Invoke the PARALLEL_ENABLE Hint
• The Cross-Session PL/SQL Function Result Cache
• The DETERMINISTIC Clause with Functions
• Usage of Bulk Binding to Improve Performance

Triggers

• Describe Triggers
• Identify the Trigger Event Types and Body
• Business Application Scenarios for Implementing Triggers
• Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
• Identify the Trigger Event Types, Body, and Firing (Timing)
• Differences between Statement Level Triggers and Row Level Triggers
• Create Instead of and Disabled Triggers
• How to Manage, Test and Remove Triggers?

Creating Compound, DDL, and Event Database Triggers

• What are Compound Triggers?
• Identify the Timing-Point Sections of a Table Compound Trigger
• Understand the Compound Trigger Structure for Tables and Views
• Implement a Compound Trigger to Resolve the Mutating Table Error
• Comparison of Database Triggers to Stored Procedures
• Create Triggers on DDL Statements
• Create Database-Event and System-Events Triggers
• System Privileges Required to Manage Triggers

PL/SQL Compiler

• What is the PL/SQL Compiler?
• Describe the Initialization Parameters for PL/SQL Compilation
• List the new PL/SQL Compile Time Warnings
• Overview of PL/SQL Compile Time Warnings for Subprograms
• List the benefits of Compiler Warnings
• List the PL/SQL Compile Time Warning Messages Categories
• Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
• View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

Manage Dependencies

• Overview of Schema Object Dependencies
• Query Direct Object Dependencies using the USER_DEPENDENCIES View
• Query an Object’s Status
• Invalidation of Dependent Objects
• Display the Direct and Indirect Dependencies
• Fine-Grained Dependency Management in Oracle Database 12c
• Understand Remote Dependencies
• Recompile a PL/SQL Program Unit

Oracle Fusion Middleware 11g: Build Applications with Oracle Forms

Running a Forms Application

• Running a Form
• Identifying the Data Elements
• Navigating a Forms Application
• Using the Modes of Operation
• Querying Data
• Inserting, Updating, and Deleting Records
• Saving Changes
• Displaying Errors

Working in the Forms Builder Environment

• Forms Builder Key Features
• Forms Builder Components
• Navigating the Forms Builder Interface
• Forms Builder Module Object Hierarchy
• Customizing Your Forms Builder Session
• Forms Executables and Module Types
• Defining Environment Variables
• Testing a Form with the Run Form Button

Creating a Basic Form Module

• Creating a New Form Module
• Creating a New Data Bock
• Using Template Forms
• Saving and Compiling a Form Module
• Module Types and Storage Formats
• Deploying a Form Module
• Producing Documentation

Creating a Master-Detail Form

• Creating Data Blocks with Relationships
• Running a Master-Detail Form Module
• Modifying the Structure of a Data Block
• Modifying the Layout of a Data Block

Working Data Blocks and Frames

• Managing Object Properties
• Creating Visual Attributes
• Controlling the Behavior and Appearance of Data Blocks
• Controlling Frame Properties
• Displaying Multiple Property Palettes
• Setting Properties on Multiple Objects
• Copying Properties
• Creating Control Blocks

Working with Text Items

• Creating a Text Item
• Modifying the Appearance of a Text Item
• 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 to a Text Item
• Displaying Helpful Messages

Creating LOVs and Editors

• LOVs and Record Groups
• Creating an LOV Manually
• Using the LOV Wizard to Create an LOV
• Setting LOV Properties
• LOV Column Mapping
• Defining an Editor
• Setting Editor Properties
• Associating an Editor with a Text Item

Creating Additional Input Items

• Input Items Overview
• Creating a Check Box
• Creating a List Item
• Creating a Radio Group

Creating Noninput Items

• Noninput Items Overview
• Creating a Display Item
• Creating an Image Item
• Creating a Push Button
• Creating a Calculated Item
• Creating a Hierarchical Tree Item
• Creating a Bean Area Item

Creating Windows and Content Canvases

• Displaying a Form Module in Multiple Windows
• Creating a New Window
• Displaying a Form Module on Multiple Layouts
• Creating a New Content Canvas

Working with Other Canvas Types

• Overview of Canvas Types
• Creating a Stacked Canvas
• Creating a Toolbar
• Creating a Tab Canvas

Producing and Debugging Triggers

• Trigger Overview
• Creating Triggers in Forms Builder
• Specifying Execution Hierarchy
• PL/SQL Editor Features
• Using the Database Trigger Editor
• Using Variables in Triggers
• Adding Functionality with Built-in Subprograms
• Using the Forms Debugger

Adding Functionality to Items

• Coding Item Interaction Triggers
• Interacting with Noninput Items

Displaying Run-Time Messages and Alerts

• Built-Ins and Handling Errors
• Controlling System Messages
• The FORM_TRIGGER_FAILURE Exception
• Triggers for Intercepting System Messages
• Creating and Controlling Alerts
• Handling Server Errors

Using Query Triggers

• Query Processing Overview
• SELECT Statements Issued During Query Processing
• Setting WHERE and ORDER BY clauses and ONETIME_WHERE property
• Writing Query Triggers
• Query Array Processing
• Coding Triggers for Enter-Query Mode
• Overriding Default Query Processing
• Obtaining Query Information at Run Time

Validating User Input

• Validation Process
• Controlling Validation by Using Properties
• Controlling Validation by Using Triggers
• Performing Client-Side Validation with PJCs
• Tracking Validation Status
• Using Built-ins to Control When Validation Occurs

Controlling Navigation

• Using Object Properties to Control Navigation
• Writing Navigation Triggers
• Avoiding the Navigation Trap
• Using Navigation Built-Ins in Triggers

Overriding or Supplementing Transaction Processing

• Transaction Processing Overview
• Using Commit Triggers
• Testing the Results of Trigger DML
• DML Statements Issued during Commit Processing
• Overriding Default Transaction Processing
• Getting and Setting the Commit Status
• Implementing Array DML

Writing Flexible Code

• What is Flexible Code?
• Using System Variables for Flexible Coding
• Using Built-in Subprograms for Flexible Coding
• Copying and Subclassing Objects and Code
• Referencing Objects by Internal ID
• Referencing Items Indirectly

Sharing Objects and Code

• Benefits of Reusable Objects and Code
• Working with Property Classes
• Working with Object Groups
• Copying and Subclassing Objects and Code
• Working with Object Libraries
• Working with SmartClasses
• Reusing PL/SQL
• Working with PL/SQL Libraries

Using WebUtil to Interact with the Client

• Benefits of WebUtil
• Integrating WebUtil into a Form
• Interacting with the Client

Introducing Multiple Form Applications

• Multiple Form Applications Overview
• Starting Another Form Module
• Defining Multiple Form Functionality
• Sharing Data among Modules

Oracle Reports Developer 10g: Build Reports

Introduction to Oracle Reports Developer

• Business Intelligence
• Enterprise Reporting
• Oracle Reports Developer
• Oracle Database 10g
• Oracle Developer Suite 10g
• Oracle Application Server 10g
• OracleAS Reports Services
• OracleAS Reports Services Architecture for the Web

Designing and Running Reports

• Understanding User Requirements
• Designing Reports
• Tabular
• Master-Detail
• Master with Two Details
• Matrix
• Retrieving and Sharing Data
• Running a Report

Exploring Oracle Reports Developer

• Reports Developer Executables
• Invoking Reports Builder
• Reports Builder Modules
• Report Data and Layout
• Reports Builder Components
• Object Navigator
• Report-Level Objects
• Data Model Objects

Creating a Paper Report

• Report Module Components
• Building a Paper Report
• Viewing the Paper Report Output
• Saving the Report Definition
• Reentering the Wizard
• Creating Break Reports
• Break Report Labels
• Creating Mailing Labels and Letters

Enhancing a Basic Paper Report

• What Is the Paper Design?
• The Paper Design Window
• Modifying a Report
• Aligning Columns
• Setting a Format Mask
• Manipulating Objects
• Modifying Visual Attributes
• Applying Conditional Formatting

Managing Report Templates

• Using Report Templates
• Modifying a Template
• Customizing the Template Margin
• Customizing the Template Body
• Adding Web Links to a Template for Report HTML Output
• Predefining Your Own Templates
• Adding a Template Preview Image

Creating a Web Report

• What Is JSP Technology?
• JSP Advantages
• Simple JSP Example
• Building a Web Report
• Using the Report Wizard
• Report Editor: Web Source View
• JSP Tags
• Web Source Example

Enhancing Reports Using the Data Model: Queries and Groups

• The Data Model Objects
• Modifying Properties of a Query
• Applying Changes
• Changing the Group Structure
• Group Hierarchy
• Ordering Data in a Group
• Query Modifications
• Filtering Data in a Group

Enhancing Reports Using the Data Model: Data Sources

• Data Source Types
• Pluggable Data Sources
• Using XML as a Data Source
• Document Type Definition File
• OLAP Data Source
• Using Text as a Data Source
• Using JDBC as a Data Source
• Using REF Cursor Queries

Enhancing Reports Using the Data Model: Creating Columns

• Data Model Columns
• Maintaining Data Source Columns
• Producing File Content Output
• Creating a Column
• Creating Summary Columns
• Displaying Subtotals
• Displaying Percentages
• Creating a Formula Column

Enhancing Reports Using the Paper Layout

• Viewing the Paper Layout
• Designing Multipanel Reports
• Printing Multipanel Reports
• Different Objects in the Paper Layout
• The Paper Layout Layers
• Report Processing
• Paper Layout Tools
• Report Bursting

Controlling the Paper Layout: Common Properties

• Modifying Paper Layout Object Properties
• Common Layout Properties
• Sizing Objects
• Anchors
• Layout Object Relationships
• Pagination Icons in the Paper Layout
• Controlling Print Frequency
• Using Format Triggers

Controlling the Paper Layout: Specific Properties

• Properties of a Repeating Frame
• Specifying Print Direction
• Controlling the Number of Records per Page
• Controlling Spacing Between Records
• Minimum Widow Records
• System Variables
• Valid Source Columns
• Displaying File Contents

Web Reporting

• Comparing Static and Dynamic Reporting
• Adding Dynamic Content
• Creating a Report Block
• Invoking the Report Block Wizard
• Examining the Web Source Code
• rw:foreach Tag
• rw:field Tag
• Customizing Reports JSPs

Extending Functionality Using XML

• Why Use XML Report Definitions?
• Creating XML Report Definitions
• Partial Report Definitions: Format Modification Example
• Partial Report Definitions: Format Exception Example
• Full Report Definition: Data Model Modification Example
• Running XML Report Definitions
• Debugging XML Report Definitions

Creating and Using Report Parameters

• Creating User Parameters
• Referencing Parameters in a Report Query
• Using Bind References
• Using Lexical References
• Hints and Tips When Referencing Parameters
• Creating a List of Values
• Referencing System Parameters
• Building a Paper Parameter Form

Embedding a Graph in a Report

• Adding a Graph to a Paper Report
• Adding a Graph to a Web Report
• Selecting the Graph Type
• Selecting the Graph Data
• Adding Options to the Graph
• Customizing Web Graphs
• The rw:graph Tag
• Customizing Graphs Using the Graph.XML File

Enhancing Matrix Reports

• The Matrix Data Model
• The Matrix Paper Layout
• Creating Matrix Summaries
• Creating the Matrix Manually
• The Matrix with Group Data Model
• The Matrix with Group Layout
• Building a Nested Matrix
• Nested Matrix Paper Layout

Coding PL/SQL Triggers

• Types of Triggers in Reports
• Trigger Code
• Using Report Triggers
• Using Data Model Triggers: PL/SQL Group Filter
• Using Data Model Triggers: Parameter Validation
• Using Layout Triggers
• Using Format Triggers
• Event-Based Reporting

Extending Functionality Using the SRW Package

• Contents of the SRW Package
• Outputting Messages
• Executing a Nested Report
• Restricting Data
• Initializing Fields
• Creating a Table of Contents
• Performing DDL Statements
• Setting Format Attributes

Maximizing Performance Using OracleAS Reports Services

• Running Reports Using OracleAS Reports Services
• Report Request Methods
• Oracle Application Server Components
• Enabling Single Sign-On Access
• Running the Web Layout: JSP Run-time Architecture
• Running the Paper Layout: Servlet Run-time Architecture
• Running a Paper Report on the Web
• Queue Manager

Building Reports: Efficiency Guidelines

• Tuning Reports
• Performance Measurement
• Non SQL Data Sources
• Investigating the Data Model
• Investigating the Paper Layout
• Running the Report
• Different Development and Run-Time Environments
• Developing Reports to Run in Different GUIs