<-----Google Analytics Code Start----> <-----Google Analytics Code Close---->
Skip to content



Default screen resolution  Wide screen resolution  Increase font size  Decrease font size  Default font size  Skip to content Default color Pink color Green color Green color
Home arrow Technical Articles arrow Oracle Apps Scripts arrow Calling a PL/SQL Concurrent Program in Real-Time
This website has now moved to http://apps2fusion.com

Calling a PL/SQL Concurrent Program in Real-Time | Print |  E-mail
Written by Anil Passi   
Thursday, 05 July 2007
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
...your variables here
      v_session_id INTEGER := userenv('sessionid');
      v_trace      VARCHAR2(2000) := fnd_trace.get_trace_filename;
      n_conc_request_id INTEGER ;
//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

                        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
Comments (4)add
written by manikandan , July 14, 2007
Its really superb.
written by Devendar , October 16, 2007
That's really great...
thnx a lot....
I want to know the below database objects are used in which reports.
written by I.K.Srinivas , November 22, 2007
Hi all

I want to know the below database objects are used in which reports.How can I do that,
is there any command or script I can run and get the details.

Please help me.
Thanks In advance
written by Sandeep Arya , February 05, 2008
Hi Anil ,
Need your help .Can u tell me while preparing the technical design (TS) whether we have to write the code first and then TS or directly preparing the TS then make the code.

and how we can make the our TS effectively .If you have any prepared TS pls send me for the guidance .


You must be logged in to a comment. Please register if you do not have an account yet.