Enabling Trace in Oracle Reports(RDF)

You can trace precisely in two ways:
1. Put this in before report trigger.
SRW.DO_SQL ('alter session set events=''10046 trace name context forever, level 4'' tracefile_identifier=''REPORT')

2.
i) Open your report (.rdf) in with Report Builder 6i.
ii) Choose Tools > Trace.
iii) Select All.

Recompile the report and place in Unixbox.

Follow the below procedure if your report is 10.7
1. Convert the report from rdf to rex:
$ORACLE_HOME/bin/r25convm batch=yes userid=> stype=rdffile source=REPORT_NAME.rdf dtype=rexfile overwrite=yes
2. Edit the rexfile and search for the beforerep trigger in the report.
Locate the following code:
IF (:p_trace_switch = 'Y') THEN
SRW.DO_SQL('alter session set sql_trace TRUE');
END IF;
Comment out the IF and END IF lines.
3. Save the report.
4. Convert the report from rex to rdf:

It would definitely generate the trace file. Look for the trace file in the following with the timestamp you started executing the report.
select value from V$PARAMETER where name = 'user_dump_dest'

Source: https://forums.oracle.com/thread/2453167

Formula column, Summary column and Place Holder column

In Oracle reports we use Formula column, Summary column and Place Holder column in different scenarios, this post is to avoid confusion and give clear picture as when and where we need to use these columns.

Formula column

We use formula column to calculate some information dynamically using information based on the columns of the data model or from the concurrent program parameters. It is basically used to apply some custom logic on input data and return some value.
Formula columns can be written using PL/SQL syntax and we can use pl/sql functions for computation on the data. Formula column will accept data of Character, Number, or Date type.


Inside or outside data group?
If we want to calculate a value for every row place the formula column in the group of the data model , so that it called every time for every record and if we want to derive a value at report level place the formula column outside to the data group.
Formula columns are generally preceded by CF_ to distinguish from other columns. Column names or parameters with a prefix ‘:’  in formula column are considers as input to a formula column.


Examples:

1. Comparison of data in two different columns/variables and perform some action.
2. Using some standard oracle pl/sql functions to find out some values like, finding out the master org using oracle standard function as shown below

   oe_sys_parameters.VALUE('MASTER_ORGANIZATION_ID',mo_global.get_current_org_id())

3. When you need to use any If-else block
4. To execute some sql query to find out email-address of a party using party_id

Note: Formula column should return some or the other value.

Summary column

Summary columns are used for calculating summary information like sum, average etc,. on specific columns of a data group.  This column uses a set of predefined oracle aggregate functions. Summary columns are generally preceded by CS_ to distinguish them from other columns.
The datatype of a summary column depends on the data type of the source of the summary. If you change the data type of the source column, the datatype of the summary also changes.
In report layout summary columns should be used out of repeating frames. Summary columns will have only one parameter as input.
Below are the standard functions provided by Oracle  for a summary column

Function	Purpose
Average	Calculates the average of the column’s values within the reset group.
Count	Counts the number of records within the reset group.
First	Prints the column’s first value fetched for the reset group.
Last	Prints the column’s last value fetched for the reset group.
Maximum	Calculates the column’s highest value within the reset group.
Minimum	Calculates the column’s lowest value within the reset group.
% of Total	Calculates the column’s percent of the total within the reset group.
Std. Deviation	Calculates the column’s positive square root of the variance for the reset group.
Sum	Calculates the total of the column’s values within the reset group.
Variance	Sums the squares of each column value’s distance from the mean value of the reset group and divides the total by the number of values minus 1.

All the above functions work only for number datatype column and return number datatype.

Place Holder column

Place holder column is an empty container at design time which is used to hold some value in run time, they are like local/global variables which can be used in the logic of a formula column or in report triggers. Value in the place holder column can be directly accessible in the report layout.


Inside or outside data group?
We can use this place holder columns inside the data group or outside the data group.
Place Holder column can be of Character, Date or Number datatype. Place Holder columns are generally preceded by CP_ to distinguish from other columns.

Oracle Reports-Frequently Use SRW Functions

SRW Package is a collection of PL/SQL constructs that provide developers with a suite of built-in functions, procedures, and exceptions that can be used in any of your libraries or reports. The following lists several of these constructs, and briefly describes how Applications uses them in reports.

SRW.DO_SQL and SRW.DO_SQL_FAILURE
When it is necessary to perform data definition statements (DDL), the SRW.DO_SQL packaged procedure must be used. You cannot perform DDL statements in PL/SQL. In conjunction with this, we use the SRW.DO_SQL_FAILURE
exception which raises an error if the statement should fail.

Example
BEGIN
SRW.DO_SQL('Create table Test...');
EXCEPTION 
  when SRW.DO_SQL_FAILURE then
  .....
END;

SRW.MESSAGE
This procedure allows developers to create their own messages and return them at runtime. It takes two arguments, a message number and message text. It can be used for error handling or debugging. Applications uses this feature to standardize and share messages across reports.

Example
SRW.MESSAGE('500','First debug point');

SRW.USER_EXIT and SRW.REFERENCE
Because Applications reports commonly call user exits it is critical that Oracle Reports support such calls. To facilitate this requirement the Package offers  SRW.USER_EXIT and SRW.REFERENCE. User exits may be called from any PL/SQL interface within a report, but it must be called with SRW.USER_EXIT. Furthermore, to ensure that the value of an object passed to the user exit contains the most recently computed or fetched value, SRW.REFERENCE will add the object to the user exit dependency list.

Example
BEGIN
SRW.REFERENCE(:Currency_code);
SRW.REFERENCE(:Currency_value);
SRW.USER_EXIT('FND FORMAT CURRENCY CODE=":Currency_code"                         
               AMOUNT=":Currency_value"                        
               DISPLAY=":Currency_formatted"');
RETURN(:Currency_formatted);
EXCEPTION WHEN
SRW.USER_EXIT_FAILURE THEN
RETURN('FORMAT ERROR');
END: