How to Generate a Trace file in Oracle Reports

Trace enable RDF Report

function BeforeReport return boolean is
  CURSOR c_program_id(p_request_id IN NUMBER) IS
SELECT concurrent_program_id, nvl(enable_trace,'N')
FROM   FND_CONCURRENT_REQUESTS
WHERE  REQUEST_ID = p_request_id;
   
CURSOR get_audsid IS
SELECT a.sid, a.serial#, b.spid FROM v$session a,v$process b
WHERE audsid = userenv('SESSIONID')
AND a.paddr = b.addr;
 
CURSOR get_dbname IS
SELECT name FROM v$database;    

v_enable_trace    FND_CONCURRENT_PROGRAMS.enable_trace%TYPE;
v_program_id      FND_CONCURRENT_PROGRAMS.concurrent_program_id%TYPE;
v_audsid NUMBER := userenv('SESSIONID');
v_sid NUMBER;
v_serial NUMBER;
v_spid VARCHAR2(9);
v_dbname VARCHAR2(25);
begin
   SRW.USER_EXIT('FND SRWINIT');
  SRW.MESSAGE( 1275, 'Report Version is 120.3 Last modified date is 02/09/2005');
-------------------------------------------Trace File Generation Code---------------------------------------------------------------------------
BEGIN
   OPEN c_program_id(:p_conc_request_id);
    FETCH c_program_id INTO v_program_id, v_enable_trace;
    CLOSE c_program_id;

    SRW.message( 1275, 'v_program_id -> '||v_program_id
            ||', v_enable_trace -> '||v_enable_trace
            ||', request_id -> '||:P_CONC_REQUEST_ID);
    IF v_enable_trace = 'Y' THEN
   
    OPEN get_audsid;
  FETCH get_audsid INTO v_sid, v_serial, v_spid;
  CLOSE get_audsid;
 
  OPEN get_dbname;
  FETCH get_dbname INTO v_dbname;
  CLOSE get_dbname;
 
  srw.message(1275,'TraceFile Name = '||lower(v_dbname)||'_ora_'||v_spid||'.trc');
 
  SRW.DO_SQL('ALTER SESSION SET EVENTS ''10046 trace name context forever, level 4''');
    END IF; --Enable Trace
    return (true);
   EXCEPTION
      WHEN OTHERS THEN
          SRW.MESSAGE( 1275, 'Error during enabling the trace. ErrCode -> '||SQLCODE ||', ErrMesg -> '||SQLERRM );
   END;
end;

Best Sellers in Video Games

Example: Submission of Standard Order Import Program

Example: Submission of Standard Order Import Program

SET SERVEROUTPUT ON;

DECLARE

     v_request_id                        NUMBER           DEFAULT 0;

    

    --Order Import Parameters

    p_operating_unit                     VARCHAR2(20)    := NULL;

    p_order_source                       VARCHAR2(20)    := 'XYZ';

    p_orig_sys_document_ref              VARCHAR2(20)    := NULL;

    p_operation_code                     VARCHAR2(20)    := NULL;

    p_validate_only                      VARCHAR2(20)    := 'N';

    p_debug_level                        VARCHAR2(20)    := '1';

    p_num_instances                      VARCHAR2(20)    := '4';

    p_sold_to_org_id                     VARCHAR2(20)    := NULL;

    p_sold_to_org                        VARCHAR2(20)    := NULL;

    p_change_sequence                    VARCHAR2(20)    := NULL;

    p_perf_param                         VARCHAR2(20)    := 'Y';

    p_rtrim_data                         VARCHAR2(20)    := 'N';

    p_pro_ord_with_null_flag             VARCHAR2(20)    := 'Y';

    p_default_org_id                     VARCHAR2(20)    := '83';

    p_validate_desc_flex                 VARCHAR2(20)    := 'N';

    -- End of Parameters -----

    v_context varchar2(100);

    FUNCTION set_context( i_user_name    IN  VARCHAR2

                         ,i_resp_name    IN  VARCHAR2

                         ,i_org_id       IN  NUMBER)

    RETURN VARCHAR2

    IS

        /* Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here */

    END set_context;

BEGIN

      -- Setting the context ----

      v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);

      IF v_context = 'F'

      THEN

        DBMS_OUTPUT.PUT_LINE('Error while setting the context');       

      END IF;

      DBMS_OUTPUT.PUT_LINE('Submit Order Import Concurrent Program');

      v_request_id:=  FND_REQUEST.SUBMIT_REQUEST (

               application  =>  'ONT'

              ,program      =>  'OEOIMP'

              ,description  =>  'Order Import'

              ,start_time   =>  SYSDATE

              ,sub_request  =>  NULL

              ,argument1    =>  p_operating_unit

              ,argument2    =>  p_order_source

              ,argument3    =>  p_orig_sys_document_ref

              ,argument4    =>  p_operation_code

              ,argument5    =>  p_validate_only

              ,argument6    =>  p_debug_level

              ,argument7    =>  p_num_instances

              ,argument8    =>  p_sold_to_org_id

              ,argument9    =>  p_sold_to_org

              ,argument10   =>  p_change_sequence

              ,argument11   =>  p_perf_param

              ,argument12   =>  p_rtrim_data

              ,argument13   =>  p_pro_ord_with_null_flag

              ,argument14   =>  p_default_org_id

              ,argument15   =>  p_validate_desc_flex

             );

       COMMIT;

       DBMS_OUTPUT.PUT_LINE('Request_id: '||v_request_id);

EXCEPTION WHEN OTHERS THEN        

       DBMS_OUTPUT.PUT_LINE(SQLCODE||' Error :'||SQLERRM);

END;