Re: Exporting tables to external ASCII files

christoph karpeter <karpeter_at_ix.netcom.com> wrote in article <32507C83.A89_at_ix.netcom.com>...
> Eugene Freydenzon wrote:
> >
> > where select statement can be created from cols view:
> > select column_name||'||'',''||'
> > from cols
> > where table_name='TABLE_NAME'
> > order by column_id;
> > for full table columns.
> >
> > As a result you'll have comma-separated list avalable for loading. Make
> > shure linesize is set up correctly.
> >
> > Hope it helps,
> >
> > Eugene.
> >
> The potential pitfall in exporting this way might be number columns
> having the ominous NULL value in the database. You will get nothing out
> when exporting them into an ASCII file, just two adjacent commas. Now
> you go and try to import this as a NULL value back into the database!
> The message goes 'invalid number' in the best case.
> So far I know SQL*Loader still does not have a mean of overcoming this (
> I tried it quite a time ago).
> Should anyone know a workaround, please post.
> Regards
> christoph

Simply do a light modifications:

select ' ''"'' || '||column_name||'||''",''||'   from cols
  where table_name='TABLE_NAME'
  order by column_id;

and it result:

spool c:\spool.dat
select
'"' || Field1||'",'||
'"' || Field2||'",'||
....
'"' || Fieldn||'"'
from table;
spool off

and use import FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

-- 
G'luck !
-----------------------------------------
Garry M. FIlimonov
LASU TRINITI, Troitsk, MR, Russia
garry_at_triniti.troitsk.ru
7-(095)-334-0408