cat concreq.sh

#!/bin/bash

. $HOME/.profile

while [ 1 -eq 1 ]; do
sqlplus apps/pwd <<-EOF
set lines 100 pages 100
col "USER_CONCURRENT_QUEUE_NAME" format a40;
--set termout off
--set arraysize 5
set echo off
set verify off
set heading on
set feed off
set colsep,;
@/test1/concreq.sql
EOF
sleep 15
done
$

$cat concreq1.sql

column tm new_value file_time noprint
select to_char(sysdate, 'MMDDYYYY') tm from dual ;
prompt &file_time
spool /test1/TEST1_CM_&file_time..csv append
col running head "Running" format 9999999
select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE
UNION ALL
SELECT (to_char(sysdate,'MM/DD/YYYY HH24:MI:SS')),0,0 from dual
WHERE  NOT EXISTS
(select to_char(r.ACTUAL_START_DATE,'MM-DD-YYYY HH24:MI:SS'),sum(decode(r.phase_code,'R',1,0)) - sum(decode(r.status_code,'W',1,0)) running,sum(decode(r.phase_code,'P',1,0)) pending
from applsys.fnd_concurrent_requests r,applsys.fnd_concurrent_processes p,applsys.fnd_concurrent_queues q
Where r.controlling_manager (+) = p.concurrent_process_id
and p.queue_application_id = q.application_id
and p.concurrent_queue_id = q.concurrent_queue_id
and q.max_processes >= 0 and (r.phase_code in ('R','P','I'))
group by r.ACTUAL_START_DATE);
spool off;
-bash-3.2$

Current output after executing the concreq.sh script:


-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0

$

-bash-3.2$ cat TEST1_CM_04102013.csv

TO_CHAR(R.ACTUAL_ST, Running,   PENDING
-------------------,--------,----------
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
04-16-2013 19:03:32,       1,         0
$