Re: Dumping results of SQL query into ASCII flat file

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/12/26
Message-ID: <34aa2024.23516675@inet16>#1/1

On Fri, 26 Dec 1997 21:39:58 +0200, "René Schutte" <Rene_at_webhost.co.za> wrote:

>Hi there,
>
>(Season's greetings:)
>
>I need to get place the results of a PL/SQL query:
>cursor c1 is
> select * from payments;
>d_c1 c1%rowtype;
>
>to be placed in an ASCII delimited "flat" file. Is there an easy way to
>do this. I am running Oracle 8.0.3, with Web Request Broker 3.
>
>Any help would really be appreciated.
>
>thanks&bye4now
>Rene

Its easiest to do this with dynamic sql. that way, we can write one routine that'll do any query. Just remember that pl/sql runs with the privs of the owner of the routine -- therefore, you will install this dynamic sql routine into any schema that wants to use it instead of just once in the database....

If you want to return it 'flat' via the WRB then the following will work:

create or replace procedure csv( p_query     in varchar2,
                                 p_separator in varchar2 default ',' )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';

begin

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    owa_util.mime_header( 'application/csv' );     loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            htp.prn( l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        htp.p;

    end loop;
    dbms_sql.close_cursor(l_theCursor);
end csv;
/

By using owa_util.mime_header, we can setup the web browser to accept files of type application/csv and perhaps associate them with a spreadsheet or something else like that to process it (or just as application/octet-stream if you want a file save as dialog to pop up).

If you don't want to do it over the web, the following works with UTL_FILE (see the application developers guide for the init.ora parameters you must set up for utl_file to work) and only applies to v7.3 and up of the database:

create or replace procedure csv( p_query     in varchar2,
                                 p_dir       in varchar2,
                                 p_fname     in varchar2,
                                 p_separator in varchar2 default ',' )
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_output        utl_file.file_type;

begin

    l_output := utl_file.fopen( p_dir, p_fname, 'w' );

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            utl_file.put( l_output, l_separator || l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );

    end loop;
    dbms_sql.close_cursor(l_theCursor);
    utl_file.fclose( l_output );
end csv;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri Dec 26 1997 - 00:00:00 CST