Oracle Report Interview Questions

1. What are the types of layouts in reports?

There are 8 default layout style provided.

a) Tabular - It is the most basic type of report where each column corresponds to a column selected from the database.

b) Form-like - It is a report one record per page. The field value is placed to the field labels.

c) Mailing Label - A mailing label report prints mailing labels in multiple columns on each page. The printing can be a landscape or a portrait.

d) Form Letter - A Form letter report contains database values embedded into any text that is entered or imported into the report editor.

e) Group Left - A group left report divides the rows of a table into sets, based on a common value in one of the columns. This style is used to restrict a column from repeating the same value several times while values of related columns change.

f) Group Above - A group above repot contains two or more groups of data. For every value of the master group, the related values of the detail group(s) are fetched from the database.

g) Matrix - A matrix (cross tab) report contain one row of tables, one column of labels and information in a grid format that is related to the row and column labels.

A distinguished feature of matrix report is that the number of columns is not known until the data is fetched from the database.

To create a matrix report, at least four groups are required. One group must be a cross-product group, two of the groups must be within the cross-product group to furnish the labels, and at least one group must provide the information to fill the cells. The groups can provide to a single query or to multiple queries.

h) Matrix with Group: A matrix with group report is a group above report with a separate matrix for each value of the master group.

2. What are the different layout objects?

There are 4 types of layout objects.

a) Repeating Frames.

b) Frames.

c) Fields.

d) Boilerplate.

a) Frames: Frames surrounds other layout objects, enabling control of multiple objects

Simultaneously, ensuring that they maintain their positions relative to each other in the report. A frame might be used to surround all objects owned by a group, to surround column labels.

b) Repeating Frames: Repeating frames act as placeholder for groups (repeating values) and present rows of data retrieved from the database. Repeating frames repeat as often as the number of rows retrieved.

c) Fields: Fields act as placeholder for column values; they define the formatting attributes for all columns displayed in the report. A field is one of the objects that can be located inside a frame or repeating frame.

d) Boilerplate: Boilerplate consists of text and graphics that appear in a report each time it is run; e.g., a label appearing above a column of data is boilerplate text. Graphics drawn in the layout as well as text added to the layout are boilerplate.

3. What is data model?

To specify the data for the report, a data model should be defined. A data model is composed of some or all of the following data definition objects.

a) Queries - Queries are ANSI-standard SQL SELECT statements that fetch data from a standard database such as Oracle, DB2 etc. These SELECT statements are fired each time the report is run.

You can select any number of queries to select data from any number of tables.

b) Groups - Groups determine the hierarchy of data appearing in the report, and are primarily used to create breaks in the report. Oracle report automatically creates a group for each query, but you are not limited to this default. You can create a new group in the data model and included a column that you want to use as the break column.

c) Column - Column contain the data values for a report. Default report columns, corresponding to the table columns included in each query's SELECT list are automatically created by the table Oracle Report, and then each column is placed in the group associated with the query that selected the column. If you want to perform summaries and computations on the database column values, you can create new columns. You can also reassign one or more columns to a group you've created.

d) Parameters - Parameters are visible for your reports that enable you to change selection criteria at runtime. Oracle Report automatically creates a set of system parameters at runtime, but you can create your own as well. You can create parameters to replace either single literal values or entire expressions in any part of a query. You can reference parameters elsewhere in the report, such as in PL/SQL constructs providing conditional logic for the report.

e) Data Link - Data link are used to establish parent-child relationship between queries and groups via column matching.

4. What is anchor?

Anchors fasten an edge of one object to an edge of another object, ensuring that they maintain their relative positions. For example, you can anchor boilerplate text to the edge of a variable-sized repeating frame, guaranteeing the boilerplate's distance and position in relation to the repeating frame, no matter how the frame's size might change.

5. What types of triggers are there in report?

As a general rule, any processing that will affect the data retrieved by the report should

be performed in the Before Parameter Form and After Parameter Form trigger.(These are the two report trigger that fire before anything is parsed or fetched). There are five global report triggers. You cannot create new global report triggers. The trigger names indicate at point trigger fires:

1. Before Report: Fires before the report is executed but after queries are parsed.

2. After Report: Fires after you exit the Previewed, or after report output is sent to a specified destination, such as a file, a printer, or an Oracle Office 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.

3. 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. In the Previewer, this trigger only fires the first time that you go to page. If you subsequently

return to the page, the trigger does not fire again.

4. 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. If the Runtime Parameter Form is suppressed, this trigger still fires. Consequently, you can use this trigger for validation of command line parameters.

5. 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. If the Runtime Parameter Form is suppressed, the After Parameter Form trigger still fires. Consequently, you can use this trigger for validation of command line parameters or other data.

6. What is the order of execution of report trigger?

Order of execution of report trigger:

1. Before Parameter Form trigger is fired.

2. Runtime Parameters Form appears (if not suppressed).

3. After Parameters Form trigger is fired (unbless the user cancels from the Runtime  

    Parameter Form).

4. Report is "compiled".

5. Queries are parsed.

6. Before Report trigger is fired.

7. Set TRANSACTION READONLY is executed (if specified via the READONLY  

    argument or setting).

8. The report is executed and the Between Pages trigger fires for each page except the last one. (Note that data can be fetched at any time while the report is being formatted).

7. What is Group filter?

A group filter is a PL/SQL function that determines which records to include in a group, if the Filter Type property is POL/SQL. The function must return a boolean value. Depending on whether the function returns TRUE of FALSE, the current record is included or excluded from the report. You can access group filters from the Object Navigator, the Property Palette (the PL/SQL Filter property), or the PL/SQL Editor.

Definition Level: Group

On Failure: Excludes the current record from the group.

Example:

function filter_comm return boolean is

begin

  if :comm IS NOT NULL then

    if :comm < 100 then

      return(FALSE);

    else

      return(TRUE);

    end if;

  else

    return(FALSE); -- for rows with NULL commissios

  end if;

end;

8. What is Formula Column?

Formula is PL/SQL functions that populate formula or placeholder columns. You can access the PL/SQL for formulas from the Object Navigator, the PL/SQL Editor, or the Property Palette (i.e. PL/SQL Formula property). A column of data type Number can only have a formula that returns a value of data type Number. A column of data type Date can only have a formula that returns a value of data type Date. A column of data type Character can only have a formula that returns a value of datatype CHARACTER, VARCHAR, or VARCHAR2.

Definition Level: column

On Failure: No value is returned for the column.

For example1: formula for adding values

function salcomm return NUMBER is

beign

  return(:sal + :comm);

end;

For example2: formula with condition 

function calcomm return NUMBER is

  temp number;

beign

  if :comm IS NOT NULL then

    temp := :sal + :comm;

  else

    temp := :sal;

  end if;

  return(:temp);

end;

9. What is Place holder Columns?                                                              

A placeholder is a column is an empty container at design time. The placeholder can hold a value at run time has been calculated and placed in to it by pl/sql code from anther object.

You can set the value of a placeholder column is in a Before Report trigger.

Store a Temporary value for future reference. EX. Store the current max salary as records is retrieved.

10. How many types of columns are there and what are they

1. Formula columns: For doing mathematical calculations and returning one value. formulas column compute their values using PL/SQL expressions. Formula can operate on multiple values per record

(e.g.:avg_price*: quantity).

2. Summary Columns: For doing summary calculations such as summations etc.

3. Place holder Columns: These columns are useful for storing the value in a variable.

11. What is Validation trigger?

Validation trigger 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 trigger are also used to validate the Initial Value property of the parameter. The function must return a boolean value.

Definition Level: parameter

On Failure: The user is return to the parameter value in the Runtime Parameter Form where they can either change it or cancel the Runtime Parameter Form.

For Example

/* this function prevent the runtime user from sending report output anywhere except a printer*/

function DESTYPEValidTrigger return boolean is

begin

  if upper(:DESTYPE) = 'PRINTER' Then

    Return(true);

  else

    Return(False);

  end if;

end;

On Failure: No value is returned for the column.

12. What is Format trigger?

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. The function must return a Boolean value (TRUE or FALSE). Depending on whether the function returns TRUE or FALSE, the current instance of the object is included or excluded from the report output. You can access format triggers from the Object Navigator, the Property Palette, or the PL/SQL Editor.

Definition Level: layout object

On Failure:  Excludes the current instance of the object from the output.

Format trigger example (highlighting a value)

/* suppose that you are building a banking report and would like it to indicate if a customer is overdrawn. To do so, you give the repeating frame around the customer information a format trigger that causes it to have a border only if a customer's account balance is less than 0 (or the required minimum balance). */

function my_formtrig return BOOLEAN is begin

  if: bal < 0 then

    srw.attr.mask := SRW.BORDERWIDTH_ATTR;

    srw.attr.borderwidth := 1;

    srw.set_attr (0, srw.attr);

  end if;

    return (true);

end;

13. What is Action trigger?

Action triggers are PL/SQL procedures executed when a button is selected in the Runtime Previewer. The trigger can be used to dynamically call another report (drill down) or execute any other PL/SQL. You can access action triggers from the Object Navigator, the Property Palette (PL/SQL Trigger property), or the PL/SQL Editor.

Definition Level: button

14. What is Ref cursor query?

A ref cursor query uses PL/SQL to fetch data for the report. In a ref cursor query, you specify a PL/SQL function that returns a cursor value from a cursor variable.

Definition Level: query

On Failure: No data is returned to the query.

Package with ref cursor and function example this package spec and body define a ref cursor type as well as a function that uses the ref cursor to return data. The function could be referenced from the ref cursor query, which would greatly simplify the PL/SQL in the query itself. If creating this spec and body as a stored procedure in a tool such as SQL*Plus, you would need

to use the CREATE PACKAGE and CREATE PACKAGE BODY commands. */

PACKAGE cv IS

  type comp_rec is RECORD

  (deptno number,

  ename varchar(10),

  compensation number);

  type comp_cv is REF CURSOR return comp_rec;

  function emprefc(deptno1 number) return comp_cv;

END;

PACKAGE BODY cv IS

 function emprefc(deptno1 number) return comp_cv is

 temp_cv cv.comp_cv;

 begin

   if deptno1 > 20 then

     open temp_cv for select deptno, ename, 1.25*(sal+nvl(comm,0)) compensation

     from emp where deptno = deptno1;

   else

     open temp_cv for select deptno, ename, 1.15*(sal+nvl(comm,0)) compensation

     from emp where deptno = deptno1;

 end if;

  return temp_cv;

 end;

END;

15. Can u have more than one layout in report?

Yes. Is possible to have more than one layout in a report by using the additional layout

option in the layout editor.

16. Can u run the report with out a parameter form?

Yes it is possible to run the report with out parameter form by setting the PARAM value to Null

17. What is the lock option in reports layout?

By using the lock option we cannot move the fields in the layout editor outside the frame. This is useful for maintaining the fields.

18. What is Flex?

Flex is the property of moving the related fields together by setting the flex property on.

19. What is default Unit of Measurement in Report?

Inch.

20. How many types of Parameters are available in Reports?

There are two types of Parameters available in Reports One is System Parameter and another is User Parameter.

Parameters can be used to:

• Restrict values in a WHERE clause SELECT NAME, SALES_REP_ID FROM S_CUSTOMER WHERE ID =User Parameters<a value>

• Substitute any part of select statement SELECT NAME, SALES_REP_ID FROM S_CUSTOMER

<a where clause>

• Substitute a single column or expression SELECT <a column/expression> FROM S_CUSTOMER

There are two ways to reference parameters in a query:

• Use a bind reference.

• Use a lexical reference.

21.  What are bind variables?

Variable that is used to replace a single value in SQL or PL/SQL, such as a character string, number, or date.  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.  Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.

A bind reference replaces a single value or expression. To create a bind reference in a query, prefix the parameter name with a colon (:). If the parameter object does not exist, Report Builder automatically creates it for you and displays a message. In this case, the parameter default datatype is Character, not Number.

22. Difference between Bind and Lexical parameters?

BIND VARIABLE:

-- Are used to replace a single value in sql, pl/sql

-- Bind variable may be used to replace expressions in select, where, group, order 

    by, having, connect by, start with cause of queries.

-- Bind reference may not be referenced in FROM clause (or) in place of reserved words  

    or clauses.

LEXICAL REFERENCE:

-- You can use lexical reference to replace the clauses appearing AFTER select,

     from, group by, having, connect by, start with.

-- You can’t make lexical reference in a pl/sql statement.

23. Which Procedures displays message number and text that you specify?

SRW.MESSAGE: This procedure displays a message with the message number and text that you specify.  The message is displayed in the format below.  After the message is raised and you accept it, the report execution will continue.

24. What are lexical parameters?

You can define lexical parameters in your reports. Lexical parameters can dynamically replace clauses in the Select statement in the data model and even the whole select statement=

A lexical reference replaces any part of a SELECT statement, such as column names, the FROM clause, the WHERE clause, the ORDER BY clause.

To create a lexical reference in a query, prefix the parameter name with an ampersand (&).

If the parameter object does not exist, Report Builder does not create. You must always create the parameter for a lexical reference in the Object Navigator.

Using Lexical References: Use a lexical reference to replace any clause in a SELECT statement, or even replace the entire statement.

Examples

• The following statements use lexical references to substitute parts of the query

at run time.

SELECT NAME, SALES_REP_ID FROM S_CUSTOMER &where_clause

• To specify a WHERE clause, ORDER BY clause, or both at run time (as two separate parameters).

SELECT NAME, SALES_REP_ID FROM S_CUSTOMER &where_clause1, &where_clause2

• To specify a WHERE clause, ORDER BY, or both clause at run time (as one parameter).

SELECT NAME, SALES_REP_ID FROM S_CUSTOMER &where_clause

• To specify two column names and the table name(s) at run time:

SELECT &P_CUSTNAME CUST, &P_SALESREP REP FROM &P_TABLE

Note: When you use lexical references in the SELECT list, you must, at run time, specify the same number of items of the correct datatype, as defined in the Data Model.

25. Which built-in is used to call the required Report?

Run_Product.

26. Can you pass runtime parameters from reports to a Graphics display?

Yes, you can.

27. Do you use bind references to replace reserved words or clauses?

Yes.

28. Which object you create to hold a value that end_users can change at runtime?

User Parameter.

29. Which property handles a situation where an expected parameter is not passed at runtime?

Initial Value.

30. Do you use System Parameter DESNAME to specify the destination type for output?

No.

31. Do you reference parts of the SRW Package in layout format triggers or report level trigger?

Yes, you do.

32. Do you think that you can call the contents of SRW Package form within any of the Developer tools?

No, you do no.

33. Which SRW Package Procedure is used to apply specified formatting attribute to the current layout object?

SRW.SET_ATTR.

34. In a Character Mode environment interaction with the host computer is continuous or not?

It is not continuous.

35. In a Bitmapped Mode environment interaction with the host computer is continuous or not?

It is continuous.

36. Do you think that Reports stored in the database generally execute faster than those stored in the file system?

No.

37. Is it advisable to remove the redundant frames in the layout?

Yes, it is.

38. Can you determine your own search paths for files called by Developer/2000 tools in a windows environment?

Yes, you can.

39. Which file is used in windows environment as the Performance File?

CAUPREFS.ORA.

40. Does Printer Definition file acts as a translator for the Printer?

Yes.

42. 'Reports printer definitions are stored as SQL Plus files', is this statement is true?

No.

43. Do you need to compile Printer definitions files before using them?

No you don't need.

44. Which script deletes Report's specific tables?

SRW2.DROP.SQL.

45. Does Confine mode keep child objects enclosed within their parent frames?

Yes, it is.

46. Do you think that it is possible for you as a developer to assign common properties to multiple objects?

Yes.

47. When you set page break for an object, an object that appears below it always moves to the next page.  Is it true?

No, it is false.

48. What are the categories for report level objects?

Data Model objects, Layout objects, Parameter Form.

49. Is an external query a collection of PL/SQL source code that can be referenced by other modules?

No, it is not.

50. Which design tool defines the appearance of an interface that allows the end-user to supply runtime values to report?

Parameter Form Editor.

51. Can External queries be saved to the database?

Yes, you can.

52. What is contained in the Database objects fields of the Tables and Column names dialog box?

Table and View names.

53. The Master_Detail layout is the combination of which two layouts?

Form and Tabular.

54. By default is there a page break after each record in the form default layout?

Yes, there is.

55. Does a break report always contain a break group?

Yes.

56. Is it true that a break report data model is created using at least two queries and at least one group?

No, it is false.

57. If you minimize the number of break columns in your break groups, will you minimize the number of columns that are added to the order by clause?

Yes, you will.

58. The link causes, which query to be executed for each instance of the Parent group?

Child query.

59. How many cursors does Reports have to open for a two-query data structure?

Two.

60. Does Default columns referred to as common columns?

No, they are not.

61. Does Default columns referred to as placeholder columns?

No, they are not.

62. Does Formula column performs a user_defined computation on another columns data?

Yes.

63. Which field in Summery column's Property Sheet shows the calculation to be performed?

Function field.

64. If a Placeholder columns datatype is LONG or LONGROW can you edit the width field?

Yes, you can.

65. Can we define a  Summery  column at  Group level?

Yes we can.

66. What anchor does?

Links one or more layout objects together.

67. Does queries are created by default?

No

68. 'Oracle Reports creates one group for each query', Is it true?

Yes.

69. Does link are created by default?

No

70. Where you can restrict maximum number of rows?

 In Query Property Sheet, in maximum rows field.

71. Because of creation of links, the relationship between which two objects is created?

Group and Query

72. To produce control break reports, to produce matrix reports and to summarize data at intermediate level which object is needed?

Group Object.

73. If you change a query name after the group has been created, Can you change the name of group?

No, You cannot.

74. If we take one query e.g. Select d.id, e.salary From EMP e, dept d Where e.dept_id = d.id Order by salary And if we create Break Group with d.id , What will happen?

Here Order By clause is modified just like order by 1, salary.

75. A Link defines a parent / child relationship between a group & a query. Is it true? If yes then via which two keys?

Primary and Foreign.

76. Which term in SQL is related to the "link" in reports?

Join.

77. Can you create a link from child query to parent query?

No. Always create a link from Parent query to Child query.

78. What is Bind reference?

A bind reference replaces a single value or expression.

79. What is a Lexical reference?

A Lexical reference replaces any part of a select statement, such as column names, from clause, where clause, order by clause.

In which case Parameter is created by default? In case of Bind reference Parameter is created by default.

80. Bind reference is used to replace which clause?

Where clause, Group by, Order by, Having, Connected by, start with.

81. What is a User Parameter?

It is an object that you create to hold a value that user can change at runtime.

82. What are the properties of Parameter?

 Datatype, Width,, Input Mask, Initial Value, Validation Trigger.

83. What is the function of 'Restrict the List of Predetermined Values'?

It is used to determine whether to prevent users from entering any value not included in your list.        

84. If you uncheck the restricted values check box what will happen?

User can also enter values to the List box.

85. What is the default value of System Parameter "COPIES"?

1.

86. What is value of System Parameter "COPIES"?

Any Integer.

87. What is the value of System Parameter "DECEMIAL"?

Any single Character.

88. What is the default value of System Parameter “DESFROMAT"?

dflt.

89. What is the function of System Parameter “DESTYPE"?

Destination type for output.

90. What are the values of System Parameter "DESTYPE"?

FILE, PRINTER, MAIL, SCREEN, AND PREVIEW.

91. What is the default value of "DESNAME"?

SCREEN

92. What do you mean by System Parameter "MODE"?

Whether report executes in Bitmapped mode or in Character mode.

93. For what purpose you use System Parameter "ORIANTATION"?

It is used to specify the print direction of printer output.

94. What are the values of System Parameter "ORIANTATION"?

Landscape & Portrait

95. What is the default value of System Parameter "ORIANTATION"?

Portrait

96. What is the value of System Parameter "THOUSAND"?

Any single Character.

97. Does Oracle Reports allow DML (i.e. insert, update, delete) in layout format triggers?

No.

98. When you use Group filter in Data Model?

When you need to restrict records in a specific group.

99. Whenever a where clause in a query is not applicable which data model trigger is useful?

A Group Filter.

100. Do you add a filter to a Matrix Cross_Product group?

No, you cannot

101. A matrix report is also referred to as "CROSS_TAB" report, is it true?

Yes.

102. How many types of matrix reports are there? Tell their names?

There are 4 types. Simple matrix, nested matrix, multiquery matrix with break and matrix break.

103. If you build a matrix report with only one query, how many groups are require in addition to one created by default?

3 groups.

104. How many types of query structures are there? Tell their names?

Two. One_Query_Matrix & Multi_Query_Matrix.

105. Matrix reports are built with four or more groups. Is it true?

Yes.

106. What is the difference between a Nested Matrix and a Matrix Break?

In case of Matrix Break, One or more groups are a parent of the cross_product group.

And in case of Nested Matrix Three or more groups are surrounded by the cross_product group.

107. What is Flex mode and Confine mode?                                             

Confine mode:

            On:   child objects cannot be moved outside their enclosing parent objects.

            Off:  child objects can be moved outside their enclosing parent objects.

Flex mode:

            On: parent borders "stretch" when child objects are moved against them.

            Off: parent borders remain fixed when child objects are moved against them.

108. What is a 'break group' and how do you create one?

109. How many types of Columns are there in Reports6i?

There are three types of Columns. Formula Column, summary column, placeholder column.

110. What are the minimum numbers of groups required for a matrix report?

4. The minimum of groups required for a matrix report are 4

111. Which of the following Option is valid for Panel/Print Order Property in Report Builder?

Across Down/Down Across

112. What is the purpose of Place Holder Column in Report?

A column for which you set the datatype and value in PL/SQL that you define. 

113. What is the purpose of Summary Column in Reports?

A performs a computation on another column's data.

114. What is the purpose of Formula Columns?

A column performs a user-defined computation on another column(s) data, including placeholder columns.

115. Can lexical reference be made in PL/SQL statement?

No

116. Following of which trigger will fire first?

A. Between Pages

B. After Parameter Form

C. Before Parameter Form

D. Before Report

Ans:-C

116. Can you call report from report? How if yes? If No Why?

Yes. Using Drill drown

117. What are the prerequisites of a matrix report?

118. What is a complex report that you have done?

119. What are the various parameters of run_product built-in?

120. How do I call reports from forms?

121. How you print a break report?

122. What is anchoring?

123. What is a user exit?

124. How can you increase the performance of reports?

125. Can you have dynamic report query? If yes, how?

126. What are SRW packages?

127. Can you include a PL/SQL block in another PL/SQL block?

128. How do suppress index using in a where clause?

129. What is a host array?

130. What is an indicator variable?

131. What are the types of layouts in reports?

132. What are Lexical parameters in Reports? What is the maximum length of laxical parameters in report? Can laxical parameters can be used to replace whole statement?

133. Explain the types and sequence of triggers in Reports.

134. What is a format trigger? How do you use it?

135. When after parameter form trigger is executed and before report trigger is going to execute, what happen in between this?

136. In report what is the advantage of between page trigger?

137. What is group filter in reporrt?

138. What does the 'On mouse over' and 'On mouse out' trigger do?

139. What is the maximum length of laxical parameters in report?

140. How do you handle exceptions in reports?

141. Can you use an insert statement in a report?

142. How can you call report in a form?

143. What is user exit? Name the user exit and their funcionality?