Shell Programming and Scripting

count=`sqlplus -s $ORACLE_ACCOUNT << EOF
set heading off
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

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

58508 Ett fel i PINCA TGL2 SV PALS f�r Piloter och Cab in 61535 Use-Case Specificati CS CAM on: UC23 -
Message l ogging for Statistic s 62641 Use Default ACV inst CS COTI ead of fitted config uration from codesha
re partner SSIM7 63055 EuroBonus Welcom Int SL FLYSAS roduction_new movies to be added for SE 63134
AUDIT: Deletion of D CS SUPEROFFIC E old data E 63136 Reopen closed TPC in SL CODS CODS and migrate to TP account

...
echo "$count"
...

select count(*) from (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);

CHANGENUMBER| SUMMARY| STATUS| ASSERT
-------------------------------------------------------------------
<some Number>| <summary>|<staus>|<assert>

x=`sqlplus -s $ORACLE_ACCOUNT << EOF
set heading off
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
spool count.txt
select ChangeNumber||'|'||Summary||'|'Status||'|'||Assert from dual;

select ChangeNumber||'|'||Summary||'|'Status||'|'||Assert from (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 );
spool off;
 
exit;

EOF`
 
cat count.txt

select ChangeNumber||'|'||Summary||'|'Status||'|'||Assert from dual
                                            *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected 


select ChangeNumber||'|'||Summary||'|'Status||'|'||Assert from (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
                                            *
ERROR at line 1:
ORA-00923: FROM keyword not found where expected