Oracle Alerts- Few FAQs

What are Oracle Alerts?

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want.

What they are capable of?

  • Keep you informed of database exception conditions, as they occur.
  • Take predefined actions when it finds exceptions in your database, without user intervention.
  • Take the actions you specify, depending upon your response to an alert message.
  • Perform routine database tasks automatically, according to the schedule you define.
  • Keep you informed of exception conditions through Email.

What are the types of Alert?

You can define one of two types of alerts: an event alert or a periodic alert.

  • An event alert immediately notifies you of activity in your database as it occurs.
  • A periodic alert, on the other hand, checks the database for information according to a schedule you define.

What are the alert actions available?

  • Sending the retrieved information to someone in an Email.
  • Run a concurrent program
  • Run an operating script
  • Run a SQL Statement script

What is On-Demand periodic alert?

It is a periodic alert with frequency as ‘On-Demand’. That means there is no specific period assigned to this alert and you can run this alert at any time you want using Request Periodic Alert Check form.

What you specify in the Alert Details window?

  • Default values for inputs variables
  • Additional characteristics for output variables
  • Application installations information you want the alert to run against

What are the Action Levels for your alert actions?

There are three types of level for your action: Detail, Summary and No Exception.

During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.

What is Action Set?

An action set can include an unlimited number of actions and any combination of actions and action groups for your alert. You can define as many action sets as you want for each alert. Oracle Alert executes the alert Select statement once for each action set you define. During each action set check, Oracle Alert executes each action set member in the sequence you specify.

What is Distribution List in Oracle Alert?

Distribution lists let you predefine a set of message recipients for use on many actions. If a recipient changes, you need only adjust it in the distribution list, not in the individual message actions.

What is Summary Threshold?

  • Oracle Alert can automatically determine whether to perform a detail or summary action, depending upon the number of exceptions found by the alert Select statement.
  • If you define a summary threshold, Oracle Alert performs a detail action for each exception found by the Select statement, but if the number of exceptions found exceeds the summary threshold, Oracle Alert performs a summary action.
  • You need to first define a detail and a summary action, include them in a threshold group, and then specify a summary threshold.

What is Periodic Set?

You can create a set of periodic alerts that Oracle Alert checks simultaneously. Use the Request Periodic Alert Check window to check the periodic set. Note that each periodic alert you include in a periodic set continues to run according to its individually defined frequency.

What is Response Processing in Alert?

Oracle Alert can process responses to your alert messages. When Oracle Alert receives a response to a specific alert message, it automatically performs the actions you define. Optionally, respondents can supply values that Oracle Alert uses to perform these actions. Response processing lets you automate routine user-entry transactions, streamlining your organization’s operations.

Note: To enable response processing, ensure that you have performed the required setup steps.

What is Action Escalation?

You can define a set of escalating detail actions, called an escalation group, for Oracle Alert to perform when it finds the same exceptions during consecutive alert checks. Oracle Alert performs a different detail action each time it encounters the same exception, so you can define actions that correspond to increasing severity levels.

How event alert works?

Once you define an event alert to monitor a table for inserts and/or updates, any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits to the concurrent manager, a request to run a concurrent program called Check Event Alert (ALECTC). The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. If the Select statement finds exceptions, Check Event Alert performs the actions defined in the enabled action set(s) for the alert. If the Select statement does not find any exceptions, Check Event Alert performs the No Exception actions in the enabled action set(s) for the alert.

Few Interesting Questions on Oracle GL Journals Entry

Few Interesting Questions on Oracle GL Journals Entry

Is There a Report That Displays Information of One Specific Journal Entry Unposted/Posted?

No. General Ledger reports display information of journal batches posted or unposted. However, you can use the below sql query to find information of a particular journal entry.

select  b.je_batch_id batch_id ,
        h.je_header_id header_id ,
        l.je_line_num line ,
        l.code_combination_id ccid ,
        g.segment1 || '.' || g.segment2 || '.' || g.segment3 ||
        '.' || g.segment4 || '.' || g.segment5 || '.' || g.segment6 ||
        '.' || g.segment7 || '.' || g.segment8 || '.' || g.segment9 ||
        '.' || g.segment10 combination ,
        l.entered_dr entered_dr,
        l.entered_cr entered_cr,
        l.accounted_dr accounted_dr,
        l.accounted_cr accounted_cr,
from    gl_je_lines l,
        gl_je_headers h,
        gl_je_batches b,
        gl_code_combinations g
where   b.je_batch_id = h.je_batch_id
        and h.je_header_id = &je_header_id
        and l.je_header_id = h.je_header_id
        and h.je_batch_id = b.je_batch_id
        and l.code_combination_id = g.code_combination_id
order by h.je_header_id, l.je_line_num;

Can a Posted General Ledger Journal Entry be deleted?

After a journal entry is posted, it cannot be deleted. Posted journal entries cannot be deleted because that would eliminate the audit trail. To nullify the accounting effect of the posted journal entry, you can reverse it.

When can not a journal batch be deleted or modified?

A journal batch cannot be deleted or modified under the following circumstances:

a. The source is frozen

b. Funds have been reserved for the batch

c. Funds are in the process of being reserved for the batch

d. The batch is in the process of being posted

e. The batch is posted

f. The batch is approved

g. The batch is in the process of being approved

A journal batch should not be updated if it comes from a sub-ledger.

Changing accounting information in a journal that originated in a sub-module will unsynchronize the accounting information between the ledger and the sub-ledger. Instead of changing the sub-ledger journal, define a new journal to adjust the accounting information if necessary.

A journal batch that has funds reversed cannot be updated because the funds would not be re-reserved appropriately.

Which report shows details of a posted journal batch?

Journals – General (180 Char) and Journals – General (132 Char)

Is possible to restrict users from entering negative amounts in journal lines?

Unfortunately, it is not possible to restrict users from entering negative amounts in journal entry lines.

How to set up journal approval in General Ledger?

This is set up using Oracle Workflow Builder. The basics steps to setup Journal Approval are as below

a) Enable Journal Approval at the Ledger level
b) Setup Journal Sources for Journal Approval
c) Configure the profile options that control how the approval list will be built
d) Define Employees and Supervisors
e) Define Approval limits for approvers
f) Associate the employees to Oracle Apps users
g) Optional Workflow Configuration

For more information refer metalink notes: ID 176459.1 & ID 278349.1

How do you attach an Excel spreadsheet to a journal entry in Oracle General Ledger?

  1. Query the Journal that needs the spreadsheet attachment.

  2. Click on the paperclip on the tool bar.

  3. Fill the following fields in the Attachment form.

     Category    –  Choose Journal from LOV

     Description –  optional

     Data Type   –  OLE Object from the LOV

  4. Right click on the large white portion of the Attachment form choose ‘Insert Object’ from the drop box.

  5. When the Insert Object Form appears check “create from file” and click on “Browse” to choose the file that should be attached from the directory structure.

  6. Save.

How do you easily copy a journal entry from one set of books to another?

There is no standard feature to copy journal entries between sets of books. However, there are some alternatives. Refer Metalink note: ID 204082.1

How to prevent user’s ability to reverse unposted journals?

For 11i, there is not a method to prevent users from reversing unposted journals. This is intended functionality to incorporate the maximum flexibility that users may require. However you can limit user access to journal reversals through user menus set up in Sys Admin responsibility.

This functionality changed in R12 – see Note 734848.1 In Release 12, a batch must be posted before it can be reversed.

How do you automatically generate a reversal journal entry for a journal category in the previous accounting period?

If you routinely generate and post large numbers of journal reversals as part of your month end closing and opening procedures, you can save time and reduce entry errors by using Automatic Journal Reversal to automatically generate and post your journal reversals.

First you define journal reversal criteria for journal categories. Journal reversal criteria let you specify the reversal method, period and date. You can also choose to enable automatic generation and posting of journals.

When you create a journal entry you want to automatically reverse, specify a journal category that has assigned reversal criteria. Your journal will be reversed based on the method, period and date criteria you defined for that journal category.

In Release 12, a reversal journal that is Unposted cannot be modified. Why?

This is the expected functionality in Release 12. However the profile GL: Edit Reverse Journals can be set to allow the modification. Refer metalink note: ID 567641.1

Reversing journal was deleted from the system, how can you still reverse the original journal?

General Ledger does not allow you to reverse a journal entry twice. . Refer metalink note: ID 145043.1 for details.

A journal entry with a source set up for automatic reversal is not reversed. Why?

General Ledger automatically submits the AutoReverse program when a period is opened if the profile option, GL: Launch AutoReverse After Open Period, is set to Yes. If a journal is created after the period has already been opened, then the AutoReverse program will need to be submitted manually.

A journal has been created and is unposted.  The following period has a reversing journal for the original journal and it is posted. Why it is so?

This is currently the functionality of the application to allow the reversing journal to be posted even if the original journal is not.




What is PL/SQL?

PL/SQL is a procedural language that has both interactive SQL and procedural programming language constructs such as iteration, conditional branching.


What are the components of a PL/SQL Block?

Declarative part

Executable part

Exception part


What are the datatypes a available in PL/SQL?

Some scalar data types such as NUMBER, VARCHAR2, DATE, CHAR, LONG, BOOLEAN. Some composite data types such as RECORD & TABLE.


What are % TYPE and % ROWTYPE? What are the advantages of using these over datatypes?

% TYPE provides the data type of a variable or a database column to that variable.

% ROWTYPE provides the record type that represents a entire row of a table or view or columns selected in the cursor.

The advantages are:

I. Need not know about variable’s data type

ii. If the database definition of a column in a table changes, the data type of a variable changes accordingly.


What is difference between % ROWTYPE and TYPE RECORD?

% ROWTYPE is to be used whenever query returns an entire row of a table or view. TYPE RECORD is to be used whenever query returns columns of different table or views and variables.


Explain the two types of Cursors?

There are two types of cursors, Implicit Cursor and Explicit Cursor.

PL/SQL uses Implicit Cursors for queries.

User defined cursors are called Explicit Cursors. They can be declared and used.


What are the cursor attributes used in PL/SQL?

% ISOPEN – Used to check whether a cursor is open or not.

% ROWCOUNT – Used to check the number of rows fetched/updated/deleted.

% FOUND – Used to check whether cursor has fetched any row. True if rows are fetched.

% NOT FOUND – Used to check whether cursor has fetched any row. True if no rows are fetched.


What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.


What is the difference between implicit and explicit cursors?

An explicit cursor is declared opened and fetched from in the program block where as an implicit cursor is automatically generated for SQL statements that process a single row only.


What are the different types of joins available in Oracle?

Equi Join: When primary and foreign key relationship exists between the tables that are going to be joined.

Self Join: If comparison comes in a single table

Cartesian Join: When tables are joined without giving any join condition.

Inner Join: The resultant set includes all the rows that satisfy the join condition.

Outer Join: The resultant set includes the rows which doesn’t satisfy the join condition.  The outer join operator Plus sign (+) will be included in the join condition.


What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.


What is an autonomous transaction?

An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction). An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction.


What is the difference between View and Materialized view?

Materialized view will not be refreshed every time you query the view. So to have good performance when data is not changed so rapidly, we use Materialized views rather than normal views which always fetches data from tables every time you run a query on it.


What is dynamic SQL?

Dynamic SQL allows you to construct a query, a DELETE statement, a CREATE TABLE statement, or even a PL/SQL block as a string and then execute it at runtime.


Can you use COMMIT in a trigger?

Yes but by defining an autonomous transaction.


What is the difference between anonymous blocks and stored procedures?

Anonymous block is compiled only when called. Stored procedure is compiled and stored in database with the dependency information as well. Former is PL/SQL code directly called from an application. Latter is stored in database. Former has declare statement. Latter doesn’t.


What is a package spec and package body? Why the separation?

Spec declares public constructs. Body defines public constructs, additionally declares and defines Private constructs.

Separation helps make development easier. Dependency is simplified. You can modify body without invalidating dependent objects.


What is Correlated Subquery?

Correlated Subquery is a subquery that is evaluated once for each row processed by the parent statement. Parent statement can be Select, Update or Delete.


What is Sequence?

Sequences are used for generating sequence numbers without any overhead of locking. Drawback is that after generating a sequence number if the transaction is rolled back, then that sequence number is lost.


What is SQL Deadlock?

Deadlock is a unique situation in a multi user system that causes two or more users to wait indefinitely for a locked resource. First user needs a resource locked by the second user and the second user needs a resource locked by the first user. To avoid dead locks, avoid using exclusive table lock and if using, use it in the same sequence and use Commit frequently to release locks.


What is SQL*Loader?

SQL*Loader is a product for moving data in external files into tables in an Oracle database. To load data from external files into an Oracle database, two types of input must be provided to SQL*Loader: the data itself and the control file.


What is the use of CASCADE CONSTRAINTS?

When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

Explain forward declaration used in functions?

A forward declaration means that modules (procedures and functions) are declared in advance of their actual body definition. This declaration makes that module available to be called by other modules even before the program’s body is defined. A forward declaration consists simply of the module header, which is just the name of the module followed by the parameter list (and a RETURN clause in case the module is a function), no more no less.

Forward declarations are required in one specific situation: mutual recursion.


What are SQLCODE and SQLERRM and why are they important for PL/SQL developers?

SQLCODE returns the value of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered. They can be used in exception handling to report, or, store in an error log table, the error that occurred in the code. These are especially useful for the WHEN OTHERS exception.


What is the difference between Truncate and Delete Commands?


TRUNCATE is a DDL command whereas DELETE is a DML command. Hence DELETE operation can be rolled back, but TRUNCATE operation cannot be rolled back. WHERE clause can be used with DELETE and not with TRUNCATE.


What is the Purpose of HAVING Clause?

The HAVING clause is used in combination with the GROUP BY clause. It can be used in a SELECT statement to filter the records that a GROUP BY returns.


What is INLINE View in SQL?

The inline view is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.


While creating a sequence, what does cache and nocache options mean?

With respect to a sequence, the cache option specifies how many sequence values will be stored in memory for faster access.


Does the view exist if the table is dropped from the database?

Yes, in Oracle, the view continues to exist even after one of the tables (that the view is based on) is dropped from the database. However, if you try to query the view after the table has been dropped, you will receive a message indicating that the view has errors.


What is an Index?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.


What types of index data structures can you have?

An index helps to faster search values in tables. The three most commonly used index-types are:

  • B-Tree: builds a tree of possible values with a list of row IDs that have the leaf value. Needs a lot of space and is the default index type for most databases.
  • Bitmap: string of bits for each possible value of the column. Each bit string has one bit for each row. Needs only little space and is very fast. (However, domain of value cannot be large, e.g. SEX(m,f); degree(BS,MS,PHD)
  • Hash: A hashing algorithm is used to assign a set of characters to represent a text string such as a composite of keys or partial keys, and compresses the underlying data. Takes longer to build and is supported by relatively few databases.


What is the difference between a “where” clause and a “having” clause?

“Where” is a kind of restriction statement. You use where clause to restrict all the data from DB. Where clause is used before result retrieving. But Having clause is using after retrieving the data. Having clause is a kind of filtering command.


Can a view be updated/inserted/deleted? If Yes – under what conditions?

A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.


What is tkprof and how is it used?

The tkprof tool is a tuning tool used to determine cpu and execution times for SQL statements. You use it by first setting timed_statistics to true in the initialization file and then turning on tracing for either the entire database via the sql_trace parameter or for the session using the ALTER SESSION command. Once the trace file is generated you run the tkprof tool against the trace file and then look at the output from the tkprof tool. This can also be used to generate explain plan output.


What is explain plan and how is it used?

The EXPLAIN PLAN command is a tool to tune SQL statements. To use it you must have an explain_table generated in the user you are running the explain plan for. This is created using the utlxplan.sql script. Once the explain plan table exists you run the explain plan command giving as its argument the SQL statement to be explained. The explain_plan table is then queried to see the execution plan of the statement. Explain plans can also be run using tkprof.


What are the Lock types?

Share Lock: It allows the other users for only reading not to insert or update or delete.

Exclusive Lock: Only one user can have the privileges of insert or update and delete of particular object, others can only read.

Update Lock: Multiple user can read, update delete .


What is Pragma EXECPTION_INIT? Explain the usage?

The PRAGMA EXECPTION_INIT tells the complier to associate an exception with an oracle error.
E.g. PRAGMA EXCEPTION_INIT (exception name, oracle error number)


What is Raise_application_error?

Raise_application_error is a procedure of package DBMS_STANDARD which allows to issue a user_defined error messages from stored sub-program or database trigger.


What are the modes for passing parameters to Oracle?

There are three modes for passing parameters to subprograms
IN – An In-parameter lets you pass values to the subprogram being called. In the subprogram it acts like a constant and cannot be assigned a value.
OUT – An out-parameter lets you return values to the caller of the subprogram. It acts like an initialized variable its value cannot be assigned to another variable or to itself.
INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.


What is the difference between Package, Procedure and Functions?

A package is a database objects that logically groups related PL/SQL types, objects, and Subprograms.

Procedure is a sub program written to perform a set of actions and can return multiple values.

Function is a subprogram written to perform certain computations and return a single value.
Unlike subprograms packages cannot be called, passed parameters or nested.


How do you make a Function and Procedure as a Private?

Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.


What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.


What is the difference between DDL, DML and DCL structures?

DDL statements are used for defining data. Ex: Create, Alter, Drop, Truncate, Rename.
DML statements are used for manipulating data. Ex: Insert, update, truncate.
DCL statements are used for to control the access of data. Ex; Grant, Revoke.
TCL statements are used for data saving. Ex; Commit, Rollback, Savepoint.


What is the difference between the snapshot and synonym?

A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.

A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.


What is the difference between data types char and varchar?

Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).


Can one call DDL statements from PL/SQL?

One can call DDL statements like CREATE, DROP, TRUNCATE, etc. from PL/SQL by using the “EXECUTE IMMEDATE” statement.


Tell some new features in PL/SQL in 10g?


-Compile time warnings

– Conditional compilation

– Improvement to native compilation


– Implicit conversion between CLOB and NCLOB

– Improved Overloading


– Global optimization enabled

– PLS_INTEGER range increased to 32bit



What is Overloading in PL/SQL?

Overloading is an oops concept (Object Oriented Programming). By using the same name we can write any number of Procedures or functions in a package but either number of parameters in the procedure/function must vary or parameter datatype must vary.


What is a mutating and constraining table?

“Mutating” means “changing”. A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered “mutating” and raises an error since Oracle should not return data that has not yet reached its final state.

Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table off which it fires. If you must have triggers on tables that have referential constraints, the workaround is to enforce the referential integrity through triggers as well.


What is Nested Table?

A nested table is a table within a table. A nested table is a collection of rows, represented as a column within the main table. For each record within main table, the nested table may contain multiple rows.  In a sense, it’s a way of storing a one-to many relationship within one table.


What is Varying Array?

A varying array is a set of objects, each with the same data types. The size of the array is limited when it is created. (When the table is created with a varying array, the array is a nested table with a limited set of rows). Varying arrays also known as VARRAYS, allows storing repeated attributes in tables.


Give some most often used predefined exceptions?

a) NO_DATA_FOUND (Select Statement returns no rows)
b) TOO_MANY_ROWS (Single row Select statement returns more than 1 row)
c) INVALID_CURSOR (Illegal cursor operations occurred)
d) CURSOR_ALREADY_OPEN (If cursor is opened & we are trying to reopen it)
e) INVALID_NUMBER (Conversion of Character to number fails)
g) DUP_VAL_ON_INDEX (Attempted to insert a duplicate value)


Give some important Oracle supplied packages?

DBMS_SQL: It is used to write Procedures & Anonymous blocks that use Dynamic SQL.

DBMS_JOB: Using it, we can submit PL/SQL programs for execution, execute PL/SQL programs on a schedule, identify when programs should run, remove programs from the schedule & suspend programs from running.

DBMS_OUTPUT: This package outputs values & messages from any PL/SQL block.

UTL_FILE: With this package, you can read from & write to Operating system files

UTL_HTTP: This package allows to make HTTP Requests directly from the database.


What is Instead Of Trigger?

This trigger is used to perform DML operation directly on the underlying tables, because a view cannot be modified by normal DML Statements if it contains joins or Group Functions. These triggers are Only Row Level Triggers. The CHECK option for views is not enforced when DML to the view are performed by Instead of Trigger.


What is the Sequence of Firing Database Triggers?

a) Before Row Level Trigger
b) Before Statement Level Trigger
c) After Row Level Trigger
d) Statement Operation
e) After Statement Level Trigger


What is the Difference between PL/SQL Table & Nested Table?

PL/SQL Table: Index by Tables are not Stored in Database.
Nested Table: Nested Tables are Stored in Database as Database Columns.


What is the Difference between Nested Table & Varray?

Nested Table
a) This are Sparse

b) We can Delete its Individual Elements
c) It do not have an Upper Boundary
d) This are Stored in System Generated Table

a) This are Dense

b) We cannot Delete its Elements
c) This are Fixed Size & always need to specify the size
d) These are Stored in Tablespaces


What are the various SQL Statements?

a) Data Retrieval: Select
b) DML: Insert, Update, Delete
c) DDL: Create, Alter, Drop, Rename, Truncate
d) Transaction Control: Commit, Rollback, Savepoint
e) DCL: Grant, Revoke
f) Session Control: Alter Session, Set Role
g) System Control: Alter System
h) Embedded SQL Statements: Open, Close, Fetch & Execute.

What is Rowid?

It is a Hexadecimal Representation of a Row in a Table. Rowid can only be Changed if we ‘Enable Row Movement’ on a Partitioned Table. Rowid’s of Deleted Rows can be Reused if Transaction is Committed.


What is Partitioning?

It Enables Tables & Indexes or Index-Organized tables to be subdivided into smaller manageable Pieces & these each small Piece is called Partition.
They are of following Types:
a) Range Partitioning
b) Hash Partitioning
c) List Partitioning
d) Composite Range-Hash Partitioning

What is a Cluster?

A cluster provides an optional method of storing table data. A cluster is comprised of a group of tables that share the same data blocks, which are grouped together because they share common columns and are often used together. For example, the EMP and DEPT table share the DEPTNO column. When you cluster the EMP and DEPT, Oracle physically stores all rows for each department from both the EMP and DEPT tables in the same data blocks. You should not use Clusters for tables that are frequently accessed individually.


What is the Difference between Nested Subquery & Correlated Subquery?

Nested Subquery
a) Inner Query runs first and executes once, returning values which are to be used by the Main query or outer query

b) Outer query is driver by Inner Query

Correlated Subquery
a) A Correlated Subquery or Inner Query execute once for each candidate row considered by outer query

b) Inner Query is Driven by Outer Query


What is the Difference between Translate & Replace?

Translate function converts each character in String with specified one whereas Replace function replaces part of the string in continuity by another sub-string.


FAQs in Oracle Forms – Part:3

FAQs in Oracle Forms – Part:3


What is the difference between Pre-Form and When-New-Form-Instance trigger?

Pre-Form trigger will be fired before entering into the form. It is the first trigger that fires when a form is run; fires before the form is visible. It is useful for setting access to form items, initializing global variables, and assigning unique primary key from an Oracle sequence.

When-New-Form-Instance trigger will be fired whenever form is ready to accept the data from the user.

Another main difference between the two is that you cannot navigate in a pre-from trigger (restricted) whereas you can navigate in a when-new-form-instance trigger. For example : go_block and execute-query will only work in when-new-form-instance trigger not in pre-from trigger.

What are the triggers fired while creating Master Detail form?

On-Clear Details (Form Level)
On-Populate-Details (Block Level)
On-Check-delete-Master (Block Level)

How will you get the block Name in a form?


What is the difference between Pre-insert and On-insert trigger?

Pre-insert trigger fires during the Post and Commit Transactions process, before a row is inserted. It fires once for each record that is marked for insert. On-insert trigger fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.

What is the difference between Pre-Query and Post-Query trigger?

Pre-Query trigger validates the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database. Post-Query trigger perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. It fires once for each record fetched into the block.

What is the trigger sequence while opening a form?


What is the difference between new form, open form & call form?

New_form:-Once we move into the destination automatically source will be closed.

Open_form:- It is a two way connection between source and destination. Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.

Call_form:- Call_form() runs an indicated form while keeping the parent form active. Without closing the destination one cannot come back to the source.

How to call a Report from a form?

By using RUN_PRODUCT Built-in.

Can we write Commit statement in forms triggers?


What is the usage of an ON-INSERT,ON-DELETE and ON-UPDATE TRIGGERS ?

These triggers are executes when inserting, deleting and updating operations are performed and can be used to change the default function of insert, delete or update respectively.

A query fetched 10 records. How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed ?

PRE-QUERY fires once. POST-QUERY fires 10 times.

How can you execute the user defined triggers in forms?

To execute a user-named trigger, you must call the EXECUTE_TRIGGER built-in procedure, as shown here: Execute_Trigger(‘my_user_named_trigger’);

What are Restricted Built-in Subprograms in forms?

Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.

For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Oracle Forms to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.) Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers. You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input.

What are System Variables in forms?

A system variable is a Oracle Forms variable that keeps track of an internal Oracle Forms state. You can reference the value of a system variable to control the way an application behaves.

List system variables available in forms 10g?















What are the triggers associated with the image item?

When-Image-activated(fires when the operator double clicks on an image Items)

When-image-pressed(fires when the operator selects or deselects the image item)

What are the built-in routines available in forms to create and manipulate a parameter list?








What are the built-ins used to trapping errors in forms?

Error_type : Returns the error message type(character)

Error_code : Returns the error number

Error_text  : Returns the message text of the Oracle Forms error

Dbms_error_code : Returns the error number of the last database error that was detected.

Dbms_error_text : Returns the message number (such as ORA-01438) and message text of the database error

What is the predefined exception available in forms ?

The FORM_TRIGGER_FAILURE exception is a predefined PL/SQL exception available only in Oracle Forms.

What are the Built-ins used for sending Parameters to forms?

You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.

How do you reference a Parameter?

In pl/sql, You can reference and set the values of form parameters using bind variables syntax.

How do you reference a parameter indirectly?

To indirectly reference a parameter use the NAME_IN or COPY built-ins.

What is forms_DDL?

It issues dynamic sql statements at run time, including server side pl/sql and DDL

What is Text_io Package?

It allows you to read and write information to a file in the file system.

What is When-Database-Record trigger?

It fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. It generally occurs only when the operator modifies the first item in the record, and after the operator attempts to navigate out of the item.

What is the difference between $$DATE$$ & $$DBDATE$$?

$$DBDATE$$ retrieves the current database date

$$DATE$$ retrieves the current operating system date.

What is a timer?

Timer is a “internal time clock” that you can programmatically create to perform an action each time the timer expires.

What are built-ins associated with timers?




What is the difference between post database commit and post-form commit?

Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.

What is the form development process?

a) open template form 

b) Save as <your form>.fmb

c) Change the form module name as form name.

d) Delete the default blocks, window, and canvas

e) Create a window.

f) Assign the window property class to window

g) Create a canvas   

h) Assign canvas property class to the canvas

i) Assign the window to the canvas and canvas to the window

j) Create a data block       

k) Modify the form level properties. (sub class item à Text item)

l)  Modify the app_cusom package. In the program unit.

m) Modify the pre-form trigger (form level)

n) Modify the module level properties

o) Save and compile the form.

p) Place the .fmx in the server directory.

q) Register in the AOL


What is template?

The TEMPLATE form is the required starting point for all development of new Forms. The TEMPLATE form includes platform–independent attachments of several Libraries.

APPSCORE :- It contains package and procedures that are required of all forms to support  the MENUS ,TOOLBARS.

APPSDAYPK :- It contains packages that control the oracle applications CALENDER FEATURES.


CUSTOM :- It allows extension of oracle applications forms with out modification of oracle application code, you can use the custom library for customization such as zoom    ( such as moving to another form and querying up specific records)

Where exactly you place your forms in APPS environment?


What are the Different PLL’s used in Forms?







What are the triggers that can be modified during Forms Customization?







What are the triggers that cannot be modified during Forms Customization?









FAQs in Oracle Forms – Part:2

FAQs in Oracle Forms – Part:2


What is a display item?

Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.

How many maximum number of radio buttons can you assign to a radio group?

Unlimited no of radio buttons can be assigned to a radio group.

Can you change the default value of the radio button group at run time?


What triggers are associated with the radio group?

Only when-radio-changed trigger associated with radio group.

What are the various states of a form?

CHANGED: Indicates that the form contains at least one block with a Changed record.

NEW: Indicates that the form contains only New records.

QUERY: Indicates that a query is open.

What are the different objects that you cannot copy or reference in object groups?

objects of different modules

another object groups

individual block dependent items

program units

What are the different modals of a window?

Modeless windows

Modal windows

What are Modeless windows?

More than one Modeless window can be displayed at the same time, and operators can navigate among them if your application allows them to do so . On most GUI platforms, Modeless windows can also be layered to appear either in front of or behind other windows.

What are modal windows?

Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.

How do you display console on a window ?

The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned. To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.

Can you have more than one content canvas attached with a window?

Yes. Each window you create must have at least one content canvas assigned to it. You can also create a window that has manipulate content canvas. At run time only one of the content canvas assign to a window is displayed at a time.

How many windows in a form can have console?

Only one window in a form can display the console, and you cannot change the console assignment at runtime.

What are the different window events activated at runtimes?





When a form call a pl/sql routine if there is an error in the pl/sql routine how do you place custom message in the form?

FND_MESSAGE.SHOW displays an informational message in a forms modal window or in a concurrent program log file only.
fnd_message.set_string(‘Message Text’);;
FND_MESSAGE.HINT to display a message in the forms status line and FND_MESSAGE.ERASE to clear the forms status line. FND_MESSAGE.HINT takes its message from the stack, displays the message, and then clears that message from the message stack.

List the built-in routines for controlling a window during run-time?








What built-in is used for changing the properties of a window dynamically?


What built-in is used for showing an alert during run-time?


Can you change alert messages at run-time?

Yes. By Set_alert_property.

What is the built-in function used for finding the alert?


What built-in routines are used to display editor dynamically?

Edit_text item


What is the difference between COPY and NAME_IN ?

Copy is package procedure that writes values into a field. Name_in is a package function that returns the contents of the variable to which you apply.

Can you attach an lov to a field at run-time?

Yes. By Set_item_proprety

What is the built-in used to get and set lov properties during run-time?



Give built-in routines related to a record groups?

Create_group (Function)









What is the built-in routine used to count the no of rows in a group?


What are the built-ins that are used to attach an LOV programmatically to an item?



How many number of columns a record group can have?

A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of column does not exceed 64K.

What is the Maximum allowed length of Record group Column?

Record group column names cannot exceed 30 characters.

What are the built-ins used for Creating and deleting groups?

CREATE-GROUP (function)



What are the difference between Lov & List Item?

Lov is a property where as list item is an item. A list item can have only one column while a lov can have one or more columns.

What are the different display styles of list items?

Pop List

Text List

Combo box

What is Pop List?

The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.

What is a Text List?

The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.

What is a Combo Box?

A combo box style list item combines the features found in list and text item. Unlike the pop list or the text list style list items, the combo box style list item will both display fixed values and accept one operator entered value.


FAQs in Oracle Forms – Part:1

FAQs in Oracle Forms – Part:1


What are different types of modules available in oracle form?

Form module – a collection of objects and code routines.

Menu module – a collection of menus and menu item commands that together make up an application menu.

Library module – a collection of user named procedures, functions and packages that can be called from other modules in the application.

What are the default extensions of the files created by forms modules?

.fmb – form module binary

.fmx – form module executable

What are the default extensions of the files created by menu module?

.mmb, .mmx

What is data block & control block?

Data Block:-It is a Logical Collection of Items.
Control Block:-It is a data block which is totally independent of Database Table or View.

Note: – A data block can have control block items but not vice versa.

What is property class & visual attributes?

Property Class:

A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent.

Visual Attributes:

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application’s interface. Visual attributes can include the following properties: Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight Color and pattern properties: Foreground Color, Background Color, Fill Pattern. Every interface object has a Visual Attribute Group property that determines how the object’s individual visual attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be set to Default, NULL, or the name of a named visual attribute defined in the same module.

What is the difference between property class and visual attribute?

We can change Visual Attribute properties dynamically at runtime, but we cannot change Property class properties. When you inherit the both Visual Attribute properties and Property class properties to an item Visual Attribute properties overrides the Property class properties.

What is object group?

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 subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

What is Record Group?

It is an internal memory data structure and a separate object in the form module. It’s main usage is to provide data to the LOV and dynamically to the list item and to perform client level validations.

What are the types of Record-Groups?

Static Record Group:- A static record group is not associated with a query; instead, you define its structure and row values at design time, and they remain fixed at runtime. Static record groups can be created and modified only at design time.

Query Based 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. Non-query record groups can be created and modified only at runtime.

How to change Record Group dynamically?

By Using Non-Query Record Group.

What is Library?

A library is a collection of subprograms, including user-named procedures, functions, and packages. We can attach a library to any other form, menu or library module. It provides an easy method of reusing objects and enforcing standards across the entire development organization.

What are the types of canvases available in forms & what is the default canvas?

Content:-it is the base view of window which occupies the entire surface of window. It can have any no of canvases but at a time only one is visible.

Stacked:-It is always displayed above the content canvas because the content Canvas is the base view. It can have any no of stacked canvases and more than one stacked canvas can be displayed at a time.

Tool bar:- A toolbar canvas often is used to create toolbars for individual windows. There are two types of tool bars Horizontal and Vertical Tool bar Canvas. Horizontal tool bar canvases are displayed at the top of window and only one horizontal tool bar can be attached to a form module. Vertical tool bar is used to display top to bottom on the left side of the window.

Tab:-It is a collection of one or more tab pages. It is mainly used to display a large amount of related information a single dynamic form builder canvas object.

Content Canvas is the default canvas.

What are Alerts?

An alert is a modal window that displays a message notifying the operator of some application condition. There are three styles of alerts: Stop, Caution, and Note.
To display an alert, your application must execute the SHOW_ALERT built-in subprogram from a trigger or user-named subprogram. SHOW_ALERT is a function that returns a numeric constant.

Show_Alert(alert_name) Return NUMBER;

What are LOVs?

An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. LOV values are derived from record groups. The LOVs in the Form Builder interface have the same auto-reduction and searching functionality as the LOVs you create for your own applications.

There are two built-in subprograms that can be used to display an LOV:

List the system variables related in Block and Field?

1. System.block_status

2. System.current_block

3. System.current_field

4. System.current_value

5. System.cursor_block

6. System.cursor_field

7. System.field_status

What is a master detail relationship?

A master detail relationship is an association between two base table blocks- a master block and a detail block. The relationship between the blocks reflects a primary key to foreign key relationship between the tables on which the blocks are based.

What are the Various Master and Detail Relationships?

The various Master and Detail Relationship are:

a) NonIsolated :: The Master cannot be deleted when a child is existing.

b) Isolated :: The Master can be deleted when the child is existing.

c) Cascading :: The child gets deleted when the Master is deleted.

What are the procedures that will be created when a master details form created?


What is mouse navigate property of button?

When Mouse Navigate is True (the default), Oracle Forms performs standard navigation to move the focus to the item when the operator activates the item with the mouse. When Mouse Navigate is set to False, Oracle Forms does not perform navigation (and the resulting validation) to move to the item when an operator activates the item with the mouse.

What is difference between PL/SQL library and object library in Forms?

PL/SQL library contains only pl/sql codes which can be used for standardizing the validations etc. in forms whereas Object Library is used to create, store, maintain, and distribute standard and reusable Form objects.

What are the different default triggers created when Master Deletes Property is set to Nonisolated?




What are the different default triggers created when Master Deletes Property is set to Cascade?




What are the different default triggers created when Master Deletes Property is set to isolated?



What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?

Show_editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.

What is the “LOV of Validation” Property of an item? What is the use of it?

When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.