How to spool output from SQL*PLUS to appear in single line with 1 space in between them? I have a table with 20 rows. I want to spool the rows into a text file where rows come side by side in one line and have one space in between to keep them separated. I intend to create a tar.gz file by keeping them in one line.

Sample data:

col1
------

file1
file2
file3

expected output:

tar cvzf tarfile.tgz file1 file2 file3
You can use the following query to get all rows in single row.

SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
FROM your_table_name;


Sample Data:

SQL&gt; select * from tbl1;

COL1
--------------------
file 1
file 2
file 3


Output:

tar cvzf file 1 file 2 file 3


To spool-

SQL&gt; set heading off
SQL&gt; spool 'output.txt'
SQL&gt; SELECT 'tar cvzf tarfile.tgz ' || LISTAGG(col1, ' ') WITHIN GROUP (ORDER BY col1)
     FROM your_table_name;
SQL&gt; spool off


Here is the fiddle.