[peisiong]

Any advice or workarounds, greatly appreciated.

Cheers,

Pei

[andrewst]

Originally posted by peisiong
Any advice or workarounds, greatly appreciated.

Cheers,

Pei

You could do this:

SQL> column tab new_value tab
SQL> select chr(9) tab from dual;

T
-


SQL> set colsep "&tab"

[peisiong]

[quote][size=1]Originally posted by andrewst
Thanks Andrew!!!

[peisiong]

Hi

I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

Any idea why?

Cheers,

Pei Siong

[andrewst]

Originally posted by peisiong
Hi

I have done what you have suggested and the spooled file looks ok when I open it in EXCEL.

But when I open it in a text editor, there seems to be spaces (lots of them) in place of tab.

Any idea why?

Cheers,

Pei Siong

There are spaces, as well as the delimiting TABs, because of the way SQL Plus formats the data WITHIN the columns, e.g.:

____DEPTNO|DNAME_________|LOC_________
----------|--------------|-------------
________10|ACCOUNTING____|NEW_YORK____
________20|RESEARCH______|DALLAS______
________30|SALES_________|CHICAGO_____
________40|OPERATIONS____|BOSTON______

(I have changed spaces to '_' and tabs to '|' so you can see better).

I'm not aware of any way to change this behaviour. A common way to get tab-delimited output without spaces is to select it that way:

SELECT deptno||CHR(9)||dname||CHR(9)||loc AS record
FROM dept;

RECORD
---------------------------------------------------------------------
10|ACCOUNTING|NEW YORK
20|RESEARCH|DALLAS
30|SALES|CHICAGO
40|OPERATIONS|BOSTON

Use SET TRIMSPOOL ON to remove the trailing spaces on the last column.

[peisiong]

Hi andrew,

Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

Cheers,

Pete

[andrewst]

Originally posted by peisiong
Hi andrew,

Thanks for your help. If I have 100+ columns in my select statement, will using concatenate affect the performance of the select?

Cheers,

Pete

Not as far as I know.