Unload Table Data to Flat Files

From: d cheng <dc4oracle_at_yahoo.com>
Date: Wed, 16 Nov 2005 04:31:29 -0800 (PST)
Message-ID: <20051116123129.44529.qmail@web34501.mail.mud.yahoo.com>

Hi Listers,    

  I need to unload several large database tables unto flat files on a Unix database server. I am using Tom Kyte's SQL*Plus unloader. However, there seems to be a problem when a varchar column contains a ^M (CR) within it. All other columns to the right of this are truncated and missing from the record in the flat file. Is there a way to workaround this truncation issue? I would like to NOT scrub the data by removing the carriage return as the end-users might expect them to be there.    

  Below are the SQL*Plus settings in effect when the extraction SQL statement was issued. If I 'set wrap on', I will see all the data for the varchar column but they are split on multiple lines - I need them to be on a single line.    

  set wrap off
set linesize 32767
set trimspool on

set     feedback off
set     pagesize 0
set     verify off

set termout off
  spool test.sql
select

c1||chr(199)||
c2||chr(199)||
n3||chr(199)||
c4||chr(199)||
d5||chr(199)||

n6
from my_table
spool off;    

  Thank you in advance for your help!    

• David

  ---

 Yahoo! FareChase - Search multiple travel sites in one click.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Nov 16 2005 - 06:33:39 CST