Re: Extract ASCII file from table

From: Andreas Breuer <breuer.andreas_at_gmx.de>
Date: Fri, 11 Jun 1999 16:06:51 +0200
Message-ID: <376117FB.BA98AE36@gmx.de>

Interlog User schrieb:

> Hi,
>
> Is there a method or utility in Oracle 7.3 or 8.0.5 to extract
> table data in ASCII format to a file like the "bcp" utility in Sybase
> ???

As I do not know what "bcp" does I assume you just want to export all rows from the table to an ASCII-File.

> I tried with sqlplus, but it comes with all kinds of header and other
> information.

I see a Quick and Dirty solution like this:

Assume a Table My_Table (col1 NUMBER, col2 VARCHAR2, col3 DATE);

create or replace
procedure export_my_table
is

    CURSOR mycursor is
      select col1, col2, col3 from My_Table;

    this_col1 NUMBER;
    this_col2 VARCHAR2;
    this_col3 DATE;

    fileHandler UTL_FILE.FILE_TYPE;
begin
  open mycursor;
  fileHandler := UTL_FILE.FOPEN('c:\tmp', 'export.txt', 'w');   LOOP
    Fetch mycursor into col1, col2, col3;     EXIT when mycursor%NOTFOUND;
    UTL_FILE.PUTF(fileHandler, TO_CHAR(col1) || ';' || col2 || ';' || TO_CHAR(col3) || '\n');
  End LOOP;
  close mycursor;
  UTL_FILE.FCLOSE(fileHandler);
end;
/

When running the procedure it shoul create a ';'-delimited ASCI-File containing the Table-Data.
Sorry for the very short example - but I like to go home now.

Andreas Breuer
Brockhaus Software & Consulting AG Received on Fri Jun 11 1999 - 09:06:51 CDT