Is there any way to make output of my SQLPLUS script look better?

Question

I am new to sql.I would like to ask if there is any way to format my output to look more complex and more like one table?

My script looks like this

spool "\\PathToPutOutputInTextFile"
SELECT a.ARCHIVEID, count(*) as "Number of Documents", ROUND(SUM(c.CLENGTH)/1024/1024,2) as "Documents Size in MB"
        FROM ds_doc d
        INNER JOIN ds_arch a ON d.ARCHIVENO = a.ARCHIVENO
        INNER JOIN ds_comp c ON d.DOCIDNO = c.DOCIDNO
        GROUP BY a.ARCHIVEID;
spool off;

And I was also able to automatize this with .bat file that looks like this

sqlplus usr/pass@nameofdb @D:\IXTENT\monitoring\ASCheck.sql -path "\\PathToPutOutputInTextFile\test.txt

I was somehow manage to make this somehow work but my output looks like sh..t :/

This is my output.

ARCHIVEID

Number of Documents

Documents Size in MB

test_rt                                                                39
                        3.03

IL                                                                                  36
                        104

TN                                                                         139823
                20683.57


ARCHIVEID

Number of Documents

Documents Size in MB


T5                                                                               6931
               331978.15

TA                                                                                  4
                        .34

TT                                                                                 23
                        3.09

Is there any way to make it complex and look more like one table?

Thanks a lot.

Answer 1

One option would be

set echo off verify off head off feed off term off lines 120 pages 0
col "Number of Documents" for 9999999999
col "Documents Size in MB" for 9999999999
col ARCHIVEID for a30
spool "\\PathToPutOutputInTextFile"
SELECT a.ARCHIVEID, count(*) as "Number of Documents", ROUND(SUM(c.CLENGTH)/1024/1024,2) as "Documents Size in MB"
        FROM ds_doc d
        INNER JOIN ds_arch a ON d.ARCHIVENO = a.ARCHIVENO
        INNER JOIN ds_comp c ON d.DOCIDNO = c.DOCIDNO
        GROUP BY a.ARCHIVEID;
spool off;

However, if you want to use this file to load data in another database, a good option is to use set markup csv , guessing you have Oracle 12 or higher.

set echo off verify off head off feed off term off lines 120 pages 0
set markup csv delimiter ";"
spool "\\PathToPutOutputInTextFile"
SELECT a.ARCHIVEID, count(*) as "Number of Documents", ROUND(SUM(c.CLENGTH)/1024/1024,2) as "Documents Size in MB"
        FROM ds_doc d
        INNER JOIN ds_arch a ON d.ARCHIVENO = a.ARCHIVENO
        INNER JOIN ds_comp c ON d.DOCIDNO = c.DOCIDNO
        GROUP BY a.ARCHIVEID;
spool off;