Re: Any Tools to spool Oracle Data into a flat file

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 25 Nov 1999 08:30:58 -0500
Message-ID: <4beq3s0cl403i83tnokomr8o5cldfdmgvn@4ax.com>

A copy of this was sent to Anurag <anurag_at_synergy-infotech.com> (if that email address didn't require changing) On Wed, 24 Nov 1999 22:45:16 -0800, you wrote:

>Hi,
>Is there any other tool available free or otherwise apart from utl_file
>which can spool data from oracle tables into a flat file with the
>specified delimiter.
>
>Actually i want to submit a job at regular intervals and doing with
>utl_file is extremely pain staking especially when your table strutures
>are frequently changing.
>
>Bye
>Anurag
>
>also reply at
>anurag_at_synergy-infotech.com
>
>
>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *
>The fastest and easiest way to search and participate in Usenet - Free!

Here is a stored procedure that dumps the results of an arbitrary query (eg: select * from T) to a file using utl_file:

create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2 default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )

return number
is

    l_output        utl_file.file_type;
    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_cnt           number default 0;

begin

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

• In 8.0.5 and up, support for lines >1033 bytes
• l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 );

    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 );
        l_cnt := l_cnt+1;

    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/

You can schedule that as a job using dbms_job. Or if you prefer to use cron under unix to schedule, see the url in my signature for sqlplus scripts that do the same sort of thing (arbitrary table -- dump to flat file or sqlldr format)

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Nov 25 1999 - 07:30:58 CST