Complete Syllabus For Oracle Apps Technical Consultant

1.SQL

Basic SQL SELECT Statements

o Basic SELECT Statement

o Selecting All Columns

o Selecting Specific Columns

o Writing SQL Statements

o Arithmetic Expressions

o Using Arithmetic Operators

o Operator Precedence

o Using Parentheses

o Defining a Null Value

o Defining a Column Alias

o Concatenation Operator

o Using the Concatenation Operator

o Duplicate Rows

o Eliminating Duplicate Rows

Restricting and Sorting Data

o Limiting Rows Using a Selection

o Limiting the Rows Selected

o Character Strings and Dates

o Comparison Conditions

o Using Comparison Conditions

o Other Comparison Conditions

o Using the BETWEEN Condition

o Using the IN Condition

o Using the LIKE Condition

o Using the NULL Conditions

o Using the AND Operator

o Using the OR Operator

o Using the NOT Operator

o Rules of Precedence

o ORDER BY Clause

o Sorting in Descending Order

o Sorting by Multiple Columns

Single-Row Functions

o SQL Functions

o Number Functions

o Using the ROUND Function

o Using the MOD Function

o Arithmetic with Dates

o Using Arithmetic Operators with Dates

o Date Functions

o Using Date Functions

o Implicit Data Type Conversion

o Explicit Data Type Conversion

o Using the TO_CHAR Function with Dates

o Using the TO_NUMBER and TO_DATE Functions

o RR Date Format

o NVL Function

o Using the NVL ,NVL2,NULLIF,COALESCE Function

o The CASE Expression

o Using the CASE Expression

o The DECODE Function

Displaying Data from Multiple Tables

o Obtaining Data from Multiple Tables

o Cartesian Products

o Types of Joins
o Joining Tables Using Oracle Syntax

o What is an Equijoin?

o Retrieving Records with Equijoins

o Additional Search Conditions Using the AND Operator 4-10

o Joining More than Two Tables

o Non-Equijoins

o Outer Joins

o Outer Joins Syntax

o Using Outer Joins

o Self Joins

o Joining a Table to Itself

5 Aggregating Data Using Group Functions

o What Are Group Functions?

o Types of Group Functions

o Group Functions Syntax

o Using the avg, sum, min, max, count, distinct Functions

o The GROUP BY Clause Syntax

o Using the GROUP BY Clause

o Grouping by More Than One Column

o Using the HAVING Clause

o Subquery

o The HAVING Clause with Subqueries

o Multiple-Row Subqueries

o Using the ANY Operator in Multiple-Row Subqueries

o Using the ALL Operator in Multiple-Row Subqueries

7 Manipulating Data

o Data Manipulation Language

o The INSERT Statement Syntax

o Inserting New Rows

o Inserting Rows with Null Values

o Inserting Special Values

o Inserting Specific Date Values

o Creating a Script

o Copying Rows from Another Table

o The UPDATE Statement Syntax

o The DELETE Statement

o Using a Subquery in an INSERT Statement

o Using the WITH CHECK OPTION Keyword on DML Statements

o The MERGE Statement

o Merging Rows

o Database Transactions

o COMMIT and ROLLBACK Statements

o Controlling Transactions

o Rolling Back Changes to a Marker

o Implicit Transaction Processing

o Data Before COMMIT or ROLLBACK

8 Creating and Managing Tables

o Database Objects 9-3

o Naming Rules

o The CREATE TABLE Statement

o Referencing Another Users Tables

o The DEFAULT Option

o Creating Tables

o Tables in the Oracle Database

o Querying the Data Dictionary

o Data Types

o Creating a Table by Using a Subquery

o Creating a Table by Using a Subquery

o Adding a Column

o Modifying a Column

o Dropping a Column

o Dropping a Table

o Changing the Name of an Object

o Truncating a Table

o Adding Comments to a Table

9 Including Constraints

o What are Constraints?

o Constraint Guidelines

o Defining Constraints

o The NOT NULL Constraint

o The UNIQUE Constraint

o The PRIMARY KEY Constraint

o The FOREIGN KEY Constraint

o FOREIGN KEY Constraint Keywords

o The CHECK Constraint

o Adding a Constraint Syntax

o Adding a Constraint
o Dropping a Constraint

o Disabling Constraints

o Enabling Constraints

o Cascading Constraints

10 Creating Views

o Database Objects

o What is a View?

o Simple Views and Complex Views

o Creating a View

o Retrieving Data from a View

o Querying a View

o Modifying a View

o Creating a Complex View

o Rules for Performing DML Operations on a View

o Using the WITH CHECK OPTION Clause

o Denying DML Operations

o Removing a View

o Inline Views

o Top-N Analysis

11 Other Database Objects

o The CREATE SEQUENCE Statement Syntax

o reating a Sequence

o Confirming Sequences

o NEXTVAL and CURRVAL Pseudocolumns

o Using a Sequence

o Modifying a Sequence

o Guidelines for Modifying a Sequence

o Removing a Sequence

o What is an Index?

o How Are Indexes Created?

o Creating an Index

o When to Create an Index

o When Not to Create an Index

o Function-Based Indexes

o Removing an Index

o Synonyms

o Creating and Removing Synonyms

12 Controlling User Access

o Objectives
o Controlling User Access

o Privileges

o System Privileges

o Creating Users

2. PLSQL

Introduction to PL/SQL

o Overview of PL/SQL

o Identify the benefits of PL/SQL Subprograms

o Overview of the types of PL/SQL blocks

o Create a Simple Anonymous Block

o How to generate output from a PL/SQL Block?

Declare PL/SQL Identifiers

o List the different Types of Identifiers in a PL/SQL subprogram

o Usage of the Declarative Section to Define Identifiers

o Use variables to store data

o Identify Scalar Data Types

o The %TYPE Attribute

o What are Bind Variables?

Write Executable Statements

o Describe Basic PL/SQL Block Syntax Guidelines

o Learn to Comment the Code

o Deployment of SQL Functions in PL/SQL

o How to convert Data Types?

o Describe Nested Blocks

o Identify the Operators in PL/SQL

Interaction with the Oracle Server

o Invoke SELECT Statements in PL/SQL

o Retrieve Data in PL/SQL

o SQL Cursor concept

o Avoid Errors by using Naming Conventions when using Retrieval and DML Statements

o Data Manipulation in the Server using PL/SQL

o Understand the SQL Cursor concept

o Use SQL Cursor Attributes to Obtain Feedback on DML

o Save and Discard Transactions

Control Structures

o Conditional processing using IF Statements

o Conditional processing using CASE Statements

o Describe While Loop Statement

o Describe For Loop Statement

Composite Data Types

o Use PL/SQL Records

o The %ROWTYPE Attribute

Explicit Cursors

o What are Explicit Cursors?

o Declare the Cursor

o Open the Cursor

o Fetch data from the Cursor

o Close the Cursor

o Cursor FOR loop

o The %NOTFOUND and %ROWCOUNT Attributes

o Describe the FOR UPDATE Clause and WHERE CURRENT Clause

Exception Handling

o Understand Exceptions

o Handle Exceptions with PL/SQL

o Trap Predefined Oracle Server Errors

o Trap Non-Predefined Oracle Server Errors

o Propagate Exceptions

o RAISE_APPLICATION_ERROR Procedure

Stored Procedures and Functions

o Create, Call, and Remove Stored Procedures

o Implement Procedures Parameters and Parameters Modes

o View Procedure Information

o Create, Call, and Remove a Stored Function

o Invoke User-Defined Functions in SQL Statements

o How to debug Functions and Procedures?

Packages

o Listing the advantages of Packages

o Describe Packages

o What are the components of a Package?

o Develop a Package

o How to enable visibility of a Packages Components?

o Create the Package Specification and Body

o Invoke the Package Constructs

o View the PL/SQL Source Code using the Data Dictionary

Implement Oracle-Supplied Packages in Application Development

o Examples of some of the Oracle-Supplied Packages

o How does the DBMS_OUTPUT Package work?

o Use the UTL_FILE Package to Interact with Operating System Files

Dynamic SQL

o The Execution Flow of SQL

o What is Dynamic SQL?

o Declare Cursor Variables

o Dynamically Executing a PL/SQL Block

o Configure Native Dynamic SQL to Compile PL/SQL Code

o How to invoke DBMS_SQL Package?

o Implement DBMS_SQL with a Parameterized DML Statement

o Dynamic SQL Functional Completeness

Triggers

o Describe Triggers

o Identify the Trigger Event Types and Body

o Business Application Scenarios for Implementing Triggers

o Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer

o Identify the Trigger Event Types, Body, and Firing (Timing)

o Differences between Statement Level Triggers and Row Level Triggers

o How to Manage, Test and Remove Triggers?

3.D2K

4.Apps Technical

AOL:

o User Creation

o Responsibility

o Data Group

o Who Column

o Menu Customizations

o Request Group

o Request Set (How to Create Request Set)

o SRS Window

o Value Set

o How to Get Name of table From Front End

o Directory Structure of Oracle Apps

o How to Attach Reports in Apps

o How to Attach Forms in Apps

o Concurrent Programs

o Incompatibility in Concurrent Programs

o Flex fields (Key and Descriptive Flex Fields)

o How to Enable DFF in the Forms.

o Profile options

o Importance of P_CONC_REQUEST_ID Parameter in reports

o How to Register PL/SQL Procedure in apps

o How to Register SQL*LOADER in Oracle Applications.

o FNDLOAD Utility

o Telnet

o FTP

o Forms Personalization

o AIM Standard for Documentations (MD50 , MD70)

o Multiple Organization Feature.

XML Publisher:

o Creating Reports Using XML Publisher.

Workflow:

o Oracle Workflow Builder.

o Understanding Workflow Definition.

P2P Cycle:

o What is P2P Cycle ?

o End- to end Flow.

O2C Cycle:

o What is O2C Cycle?

o End- to end Flow.