Created
December 15, 2016 17:20
-
-
Save jmacdotorg/c0dea87e37ea68ca9e0129076bd4869f to your computer and use it in GitHub Desktop.
A Perl script to dump an Oracle database schema into CSV files.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 ); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment