Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Urgent: Help me on Spooling with SqlPlus and viewing with VI editor

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

From: Gene Hubert <gwhubert_at_hotmail.com>
Date: 17 Aug 2001 11:28:27 -0700
Message-ID: <7e3fa619.0108171028.5a5a68e9@posting.google.com>


The only thing that comes to mind is the possibility of line breaks (\n characters) stored in your data.

I think the mystery might be further resolved if you could post your actual sql statement, table definition and a few lines from your output file.

Gene Hubert
Durham, NC


mabal_at_tdc.dk (Ajith) 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.
Received on Fri Aug 17 2001 - 13:28:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US