Re: Urgent: Help me on Spooling with SqlPlus and viewing with VI editor

"Ajith" <mabal_at_tdc.dk> wrote in message
news:3b855089.0108170519.6b2970da_at_posting.google.com...
> 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.

Question:
Did you set the width of your vi lines appropiately? I am afraid you are looking at linewrapping which was to be expected with a width of 2000.
You should verify that using od (octal dump) if your really believe there are erroneous LFs and blank lines.

Hth,

Sybrand Bakker, Senior Oracle DBA Received on Fri Aug 17 2001 - 13:28:09 CDT