Re: Exporting tables to external ASCII files

Eugene Freydenzon wrote:
>
> Ed DeNaro wrote:
> >
> > Does anyone know of a way to export data from Oracle 7.1 to external
> > ASCII files? SQL*Loader is able to import but not export and
> > the Export utilitiy only creates Oracle dump files. I understand there
> > is a new PL/SQL proc in Oracle 7.3 to write to files on the operating
> > system but nothing similiar in 7.1. Just wanted to find out how others
> > have accomplished this task in versions prior to 7.3.
> >
> > Thanks,
> > Ed DeNaro
> > edenaro_at_fourthtier.com
> Something like this:
> sqlplus username/password_at_TNS
> set echo off
> set feedback off
> set arraysize 1
> set heading off
> set pagesize 0
> set linesize 2000
>
> spool c:\spool.dat
> select
> Field1||','||
> Field2||','||
> ....
> Fieldn
> from table;
> spool off
>
> {set everything back}
> exit
>
> 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.
>
> --
> ********************************************************
> * Everything above is only my opinion *
> ********************************************************
> * *
> * If you see a lion in a cage and sign says "elephant" *
> * , *
> * DO not belive your eyes ! *
> * (Kozma Prutkov. (informal translation)) *
> * *
> ********************************************************
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

• believe nothing you hear ***
• and just a half of what you see ***