Parameters and logs for Oracle Concurrent program

Concurrent programs using PL/SQL stored procedures can generate log files or output files.

Your stored procedure concurrent program must have errbuf and retcode as the first two parameters. Use

1. errbuf to return any error messages, and 
   
2. retcode to return completion status. 

The parameter retcode returns

• 0 for success, 
• 1 for success with warnings, and 
• 2 for error. 

After your concurrent program runs, the concurrent manager writes the contents of both errbuf and retcode to the log file associated with your concurrent request.

Example

 /*
This is sample PL/SQL concurrent program submits 10  sub-requests. The sub-requests are submitted one at a time.  Each time a sub-request is submitted, the parent exits to the Running/Paused state, so that it does not consume any resources while waiting for the child request, to complete.  When the child completes the parent is restarted.  */

create or replace procedure parent  (errbuf out varchar2,
                                 retcode   out number) is
   i number;
   req_data varchar2(10);
   r number;

begin
   --
   -- Read the value from REQUEST_DATA.  If this is the
   -- first run of the program, then this value will be
   -- null.
   -- Otherwise, this will be the value that we passed to
   -- SET_REQ_GLOBALS on the previous run.
   --
   req_data := fnd_conc_global.request_data;

   --
   -- If this is the first run, we'll set i = 1.
   -- Otherwise, we'll set i = request_data + 1, and we'll
   -- exit if we're done.
   --
   if (req_data is not null) then
     i := to_number(req_data);
     i := i + 1;
     if (i < 11  ) then
       errbuf := 'Done!';
       retcode := 0 ;
       return;
     end if;
   else
     i := 1;
   end if;

   --
   -- Submit the child request.  The sub_request parameter
   -- must be set to 'Y'.
   --
   r := fnd_request.submit_request('FND', 'CHILD',
                        'Child ' || to_char(i), NULL,
                        TRUE, fnd_conc_global.printer);

   if r = 0 then
     --
     -- If request submission failed, exit with error.
     --
     errbuf := fnd_message.get;
     retcode := 2;
   else
     --
     -- Here we set the globals to put the program into the
     -- PAUSED status on exit, and to save the state in
     -- request_data.
     --
    fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
                                request_data => to_char(i));
    errbuf := 'Sub-Request submitted!';
    retcode := 0 ;
   end if;

   return;

end;