sqlplus HTML markup output

Question

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

Vladimir Kocjancic · Accepted Answer · 2015-08-20 12:19:19Z

1

That would be because your else statement doesn't have an opening quote before <tr> .

answered Aug 20, 2015 at 12:19

Vladimir Kocjancic

1,8404 gold badges23 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nyehus · Accepted Answer · 2015-08-20 12:57:24Z

0

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>

answered Aug 20, 2015 at 12:57

nyehus

359 bronze badges

1 Comment

Vladimir Kocjancic Over a year ago

Your SQL returns html color based on when it was run. Looking at your results and assuming you ran it at 13:51 something, only first entry should be red all others green with no text.What seems to be the problem?

Collectives™ on Stack Overflow

sqlplus HTML markup output

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related