Shell Programming and Scripting

count=`sqlplus -s $ORACLE_ACCOUNT << EOF
set heading on
set wrap on
set feedback off
column ChangeNumber format a12
column Summary format a20
column Status format a6
column Assert format a10
set pagesize 50000
set linesize 32000
set colsep |

select distinct c.chg_ref_num as ChangeNumber, c.summary as Summary, c.status as Status, n.NR_PRIM_SEARCH_KEY as Assert from chgalg al, chg c, net_res n where
c.id = al.change_id and c.zasset = n.id and (al.description like 'Status changed from%to%SL%' or al.description like 'Status changed from%to%SV%')
and al.time_stamp between
(Select (TO_DATE(to_char(trunc(sysdate-7)+(.041987 * 13),'dd/mm/rrrr HH24:MI:SS'), 'DD/MM/RRRR HH24:MI:SS')-TO_DATE('01/01/1970 00:00:00', 'DD/MM/RRRR HH24:MI:SS'))* 24 * 60 * 60 from dual)
and
(select (TO_DATE(to_char(trunc(sysdate),'dd/mm/rrrr HH24:MI:SS'),'dd/mm/rrrr HH24:MI:SS') - TO_DATE('01-JAN-1970 00:00:00', 'DD/MM/RRRR HH24:MI:SS')) * 24 * 60 * 60 from dual) order by c.chg_ref_num ;

exit;
EOF`
echo "$count"

CHANGENUMBER|SUMMARY                                                    |STATUS|ASSERT
------------|--------------------------------------------------|------|----------
61535             |Use-Case Specification: UC23 - Message logging for|CS    |CAM
             | Statistics                                           |      |

62956             |Create a list of TP members e-mail and physical ad|SL    |CODS
             |dress                                                    |      |

63055             |EuroBonus Welcom Introduction_new movies to be add|SL    |FLYSAS
             |ed for SE                                                    |      |

63134             |AUDIT: Deletion of DE old data                         |CS    |SUPEROFFIC
             |                                                             |      |E

63136             |Reopen closed TPC in CODS and migrate to TP accoun|SV    |CODS
             |t                                                             |      |