Re: Data Dump

On Mon, 17 Nov 1997 17:34:24 -0600, Ed Gillispie <eg76440_at_deere.com> wrote:

>Does any one have a script that will dump the data from a selected
>Oracle table (preferably in the order of the primary key) to an ASCII
>text file for loading to a mainframe?
>
>Your help is greatly appreciated!

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 40 set feedback off set verify off set echo off set newpage none set pagesize 0
• format the output column a format a30 column d format 0999.00
• start writing to the output file spool d:\temp\fred.lis
• issue the query and close the output file select a,d from bill order by a;

spool off
exit success

This can be run with e.g.
sqlplus / @d:\fred.sq

Within a script, the return code should be checked for errors. The whenever ...error exit .. may need to be modified on systems (e.g. AIX) where the exit code is limited to the range 0 - 255.

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;
/

I thought this could be speeded up by using put to avoid flushing the output buffer which I think is what put_line does e.g.

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

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

This works a lot faster. It also highlights a bug in the file i/o functions, only the first 2k or so of output is written to the file, despite the fact that no error is reported. Received on Wed Nov 19 1997 - 00:00:00 CST