# This Perl script dumps a given Oracle database schema into a set of CSV
# files. The files will be named for the tables, have column headers, and
# will all appear in the current working directory.
# Original script by Jason McIntosh <jmac@jmac.org>
#
# You will need:
# * Perl 5
# * DBD::Oracle all set up, and knowledge of what your DBI DSN string is.
# * Text::CSV, available from CPAN
#
# Configuration:
# Set the following three variables to your Oracle schema's DBI DSN string,
# username, and password.
my $dsn  = 'dbi:Oracle:whatever';
my $user = 'whatever';
my $pass = 'whatever';
# You don't need to edit anything below this line. Just run the script!

use warnings;
use strict;

use DBI;
use Text::CSV;

my $dbi = DBI->connect( $dsn, $user, $pass );

my $sth = $dbi->prepare( 'SELECT table_name FROM user_tables' );
$sth->execute;
while ( my $row = $sth->fetchrow_arrayref ) {
    dump_table( $row->[0] );
}

sub dump_table {
    my ( $table_name ) = @_;

    print "Dumping $table_name...\n";

    my @column_names;
    my $column_name_sth = $dbi->prepare(
        "SELECT column_name FROM USER_TAB_COLUMNS "
        . "WHERE table_name = '$table_name'"
    );
    $column_name_sth->execute;
    while ( my $row = $column_name_sth->fetchrow_arrayref ) {
        push @column_names, $row->[0];
    }

    my $csv = Text::CSV->new ( { binary => 1 } );
    $csv->eol ("\n");

    open ( my $fh,
        ">:encoding(utf8)",
        "$table_name.csv"
    ) or die "$table_name.csv: $!";

    $csv->print ( $fh, \@column_names );

    my $data_sth = $dbi->prepare( "select * from $table_name" );
    $data_sth->execute;
    while ( my $row = $data_sth->fetchrow_arrayref ) {
        $csv->print( $fh, $row );
    }
}