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