How to switch RUN File System and Patch File System in R12.2

The existence of the dual file system has implications for patches that change the system configuration. Depending on the specific situation, configuration changes can be made to either the run file system or the patch file system.

Sourcing Environment for run File system:

. /u01/app/EBSapps.env RUN

Sourcing Environment for patch File system:

. /u01/app/EBSapps.env patch

  

How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

How to Synchronize FND_NODES, ADOP_VALID_NODES, and FND_OAM_CONTEXT_FILES in 12.2 When ADOP Phase=Prepare Fails with Error

After a recent change or addition to the 12.2 E-Business Suite application tiers, adop phase=prepare fails with the following error:

Error
$ adop phase=prepare

Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:

Validating credentials...
Initializing.
Run Edition context : /u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpnode.xml
Patch edition context: /u01/app/fs2/inst/apps/ SID_erpnode /appl/admin/SID_erpnode.xml
Patch file system free space: 26.63 GB

Validating system setup.
Node registry is valid.

Checking for existing adop sessions.
Continuing with existing session [Session ID: 10].
Session Id : 10
Prepare phase status : NOT COMPLETED
Apply phase status : NOT COMPLETED
Cutover phase status : NOT COMPLETED
Abort phase status : NOT COMPLETED
Session status : FAILED

===========================================================================
ADOP (C.Delta.9)
Session ID: 10
Node: erpnode
Phase: prepare
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/adop.log
===========================================================================

Validating configuration on node: [erpnode].
Log: /u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode
[WARNING]: There could be issues while validating the ports used for E-Business Suite instance against ports used in /etc/services. Refer the log file for more details.
[WARNING]: Found invalid cross references in FS config files.
[ERROR]: The value of s_patch_service_name is not set correctly in atleast one of the context files.
[UNEXPECTED]Error occurred running "perl /u01/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPValidations.pl -contextfile=/u01/app/fs1/inst/apps/SID_erpnode/appl/admin/SID_erpt_erpnode.xml -phase=prepare -logloc=/u01/app/fs_ne/EBSapps/log/adop/10/20171012_132509/prepare/validate/erpnode -promptmsg=hide"
[UNEXPECTED]Error 1 occurred while Executing txkADOPValidation script on erpnode

[STATEMENT] Please run adopscanlog utility, using the command

"adopscanlog -latest=yes"

to get the list of the log files along with snippet of the error message corresponding to each log file.

Cause
There is a synchronization error between fnd_nodes, adop_valid_nodes, and fnd_oam_context_files.

Solution
Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is EXPECTED / REQUIRED that the Applications have been shutdown.
The only thing running should be the Database Tier.
Note: A full backup should also be taken before any testing begins.

Test the following steps in a development instance, and then migrate accordingly once the desired result is confirmed:

1. Backup the fnd_oam_context_files, fnd_nodes, and adop_valid_nodes tables in the EBS env:

 sqlplus applsys/pwd

 create table fnd_oam_context_files_bkp as select * from fnd_oam_context_files;
create table fnd_nodes_bk as select * from fnd_nodes;
create table adop_valid_nodes_bk as select * from adop_valid_nodes;

2. Truncate the following tables:

truncate table fnd_oam_context_files;

truncate table fnd_nodes;

truncate table adop_valid_nodes;

3. Run AutoConfig on the DB tier
Confirm Autoconfig completes successfully

4. Run Autoconfig on the run file system.
    Source the Environment File to switch to run file system
    . /u01/app/EBSapps.env RUN

Confirm Autoconfig completes successfully

5. Run Autoconfig on the patch file system
   Source the Environment File to switch to patch file system
   . /u01/app/EBSapps.env patch

 Due to the method required for "cleaning out" / "re-synchronizing" the following tables, it is           EXPECTED / REQUIRED that the Applications have been shutdown.
 The only thing running should be the Database Tier.
 Before running Autoconfig on the patch file system the ebs_login trigger MUST be disabled
 After the successful completion of Autoconfig the ebs_login trigger MUST be re-enabled.
 This needs to be done as the SYSTEM schema user.
   a. Disable the ebs_login trigger using the following SQL.
     SQL> alter trigger ebs_logon disable;
  At this time Run autoconfig with the patch env sourced.
  Make sure Autoconfig completes ok
   b. Enable the ebs_login trigger using the following SQL.
  SQL> alter trigger ebs_logon enable;

6. After Autoconfig has been run successfully on all nodes, run the following two (2) queries in order to verify the tables have been correctly populated:
SQL> select node_id, platform_code, support_db D, support_cp C, support_admin A,
support_forms F, support_web W, node_name, server_id, server_address, domain, webhost, virtual_ip, status from fnd_nodes order by node_id;

SQL> select NAME,VERSION,PATH, STATUS from FND_OAM_CONTEXT_FILES;

Check adop with prepare phase. This time it should complete successfully.
$ adop phase=prepare

The prepare phase completed successfully.
adop exiting with status = 0 (Success)

Reference: Doc ID 2064223.1

Linux Command Reference

    Tools to Monitor Linux PerformanceLinux

     How to Close and Open Ports with iptables 

     How to Reset "root" Password with GRUB on Enterprise Linux

     Forcefully unmount a Linux disk partition

     Get Tape Serial Num in Linux

     Mount Windows CDROM in Linux

     VNC in LinuxLinux

Validating Parameters in Oracle Reports using PL/SQL

Parameters can be populated and validated using various srw pl/sql triggers.

The following gives examples of:

Validation trigger in parameter spread sheet 

Before parameter form trigger

After parameter form trigger

Before report trigger   

Examples of validation triggers on the property sheet for parameter PARAM_SAL. 

Query: select * from emp where sal > :PARAM_SAL

These functions validate just this one trigger. The validation occurs when 

the user hits next field after inputting a value for the parameter. When the 

trigger is failed it returns to the parameter form.

Example 1:

This trigger aborts the report execution if no rows match the query criteria 

once the user has entered a value for param_sal.

function PARAM_SALValidTrigger return boolean is

hold_count number(4);

hold_sal  number(10);

begin

  hold_sal := :param_sal;

  select count(*) into hold_count from emp where sal > hold_sal; 

  if hold_count = 0 then

     srw.message(001,'this report returns no employees');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 2

In this trigger the users value for param_sal is compared to the maximum 

salary in the EMP table. If it is greater the report execution is aborted.

example query for your report: select * from emp where sal >= :parm_sal

function PARAM_SALValidTrigger return boolean is

hold_max number(10);

begin

  select max(sal) into hold_max from emp;

  if :param_sal > hold_max then

     srw.message(002,'SAL must be equal to or less than MAX(SAL)= '||

     to_char(hold_max));

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 3

'Before parameter form' triggers can be used set up the environment for the

report e.g. create a table. It can also be used to supply default parameter 

values.

This function populates the initial value of the parameter param_sal with the 

lowest salary value from the emp table.

function BeforePForm return boolean is

min_sal number(10);

begin

  select min(sal) into min_sal from emp;

  :param_sal := min_sal;

  return(true);

end;

Example 4

'After parameter form' triggers can be used to validate a combination of 

parameters. Failing results in a return to the PARAMETER FORM.

Query: select * from emp where job=:jb and deptno=:dt

function  AfterPForm return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(003,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

Example 5

'Before report triggers' can be used to validate a combination of parameters.

The example below is the same as the after parameter form trigger above 

other than on failure return is passed to the MAIN MENU.

A 'Before Report Trigger' is executed right before formatting the report,

that is after initializing all internal structures, opening all SQL cursors

etc. In other words, after 'compiling' the report definition.

A second use of this trigger may be to launch a number of other reports

using the SRW.RUN_REPORT procedure.

function BeforeReport return boolean is

begin

  if (:dt = 20) and (:jb = 'MANAGER') then 

     srw.message(004,'cannot report on Managers in Dept 20');

     raise srw.program_abort;

  end if;

  return(true);

end;

ORACLE REPORTS PERFORMANCE TIPS

Doc ID 61535.1

Performing operations in SQL may be faster than performing them in Oracle
Reports or PL/SQL.  The list below explains the most common cases where using
SQL would improve performance:

 -  perform calculations directly in your query rather than in a formula or
    summary,

 -  use a WHERE clause instead of a group filter or format trigger to exclude
    records,

 -  use the SUBSTR function to truncate character strings instead of
    truncating in Oracle Reports.

SQL can perform calculations more quickly than a summary or formula.  WHERE
and SUBSTR can reduce unnecessary fetching because they operate on the data
during, rather than after, data retrieval.


SRW.DO_SQL Statements
---------------------

SRW.DO_SQL enables you to add any DDL or DML operation to your report.  This
functionality is very valuable, but it can also be very expensive if used
unwisely.

Only use SRW.DO_SQL when necessary.  SRW.DO_SQL statements are parsed, a
cursor is opened to the database, and then the statement is executed.  Unlike
queries, an SRW.DO_SQL statement will do those things each time its owner
(a group) fetches data.  For example, if your SRW.DO_SQL statement is owned by
a group that fetches 10 records, the statement will be parsed 10 times, 10
cursors will be opened, and the statement will be executed 10 times.
Perform computations within the query or PL/SQL instead of SRW.DO_SQL
owned by a group.


CDE_MM.GET_REF
--------------

Only use the CDE_MM.GET_REF packaged procedure when necessary.  It is
intended to reduce the amount of temporary space used by Oracle Reports.
Oracle Reports will not cache a column retrieved via CDE_MM.GET_REF in a
temporary file.  While this reduces the need for temporary space, it slows
performance because the column's values must always be retrieved from the
database.


When You Should Use Multi-Query Data Models
-------------------------------------------

Reduce the number of queries in your report as much as possible.  The fewer
queries it contains, the faster your report will run.  Multi-query data models 
are easier to understand, but single-query data models tend to execute more 
quickly.

Use multi-query data models only when:

 - you are fetching many large columns from the parent and
   only a few small columns from the child,

 - you are trying to do things that SELECT does not support directly
   (multi-way outer join),

 - you have complex views (distributed queries or GROUP BY queries).

 - you need, but do not have or want, to use a view.

For a one-query report, only one cursor is opened to fetch all the master
and detail records.  For a two-query report, Oracle Reports opens two cursors
(one for each query) after appending the detail query's link to the WHERE
clause of the detail query.  For each master record fetched, Oracle must
rebind, execute, and fetch data from the detail query.


Indexes
-------

Be sure to have indexes on columns used in the SELECT statements' WHERE clauses,
on database key columns, and on the table(s) in the detail queries.  Indexes 
have little impact on master queries, because those queries access the database 
only once.  Indexes significantly improve performance of master/detail reports.
The lower the ratio of master to detail records, the more important indexes on
the detail query become for two-query reports.

Indexes are recommended for tables in the detail queries because Oracle
Reports implicitly creates a WHERE clause from the parent/child relationships
and adds it to the detail query.


IMPORTANT: Query Modifications
------------------------------

Oracle Reports modifies your queries in the following cases:

1.  For each link you create, Oracle Reports will append a clause to the
    child query as specified in the link.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         WHERE sal > 1000

    If you create a link to this query using DEPTNO as the child column, a SQL
    clause of WHERE, and a condition of "equal to", then your query will be
    modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         WHERE (sal > 1000) AND (deptno = :deptno)

    NOTE:  This is not true for multi-query matrix report data models.

2.  For each database column with Break Order set, Oracle Reports will
    PREPEND an ORDER BY clause to the query.

    For example:
         SELECT deptno, ename, sal
         FROM emp
         ORDER BY sal

    If you create a break group with DEPTNO as the break column, then your 
    query will be modified as follows:

         SELECT deptno, ename, sal
         FROM emp
         ORDER BY 1, sal

    These SQL statements will be sent to the database, then the SQL optimizer 
    will determine the optimal way to get the data from the database and return 
    it to Oracle Reports.  The optimizer will determine whether to use indexes, 
    which table to use as the "driving" table, and so forth.


Break Columns
-------------

When you create a break group, place as few columns as possible in the group.
Try to keep a 1:1 ratio of break columns to break groups.  Try to ensure
that the break column is as small as possible.  A break column that is shorter
in length will typically give better performance than a break column that is
longer.  For larger break columns, it may help performance to use the SUBSTR
function to reduce the length of the column.

For each break group, Oracle Reports prepends its break columns to the ORDER
BY clause of the query.  (The only exception to the rule is when the break
column is a formula column.)  By minimizing the number of break columns in
your break groups, you minimize the number of columns that are added to the
ORDER BY clause.  The fewer the columns added to the ORDER BY clause, the less
the processing that needs to be done when the query runs.  The size of the key
that Oracle Reports uses internally to build indexes will also be smaller,
resulting in better performance.


Maximum Rows And Group Filters
------------------------------

Use the Maximum Rows property in the Query property sheet to reduce the number
of records retrieved by the report's queries.  When designing a report that
accesses large amounts of data, you may want to restrict the amount of data
retrieved, so the report will run more quickly during testing.

Maximum Rows in the Query property sheet restricts the number of records
fetched by the query.  A group filter determines which records to include and
which records to exclude.  Since Maximum Rows actually restricts the amount of
data retrieved, it is faster than a group filter in most cases.

If you use a group filter of Last or Conditional, Oracle Reports must retrieve
all of the records in the group before applying the filter criteria.   Maximum
Rows or a Filter of First is faster.  Typically Maximum Rows is faster than a
Filter of First because it only retrieves as many records as needed.  The
performance difference may vary depending upon the ARRAYSIZE you have
specified.


Unused Data Model Objects
-------------------------

Make sure that you remove or suppress any data model objects that are not
actually used in your report.  If your data model includes a query that is
only used in certain cases (when a parameter is set to a certain value), you
can conditionally suppress the query with the SRW.SET_MAXROW packaged
procedure.  SRW.SET_MAXROW (queryname, 0) will cause the query to fetch no
records.


Unused Frames
-------------

Remove any unnecessary frames from the layout.  When Oracle Reports creates a
default layout, it puts frames around virtually everything.  This is done to 
protect the objects in the frames from being overwritten by other objects in 
the output.  If you know that the objects in the frames are not in danger of 
being overwritten, you can eliminate the frame without adversely affecting 
your report output.

The fewer objects in the layout, the fewer objects Oracle Reports must format
at runtime.  As a result, performance is better when you reduce the number of
objects in the layout.


Total Number Of Pages
---------------------

Limit your use of total number of pages as the source of fields (Total Logical
Pages).  When you use a total number of pages field source, Oracle Reports
must save all of the pages in temporary storage in order to determine the
total number of pages.  This can significantly increase the amount of
temporary disk space used by Reports, and the additional writing to files can
slow performance.


Format Triggers
---------------

Place PL/SQL in the Format Trigger of the object with the lowest frequency
possible.  PL/SQL in the Format Trigger of a frame instead of a field
typically makes the report run faster.

PL/SQL in Format Triggers is executed for each instance of its object.  The
lower the frequency of the object, the fewer times the PL/SQL will be executed
and the faster the report will run.


Oracle Graphics Integration
---------------------------

If an Oracle Graphics display referenced by a report uses some or all of the
same data as the report, pass the data from the report to the display.  If the
report and the display use the same data, passing the data reduces the amount
of fetching that needs to be done.  If you do not pass the data from the
report to the display, the data is actually fetched twice: once for the report
and once for the display.

Using SRW Packaged Procedures in Reports

Oracle Reports is shipped with a collection of PL/SQL constructs that contain

many functions, procedures, and exceptions that you can reference in your

libraries or reports.  The name of Oracle Reports' package is SRW.  Therefore,

anytime you reference a construct in the SRW package, you must prefix it with

SRW (such as, SRW.DO_SQL).

 SRW Packages

Oracle Reports contains the following packages:

-  SRW.BREAK                        -  SRW.RUN_REPORT_BATCHNO

-  SRW.CONTEXT_FAILURE              -  SRW.SET_FIELD_CHAR

-  SRW.DO_SQL                       -  SRW.SET_FIELD_DATE

-  SRW.DO_SQL_FAILURE               -  SRW.SET_FIELD_NUM

-  SRW.GETERR_RUN                   -  SRW.SET_MAXROW

-  SRW.GET_PAGE_NUM                 -  SRW.TRACE_ADD_OPTION

-  SRW.INTEGER_ERROR                -  SRW.TRACE_END

-  SRW.MAXROW_INERR                 -  SRW.TRACE_REM_OPTION

-  SRW.MAXROW_UNSET                 -  SRW.TRACE_START

-  SRW.MESSAGE                      -  SRW.TRUNCATED_VALUE

-  SRW.NULL_ARGUMENTS               -  SRW.UNKNOWN_QUERY

-  SRW.PROGRAM_ABORT                -  SRW.UNKNOWN_USER_EXIT

-  SRW.REFERENCE                    -  SRW.USER_EXIT

-  SRW.RUN_REPORT                   -  SRW.USER_EXIT20

-  SRW.SET_ATTR                     -  SRW.USER_EXIT_FAILURE

-  SRW.RUN_REPORT_FAILURE

Commonly Used SRW Packages And Examples

SRW.DO_SQL executes a specified SQL statement.  This procedure executes

any DDL or DML statements.  However, DML statements are usually faster

when they are in PL/SQL than when executed via SRW.DO_SQL.  Since you

cannot perform a DDL statement in PL/SQL, this packaged procedure is useful

for performing them within Reports rather than using a user exit.

Avoid DDL statements that modify the tables on which the report is based.  A

snapshot of the tables is taken prior to report execution and must remain

valid throughout the execution of the report.

Example 1 - SRW.DO_SQL

Syntax/Explanation:

  SRW.DO_SQL(sql_statement CHAR); -- Executes specified SQL statement.

  SRW.DO_SQL_FAILURE; -- Stops report execution upon SRW.DO_SQL failure.

  SRW.MESSAGE(msg_number NUMBER, msg_text CHAR);

                      -- Displays a specified message and message number.

  SRW.PROGRAM_ABORT;  -- Stops execution of report when raised.

Example:

Create a temporary table named TEMP prior to the Runtime Parameter Form:

FUNCTION createtab RETURN BOOLEAN IS

BEGIN

   SRW.DO_SQL(CREATE TABLE temp (empno NUMBER NOT NULL PRIMARY KEY,

                                 sal   NUMBER (10,2))

                             PCTFREE 5 PCTUSED 75');

   RETURN TRUE;

EXCEPTION

   WHEN SRW.DO_SQL_FAILURE THEN

      SRW.MESSAGE(100,'ERROR WHILE CREATING CHECK TABLE.');

      SRW.MESSAGE(50, 'REPORT WAS STOPPED BEFORE THE PARM FORM.');

      RAISE SRW.PROGRAM_ABORT;

END;

Example 2 - SRW.RUN_REPORT

Syntax/Explanation:

  SRW.RUN_REPORT(command_line CHAR);  -- Executes specified R25RUN command

  SRW.RUN_REPORT_FAILURE;    -- Stops report execution when failure of

                             -- SRW.RUN_REPORT occurs.

Example:

Drill Down Report called from a report button:

FUNCTION foo RETURN BOOLEAN IS

BEGIN

  SRW.RUN_REPORT('report=REP_A p_parm1=20');      -- calls report REP_A and

  EXCEPTION                                       -- display to screen;

     WHEN SRW.RUN_REPORT_FAILURE THEN             -- passes value 20 to the

       SRW.MESSAGE(100, 'ERROR CALLING REPORT.')  -- p_parm1 parameter

       RAISE SRW.PROGRAM_ABORT;

  RETURN TRUE;

END;

Note that you can only call another report to the screen using SRW.RUN_REPORT

from a button.  If you issue SRW.RUN_REPORT from a report trigger, you

must pass BATCH=YES.  Therefore, DESTYPE can only be FILE, PRINTER, or MAIL.

Example 3 - SRW.SET_ATTR

Syntax/Explanation

SRW.SET_ATTR applies attribute settings, such as font size or color, to layout

objects.  This procedure applies formatting attributes to the current frame,

repeating frame, field, or boilerplate object.  You specify which formatting

attributes should be applied to an object by defining a mask.  A mask is a

collection of attributes; each attribute has one or more values.  You can

change a number of attributes, such as the borderwidth, background border

color, border pattern, foreground border color, fill pattern, global text

color, weight and several other attributes.  For a complete list, refer to

the "The Oracle Reports PL/SQL Package: SRW" section in the

"PL/SQL Interface" chapter of the Oracle Reports Reference Manual.

SRW.SET_ATTR(object_id NUMBER, attr SRW.GENERIC_ATTR);

object_id   Is always zero.  (The object must always set its own attributes.)

attr        Is srw.attr (that is, the attributes to change and their values).

Example:

If salary equals 2000, this code segment sets the following attributes:

   Border width = 1

   Foreground border color = red

   Background border color = blue

   Border pattern = checker

   Foreground fill color = yellow

   Background fill color = green

   Fill pattern = crisscross

IF :sal = 2000 THEN

    SRW.ATTR.MASK  :=           SRW.BORDERWIDTH_ATTR       +

                                SRW.FBCOLOR_ATTR           +

                                SRW.BBCOLOR_ATTR           +

                                SRW.BORDPATT_ATTR          +

                                SRW.FFCOLOR_ATTR           +

                                SRW.BFCOLOR_ATTR           +

                                SRW.FILLPATT_ATTR;

    SRW.ATTR.BORDERWIDTH   := 1;

    SRW.ATTR.FBCOLOR       := 'red';

    SRW.ATTR.BBCOLOR       := 'blue';

    SRW.ATTR.BORDPATT      := 'checker';

    SRW.ATTR.FFCOLOR       := 'yellow';

    SRW.ATTR.BFCOLOR       := 'green';

    SRW.ATTR.FILLPATT      := 'crisscross';

SRW.SET_ATTR(0, SRW.ATTR);

END IF;

RETURN TRUE;

Example 4 - SRW. SET_FIELD

Syntax/Explanation:

SRW.SET_FIELD_CHAR(object_id, text CHAR);

SRW.SET_FIELD_DATE(object_id, date DATE);

SRW.SET_FIELD_NUM(object_id, number NUMBER);

These SRW SET_FIELD procedures are very useful in format triggers.  They are

often used to change the data that will be displayed in a particular item

based on a specific condition.  These are invaluable, since you cannot use

assignment statements to set a field's value in Reports.  SRW.SET_FIELD_XXX

must be used to accomplish this.

Example:

To display the text "LOW" in the salary_range field for every employee whose

salary is less than 20,000, create the following format trigger on the

salary_range field.

FUNCTION chgfield RETURN BOOLEAN IS

BEGIN

  IF :sal < 20000 THEN

     SRW.SET_FIELD_CHAR(0, 'LOW');

  ELSE

     END IF;

RETURN TRUE;

END;

For additional examples using the SRW.SET_FIELD_XXXX procedures in format

triggers, see:

Note31364.1  Using Format Triggers in Reports.

Running SQL*Trace on an Applications Report

There are two methods to running a Report with SQL*Trace which are:

Method #1:  Normally, in order to run a report with SQL*Trace, you would have to do the following:

    1. Turn SQL*Trace on by modifying your init.ora file.
    2. Shut down the concurrent managers and the database.
    3. Bring the database and the concurrent managers back up.
    4. Arrange to run JUST that specific report without running
       anything else.
    5. After running the report, shut down the database and
       concurrent managers.
    6. Turn SQL*Trace off by modifying your init.ora file.
    7. Bring the database and concurrent managers back up.

Method #2:  A simpler method is as follows (detailed instructions follow):

    1. Backup your current .rdf of the report.
    2. Modify the .rex for the report, adding two simple lines.
    3. Convert the new .rex into the new .rdf file.
    4. Run the report.
    5. Restore the original .rdf file.


Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.
   
2. Create the .rex file if necessary:

   Make sure a .rex text file of the report exists.  (Customers are
   not shipped the .rex text file to save space on their system).
   If the .rex does not exist, create one by issuing the following
   command as the applmgr user:
 
     r25convm userid=<userid>/<password> source=<reportname>.rdf
     stype=rdffile dtype=rexfile dest=<reportname>.rex  
 
   For Example:

     r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
     dtype=rexfile dest=RAXIIR.rex
 
3. Edit the .rex file:

   A. Pull the .rex file into your editor of choice and search
      for 'SRWINIT'.  You should find an entry similar to the
      following, with a NAME of 'beforerep____':

      Example I:

        DEFINE  TOOL_PLSQL
        BEGIN
          ITEMID           = 103
          NAME             = <<"beforerep0040">>
          PLSLFID_EP       = (BLONG) NULLP
          OBJECT_ID        = 0
          TYPE
          PLSLFID_ST       = (TLONG)
        <<"SRW.USER_EXIT('FND SRWINIT');
        ">>
        END

      Example II:

        BEGIN
          SRW.USER_EXIT('FND SRWINIT');
          IF (:p_debug_switch = 'Y') THEN
            SRW.MESSAGE('1','After SRWINIT');
          END IF;
        END

   B. Immediately after the ; which follows 'FND SRWINIT', (and
      remaining within the "" that surround the line) add the
      following line:

        srw.do_sql('alter session set sql_trace=TRUE');

      The modified versions of the above examples should look
      like the following:    

      Example I:

        DEFINE  TOOL_PLSQL                                
        BEGIN  
          ITEMID           = 103                                  
          NAME             = <<"beforerep0040">>        
          PLSLFID_EP       = (BLONG) NULLP            
          OBJECT_ID        = 0      
          TYPE                                                              
          PLSLFID_ST       = (TLONG)      
        <<"SRW.USER_EXIT('FND SRWINIT');
        srw.do_sql('alter session set sql_trace=TRUE');                      
        ">>                                                                  
        END
                                                                 
      Example II:
                                                           
        BEGIN                                                                
          SRW.USER_EXIT('FND SRWINIT');
          srw.do_sql('alter session set sql_trace=TRUE');                    
          IF (:p_debug_switch = 'Y') THEN                                    
            SRW.MESSAGE('1','After SRWINIT');                            
          END IF;                                                              
        END

   C. Find the line similar to the following, by searching
      for 'SRWEXIT':

        <<"SRW.USER_EXIT('FND SRWEXIT');
        ">>
        END

   D. Immediately after the ; which follows 'FND SRWEXIT',
      but remaining within the "", add the following line:

        srw.do_sql('alter session set sql_trace=FALSE');

      For Example:

        <<"SRW.USER_EXIT('FND SRWEXIT');
        srw.do_sql('alter session set sql_trace=FALSE');
        ">>
        END

      NOTE: This is really not necessary as the trace session
      will terminate when the report completes, but it's a
      good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:
 
   To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):
   
     r25convm userid=<userid>/<password> source=<reportname>.rex
     stype=rexfile dtype=rdffile dest=<reportname>.rdf

   For Example:

     r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
     dtype=rdffile dest=RAXIIR.rdf
 
   NOTE: If you did not properly modify the .rex file, the system
   will not be able to generate the .rdf file.  An example of the
   message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

   Logon to the Applications and run the report through the concurrent
   managers.  When it has completed, exit the Applications.

6. Access the trace file:

   The trace file will be located in the directory specified by the  
   user_dump_dest variable in your init.ora file.    

7. Restore the original report files:

   Restore the original .rdf file (and .rex if one existed before  
   modification).  You may wish to backup the .rdf file containing
   the SQL*Trace code, just in case you need to run it at a later
   time.  Once the issue you're troubleshooting is resolved, you
   can delete it.


Click to add to Favorites Running SQL*Trace on an Applications Report (Doc ID 1019231.6) To BottomTo Bottom
Applies to:
Oracle Application Object Library - Version 11.5.10.2 to 12.1.3 [Release 11.5 to 12.1]
Information in this document applies to any platform.
***Checked for relevance on 10-Jan-2013***
Goal

 The goal of this document is to provide instructions to run a report with SQL*Trace
Solution

There are two methods to running a Report with SQL*Trace which are:

Method #1:  Normally, in order to run a report with SQL*Trace, you would have to do the following:

    1. Turn SQL*Trace on by modifying your init.ora file.
    2. Shut down the concurrent managers and the database.
    3. Bring the database and the concurrent managers back up.
    4. Arrange to run JUST that specific report without running
       anything else.
    5. After running the report, shut down the database and
       concurrent managers.
    6. Turn SQL*Trace off by modifying your init.ora file.
    7. Bring the database and concurrent managers back up.

Method #2:  A simpler method is as follows (detailed instructions follow):

    1. Backup your current .rdf of the report.
    2. Modify the .rex for the report, adding two simple lines.
    3. Convert the new .rex into the new .rdf file.
    4. Run the report.
    5. Restore the original .rdf file.


Detailed instructions to run a Report with SQL*Trace using Method #2:

1. Backup your current .rdf and .rex files.
   
2. Create the .rex file if necessary:

   Make sure a .rex text file of the report exists.  (Customers are
   not shipped the .rex text file to save space on their system).
   If the .rex does not exist, create one by issuing the following
   command as the applmgr user:
 
     r25convm userid=<userid>/<password> source=<reportname>.rdf
     stype=rdffile dtype=rexfile dest=<reportname>.rex  
 
   For Example:

     r25convm userid=ar/ar source=RAXIIR.rdf stype=rdffile
     dtype=rexfile dest=RAXIIR.rex
 
3. Edit the .rex file:

   A. Pull the .rex file into your editor of choice and search
      for 'SRWINIT'.  You should find an entry similar to the
      following, with a NAME of 'beforerep____':

      Example I:

        DEFINE  TOOL_PLSQL
        BEGIN
          ITEMID           = 103
          NAME             = <<"beforerep0040">>
          PLSLFID_EP       = (BLONG) NULLP
          OBJECT_ID        = 0
          TYPE
          PLSLFID_ST       = (TLONG)
        <<"SRW.USER_EXIT('FND SRWINIT');
        ">>
        END

      Example II:

        BEGIN
          SRW.USER_EXIT('FND SRWINIT');
          IF (:p_debug_switch = 'Y') THEN
            SRW.MESSAGE('1','After SRWINIT');
          END IF;
        END

   B. Immediately after the ; which follows 'FND SRWINIT', (and
      remaining within the "" that surround the line) add the
      following line:

        srw.do_sql('alter session set sql_trace=TRUE');

      The modified versions of the above examples should look
      like the following:    

      Example I:

        DEFINE  TOOL_PLSQL                                
        BEGIN  
          ITEMID           = 103                                  
          NAME             = <<"beforerep0040">>        
          PLSLFID_EP       = (BLONG) NULLP            
          OBJECT_ID        = 0      
          TYPE                                                              
          PLSLFID_ST       = (TLONG)      
        <<"SRW.USER_EXIT('FND SRWINIT');
        srw.do_sql('alter session set sql_trace=TRUE');                      
        ">>                                                                  
        END
                                                                 
      Example II:
                                                           
        BEGIN                                                                
          SRW.USER_EXIT('FND SRWINIT');
          srw.do_sql('alter session set sql_trace=TRUE');                    
          IF (:p_debug_switch = 'Y') THEN                                    
            SRW.MESSAGE('1','After SRWINIT');                            
          END IF;                                                              
        END

   C. Find the line similar to the following, by searching
      for 'SRWEXIT':

        <<"SRW.USER_EXIT('FND SRWEXIT');
        ">>
        END

   D. Immediately after the ; which follows 'FND SRWEXIT',
      but remaining within the "", add the following line:

        srw.do_sql('alter session set sql_trace=FALSE');

      For Example:

        <<"SRW.USER_EXIT('FND SRWEXIT');
        srw.do_sql('alter session set sql_trace=FALSE');
        ">>
        END

      NOTE: This is really not necessary as the trace session
      will terminate when the report completes, but it's a
      good idea to include it as a precautionary measure.

4. Convert the modified .rex file into a .rdf file:
 
   To do this, issue the following command as the applmgr user -- rwcon60 (Reports 6.0.x) or r25convm (Reports 2.5.x) or r25mrepm (Reports 2.5.x):
   
     r25convm userid=<userid>/<password> source=<reportname>.rex
     stype=rexfile dtype=rdffile dest=<reportname>.rdf

   For Example:

     r25convm userid=ap/ap source=RAXIIR.rex stype=rexfile
     dtype=rdffile dest=RAXIIR.rdf
 
   NOTE: If you did not properly modify the .rex file, the system
   will not be able to generate the .rdf file.  An example of the
   message you may get is: "Conversion of 'RAXNCAR.rex' cancelled".

5. Run the report:

   Logon to the Applications and run the report through the concurrent
   managers.  When it has completed, exit the Applications.

6. Access the trace file:

   The trace file will be located in the directory specified by the  
   user_dump_dest variable in your init.ora file.    

7. Restore the original report files:

   Restore the original .rdf file (and .rex if one existed before  
   modification).  You may wish to backup the .rdf file containing
   the SQL*Trace code, just in case you need to run it at a later
   time.  Once the issue you're troubleshooting is resolved, you
   can delete it.
References
NOTE:1070541.6 - How To Generate A Report ( .RDF File) From The Unix Command Line
NOTE:1020489.102 - How to Generate a Report from a Windows NT Command Line
NOTE:1019231.6 - Running SQL*Trace on an Applications Report