New Book : Oracle Data Integrator 11g Cookbook

I got hold on this book, this is first book i seen on ODI. Currenty reading it and will publish review soon.

Oracle Data Integrator (ODI) is Oracle’s strategic data integration platform for high-speed data transformation and movement between different systems. From high-volume batches, to SOA-enabled data services, to trickle operations, ODI is a cutting-edge platform that offers heterogeneous connectivity, enterprise-level deployment, and strong administrative, diagnostic, and management capabilities.

“Oracle Data Integrator 11g Cookbook” will take you on a journey past your first steps with ODI to a new level of proficiency, lifting the cover on many of the internals of the product to help you better leverage the most advanced features.

  • Chapter 1: Installation, Deployment, and Configuration
    • Introduction
    • Deploying and configuring a standalone agent manually
    • Deploying a JEE ODI Agent
    • Configuring a standalone agent with OPMN
    • Deploying JDBC drivers with a JEE ODI Agent
  • Chapter 2: Defining the Oracle Data Integrator Security
    • Introduction
    • Setting up LDAP security for Oracle Data Integrator
    • Setting external authentication with Oracle Data Integrator
    • Creating users using generic profiles
    • Creating users using non-generic profiles
    • Creating new custom profiles in the Security navigator
  • Chapter 3: Advanced Topology
    • Introduction
    • Creating a new technology
    • Modifying actions to get more from your diagrams
    • Modifying and expanding datatypes
    • Changing the case sensitivity for code generation
    • Best practice – using the Staging Area User to access your target schema
  • Chapter 4: Using Variables
    • Introduction
    • Passing start-up parameters to a scenario using variables
    • Using table names that run in all contexts using getObjectName
    • Using variables to filter data based on a timestamp
    • Using variables in KM options (and reusing the variables in an interface, package, and so on)
    • Using variables in topology
    • Using variables to control loops inside packages
  • Chapter 5: Knowledge Module Internals
    • Introduction
    • Using the substitution passes
    • Using Java variables in KMs
    • Using Java for condition code generation
    • Invoking Java from the KMs
    • Using substitution methods in Java
    • Combining substitution methods in a KM
  • Chapter 6: Inside Knowledge Modules – SCD and CDC
    • Introduction
    • Implementing Slowly Changing Dimensions (SCD) using ODI
    • Modifying a Slowly Changing Dimension KM to allow undefined behaviors
    • Using Changed Data Capture (CDC) – simple
    • Using Changed Data Capture (CDC) – consistent set
    • Using one single interface to load changes that occur in any dimensions
  • Chapter 7: Advanced Coding Techniques
    • Introduction
    • Using diagrams to develop and maintain models
    • Generating DDL from data models
    • Generating interfaces from data models or diagrams
    • Creating a temporary interface (subquery)
    • Loading data from an SQL query
    • Performing a pivot
    • Loading data using partition exchange
  • Chapter 8: Package Loops and File Processing
    • Introduction
    • Defining packages and loops for near real-time execution using a hybrid loop
    • Using a file from a parameter variable
    • Detecting files with a variable name
    • Processing all files in a directory
    • Processing a large number of files in parallel
  • Chapter 9: XML and Web Services
    • Introduction
    • Defining a connection to XML within ODI
    • Processing complex files with ODI
    • Processing XML data within an RDBMS not in memory
    • Invoking web services from ODI
    • Invoking asynchronous ODI web services with callbacks
    • Configuring container-based authentication with ODI web services
  • Chapter 10: Advanced Coding Techniques Using the ODI SDK
    • Introduction
    • Creating the Master and Work repositories using the SDK
    • Creating a project using the SDK
    • Automating the import of artifacts using the SDK
    • Creating models and datastores using the SDK
    • Creating an interface using the SDK
    • Invoking and monitoring a scenario using the SDK
  • Chapter 11: More on ODI
    • Introduction
    • Invoking an external program that requires a password
    • Tuning a standalone ODI agent
    • Loading a file containing a LOB
    • Using ODI versioning
    • Performing a Smart Export/Smart Import
    • Accessing an Excel spreadsheet
    • Impacting the data flow by changing the staging area location
    • Automating Smart Export/Smart Import with the ODI SDK

Template Form in Oracle Applications

Template Form in Oracle Apps

This document provides an overview of the template form. This form derives its importance from the fact that this form is the starting point of all development involving forms. The document highlights the importance of Template.fmb in forms development and also provides a detailed explanation of the various components of the Template form.

Overview of the Template Form

The TEMPLATE form is the starting point for all development of new forms. The first step in creating a form for use in Oracle Applications is to copy the template form from $AU_TOP/forms/US, to a local directory and renaming it.

The Template form is unique because it contains some special libraries and triggers that render the application using the template form some standard characteristics. The components of the template form are:

· References to object groups: The template form contains platform–independent references to predefined standard object groups in the APPSTAND form. STANDARD_PC_AND_VA, STANDARD_TOOLBAR, and STANDARD_CALENDAR).

· Libraries: The template form contains platform–independent attachments of several libraries (including FNDSQF, APPCORE, and APPDAYPK).

· Special triggers: The template form contains several form–level triggers with required code. These are responsible for standard the behavior of the form.

· Predefined Program Units: The template form contains predefined program units that include a spec and a body for the package APP_CUSTOM, which contains default behavior for window opening and closing events.

· Applications Color Palette: The template form contains the application color palette. This gives the forms developed using the template form the look and feel of Oracle Applications

· Many referenced objects (from the object groups) that support the Calendar, the toolbar, alternative regions, and the menu. These objects include LOVs, blocks, parameters, and property classes, and so on.

· The TEMPLATE form contains sample objects that can be seen as examples for the expected layout cosmetics. These samples can be completely removed from the form later as they are only examples and are not required. The following objects are the samples and can be removed:



§ Canvas–view: BLOCKNAME

Hence, the template form comes along with many attachments, predefined program units, and defined visual attributes as well as examples that not only give the forms that are developed using the template.fmb a standard look and feel, but also make t easier to develop forms with consistent and standard functionality.

Libraries in the Template form

As stated above, the template form contains platform–independent attachments of several libraries. These libraries are used while running the form as a part of Oracle Applications. Hence, these libraries should not be changed or modified. There are three main libraries that are attached to the template form:

APPCORE: APPCORE contains the packages and procedures that are responsible for the standard ‘Oracle Applications’ behavior of the forms. The forms have to support the menu, toolbar, and other required standard behaviors in compliance with oracle applications. The Oracle Applications forms also have to display specific runtime behaviors in accordance with the Oracle Applications Interface Standards, such as the way in which fields are enabled, behaviors of specific types of windows etc. APPCORE contains the procedures to support this standard runtime behavior. APPCORE also contains various other utilities for exceptional handling, message levels, and so on.

APPDAYPK: The APPDAYPK library contains the packages that control the Oracle Applications Calendar feature. The calendar (or the date picker) is a utility that oracle apps provide to pick the dates for a date type field.

FNDSQF: FNDSQF contains packages and procedures for Message Dictionary, flexfields, profiles, and concurrent processing. It also has various other utilities for navigation, multicurrency, WHO, etc.

Other Libraries à The template form also contains a few other libraries that are not linked directly to the template form, but are linked to the three libraries listed above. Although, while using the form it makes no difference whether the library is linked directly to template or to another library that is linked to template. These are discussed below.

CUSTOM library: The CUSTOM library (CUSTOM.pll) is probably the most widely used and customized in the libraries attached to the template form. This library allows extension of Oracle Applications forms without modification of Oracle Applications code. Any form goes to the CUSTOM.pll whenever any event fires on the form. Code can be written in the CUSTOM.pll with the logic branching based on the form, block and trigger on which you want it to run.

You can use the CUSTOM library for customizations such as Zoom (such as moving to another form and querying up specific records), enforcing business rules (for example, vendor name must be in uppercase letters), and disabling fields that do not apply for your site.

GLOBE: The GLOBE library allows Oracle Applications developers to incorporate global or regional features into Oracle Applications forms
without modification of the base Oracle Applications form. Oracle Applications sends events to the GLOBE library. Regional code can
take effect based on these events. The GLOBE library calls routines in
the JA, JE, and JL libraries.

VERT: The VERT library allows Oracle Applications developers to incorporate vertical industry features (for automotive, consumer packaged goods, energy, and other industries) into Oracle Applications forms without modification of the base Oracle Applications form. Oracle Applications sends events to the VERT library. Vertical industry code can take effect based on these events. The VERT library calls routines in various other libraries.

JA: The JA library contains code specific to the Asia/Pacific region and is called by the GLOBE library.

JE: The JE library contains code specific to the EMEA (Europe/ MiddleEast/ Africa) region and is called by the GLOBE library.

JL: The JL library contains code specific to the Latin America region and is called by the GLOBE library.

Thanks Shivmohan Purohit

Oracle Applications – Basics – Part 1

Hello Friends, here i am posting some of basic oracle applications questions , will try to collect more and put in future posts, here i am putting quite easy and basic questions. If you like to know Questions of any specific area or modules, plz communicate so i will post those as well.

What are Oracle Apps API?

Where APIs are physically store? n What is the latest version of API?

They are Application Program Interfaces, which are standard stored procedure, package or functions created for performing specific activities in the Oracle Apps E-Business Suite.

Ex: The Order Import API when called would create orders corresponding to the record in the Order Interface tables.

How to attach reports in Oracle Applications?

The steps are as follows:

1.          Design your report.

2.          Generate the executable file of the report.

3.          Move the executable as well as source file to the appropriate product’s folder.

4.          Register the report as concurrent executable.

5.          Define the concurrent program for the executable registered.

6.          Add the concurrent program to the request group of the responsibility.

How to attach Forms in Oracle Applications

1. Develop the form in Developer 2000.

2. Save the FORM and move it to UNIX. [Form to be moved to $CUSTOM_TOP/forms/US]

3. Define the FORM in Application Developer

4. Define FORM as FUNCTION.

5. Identify MENU, where the FORM to be attached. [SysadminResponsibilityDefine]

6.Copy the above MENU and go to Application Developer and query on MENU tab

What is the difference between Customization and Personalization?

Customization is done by making the changes in the cutom.pll whereas the Form Personalization is done through the Front End in Oracle Apps.

Difference between Key and Descriptive Flexfield?

key flex fields are used to identify particular entity like(accounts,objects) supoose take debit cards each debit card contain unique number to identify them. eg; 100-023-323 key flex fields are used to store this number along with description.

Unique Identifier

Key Flexfield are stored in segment

For key flexfield there are flexfield Qualifier and segment Qualifier

DFF’s are used to capture additional informtion required for our organization without any programming.

To capture extra information
Stored in attributes
Context-sensitive flexfield is a feature of DFF. (descriptive flexfield)

What is the difference between data conversion and data migration?

Data Migration is upgrading from one version to another version fro e.g. from 11.5.9 to 11.5.10.. Data Conversion is receiving data from a legacy system or receiving data from another module ( e.g. PA), validating it and converting it to ur our the target module using the standard import program.

What is the difference between Organization_id and Org_id ?

OrgId: Org Id is an unique ID for the Operating Unit.Organisation Id: The Organisation Id is an ID for the Inventory Organisation which is under an Operating Unit.

What are _ALL tables in Oracle Apps?

_all tables will store information about multiple oraganizations. these table contain ORG_ID column that  picks particular organizations from multiple organizations. _all tables will be created when we install multiorg.

What is one full life cycle implementation?

Full Life Cycle means implementing a project from the begining to the production and maintenance stage.

(1) System Planning:

(Scope & Budget of the Project)

(2) Business Analysis:

(Business Requirements, Mapping & GAP Analysis)

(3) System Analysis & Design

(Design Conceptual & Functional Data Models)

(4) System Development

(Programming & System/Integration/Validation Testing)

(5) System Implementation

(Data Conversions, Interfaces, Extensions,User Manuals & User Training)

(6) System Maintenance & Support

Thanks – Shivmohan Purohit

Distinction between configuration, personalization, and extensibility.

Distinction between configuration, personalization, and extensibility.

Configuration provides setup and administrative choices using the native features of the product. Some configuration examples include:

  • Profile Options
  • User-defined fields (Flexfield)
  • Function Security Setup
  • Data Security Setup
  • and a lot more….

Personalization enables you to declaratively tailor the UI look-and-feel, layout or visibility of page content to suit a business need or a user preference. Some personalization examples include:

  • Tailor the order in which table columns are displayed.
  • Tailor a query result.
  • Tailor the color scheme of the UI.
  • Folder Forms
  • Forms Personalization
  • Oracle Application Framework (OAF)

Extensibility is about extending the functionality of an application beyond what can be done through personalization. Some extensibility examples include:

  • Add new functional flows
  • Extend or override existing business logic
  • Using Oracle Forms Developer, Oracle JDeveloper and Oracle Workflow

Oracle SQL / PLSQL Developer – Technical Question Answers – Part 5



Question: What are the Pct Free and Pct Used

Answer:  Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table eg.:: Pctfree 20, Pctused 40 




Question: What is Row Chaining

Answer: The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks. 




Question: What is a 2 Phase Commit
Answer: Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit  Phase ::

Commit all participants to coordinator to Prepared, Read only or abort Reply 


Question: What is the difference between  deleting and truncating of tables


Answer:  Deleting   a table will not remove the rows from the table but entry is there in  the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved. 




Question: What are mutating tables

Answer: When a table is in state of transition it is said to be mutating eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select. 




Question:  What are Codd Rules
Answer:  Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.

Question: What is the Difference between a post query and a pre query

Answer: A post query will fire for every row that is fetched but the pre query will fire only once. 




Question:  Deleting the Duplicate rows in the table

Answer: We can delete the duplicate rows in the table by using the Rowid 




Question: Can U disable database trigger? How?
Answer: Yes. With respect to table
     [   DISABLE all_trigger ]

Question: What is pseudo columns ? Name them?
Answer:  A pseudocolumn behaves like a table column, but is not actually stored in the table.  You can select from pseudocolumns, but you cannot insert, update, or delete their values. 

    * LEVEL
    * ROWID
    * ROWNUM 
Question:  How many columns can table have?










Answer: The number of columns  in a table can range from 1 to 254. 


Question:  What is clustered index?


Answer:  In an indexed cluster, rows are stored together based on their cluster key values. Can not apply for HASH. 


Question:  What are attributes of cursor?




Question: Can you use select in FROM clause of SQL select?
Answer:  Yes.


Understanding Oracle SQL*Loader

SQL*Loader is an Oracle utility that enables you to efficiently load large amounts of data into a database. If you have data in a flat file, such as a comma-delimited text file, and you need to get that data into an Oracle database, SQL*Loader is the tool to use. This chapter introduces you to the SQL*Loader utility, discusses its control file, provides the syntax for using the SQL*Loader command, and provides examples of using SQL*Loader to load data into databases.


Using SQL*Loader, you can do the following:

Ø       Load data from a delimited text file, such as a comma delimited file

Ø       Load data from a fixed-width text file

Ø       Load data from a binary file

Ø       Combine multiple input records into one logical record

Ø       Store data from one logical record into one table or into several tables

Ø       Write SQL expressions to validate and transform data as it is being read from a file

Ø       Combine data from multiple data files into one

Ø       Filter the data in the input file, loading only selected records

Ø       Collect bad records—that is, those records that won’t load—into a separate file where you can fix them


Control Files

Control files, contain a number of commands and clauses describing the data that SQL*Loader is reading. Control files also tell SQL*Loader where to store that data, and they can define validation expressions for the data. Understanding control file syntax is crucial to using SQL*Loader effectively. The control file is aptly named, because it controls almost every aspect of how SQL*Loader operates. The control file describes the format of the data in the input file and tells SQL*Loader which tables and columns to populate with that data.


Understanding the SQL*Loader Commands

The syntax for

the SQL*Loader command looks like this:   sqlldr [param=value[, param=value…]]


SQL*Loader Parameters

Parameter Description

userid :   Passes in your username, password, and Net8 service name. The syntax to use is the same as for any other command-line utility, and looks like this: userid=username[/password][@service]


control: Passes in the control file name. Here’s an example: control=[path]filename[.ext] The default extension for control files is .ctl.


log:  Passes in the log file name. Here’s an example: log=[path]filename[.ext] The default extension used for log files is .log. If you don’t supply a file name, the log file will be named to match the control file.


bad Passes in the bad file name. Here’s an example: bad=[path]filename[.ext] The default extension for bad files is .bad. If you don’t supply a file name, the bad file will be named to match the control file. Using this parameter overrides any file name that may be specified in the control file.


data Passes in the data file name. Here’s an example: data=[path]filename[.ext] The default extension used for data files is .dat. Specifying a data file name on the command line overrides the name specified in the  control file. If no data file name is specified anywhere, it defaults to the same name as the control file, but with the .dat extension.


discard Passes in the discard file name. Here’s an example: discard=[path]filename[.ext]

The default extension used for discard files is .dis. If you don’t supply a file name, the discard file will be named to match the control file. Using this parameter overrides any discard file name that may be specified in the control file.


discardmax Optionally places a limit on the number of discarded records that will

be allowed. The syntax looks like this: discardmax=number_of_records If the number of discarded records exceeds this limit, the load is aborted.


skip Allows you to skip a specified number of logical records. The syntax looks like this:

skip=number_of_records Use the skip parameter when you want to continue a load that has

been aborted and when you know how far into the file you want to go before you restart.

load Optionally places a limit on the number of logical records to load into the database. The syntax looks like this:


load=number_of_records Once the specified limit has been reached, SQL*Loader will stop.


Errors Specifies the number of errors to allow before SQL*Loader aborts the load. The syntax looks like this: errors=number_of_records SQL*Loader will stop the load if more than the specified number of errors has been received. The default limit is 50. There is no way to allow an unlimited number. The best you can do is to specify a very high value, such as 999999999.


Rows indirectly controls how often commits occur during the load process. The rows parameter specifies the size of the bind array used for conventional-path loads in terms of rows. SQL*Loader will round that value off to be some multiple of the I/O block size. The syntax for the rows parameter looks like this: rows=number_of_rows The default value is 64 for conventional-path loads. Direct-path loads, by default, are saved only when the entire load is done. However, when a direct-path load is done, this parameter can be used to control the commit frequency directly.

4. Run the applypreferences script. 


Ø       SQL*Loader is a versatile utility for loading large amounts of data into an Oracle database.

Ø       SQL*Loader control files are used to describe the data being loaded and to specify the table(s) into which that data is stored.

Ø       You can use the INFILE clause to identify the file, or files, that you want SQL*Loader to read.

Ø       You can use the INTO TABLE clause to identify the table, and the columns within that table that you wish to populate using the data read from the input file.

Ø       You can use the APPEND option after the INFILE clause to tell SQL*Loader to insert data into a table that already contains data to begin with.

Ø       You can use SQL*Loader to load delimited data, such as comma-delimited data, or you can use it to load data stored in fixed-width columns.

Ø       SQL*Loader fully supports all of Oracle8i’s datatypes, even to the point of allowing you to populate LOB columns.


Thanks Shivmohan Purohit


Oracle Forms Development (Developer 2000) : Part 1

Question: Which triggers are created when master -detail relation?

1) master delete property*  NON-ISOLATED (default)
a) on check delete master
b) on clear details
c) on populate details
a) on clear details
b) on populate details
a) per-delete
b) on clear details
c) on populate details

Question: which system variables can be set by users?







Question: What are object group?
Answer: An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.

Question: What are referenced objects?

Answer: Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open or regenerate the module that contains the reference object. 


Question: Can you issue DDL in forms?
Answer: yes, but you have to use FORMS_DDL.

Restrictions: The statement you pass to FORMS_DDL may not contain bind variable

references in the string, but the values of bind variables can be concatenated into the string before passing the result to FORMS_DDL.   
Question: What is SECURE property?


Answer: – Hides characters that the operator types into the text item.  This setting is typically used for  password protection.





Question: What are the types of triggers and how the sequence of firing in text item
Answer: Triggers can be classified as Key Triggers, Mouse Triggers ,Navigational Triggers.
Key Triggers: Key Triggers are fired as a result of Key action.e.g :: Key-next-field, Key-up,Key-Down
Mouse Triggers: Mouse Triggers are fired as a result of the mouse navigation.e.g. When-mouse-button-presed,when-mouse-doubleclicked,etc
Navigational Triggers :: These Triggers are fired as a result of Navigation. E.g :  Post-Text-item,Pre-text-item.
We also have event triggers like when –new-form-instance and when-new-block-instance. We cannot call restricted procedures like go_to(‘my_block.first_item’) in the Navigational triggers But can use them in the Key-next-item.

The Difference between Key-next and Post-Text is an very important question. The key-next is fired as a result of the key action while the post text is fired as a result of the mouse movement. Key next will not fire unless there is a key event.
The sequence of firing in a text item are as follows::
    a) pre – text
    b) when new item
    c) key-next
    d) when validate
    e) post text 


Question: Can you store pictures in database? How?

Answer: Yes, in long raw datatype. 


Question: What are property classes? Can property classes have trigger?

Answer: Property class inheritance is a powerful feature that allows you to quickly define objects that conform to your own interface and functionality standards. Property classes also allow you to make global changes to applications quickly.  By simply changing the definition of a property class, you can change the definition
of all objects that inherit properties from that class. Yes. All type of triggers. 


Question: If you have property class attached to an item and you have same trigger written for the item.  Which will fire first?
Answer: Item level trigger fires , If item level trigger fires, property level trigger won’t fire. Triggers at the lowest level are always given the first preference. The item level trigger fires first and then the block and then the Form level trigger.
Question: What are record groups?  Can record groups created at run-time?

Answer: A record group is an internal Oracle Forms data structure that has a column/row framework similar to a database table.  However, unlike database tables, record groups are separate objects that belong to the form module in which they are defined.  A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64K.  Record group column names cannot exceed 30 characters.Programmatically, record groups can be used whenever the functionality offered by a two-dimensional array of multiple data types is desirable.
Query Record Group    A query record group is a record group that has an associated SELECT statement. 

The columns in a query record group derive their default names, data types, and lengths from the database columns referenced in the SELECT statement.  The records in a query record group are the rows retrieved by the query associated with that record group.
Non-query Record Group: A non-query record group is a group that does not have an associated query, but whose structure and values can be modified programmatically at runtime.
Static Record Group: A static record group is not associated with a query; rather, you define its structure and row values at design time, and they remain fixed at runtime.


 Shivmohan Purohit