sqlplus and spool options

Question

I have a batch script which runs a SQL*Plus command and I am spooling the output to a CSV file. All outputs have only one column "count". Is there a way to add text to the results?

"The_current_valueOf Query1" "Count1"
"The_current_valueOf Query2" "Count2"

Any help/advise will be greatly appreciated.

set colsep ,
set pagesize 0
set trimspool on
set headsep off
set Newpage none

spool D:\TRHMIBS\TRH\TRHStats.csv
select count(*) from host.ufm where insert_time between trunc(sysdate) and sysdate
/
spool D:\TRHMIBS\TRH\TRHStats.csv APPEND

select count(*) from host.amendment where insert_time between trunc(sysdate) and sysdate and msg_source = 'AUDIT'
/
spool D:\TRHMIBS\TRH\TRHStats.csv APPEND
select count(*) from host.ufm_amendment where insert_time between trunc(sysdate) and sysdate and msg_source = 'DAS' and ext_token is null
/
spool off; 

exit;

Answer 1

You could just select the message as a hard-coded literal. E.g.:

spool D:\TRHMIBS\TRH\TRHStats.csv
SELECT 'The_current_valueOf Query1:', COUNT(*)
FROM   host.ufm
WHERE  insert_time BETWEEN TRUNC(sysdate) AND sysdate
/

Answer 2

This is nothing to do with spooling. Your second and third spool commands are redundant - everything is spooled to the open file until you turn it off. But that's a side issue.

You can can add another column to your output with a column expression, which can be a string literal. So you can change the first query to:

select 'The_current_valueOf Query1', count(*)
from host.ufm
where insert_time between trunc(sysdate) and sysdate

and then do the same thing for the other two queries.

Since you want it as CSV you could leave colsep alone and have a single column with a comma embedded in it, which you can do with concatenation:

select 'The_current_valueOf Query1,' || count(*)
from host.ufm
where insert_time between trunc(sysdate) and sysdate

And rather than running three separate queries you could union them together; doesn't save on processing but means the output all comes together.

select 'The_current_valueOf Query1,' || count(*)
from host.ufm
where insert_time between trunc(sysdate) and sysdate
union all
select 'The_current_valueOf Query1,' || count(*)
from host.amendment
where insert_time between trunc(sysdate) and sysdate
union all
...