SQLPlus settings to generate tab-separated data file

One particular script that I have stolen on more than one occasion comes from an AskTom thread on extracting data to a flat file. If I needed a quick and dirty flat file out of SQL*Plus. I would tend to prefer the DUMP_CSV function Tom posted earlier on that thread for any sort of ongoing process, though.

As Justin pointed out in his link, using the set colsep function SQLPlus command saves typing a separator for each column.

But for tab-delimited, set colsep Chr(9) won't work.

For UNIX or LINUX, use set colsep ' ' with the space between the single-quotes being a typed tab.

For Windows, use these settings:

col TAB# new_value TAB NOPRINT
select chr(9) TAB# from dual;
set colsep "&TAB"

select * from table;

I got a stupid solution. It worked very well.

Solution

SELECT column1 || CHR(9) || column2 || CHR(9) || column3 ... ...
FROM table

principle behind

Actually, it's just a string concatenation.

CHR(9) -> '\t'

column1 || CHR(9) || column2 -> concat(column1, '\t', column2)