Lexical Parameters Interview Questions

Oracle Apps - Lexical Parameters Interview Questions

1. Defining Runtime Queries with Lexical Parameters

            You can modify the report query at runtime using lexical parameters. A lexical parameter is a placeholder column containing the actual text to be used in a query. To illustrate this concept, open the matrix report built earlier. For this report, create a parameter for the report year and a parameter for the user to enter one of the following values, depending on the data preference:

2. Function  Data Value 

SUM  Monthly total orders shipped 

AVG  Average shipping delay by month 

MAX  Maximum shipping delay by month 

Also, create the appropriate validation triggers for the individual parameters.

            Next, create a placeholder column at the report level called SELECTION_DATA. Set this field as a character field with a width of 100 characters and assign a default value of h.hist_ord_shipped. Next, create a before report trigger as follows:

function Before_Report_Trigger return boolean is

begin

            if :P_REPORT_TYPE = `SUM' then

                        :SELECTION_CRITERIA := `h.hist_ord_shipped';

            elsif :P_REPORT_TYPE = `AVG' then

                        :SELECTION_CRITERIA := `h.hist_ship_days / h.hist_ord_shipped';

            else

                        :SELECTION_CRITERIA := `h.hist_max_days';

            end if;

end;

Finally, modify the query:

select w.wh_name WAREHOUSE, h.hist_month_no    MONTHNO, to_char (to_date (to_char (h.hist_month_no), `MM'),'MON') RPT_MONTH, &SELECTION_CRITERIA from warehouses w, warehouse_history h where w.wh_code = h.hist_wh_code and h.hist_year = :P_year

            h.hist_month_no    MONTHNO, to_char (to_date (to_char (h.hist_month_no), `MM'),'MON') RPT_MONTH, &SELECTION_CRITERIA from warehouses w, warehouse_history h where w.wh_code = h.hist_wh_code and h.hist_year = :P_year

            You reference the lexical parameter in the query using an ampersand (&) before the parameter name. Lexical parameters within a query substitute the text stored in the parameter directly into the query. For this reason, when using a lexical parameter, you must enter a default value for NULL values to assist with compilation in the designer.

3. Ten Tips for Oracle Reports

            Oracle Reports is a powerful tool that you can use to generate useful reports against Oracle databases. Although there are no hard and fast rules regarding how you should use the tool, experience has borne a number of tips that make use of this tool much easier:

             Attempt to lay out the report on paper. This assists with the development of the data model as well as the final layout. Understand where subtotals should be provided to create the data breaks up front. When the default layout is used, define the page size to be excessively wide. You can then resize and reposition the data columns to fit within the printable page. If possible, formulate the data retrieval in a single query. Experience has shown that a single, somewhat inefficient query can perform better than several, dependent, well-tuned queries.

            Complete the data model before attempting to finalize the layout. The addition of a single column in a query might necessitate a redesign of the layout and thus a misuse of time.

When adding an additional break level to an existing report, 90 percent of the time it is faster to redo the default layout. Adding another intermediate level frame is one of the most difficult tasks to be done. It can be done but is often not worth the effort.

            Rather than try to resize or reposition objects in the Layout Editor, use the Size Objects and Align Objects tools. You can select several columns at once, quickly make them all the same custom size, and then align and space them with minimal effort. To lock the relative position of multiple objects, select them and create a group to join them together. Use the Magnify tool to zoom in to view the relative positions of the objects or to zoom out to view the total report structure. When you make a mistake in the editor, use Edit | Undo to reverse the action rather than try to correct it with the mouse.

Before running any report, save it in a file to make sure that it can be recovered. Also, save different versions to facilitate recovery.