I am running a win bat script which calls a sqlplus script, the output from the sqlplus script is then emailed, this is my script
ttitle left 'Last Successful Run for Oracle or Siebel'
break on cntry skip 1;
set pages 100;
select lpad(country,6,' ') cntry, max(timestamp) Timestamp,substr(LPAD(test_type,10,' '),0,10) Type,
CASE
WHEN ((sysdate-max(timestamp))*1440) >=60 THEN '<===== ERROR over 60 minutes since last run'
WHEN ((sysdate-max(timestamp))*1440) >=30 THEN '<===== WARNING over 30 minutes since last run'
ELSE ''
end as status
from rfgdba.perf_test_results ptr, rfgdba.perf_tests pt
where country is not null and test_id in ((select id from rfgdba.perf_tests where live='Y')) and test_type in ('ORACLE','SIEBEL')
and timestamp > sysdate-30 and ptr.test_id=pt.ID
group by country, test_type
order by country, timestamp ;
the output is in table format, but I want to add colour coding around the CASE section, ie if value is >60 then colour the txt backgroudn red, if value is >30<60 then txt backgroudn orange and if value is <30 then txt background green, but I cant get it to work, I have tried the following
CASE
WHEN ((sysdate-max(timestamp))*1440) >=60 THEN '<tr style="color:red'||'<===== ERROR over 60 minutes since last run'||'"></tr>'
WHEN ((sysdate-max(timestamp))*1440) >=30 THEN '<tr style="color:orange'||'<===== WARNING over 30 minutes since last run'||'"></tr>'
ELSE <tr style="color:green'||''||'"></tr>'
end as status
but it is giving an error
ERROR: ORA-01756: quoted string not properly terminated
so I am stumped :(
Now my sql is completing by the output looks like this
Last Successful Run for Oracle or Siebel
CNTRY TIMESTAMP TYPE STATUS
at 08-20-2015 12:51:45 SIEBEL <tr style="color:red<===== ERROR over 60 minutes since last run"></tr>
08-20-2015 13:48:21 ORACLE <tr style="color:green"></tr>
be 08-20-2015 13:46:53 ORACLE <tr style="color:green"></tr>
08-20-2015 13:51:28 SIEBEL <tr style="color:green"></tr>