All Oracle Apps

Frequently Asked Interview Questions

Oracle Reports

1) What is Ref Cursor Query?

Description: 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.

Usage Notes

  • When you make a ref cursor query the child in a data link, the link can only be a group to group link. It cannot be a column to column link.
  • If you use a stored program unit to implement ref cursors, you receive the added benefits that go along with storing your program units in the Oracle database.

Example:

      set serveroutput on
      declare
            type t_cursor is ref cursor;
            r_cursor t_cursor;
            t_val Number := 2;
            c_val Number;
      begin
            if t_val = 1 then
                  open r_cursor for
                  select no from t where no=10;
            else
                  open r_cursor for
                  select num from t_1 where num=111;
            end if;
            fetch r_cursor into c_val;
            dbms_output.put_line('Value :'||c_val);
            close r_cursor;
            exception
            when others then
            dbms_output.put_line('Error occurred :'||substr(sqlerrm,1,250));
      end;
      /
      Output : Value :111

Package with ref cursor example:

      /* This package spec defines a ref cursor type that could be referenced from a ref cursor query function. If creating this spec as a stored procedure in a tool such as SQL*Plus, you would need to use the CREATE PACKAGE command.*/

      PACKAGE cv IS
      type comp_rec is RECORD
      (deptno number,
      ename varchar(10),
      compensation number);
      type comp_cv is REF CURSOR return comp_rec;
      END;

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;

2) How do you debug Report from Reports Builder?

Tools->Trace


Specify tracing options via the Report Builder built-in package using

      SRW.TRACE_START
      SRW.TRACE_END
      SRW.TRACE_ADD_OPTIONS
      SRW.TRACE_REM_OPTIONS

Trace Option Syntax Parameter Description
SRW.TRACE_START SRW.TRACE_START
(filename,
{SRW.TRACE_REPLACE
|SRW.TRACE_APPEND},
SRW.TRACEOPTS);
Filename Is the name of the file in which Report Builder stores logging Information?
SRW.TRACE_APPEND Adds the new information to the end of the file.
SRW.TRACE_REPLACE Adds the new information to the end of the file.
SRW.TRACEOPTS Applies the trace options mask previously defined by SRW.TRACEOPTS.MASK.
SRW.TRACE_END SRW.TRACE_END();
SRW.TRACE_ADD_OPTION SRW.TRACEOPTS.MASK :=
SRW.TRACE_opts
[+ SRW.TRACE_opts . . .];
SRW.TRACE_ADD_OPTION
(SRW.TRACEOPTS);
SRW.TRACE_REM_OPTION SRW.TRACEOPTS.MASK :=
SRW.TRACE_opts
[+ SRW.TRACE_opts . . .];
SRW.TRACE_REM_OPTION
(SRW.TRACEOPTS);

Mask Option Description
SRW.TRACE_ALL Includes all possible trace information in the log file.
SRW.TRACE_APP Includes trace information on all the report objects in the log file.
SRW.TRACE_BRK Lists breakpoints in the log file.
SRW.TRACE_DST Lists distribution lists in the log file. You can use this information to determine which section was sent to which destination.
SRW.TRACE_ERR Includes error messages and warnings in the log file.
SRW.TRACE_PLS Includes trace information on all the PL/SQL objects in the log file.
SRW.TRACE_PRF Includes performance statistics in the log file.
SRW.TRACE_SQL Includes trace information on all the SQL in the log file

3) Can you have more than one layout in a Report?

      It is possible to have more than one layout in a report by using the additional layout option in the layout editor.

4) What is the Lock Option in the Report?

      By using the lock Option, we cannot move the fields in the Layout Editor outside the frame. This is useful for maintaining the fields.

5) What is defaulting Unit of Measure in Report?

      Inch

6) Which Procedure displays message number and Text you specify?

      SRW.MESSAGE(Number, Text);

7) How many Types of Columns are there in Reports?

      Formula column, Summary Column and Placeholder Column.

8) What is the Minimum Number of Groups required fro a Matrix report?

      Minimum 4 our Groups are required for a matrix Report.

9) Which of the following Options is valid for Panel/Print Order Property in Report Builder?

      Across Down/Down Across

10) Can Lexical Reference be made in PL/SQL Statement?

      No

11) What is User Exit In Oracle Reports?

      A user exit is a program that you write and then link into the Report Builder executable or user exit DLL files. You build user exits when you want to pass control from Report Builder to a program you have written, which performs some function, and then returns control to Report Builder.

      User exits can perform the following tasks:

  • Perform complex data manipulation
  • Pass data to Report Builder from operating system text files
  • Manipulate LONG RAW data
  • Support PL/SQL blocks
  • Control real time devices, such as a printer or a robot

      You can use user exits for other tasks, such as mathematical processing. However, Oracle Corporation recommends that you perform such tasks with PL/SQL within Report Builder.

      You can write the following types of user exits:

  • ORACLE Precompiler user exits
  • OCI (ORACLE Call Interface) user exits
  • Non-ORACLE user exits

      You can also write a user exit that combines both the ORACLE Precompiler interface and the OCI.

Precompiler Oracle User Exit:

      An ORACLE Precompiler user exit incorporates the ORACLE Precompiler interface. This interface supports the following:

  • You can write a subroutine in one of the host languages (Ada, C, COBOL, FORTRAN, Pascal, PL/l) and embed SQL commands.
  • The user exit can access ORACLE (via embedded SQL commands) and Report Builder variables, parameters, and columns.

      Because of these features, you will write most of your user exits as ORACLE Precompiler user exits.

OCI (Oracle Call Interface) User exit:

      An OCI user exit incorporates the ORACLE Call Interface. This interface allows you to write a subroutine that contains calls to ORACLE. A user exit that incorporates only the OCI (and not the ORACLE Precompiler interface) cannot access Report Builder' variables, parameters, and columns.

Non-Oracle user exit:

      A non-ORACLE user exit does not incorporate either the ORACLE Precompiler interface or the OCI. For example, a non-ORACLE user exit might be written entirely in C. By definition, a non-ORACLE user exit cannot access ORACLE or Report Builder variables, parameters, and columns.

12) Why do we call FND SRWINIT user exit from Before Report Trigger?

      FND SRWINIT sets your profile option values and allows Oracle Application Object Library user exits to detect that an Oracle Reports program has called them. FND SRWINIT also allows your report to use the correct organization automatically.

13) Why do we call FND SRWEXIT user exit from After Report Trigger?

      FND SRWEXIT ensures that all the memory allocated for Oracle Application Object Library user exits has been freed up properly.

14) Why do we call FND FLEXSQL user exit from Before Report Trigger?

FND FLEXSQL :

      Call this user exit to create a SQL fragment usable by your report to tailor your SELECT statement that retrieves flexfield values. This fragment allows you to SELECT flexfield values or to create a WHERE, ORDER BY, GROUP BY, or HAVING clause to limit or sort the flexfield values returned by your SELECT statement. You call this user exit once for each fragment you need for your select statement. You define all flexfield columns in your report as type CHARACTER even though your table may use NUMBER or DATE or some other datatype.

Syntax:

      FND FLEXSQL
      CODE=”flexfield code”
      APPL_SHORT_NAME=”application short name”
      OUTPUT=”:output lexical parameter name”
      MODE=”{ SELECT | WHERE | HAVING | ORDER BY}”
      [DISPLAY=”{ALL | flexfield qualifier | segment
      number}”]
      [SHOWDEPSEG=”{Y | N}”]
      [NUM=”:structure defining lexical” |
      MULTINUM=”{Y | N}”]
      [TABLEALIAS=”code combination table alias”]
      [OPERATOR=”{ = | < | > | <= | >= | != | ”||” |
      BETWEEN | QBE}”]
      [OPERAND1=”:input parameter or value”]
      [OPERAND2=”:input parameter or value”]

CODE:

      Specify the flexfield code for this report (for example, GL#). You call FLEXSQL multiple times to set up SQL fragments when reporting on multiple flexfields in one report.

APPL_SHORT_NAME:

      Specify the short name of the application that owns this flexfield (for example, SQLGL).

OUTPUT:

      Specify the name of the lexical parameter to store the SQL fragment. You use this lexical later in your report when defining the SQL statement that selects your flexfield values. The datatype of this parameter should be character.

MODE:

      Specify the mode to use to generate the SQL fragment. Valid modes are:

      i) SELECT:

            Retrieves all segments values in an internal (non–displayable) format. If you SELECT a flexfield qualifier, and that flexfield segment is a dependent segment, then flexfields automatically selects both the parent segment and the dependent segment. For example, if the qualifier references the Subaccount segment, then both the Account (the parent) and the Subaccount segment columns are retrieved.

            Note: You reuse the lexical you use in the SELECT clause in the GROUP BY clause.

      ii) WHERE:

            Restrict the query by specifying constraints on flexfield columns. The fragment returned includes the correct decode statement if you specify MULTINUM.

            You should also specify an OPERATOR and OPERANDS.

            You can prepend a table alias to the column names using the TABLEALIAS token.

      iii) HAVING:

            calling procedure and functionality as WHERE

      iv) ORDER BY:

            Order queried information by flexfield columns. The fragment orders your flexfield columns and separates them with a comma. The fragment returned includes the correct decode statement if you specify MULTINUM.

      You use the MODE token with the DISPLAY token. The DISPLAY token specifies which segments are included in your SQL fragment in your lexical parameter. For example, if your MODE is SELECT, and you specify DISPLAY=”ALL”, then your SELECT statement includes all segments of the flexfield. Similarly, if your MODE is WHERE, and you specify DISPLAY=”ALL”, then your WHERE clause includes all segments. Frequently you would not want all segments in your WHERE clause, since the condition you specify for the WHERE clause in your actual query would then apply to all your segments (for example, if your condition is ” = 3”, then SEGMENT1, SEGMENT2, ... , SEGMENTn would each have to be equal to 3).

      v) SHOWDEPSEG:

            SHOWDEPSEG=”N” disables automatic addition of depended upon segments to the order criteria. The default value is ”Y”. This token is valid only for MODE=”ORDER BY” in FLEXSQL.

      vi) NUM or MULTINUM:

            Specify the name of the lexical or source column that contains the flexfield structure information. If your flexfield uses just one structure,specify NUM only and use a lexical parameter to hold the value. If your flexfield uses multiple structures, specify MULTINUM only and use a source column to hold the value. In this case the user exit builds a decode statement to handle the possible changing of structures mid–report. The default is NUM=”101”.

      vii) TABLEALIAS :

            Specify the table alias you would like pretended to the column names. You use TABLEALIAS if your SELECT joins to other flexfield tables or uses a self–join.

      viii) OPERATOR:

            Specify an operator to use in the WHERE clause. The operators ”= | < | > | <= | >= | != | QBE | BETWEEN” perform lexical comparisons, not numeric comparisons. With QBE (Query By Example) and BETWEEN operators, the user can specify partial flexfield values to match for one or more segments.

            For example, if OPERAND1 is ”01––CA%–” (assuming a four–segment flexfield with a delimiter of ’–’), the first segment must match 01 and the third segment is like ’CA%’. The resulting SQL fragment is:

            SEGMENT1=’01’ AND SEGMENT3 LIKE ’CA%

            For the BETWEEN operator, if OPERAND1 is ”01––CA–” and OPERAND2 is ”05––MA–” then the resulting SQL fragment is:

            SEGMENT1 BETWEEN ’01’ AND ’05’) AND (SEGMENT3 BETWEEN ’CA’ AND ’MA’)

      ix) OPERAND1:

            Specify an operand to use in the WHERE clause.

      x) OPERAND2:

            Specify a second operand to use with OPERATOR=”BETWEEN”.

15) What is the use of FND FLEXIDVAL user exit?

FND FLEXIDVAL:

      Call this user exit to populate fields for display. You pass the key flexfields data retrieved by the query into this exit from the formula column. With this exit you display values, descriptions and prompts by passing appropriate token (any one of VALUE, DESCRIPTION, APROMPT or LPROMPT).

Syntax:

      FND FLEXIDVAL
      CODE=”flexfield code”
      APPL_SHORT_NAME=”application short name”
      DATA=”:source column name”
      [NUM=”:structure defining source column/lexical”]
      [DISPLAY=”{ALL|flexfield qualifier|segment number}”]
      [IDISPLAY=”{ALL|flexfield qualifier|segment
      number}”]
      [SHOWDEPSEG=”{Y | N}”]
      [VALUE=”:output column name”]
      [DESCRIPTION=”:output column name”]
      [APROMPT=”:output column name”]
      [LPROMPT=”:output column name”]
      [PADDED_VALUE=”:output column name”]
      [SECURITY=”:column name”]

i) CODE:

      Specify the flexfield code for this report (for example, GL#). You callFLEXIDVAL multiple times, using a different CODE, to display information for multiple flexfields in one report.

ii) APPL_SHORT_NAME:

      Specify the short name of the application that owns this flexfield (for example, SQLGL).

iii) DATA:

      Specify the name of the field that contains the concatenated flexfield segment values retrieved by your query.

iv) NUM:

      Specify the name of the source column or parameter that contains the flexfield structure information.

v) DISPLAY:

      The DISPLAY parameter allows you to display segments that represent specified flexfield qualifiers or specified segment numbers, where segment numbers are the order in that the segments appear in the flexfield window, not the segment number specified in the Define Key Segments form. The default is ALL, which displays all segments. Alternatively, you can specify a flexfield qualifier name or a segment number. You can use these parameters as toggle switches by specifying them more than once. For example, if you to display all but the first segment, you would specify:

      DISPLAY=”ALL”

      DISPLAY=”1”

vi) IDISPLAY:

      You use this parameter to tell FLEXIDVAL what segments you used in your SELECT clause in the corresponding FLEXSQL call. FLEXIDVAL needs this information to determine the format of raw data retrieved by FLEXSQL. You set IDISPLAY to the same value as your DISPLAY parameter in your FLEXSQL call. The default value is ALL, so if you used DISPLAY=”ALL” in FLEXSQL, you do not need to use IDISPLAY here

vii) SHOWDEPSEG :

      SHOWDEPSEG=”N” disables automatic display of depended upon segments. The default value is Y.

ix) VALUE:

      Specify the name of the column in which you want to display flexfield values.

x) DESCRIPTION :

      Specify the name of the column in which you want to display flexfield descriptions.

xi) APROMPT:

      Specify the name of the column in which you want to display flexfield above prompts.

xii) LPROMPT:

      Specify the name of the column in which you want to display flexfield left prompts.

xiii) PADDED_VALUE :

      Specify the name of the column in which you want to display padded flexfield values. The segment values are padded to the segment size with blanks.

xiv) SECURITY :

      Specify the name of the column into which flag ”S” will be placed if the segment values are secured. You then write logic to hide or display values based on this flag. This token is applicable only for segment values and does not apply to description, left prompt or above prompt.
Note: The datatype of the column as specified by VALUE, DESCRIPTION, APROMPT and LPROMPT is CHARACTER.

                                                                                                                                                 Next