Oracle DataBase

Course Description

Advance SQL /PLSQL Programming, Industrial level Form Builder 6I/10g, Industrial Level Reports Builder 6I/10g, Business Logic Algorithm, Server/Client Configuration, Project Handling and Deployment

course duration
Oracle 4 MONTHs & 1 month internship

Course outline

What Is a Relational Database?

Learn about relational databases.

Be introduced to the Structured Query Language (SQL), which is used to access a database.

Use SQL*Plus, Oracle's interactive text-based tool for running SQL statements.

Briefly see PL/SQL, Oracle's procedural programming language built around SQL. PL/SQL allows you to develop programs that are stored in the database.

Retrieving Information from Database Tables

Retrieve information from one or more database tables using SELECT statements.

Use arithmetic expressions to perform calculations.

Filter rows to just those you are interested in using a WHERE clause.

Sort the rows retrieved from a table.

Using Simple Functions

Storing and Processing Dates and Times

Types of Subqueries

Learn how to place a SELECT statement within an outer SELECT, UPDATE, or DELETE statement. The inner SELECT statement is known as a subquery.

Learn about the different types of subqueries and see how to use them.

See how subqueries allow you to build up very complex statements from simple components.

Advanced Queries

Learn how to use the set operators, which allow you to combine rows returned by two or more queries.

Use the TRANSLATE() function to convert characters in one string to characters in another string based on a set of translation characters.

Use the DECODE() function to search a set of values for a certain value.

Learn how to use the CASE expression to perform if-then-else logic in SQL without having to use PL/SQL.

Learn how to perform queries against data that is organized into a hierarchy.

Use the ROLLUP clause to return rows containing a subtotal for each group.

Use the CUBE clause to return rows containing a subtotal for all combinations of columns along with a total at the end.

Learn about the analytic functions that enable you to perform complex calculations such as finding the top-selling product type for each month, the top salespersons, and so on.

Use the new Oracle Database 10g MODEL clause to perform inter-row calculations.

Changing Table Contents

How to add, modify, and remove rows using the INSERT, UPDATE, and DELETE statements.

That a database transaction may consist of multiple INSERT, UPDATE, and DELETE statements.

How to make the results of your transactions permanent using the COMMIT statement, or undo their results entirely using the ROLLBACK statement.

How an Oracle database can process multiple transactions at the same time.

Database Security

Learn more about users

See how privileges are used to enable users to perform tasks in the database

Explore how privileges are divided into two types: system privileges and object privileges

Learn how system privileges allow you to perform actions such as execute DDL statements

See how object privileges allow you to perform actions such as execute DML statements

Explore how to manage privileges using roles

Creating Tables, Sequences, Indexes, and Views

Learn more about tables

See how to create and use sequences; a sequence generates a series of numbers

Explore how to create and use indexes; an index can improve the performance of queries

Learn how to create and use views, which are predefined queries on one or more tables; among other benefits, views allow you to hide complexity from a user, and implement another layer of security by only allowing a view to access a limited set of data in the tables

Introducing PL/SQL Programming

Block structure

Variables and types

Conditional logic

Loops

Cursors, which hold the results returned by a query

Procedures

Functions

Packages, which may be used to group procedures and functions together in one unit

Database Objects

Be introduced to objects in the database

Learn how to create object types

Use object types to define column objects and object tables

Perform DML operations with objects

Use objects in PL/SQL

Learn how a type may inherit from another type

Define your own constructors

Collections

Be introduced to collections

Learn how to create collection types

Use collection types to define columns in tables

Perform DML operations with collections

Learn how a collection type may contain elements that are also collection types

Use collections in PL/SQL

Examine Oracle10g enhancements to collections

Large Objects

Be introduced to large objects (LOBs)

See files whose content will be used to populate example LOBs

Understand the differences between the different types of LOBs

Create tables containing LOBs

Use LOBs in SQL

Use LOBs in PL/SQL

Use LONG and LONG RAW types

Examine some of the Oracle Database 10g enhancements to LOBs

Oracle Reports 6i/10g

Building Basic Reports

Building a Tabular Report

Building a Mailing Label Report

Building a Form Letter Report

Building a Master/Master Report

Building a Summary Report

Building Group Reports

Building a Single-Query Group Report

Building a Two-Query Group Report

Building an Across Group Report

Building a Group Left Summary Report

Building a Group Left Formula Report

Building Reports with Special Formatting

Building a Wrapped Field Report

Building a Header and Footer Report

Building a Header with Database Values Report

Building a Report with Graphics, Text, and Color

Building a Report that Renumbers Pages by Repeating Frame

Building an Intermixed Fields Report

Building a Report that Suppresses Labels

Building a Report with Conditional Highlighting

Building a Conditional Form Letter Report

Building a Report with Dynamic Graphics

Building Matrix Reports

Building a Matrix Report

Building a Nested Matrix Report

Building a Matrix with Group Above Report

Oracle Form Builder

Introduction to Forms 6i/10g

Running Forms 6i/10g

Introduction

Runtime Dialog

Simplifying Access

Using Forms 6i/10g at Runtime

Interface

Normal and Enter Query Mode

Query Criteria

Counting Query Hits

List of Values

Manipulating Records

Shortcut Keys

Exercise One

Forms 6i/10g Interface

Welcome to the Form Builder

Object Navigator

PL/SQL Libraries

Database Objects

Data Block Wizard in Forms 6i/10g

Layout Wizard in Forms 6i/10g

Layouts in Forms 6i/10g

Types of Layouts

Form Layout in Forms 6i/10g

Tabular Layout in Forms 6i/10g

Grid

Functionality

Compiling Forms in Forms 6i/10g

Errors Opening Modules

Forms Builder

Compiler Errors

Introduction to Windows

MDI Application Windows

Document Windows

Dialog Window

SDI Windows

Canvases in Forms 6i/10g

Content Canvas in Forms 6i/10g

Stacked Canvas in Forms 6i/10g

Tab Canvas in Forms 6i/10g

Toolbar Canvas in Forms 6i/10g

Views in Forms 6i/10g

Blocks in Forms 6i/10g

Data Blocks in Forms 6i/10g

Control Blocks in Forms 6i/10g

Canvas Items in Forms 6i/10g

Program Units in Forms 6i/10g

Procedures in Forms 6i/10g

Functions in Forms 6i/10g

Packages in Forms 6i/10g

Package Body in Forms 6i/10g

Property Palette in Forms 6i/10g

Master-Detail Relations in Forms 6i/10g

Creating Relations

PL/SQL in Forms 6i/10g

Referencing Forms Items

Referencing Stored Programs

Stored Programs

Local Variables

Referencing Local Variables

Performing DML

Forms Packages

Database Packages

The PL/SQL Editor in Forms 6i/10g

Creating Programs

Colour Coding

Triggers in Forms 6i/10g

Positioning Triggers within Forms

Purpose of Form Level Triggers

Purpose of Block Level Triggers

Purpose of Item Level Triggers

Execution Hierarchy of Triggers

Disabling Triggers in Forms 6i/10g

Creating Triggers in Forms 6i/10g

Key Triggers in Forms 6i/10g

Using Key-Others in Forms 6i/10g

When-Validate-Item Trigger