Log and Output Files of Concurrent Requests

I do not perform user acceptance testing but recently I had to help users with acceptance testing. I was supposed to run a request set and save log files and output files. That request set had 38 concurrent programs and users wanted to save 20 log and output files. It was painful to save log and output files. So I created this small script that can save log and output files for all requests. This script should be modified for

1. Username who ran the request sets
2. Request set run start and end time
3. Test Case Number (It is used for group results of a request set run, it could be Request Set request id too)
4. Directory name where file should be copied

The output of this script can be transferred to shell script and by executing that shell script, log and output files can be copied to desired directory.

DECLARE l_outfile_suffix varchar2(30); l_test_case_id varchar2(15) := 'TestCase01_' ; l_dir varchar2(60) := '/home/myid/tmp'; l_user_name varchar2(30) := 'myOracleUserName'; l_start_time varchar2(30) := '07-SEP-2016 14:23:51'; l_end_time varchar2(30) := '07-SEP-2016 20:23:51'; CURSOR c_req IS SELECT req.logfile_name , nvl(( SELECT file_name FROM fnd_conc_req_outputs WHERE concurrent_request_id = req.request_id ), outfile_name ) outfile_name , translate(prog.user_concurrent_program_name, ' '|| chr(38), '_-') name , req.request_id FROM apps.fnd_concurrent_programs_vl prog , apps.fnd_concurrent_requests req , apps.fnd_responsibility_vl resp , apps.fnd_user usr WHERE prog.concurrent_program_id = req.concurrent_program_id AND prog.application_id = req.program_application_id AND req.responsibility_id = resp.responsibility_id AND req.responsibility_application_id = resp.application_id AND req.requested_by = usr.user_id AND usr.user_name = l_user_name AND req.actual_start_date BETWEEN to_date(l_start_time, 'dd-mon-yyyy hh24:mi:ss') AND to_date(l_end_time , 'dd-mon-yyyy hh24:mi:ss') -- -- The condition below will remove request set stages. Request set stage log files do not have -- any relevant information for user acceptance -- AND prog.user_concurrent_program_name NOT LIKE '%Request%Set%Stage' ORDER BY req.actual_start_date ; BEGIN FOR l_req in c_req LOOP l_outfile_suffix := substr(l_req.outfile_name, instr(l_req.outfile_name, '.')); IF l_outfile_suffix = '.out' THEN l_outfile_suffix := '_out.txt'; END IF; dbms_output.put_line('cp ' || l_req.logfile_name || ' ' || l_dir || '/' || l_test_case_id || l_req.request_id || '_' || l_req.name || '_log.txt' || ';'); dbms_output.put_line('if [[ -e ' || l_req.outfile_name || ' ]] && [[ -s ' || l_req.outfile_name || ' ]] ; then' ); dbms_output.put_line('cp ' || l_req.outfile_name || ' ' || l_dir || '/' || l_test_case_id || l_req.request_id || '_' || l_req.name|| l_outfile_suffix || ';'); dbms_output.put_line('fi'); END LOOP; dbms_output.put_line('zip ' || l_test_case_id || ' *.*' ); dbms_output.put_line('rm ' || l_test_case_id || '*.txt'); dbms_output.put_line('rm ' || l_test_case_id || '*.PDF'); END;