ORACLE 11g DEVELOPMENT Training

INTRODUCTION TO ORACLE DATABASE

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

• 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

• 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

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 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 11G: BUILD REPORTS

• Business Intelligence
• Enterprise Reporting
• Oracle Reports Developer
• Oracle Database 11g
• Oracle Developer Suite 11g
• Oracle Application Server 11g
• 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

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