When i run the following script am getiing the output correct but i want to get it in the form of a table, could any one help me please. the script is a s follows
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
output is:
58508 Ett fel i PINCA TGL2 SV PALS fr 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
i could see the query which you provided doesnt contain the COUNT(*) . Please change the query to
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);
The query output is assigned to a variable called count and am printing it when i print it am getting the above mentioned output but i need it to be in a table format like in pl/sql like
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
What I ment was the echo in the end of your script strips newline and space characters from your output. Something like this should work:
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"
am Getting the desired output but when am sending this using mailx command am facing the alignment problem as follows...
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 | |