Interview Questions and Answers for Oracle Report6i

1. What are the various types of reports ?

Ans : Tabular, Master Detail, Form, Form Letter, Mailing Labels, Matrix

2. What is the difference between Master - Detail Report and report created by breaks ?

Ans : Master/detail data models are very similar to break report data models. However, a master/detail data model is created using two queries, each of which owns at least one group, and a data link. A break report data model is created using one query and at least two groups. While reports based on a single query are usually more efficient than reports based on multiple queries, sometimes the structure of your data tables may require you to link multiple tables.

3. What are Anchors ?

Ans : An anchor defines the relative position of an object to the object to which it is anchored. Anchors are used to determine the vertical and horizontal positioning of a child object relative to its parent. Since the size of some layout objects may change when the report runs (and data is actually fetched), you need anchors to define where you want objects to appear relative to one another.

4. What are the various types of anchors in Reports ?

Ans : A There are two types of anchors in Oracle Reports:

       (1)    Implicit (anchors that Oracle Reports creates when a report is run) 

               At runtime, Oracle Reports generates an implicit anchor for each layout object that does not already have an explicit anchor. It determines for each layout object which objects, if any, can overwrite it, then creates an anchor from the layout object to the closest object that can overwrite it. This prevents the object from being overwritten. The implicit anchor functionality saves you from having to define the positioning of each object. Implicit anchors are not visible in the Layout editor. However, you can specify that the Object Navigator display anchoring information using the Object Navigator Options dialog.

       (2)   Explicit (anchors you create)

              Create an anchor in the Layout editor by clicking on the Anchor tool, dragging from one edge of the child to the one of the parent's edges, then specifying the anchor's properties in its property sheet. Any anchor you create for an object will override its implicit anchoring. Explicit anchors are always visible in the Layout editor unless you specify otherwise via the Layout Options dialog

5. What are the various report triggers ?

Ans : There are eight report triggers.

       Global triggers called the Report Triggers :

       (1) Before Parameter Form

       (2) After Parameter Form

       (3) Before Report

       (4) After Report

       (5) Between Pages

        Other Triggers :

        (1) Validation Triggers

        (2) Format Triggers

        (3) Action Triggers

6. What is order of firing report triggers ?

Ans : Report triggers are fired in following orders :

       (1) Before Parameter Form

            Fires before the Runtime Parameter Form is displayed. From this trigger, you can access and change the values of parameters, PL/SQL global variables, and report-level columns. (Note : If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters).

       (2) After Parameter Form

            Fires after the Runtime 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 Runtime Parameter Form. Columns from the data model are not accessible from this trigger. (Note : If the Runtime Parameter Form is suppressed, the After Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data).

       (3) Before Report

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

       (4) 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. (Note : In the Previewer, this trigger only fires the first time that you go to a page. If you subsequently return to the page, the trigger does not fire again.)

       (5) After Report

            Fires after you exit the Previewer, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle*Mail userid. This trigger can be used to clean up any initial processing that was done, such as deleting tables. Note, however, that this trigger always fires, whether or not your report completed successfully.

       (6) 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 Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet.

       (7) 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.

       (8) 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.

7. What are Placeholder Columns ?

Ans : A placeholder is a "dummy" column for which you can conditionally set the datatype and value via PL/SQL or a user exit. Placeholder columns are useful when you want to selectively populate a column with a value (e.g., each time the nth record is fetched, or each time a record is fetched containing a specific value, etc.).

8.  What are the various Module Types in Reports ?

Ans : You can build three types of modules with Oracle Reports:

        (1) external queries, which are ANSI-standard SQL SELECT statements that can be referenced by modules

        (2) external PL/SQL libraries, which are collections of PL/SQL source code that can be referenced by modules

       (3) reports, which are collections of report-level objects and references to external queries and PL/SQL libraries (optional) that can be referenced by modules

9. What are Physical and Logical pages in Reports ?

Ans : A report page can have any length and any width. Because printer pages may be smaller or larger than your report's "page," the concept of physical and logical pages is used.

       Physical Page : A physical page (or panel) is the size of a page that will be output by your printer.

      Logical Page : A logical page is the size of one page of your actual report; one logical page may be made up of multiple physical pages. The Previewer displays the logical pages of your report output, one at a time.

10. What are Bind Referencing and Lexical Referencing ?

Ans : Bind Referencing : Bind references are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Use bind reference when you want the parameter to substitute only one value at runtime. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries.

        Lexical Referencing : Lexical references are placeholders for text that you embed in a SELECT statement. Use Lexical reference when you want the parameter to substitute multiple values at runtime. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.

11. What is 'page protect' property for objects ?

Ans : Page protect property for an object indicates whether to try to keep the entire object and its contents on the same logical page. Checking Page Protect means that if the contents of the object cannot fit on the current logical page, the object and all of its contents will be moved to the next logical page.

12. What is the 'Print Condition Type' property ?

Ans : 'Print Condition Type' property specifies the frequency with which you want the object to appear in the report. The Print Condition Type options indicate the logical page(s) on which the object should be triggered to print with regard to the Print Condition Object.

13. What are the various report layout regions ?

Ans : There are three report regions in the Layout editor :

        (1) Header

            The report header pages appear once at the beginning of each report on a set of separate pages. They can contain text, graphics, data, and computations.

        (2) Body/Margin

             The body/margin pages appear between the header and trailer pages, and are the bulk of the report. Each physical page in this section consists of a body and a margin. The body contains the majority of the report's text, graphics, data, and computations.

             A top and bottom margin appear on each page, until all data within the body has been formatted. A margin may include text, graphics, page numbers, page totals, and grand totals. The default margin size is one half inch each for the top and bottom margins and zero for the left and right margins.

        (3) Trailer

              The report trailer pages appear once at the end of each report on a set of separate pages. They can contain text, graphics, data, and computations.

14. Name some of the procedures in the SRW package ?

Ans : SRW.Message, SRW.User_Exit, SRW.Do_Sql, SRW.Run_Report

15. What is the 'Print Direction' Property of Repeating frames ?

Ans : 'Print Direction' Property specifies the direction in which successive instances of the repeating frame appear.

16. What are the various values of the 'Print Direction' Property of Repeating frames ?

Ans : Across : Across means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance across the logical page.

       Across/Down : Across/Down means that each instance of the repeating frame subsequent to the first instance is printed to the right of the previous instance until an entire instance cannot fit between the previous instance and the right margin of the logical page. At that time, Oracle Reports prints the instance below the left-most instance on the logical page, provided there is enough vertical space left on the logical page for the instance to print completely.

       Down : Down means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance down the logical page.

       Down/Across : Down/Across means that each instance of the repeating frame subsequent to the first instance is printed below the previous instance until an entire instance cannot fit inside the bottom margin of the logical page. At that time, Oracle Reports prints the instance to the right of the topmost instance on the logical page, provided there is enough horizontal space left on the logical page for the instance to print completely.

17. What is the 'Keep with Anchoring Object' object property ?

Ans : 'Keep with Anchoring Object' object property indicates whether to keep an object and the object to which it is anchored on the same logical page. Checking Keep with Anchoring Object means that if the object, its anchoring object, or both cannot fit on the logical page, they will be moved to the next logical page.

18. What is 'Page Break Before' object property ?

Ans : 'Page Break Before' object property indicates that you want the object to be formatted on the page after the page on which it is initially triggered to print. Note that this does not necessarily mean that all the objects below the object with Page Break Before will move to the next page.

19. What is 'Page Break After' object property ?

Ans : 'Page Break After' object property indicates that you want all children of the object to be moved to the next page. In other words, any object that is a child object of an anchor (implicit or explicit) to this object will be treated as if it has Page Break Before set. Note that this does not necessarily mean that all the objects below the object with Page Break After will move to the next page.

20. What is the 'Break Order' property of columns ?

Ans : The 'Break Order' property is the order in which to display the column's values. This property applies only to columns that identify distinct values of user-created groups (i.e., break groups). The order of column values in a default group is determined by the ORDER BY clause of the query. For column values in user-created groups, however, you must use Break Order to specify how to order the break column's values.

21. Types of Matrix report ?

Ans : There are 4 different types of Matrix reports available

         1. Single query

         2. Multi query

         3. Nested Query

        4. Matrix Break