CODEWITHSUNDEEP
sqloracle-databaseoutputsqlplus
Arnab
Arnab

Reputation: 7

SQL*Plus output to a text file on single line

I am running this SQL*Plus command to output the results to a text file:

set pagesize 200
spool d:\TRHMIBS\audit_ufm_report.txt

SELECT to_char(systimestamp,'yyyymmdd hh24:mi:ss') as now , to_char(COUNT(*)/(10*60),'999.99') as processing_rate 
  FROM AUDIT_TRANS U 
  WHERE U.LAST_UPDATED > SYSDATE -10/1440 
/
spool off 
exit;

I am receiving the output below. I am trying to have the row on a single line, like 20150902 5:00:00 36.51. Is it possible to do this?

Output file:

NOW                                                                             
---------------------------------------------------------------------------     
PROCESSING_RATE                                                                 
--------------------------------------------------------------------------------
20150902 15:00:00                                                               
  36.51

Upvotes: 0

Views: 1657

Answers (1)

Husqvik
Husqvik

Reputation: 5809

SQL*Plus uses as default returned column metadata which can be tricky when functions returning variable text are used. You can manually define format for any column using

COLUMN NOW FORMAT A20 HEADING "Now"
COLUMN processing_rate FORMAT 999D99 HEADING "Processing rate"

Headings are optional.

Check the documentation http://docs.oracle.com/database/121/SQPUG/ch_six.htm#SQPUG016.

Upvotes: 3

Related Questions