0

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

2
  • Please provide provide your expected output and some sample data.
    – atokpas
    Mar 13, 2017 at 4:06
  • added some description please go through and help. Mar 13, 2017 at 4:31

1 Answer 1

2

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> select * from tbl1;

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

Output:

tar cvzf file 1 file 2 file 3

To spool-

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

Here is the fiddle.

5
  • Command works perfectly but I am getting kind of a warning message tar: Removing leading `/' from member names Mar 13, 2017 at 4:59
  • It is related to the OS command tar. You might have unsupported commands in your output. Please verify that output only contains supported commands. Or post that as question in unix/liux site.
    – atokpas
    Mar 13, 2017 at 5:17
  • I sorted that out. Now a new error is coming. I do not know if this is ignorable. tar: /u01/app/oracle/oradata/db5/sysaux01.dbf: file changed as we read it I plan to use this tar.gz for database recovery purposes so will this cause problems? Mar 13, 2017 at 5:25
  • 1
    If you are looking at database recovery, I suggest you lean about RMAN
    – BobC
    Mar 14, 2017 at 2:14
  • @GirishSunkara: yes, that will cause a lot of problems. Oracle has its own backup tool. Check the Database Backup and Recovery User's Guide
    – miracle173
    May 22, 2017 at 12:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.