Oracle Reports

What is an Oracle Report? List its various types.

Ans: Oracle Report is a tool provided by Oracle Fusion Middleware, which is used to generate reports based on the data stored in the Oracle database. It consists of Oracle Reports Developer, a report designing tool and Oracle Application Server Reports Services.

Various types of reports include:

• Tabular
• Master-Detail Reports
• Form Reports
• Form Letter Reports
• Mailing Labels Reports
• Matrix Reports

What is an implicit anchor and how is it a different form explicit anchor in a report builder?

Ans: An anchor is used to determine the position of an object in horizontal and vertical directions. This position of an object will always be relative to a position of the other objects, which can be called as parent objects for these child objects.

During runtime, an implicit anchor will be generated by the Oracle Forms Builder for each layout object, which is not holding an explicit anchor. Implicit anchor will be created during runtime of a report while explicit anchors are created by a user explicitly.

Name the different triggers supported by Oracle Reports and their firing order.

Ans: Listed below are the triggers supported by Oracle reports:

• Before Parameter Form: Gets fired before a display of runtime parameter form on a screen.
• After Parameter Form: Gets fired after the display of runtime parameter form on a screen.
• Before Report: Gets fired before the execution of a report but after the queries get parsed.
• Between Pages: Gets fired before formatting is done for every page except the first page.
• After Report: Gets fired either at the exit of Previewer or once the report output is shared with a destination.

What is the difference between bind and lexical parameter?

Ans: Bind parameters are the variables, which can replace a single value in SQL/PLSQL such as number, character, string or date.

While lexical parameter can replace clauses or multiple values embedded in SELECT query possibly after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

List the different types of columns in Oracle Reports.

Ans: There are three types of columns in Oracle reports.

They are:

• Formula Columns: Column that can do user-defined calculations on values within other columns and return some value.
• Summary Columns: Column, which can do summary computations like sum, average etc. on values placed in the other columns.
• Placeholder Columns: Column for which data type or value can be set using PL/SQL.

What is a User exit program in oracle reports?

Ans: User exit is a program which is written to perform some relevant action. They can be called from report triggers and once executed, it gives back the control to Report Builder.

Few of the user exits are listed as shown below:

• FND SRWINIT
• FND SRWEXIT
• FND FORMAT_CURRENCY
• FND FLEXSQL
• FND FLEXIDVAL

How can we generate report output in Excel format?

Ans: To get report data in an Excel format, we can use:

• SPOOL Command
• Text_IO Package
• UTL Package

What is the difference between flex mode and confined mode?

Ans: Confined mode if set restricts the child object within enclosing parent objects. If not set on, child an object can move out of parent objects.

During flex mode, parent objects will adjust its border if child object expands or moves. If not set, parent borders stay fixed when the child objects move.

What is a matrix report and how many minimum groups are required to prepare the same?

Ans: A matrix is a kind of report that looks like an information grid with one row of labels and one column of columns.

At least 4 groups are required in the data model to prepare a matrix report. One should be a cross product group, one cell group & at least two groups should be within a cross product group.

Is it possible to have multiple layouts in a report?

Ans: Yes, it is possible to have multiple layouts. We can use additional layout option in the layout editor tool.

Conclusion

Hope the above article will help you to prepare for your interview and increase your knowledge of the concept of Oracle Forms and Reports.

Oracle Report Triggers

There are eight report triggers. Of these, there are five global triggers called the Report Triggers. They are fired in the following order :

* Before Parameter Form

* After Parameter Form

* Before Report

* Between Pages

* After Report

Apart from the above Five Report Triggers, there are three other types of triggers:

* Validation Triggers

* Format Triggers

* Action Triggers

Before Form: Fires before the run-time Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns.

After Form: Fires after the run-time Parameter Form is displayed. From this trigger, you can access parameters and check their values. This trigger can also be used to change parameter values or, if an error occurs, return to the run-time Parameter Form. Columns from the data model are not accessible from this trigger.

Before Report: Fires before the report is executed but after queries are parsed and data is fetched.

Between Pages: Fires before each page of the report is formatted, except the very first page. This trigger can be used for customized page formatting.

After Report: Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer. This trigger can be used to clean up any initial processing that was done, such as deleting tables.

Validation Triggers: Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the run-time Parameter Form. Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

Format Triggers: Format Triggers are PL/SQL functions executed before the object is formatted. The trigger can be used to dynamically change the formatting attributes of the object.

Action Triggers: Action Triggers are PL/SQL procedures executed when a button is selected in the Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL.

What is a lexical parameter?

Lexical parameter can be used to replace clauses after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH

Cannot make lexical reference in PL/SQL statement