Re: Utility for extracting table's data into flat file ?

On 18 Nov 1997 18:03:29 GMT, Shirley Donor <sdonor_at_cs.ucr.edu> wrote:

>
>
> Is there an Oracle Utility that can dump a table's data
> into a flat file ?
>
> Thank you very much.
>
>--

You can use sqlplus or pl/sql.

The following sqlplus command file will do the sort of thing required assuming that you require a text format file. If you require binary representation of numbers etc then it would get more tricky.

• define the sql*plus environment for flat file output whenever oserror exit sql.oscode whenever sqlerror exit sql.sqlcode set termout off set linesize 2000 set trimspool on set feedback off set verify off set echo off set newpage none set pagesize 0
• start writing to the output file spool d:\temp\fred.lis
• issue the query and close the output file select * from &1;

spool off
exit success

this file can be run with sqlplus / @<filename.sql> <tablename>

Alternatively, you can use PL/SQL file io functions which are significantly faster e.g.

create or replace procedure dumpf as
  f utl_file.file_type;
  cursor c is
    select rpad(to_char(a),11) || b x
    from bill
    order by a;
begin
  f := utl_file.fopen('d:\temp','test1.txt', 'w' );   for r in c loop

     utl_file.put_line( f, r.x );
  end loop;
  utl_file.fflush(f);
  utl_file.fclose(f);
end;
/

the above procedure is specific to a particular table. Received on Wed Nov 19 1997 - 00:00:00 CST