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

  • Please provide provide your expected output and some sample data. – JSapkota Mar 13 '17 at 4:06
  • added some description please go through and help. – Girish Sunkara Mar 13 '17 at 4:31
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.

  • Command works perfectly but I am getting kind of a warning message tar: Removing leading `/' from member names – Girish Sunkara Mar 13 '17 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. – JSapkota Mar 13 '17 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? – Girish Sunkara Mar 13 '17 at 5:25
  • 1
    If you are looking at database recovery, I suggest you lean about RMAN – BobC Mar 14 '17 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 '17 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.