Oracle Apps Scripts

Calling a PL/SQL Concurrent Program in Real-Time

Lets say there is an Oracle Delivered concurrent program of type PL/SQL.

Your requirement is such that you wish to call that PL/SQL Executable [attached to concurrent program] directly from some other process, in real-time mode, in the same session as that of the parent Oracle session[forms or report or some other plsql ]. In order to call that pl/sql program within the same session, your challenge may be that the concurrent  program could be writing into log/output file using fnd_file API which you want to display to the user somehow.

To get a handle to the log/output of the API [which is executable of Conc Program], we need to do the following:-

1. Call fnd_global.initialize ensuring you pass it two parameters in bold below

declare

...your variables here

  v_session_id INTEGER := userenv('sessionid');

      v_trace      VARCHAR2(2000) := fnd_trace.get_trace_filename;

      n_conc_request_id INTEGER ;

begin

//optionally trace the API Call with bind variable values

  execute immediate 'alter session set events=''10046 trace name context forever, level 12''' ; 

    //the trace file details will be in variable v_trace     

    //Now call fnd_global.initialize ensuring you pass it two parameters in bold below

  fnd_global.initialize

    (

    session_id                     => v_session_id

                       ,user_id                          => 18594 //or pass the current fnd_global.user_id itself

                       ,resp_id                          => 4650126 //or pass the current fnd_global.resp_id itself

                       ,resp_appl_id            => 8405 //or pass the current fnd_global.resp_appl_id itself

                       ,security_group_id       => 0

                       ,site_id                           => NULL

                       ,login_id                     => 3115003

                       ,conc_login_id              => NULL

                       ,prog_appl_id           => NULL

                       ,conc_program_id       => 229233 //The program id of concurrent request

                   ,conc_request_id    => n_conc_request_id //From fnd_concurrent_requests_s.nextval

                      ,conc_priority_request    => NULL

   );

      //Lets assume the nextval from sequence is 14633154

//now call the API, i have arbitrarily typed in just any API

    ar_process_trx.place_hold( v_errbuf, v_retcode, v_trx_id ) ;

end ;

2. Run the SQL on fnd_temp_files, as below, using the fictional request_id generated from sequence [ in variable n_conc_request_id ]

3. In order to read those files, ensure that the directory [/usr/tmp in this case] is listed in v$parameter utl_file_dir

In this approach, no change is made to the Original concurrent program. Hence, if need be, users can continue to run the concurrent process as usual from SRS.

Trace file with bind variables

This approach also makes it easy to trace a concurrent program with bind variables. The location of the trace will can be determined by using API fnd_trace.get_trace_filename. The beauty of this API construct is that it takes care of different platforms like Linux, NT, HP, AIX etc , when constructing the name and full path of the trace file for Concurrent Program

Anil Passi

Add comment