Re: Tool for CSV dump?

From: Jeremy <jeremy0505_at_gmail.com>
Date: Tue, 28 Oct 2008 12:49:55 -0000
Message-ID: <MPG.23711acffcf2251d989696@News.Individual.NET>

In article <ge70m2$3jc$1_at_registered.motzarella.org>, gogala.mladen_at_gmail.com says...>
> Tellie wrote:
>
> > Hi experts,
> >
> > are there tools to make a fast dump of complete databases to CSV?
> > Without needing any additional licenses from Oracle...?
> >
> > TIA Tellie
>
>
> Dump complete database to CSV??? I am not sure about that, but I have a
> script that dumps a table or a result of a query to a CSV file. It is
> written in Perl and requires the corresponding Perl modules.

Here is a function for any query. It is based on a Tom Kyte example:

---

• DUMP_CSV

  ---

  function dump_csv( p_query in varchar2, p_separator in varchar2 default ',', p_filename in varchar2 ) return number --
• this code is courtesy of Tom Kyte of Oracle, modified by jeremy0505 -- is l_output utl_file.file_type; l_theCursor integer default dbms_sql.open_cursor; l_columnValue varchar2(4000); l_status integer; l_colCnt number default 0; l_separator varchar2(10) default ''; l_cnt number default 0;

    l_descTbl dbms_sql.desc_tab;
begin

    begin

      l_output := utl_file.fopen( 'UTFD_EXTRACT',
                                  p_filename, 'w',32000 );
    exception
      when others then
        dbms_output.put_line('Error opening file with utl_file');
        raise;

    end;

    --
    -- column headings first
    --

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

    dbms_sql.describe_columns( l_theCursor,

                               l_colCnt, l_descTbl );

    for i in 1..l_colCnt
    loop

      if i = 1 then
        utl_file.put( l_output, l_descTbl(i).col_name);
      else
        utl_file.put( l_output, p_separator ||  
                                l_descTbl(i).col_name );
      end if;

    end loop;
    utl_file.new_line( l_output );     

    --
    -- now to the meat 
    --
    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,

                            4000 );

    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 );
            l_columnValue := replace(l_columnValue,'&apos;','''');
            l_columnValue := replace(l_columnValue,'&amp;','&');
            l_columnValue := replace(l_columnValue,'"','''');
            l_columnValue := replace(l_columnValue,chr(13),null);
            l_columnValue := replace(l_columnValue,chr(10),' ');
            l_columnValue := '"'||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;

-- 
jeremy

Received on Tue Oct 28 2008 - 07:49:55 CDT