Interview FAQ's-Oracle Reports

1.      REPORT TYPES

A) Tabular Report                                     B) Group Left Report

C) Group Above Report                             D) Form like report

E) Matrix Report                                       F) Multi Media Report

G) Mailing Label Report                           H) OLE Report.

2.      ANCHOR

Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent object. The end of the anchor should be attached to the parent object.

A) Parent Object                                       B) Child Object

Since the size of the some layout objects may change when the report runs (When the data is actually fetched), anchors need to be defined to make the appearance of the object.

An Anchor defines the relative position of an object to the object to which if this anchored.

* Symbol is by pressing the shift key Anchor can be moved.

Anchor Properties :

A) Child Edge percent on child edge type            B) Child Object Name

C) Collapse Horizontally                                       D) Collapse vertically

E) Comments                                                                F) Name

* Two child objects can be related to one parent object by an Anchor.

3.      User Exits :

An user exit is :- Program that can be written and linked into the report builder executable or user exit DLL files.

User exits are build when ever the control need to be passed from the report builder to a program, which performs some function and then control returns to the Report Builder.

Types of user exits :-

A)     Oracle Pre-Complier user exits.

B)     OCI (Oracle Call Interface user exits)

C)     Non – Oracle user exits.

        User exits can perform the following tasks.

Perform complex data manipulation.

Pass data to report builder from OS text files.

Support PL/SQL blocks.

Control real time devices be printer or robot.

4.      Types of Triggers in Reports :-

* Report Triggers  * Data model Triggers               * Layout Triggers

Report Triggers :-

After Parameter form Trigger.

After Report Trigger

Before parameter form Trigger

Before Report Trigger.

Between Pages Trigger

+ Firing Sequence

* Before Parameter form Trigger

-          Fires before the runtime parameter form is displayed.

-          The parameter values can be accessed and changed.

*    After Parameter form Trigger.

-          fires after the runtime parameter form is displayed.

-          The parameters can be accessed and their values can be checked.

*    Before Report Trigger

-          Fires before the report is executed but after the queries are passed and data is fetched.

*    Between Pages Trigger.

-          Fires between each page of the report is formatted, except the very first pages.

-          This is used for customized page formatting.

*    After Report Trigger

-          Fires after exiting from the run time premier or after report output is sent to a specified destination. (File, Printer, Mai lid etc….)

-          This is used to clean up any initial processing that was done such as deleting the tables.

-          This Trigger always fires irrespective of success or failure of the report.

DATA TRIGGERS:

*    Ref Cursor Query:

-          This uses PL/SQL to fetch data for the report.

-          In this a PL/SQL function need to be specified to return a cursor value from a cursor variable.

* Group Filter:

This is PL/SQL function that determines which records to be included in a group in the         property is PL/SQL

-          The function must return a BOOLEAN value.

True ……. Includes the current record in the report.

            False ……. Excludes the current record from the report.

    

     *  Formula Trigger

-          These are Pl/SQL functions that populate formula or place holder columns.

* Validation Trigger       

-          These are also PL/SQL functions that are executed when parameter values are specified on the command line and when the runtime parameter form is accepted.

-          Are also used to validate the initial value property of the parameter.

* Layout Triggers

* Format Trigger.

-          These are PL/SQL functions executed before the object is formatted.

-          Used to dynamically change the formatting attributes of the object.

* Action Trigger

-          These are Pl/SQL procedures executed when a button is selected in the run time previewer.

-          This can be used to dynamically call another report or execute any other PL/SQL.

5    Formula Column

It performs a user-defined computation on another columns data, including Placeholder columns.

Formulas are PL/SQL functions that populate formula or place holder columns.

Cannot be used to populate parameter values.

6        Summary Column

-          Performs a computation on another columns data like sum, average, count, minimum, maximum, %, total.

-          For group reports, the report wizard and data wizard create ‘n’ summary fields in the data model for each summary column that is defined.

          -à One at each group level above the column being summarized.

           -à        One at the report level.

7        Place Holder Column 

-          A Place holder column is a column for which, the data type and value can be set dynamically (Programmatically)

*    The value can be set for a place holder column in the following places.

-          Before report trigger if the place holder is a report level column.

-           Report level formula column, if the place holder is a report level column.

-          A formula in the place holders group below it (The value is set once for each record of the group)

8        Repeating Frame 

-          Repeating frame surrounds all of the fields that are created for a groups columns.

-          Repeating frame prints once for each record of the group.

-          For frames and repeating frames, the property elasticity defines whether the size of the frame or repeating frame should with the objects inside of it at runtime.

9        Frame

-          Surrounds the objects and protect them from being over written or pushed by other objects.

10     System Parameters in Reports

* Background                                * Copies                                  * Currency                 

*Decimal                                       * Desformat                *Desname

* Destype                                      * Mode                                    * Orientation

* Print Job                                     * Thousands.

11     Data Link

-          Data links relate the results of multiple queries.

-          A data link (Parent – Child Relation Ship) causes the child query to be executed once for each instance of its parent group.

12      Parameters

-          A parameter is a variable whose value can be set at runtime (from the run time parameter of the command line).

-          User parameters are created by the user and system parameters are created by Report Builder.

-          System parameters cannot be renamed or deleted.

Bind Parameters (Variables)

-          Bind references  (or Variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number or date.

-          Bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY and START WITH clauses of queries.

-          Bind references cannot be referenced in FROM clauses.

-          Bind variables can be referenced by entering a colon (:) followed immediately by the column or parameter name.

-          If the parameter / column is not created before making a bind reference, report builder will create a parameter.

Lexical Parameters (Variables)

-          Lexical references are place holders for text that is embedded in a SELECT statement.

-          Lexical Variables can replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

*************************************************************************************