Emergency 'SPOOL' strange problem

Hi all:

Oarcle 8i on Solaris 2.6

I want to 'spool' the data of a table (tab_A) to a flat file. 'tab_A' has a column col_A is 'varchar2(256)'. My 'spool' script like following, I use 'ctrl_F' as delimiter. The max row length is less than 2000.

set termout off
set pagesize 0
set feedback off
set verify off
set linesize 2000
set heading off
set trimspool on

spool data.dat
select col_1||'ctrl_F'||col_2||'ctrl_F'||col_A||'ctrl_F'||....||col_end from tab_A;
spool off

Then I got the 'data.dat' flat file. But when I use 'vi' (or other editors) to open it, the format is NOT right. Every record in tab_A is supposed to be in one single line. Actually the flat file is like:

this is the first line: <value of col_1>ctrl_F...ctrl_F<value of col_A>ctrl_F
this is the 2nd line: <value of column after col_A>ctrl_F...ctrl_F<col_end>
<blank line>
<next record>

You see, the record is broken into two lines in the flat file! And there's a blank line between records in flat file. This is NOT good when I want to use 'sqlldr' to load these data back to 'tab_A' table, simply doesn't work.

The col_A has value like ' aaaa bbbbb cccccc 123.123.123.123/12345 ddddddd eeee 234.234.234.234/23456 '. When col_A has more characters(has lots of white spaces also) like this, the format of output flat file is wrong and there's a blank line. But when 'col_A' has less chars like 'aaaaa bbbbb', the format is right and no blank line at all!

What's going on? I think I may miss some SQL*PLUS formats or options? Any help or suggestion? Pls help, emergency!!!!

Thanks a lot!
Pls also email me a copy.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 27 2000 - 20:45:51 CST