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 triggercan 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 Parameterand 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 Propertyin 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?

#############################################################################################################################################################################################################################################################################################

Some Basic Interview Questions:

1. Report registration?
2. Report Parameters?
3. Sequence of execution of Report Parameters?
4. Difference B/W After parameter and before report Trigger?
5. Difference B/W bind and lexical parameter?
6. How to use Lexical parameter in report?
7. Can the value be passed dynamically?
8. How to design Apps Report from Scratch?
9. Difference B/W Apps and D2K report?
10. What does P_conc_request_id do in the report?
11. User exits in report?
12. SRW FND FORMAT CURRENCY?
13. Where you will use SRW EXIT AND SRW INIT?
14. Which report you find difficult in designing?

FORMS

1. How to design Apps Form from the scratch?
2. How to register the Form?
3. If you don’t want user to see the form name in the menu how will you do that?
4. Differences B/W call form, open form and new form?
5. Difference B/W record group and property class?
6. Types of canvases in forms?
7. Master Item form is based on which canvas?
8. You designed Form on view or table?
9. Advantage of using view or table?
10. How will you use Multi-Org in forms?
11. What changes you will do in desktop when designing the form?
12. When you navigate to a new field what is the sequence of trigger firing?
13.  How will you assign LOV dynamically to an Item?
14. Custom.pll?
15. What customization you made in the forms?

PL/SQL AND APPPS

1. Rating in Pl/Sql?
2. Triggers?
3. How we can use Commit in triggers?

Changes made within triggers should be committed or rolled back as part of the transaction in which they execute.

Thus, triggers are NOT allowed to execute COMMIT or ROLLBACK statements (with the exception of autonomous triggers).

As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction. Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements.

Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig

    AFTER insert ON tab1

  DECLARE

    PRAGMA AUTONOMOUS_TRANSACTION;

  BEGIN

    INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');

    COMMIT; -- only allowed in autonomous triggers

  END;

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

4. Autonomus Transaction?
5. Mutating Table Error?

A mutating table is a table that is currently being modified by an update, delete, or insert statement. You will encounter the ORA-4091 error if you have a row trigger that reads or modifies the mutating table.

A constraining table is a table that a triggering statement might need to read either directly, for a SQL statement, or indirectly, for a declarative referential integrity constraint. A table is mutating or constraining only to the session that issued the statement in progress.

For example, if your trigger contains a select statement or an update statement referencing the table it is triggering off of you will receive the error. Another way this error can occur is if the trigger has statements to change the primary, foreign or unique key columns of the table the trigger is triggering off of.

6. How to remove that?

If you need to update a mutating or constraining table, then you could use a temporary table, a PL/SQL table, or a package variable to bypass these restrictions.

For example, in place of a single AFTER row trigger that updates the original table, resulting in a mutating table error, you may be able to use two triggers. The first is anAFTER row trigger that updates a temporary table, and the second an AFTERstatement trigger that updates the original table with the values from the temporary table.

http://www.orafaq.com/forum/t/58034/0/

7. Exceptions? User defined and Pre defined?
8. Collections?

“A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection. Collections are Oracle's version of arrays; collections are single-dimensioned lists

PL/SQL offers these collection types:

·         ·         Index-by tables, also known as associative arrays, let you look up elements using arbitrary numbers and strings for subscript values. (They are similar to hash tables in other programming languages.)

·         ·         Nested tables hold an arbitrary number of elements. They use sequential numbers as subscripts. You can define equivalent SQL types, allowing nested tables to be stored in database tables and manipulated through SQL.

·         ·         Varrays (short for variable-size arrays) hold a fixed number of elements (although you can change the number of elements at runtime). They use sequential numbers as subscripts. You can define equivalent SQL types, allowing varrays to be stored in database tables. They can be stored and retrieved through SQL, but with less flexibility than nested tables.

9. PL/SQL table?

A PL/SQL table is a one-dimensional, unbounded, sparse collection of homogeneous elements, indexed by integers. In technical terms, it is like an array; it is like a SQL table; yet it is not precisely the same as either of those data structures. This chapter explains these characteristics of the PL/SQL table in detail, so that you will understand the differences between PL/SQL tables and traditional arrays and SQL tables, and so that you will know how to use PL/SQL tables in your programs.

1  PROCEDURE set_name (name_in IN VARCHAR2, row_in in INTEGER)

2  IS

3

4     TYPE string_tabletype IS

5        TABLE OF VARCHAR2(30) INDEXED BY BINARY_INTEGER;

6

7     company_name_table string_tabletype;

8

9  BEGIN

10    company_name_table (row_in) := name_in;

11 END;

The TYPE statement in lines 4-5 defines the structure of a PL/SQL table: a collection of strings with a maximum length of 30 characters. The INDEXED BY BINARY_INTEGER clause defines the integer key for the data structure. The table declaration in line 7 declares a specific PL/SQL table based on that table structure. In line 10, traditional array syntax is used to assign a string to a specific row in that PL/SQL table.

10. Record Group?

A record group is an internal Oracle Forms that structure that
hs a column/row framework similar to a database table. However, unlike
database tables, record groups are separate objects that belong to
the form module which they are defined.

11. V Array?

CREATE OR REPLACE TYPE ftx_t (
ftx_code CHAR(8) ,
ftx_code_desc VARCHAR2(32),
primary_ftx_code_ind CHAR(1)
);
rem
rem ftx_v is a VARRAY of 6 elements
rem
CREATE OR REPLACE TYPE ftx_v AS VARRAY(6) OF ftx_t;

12. How to create log file from the procedure? Ans Fnd_file.put_line(Fnd_file.log      ‘sqlcode’)
13. How you have copied data from 10.7 to 11i?
14. From where data was coming?
15. How you load data from flat file to staging table?
16. How you run Sql* loader?
17.  How you run Procedure to load data from staging table to interface table?
18. How you registered the procedure in Apps?
19. What are the mandatory parameters?
20. What do they do?
21. How often you run that procedure?
22. What are the different methods of executions in Concurrent Program?
23. What work you have done on UNIX?
24. How to register shell scripts?
25. What extension we give to shell scripts?
26. How to create Soft link?
27. Where will we Place Shell Script in the oracle Application Directory?
28. What work you have done on the shell script?
29. Utl_File? Why you used that?
30. Exception in Utl_file?
31. How you run the Utl_file?
32. Sql tuning and Package tuning?
33. What is bulk collect?

A bulk collect is a method of fetching data where the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection. The SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine. The larger the number of rows processed, the more performance is gained by using a bulk collect. Bulk collecting data is easy.  First, we define the collection or collections that will be used in the bulk collect.  Next, define the cursor to retrieve the data and finally, bulk collect the data into the collections.  The example below demonstrates a simple bulk collect:

SQL> declare
  2    type number_array is varray(10000) of
                                           number;
  3    type string_array is varray(10000) of
                                    varchar2(100);
  4
  5    a_store string_array;
  6    a_qty   number_array;
  7
  8    cursor c1 is
  9      select store_key, sum(quantity) from sales
 10      group by store_key;
 11  begin
 12    open c1;
 13      fetch c1 bulk collect into a_store, a_qty;
 14    close c1;
 15    for indx in a_store.first..a_store.last loop
 16      dbms_output.put_line(
 17            a_store(indx)||'....'||a_qty(indx));
 18    end loop;
 19  end; /
 

S102....21860
S105....13000
S109....12120
S101....2180
S106....6080
S103....7900
S104....13700
S107....24700
S108....5400
S110....3610

There is nothing new in the above example except line 13.  Here instead of fetching one row, we bulk collect all the rows at once.  Notice that this also allowed us to close the cursor and free the database resources it had obtained immediately after collecting the rows.  The data can now be processed as needed in memory.    In this example, lines 16 and 17 print out the data from the in-memory collection.

Also, note that the last example also used two arrays.  The arrays were loaded together in the one BULK COLLECT INTO  statement.  As the SQL engine loaded the arrays, it places the data at the same index location in each array.  If a store key existed in the table with no orders (not the case in this example), the store key would be place in the a_store array and a null would be placed in the a_qty array.  This allows you to search the store array and retrieve the qty using the store array index.  This is demonstrated in lines 15 through 18 in the example.

In database versions 9iR2 and later, you can bulk collect into records:
 

SQL> declare
  2    type sales_tab is table of sales%rowtype;
  3    t_sal sales_tab;
  4  begin
  5    select * bulk collect into t_sal from sales;
  6    dbms_output.put_line(t_sal.count);
  7  end;
  8  / 

100

While a bulk collect  retrieves data in bulk, the bulk load will change data in bulk

SQL> Declare

  2    TYPE   TYP_TAB_TEST IS TABLE OF TEST%ROWTYPE ;

  3    tabrec TYP_TAB_TEST ;

  4    CURSOR C_test is select A, B From TEST ;

  5  Begin

  6     -- Load the collection from the table --

  7     Select A, B BULK COLLECT into tabrec From TEST ;

  8    

  9     -- Insert into the table from the collection --

 10     Forall i in tabrec.first..tabrec.last

 11         Insert into TEST values tabrec(i) ;

 12     

 13     -- Update the table from the collection --

 14     For i in tabrec.first..tabrec.last Loop

 15         tabrec(i).B := tabrec(i).B * 2 ;   

 16     End loop ;

 17    

 18     -- Use of cursor --

 19     Open  C_test ;

 20     Fetch C_test BULK COLLECT Into tabrec ;

 21     Close C_test ;

 22        

 23  End ;

 24  /

34. Analytical Function in PL/SQL?

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

The Syntax

The Syntax of analytic functions is rather straightforward in appearance

Analytic-Function(<Argument>,<Argument>,...)
OVER (
  <Query-Partition-Clause>
  <Order-By-Clause>
  <Windowing-Clause>
)

35. Which APIs you have you used?
36. How will you use API to load data from the flat file to Oracle Base Tables?
37. Which conversion you have done?
38. What are the Mandatory columns in the interface tables?
39. How to register concurrent program from backend?