Search code examples
htmlsqlplusmarkup

sqlplus HTML markup output


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 :(


Solution

  • 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> 
    

    enter image description here