Shell Programming and Scripting

sqlplus -s un/pw <<EOF > test.log
SET ECHO OFF HEAD OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON
SELECT tran_date || ',' || event_code || ',' || event_desc || ',' || time_out_count || ',' || counted
FROM (
    SELECT   /*+ ORDERED INDEX (A FK_EVENT_HIST_1) */
             TRUNC (start_dtg) AS tran_date 
            ,a.event_code
            ,b.event_desc
            ,SUM (CASE
                      WHEN ERROR_TEXT LIKE 'SystemError:Operatio%'
                          THEN 1
                      ELSE 0
                  END) AS time_out_count
            ,COUNT (*) counted
    FROM     trn_event_history a
            ,trn_event b
    WHERE    TRUNC (start_dtg) >=   TRUNC (SYSDATE)
                                  - 2
    AND      a.event_code = b.event_code
    GROUP BY TRUNC (start_dtg)
            ,a.event_code
            ,b.event_desc
    HAVING   SUM (CASE
                      WHEN ERROR_TEXT LIKE 'SystemError:Operatio%'
                          THEN 1
                      ELSE 0
                  END) > 0
    ORDER BY 1
            ,3);
EOF
$ cat test.log                                                                                

21-SEP-05,1,111,1,1