#!/usr/bin/perl
#
# oraqry2csv.pl - dumps Oracle query to CSV
#
# Developed by Ondrej Jombik <nepto@platon.sk>
# Copyright (c) 2005 Platon SDG, http://platon.sk/
# Licensed under terms of GNU General Public License.
# All rights reserved.
#
# Changelog:
# 2005-04-30 - created
#
# $Platon: scripts/perl/oracle/oraqry2csv.pl,v 1.1 2005-05-01 20:51:49 nepto Exp $
use strict;
use DBI;
use Getopt::Long 2.16;
use POSIX;
use Term::ReadKey 2.14;
#***********************************************************************
# Local subroutines
#***********************************************************************
sub escape_data($$); # escapes data for an SQL statement
#***********************************************************************
# Package 'main' variables set by the mod_perl CGI environment
#***********************************************************************
$ENV{ORACLE_HOME} = '/usr/oracle/app/oracle/product/8.0.5'
if ( !exists $ENV{ORACLE_HOME} );
my $ORA_HOSTNAME;
my $ORA_VERSION;
my ( $MAJOR_VERSION, $MINOR_VERSION ) = q$Revision: 1.2 $ =~ /(\d+)\.(\d+)/;
my $VERSION = sprintf( "%d.%02d", $MAJOR_VERSION - 1, $MINOR_VERSION );
my $dbh; # database handle
my $sth; # statement handle
my $query; # current query
#***********************************************************************
# Parse command-line switches
#***********************************************************************
my $opt_database; # database
my @opt_queries; # tables
my $opt_debug; # --debug
my $opt_help; # -?|--help
my $opt_force; # -f|--force
my $opt_host; # -h|--host
my $opt_owner; # -o|--owner
my %opt_set_variable; # -O|--set-variable
my $opt_password; # -p|--password
my $opt_port; # -P|--port
my $opt_quick; # -q|--quick
my $opt_socket; # -S|--socket
my $opt_tab; # -T|--tab
my $opt_user; # -u|--user
my $opt_verbose; # -v|--verbose
my $opt_version; # -V|--version
my $help = <<MARK; # help statement
oraqry2csv v$VERSION
Dumping definition and data for an Oracle database or table
Usage: oraqry2csv [options] database [tables]
* -- Only switches with an asterisk are currently supported.
--debug=... Output debug log. Often this is 'd:t:o,filename`
* -?, --help Displays this help and exits.
-f, --force Continue even if we get an sql-error.
-h, --host=... Connect to host.
* -o, --owner=# Owner of tables if not user.
-O, --set-variable var=option
give a variable an value. --help lists variables
* -p, --password[=...] Password to use when connecting to server.
If password is not given it's asked from the tty.
-P, --port=... Port number to use for connection.
-q, --quick Don't buffer query, dump directly to stdout.
-S, --socket=... Socket file to use for connection.
* -u, --user=# User for login if not current user.
-v, --verbose Print info about the various stages.
* -V, --version Output version information and exit.
Possible variables for option --set-variable (-O) are:
<none>
MARK
## We want exact matches to the switches
Getopt::Long::config( 'no_auto_abbrev', 'no_ignore_case' );
my $rc = GetOptions(
"debug=s" => \$opt_debug,
"help|?" => \$opt_help,
"f|force" => \$opt_force,
"h|host=s" => \$opt_host,
"o|owner=s" => \$opt_owner,
"O|set-variable=s%" => \%opt_set_variable,
"p|password:s" => \$opt_password,
"P|port=i" => \$opt_port,
"q|quick" => \$opt_quick,
"S|socket=i" => \$opt_socket,
"u|user=s" => \$opt_user,
"v|verbose" => \$opt_verbose,
"V|version" => \$opt_version,
);
$opt_database = shift @ARGV; # grab database name
@opt_queries = splice( @ARGV, 0 ); # grab optional list of tables
# checking for user/pass@database
if ($opt_database =~ /^(.+)@(.+)$/) {
$opt_database = $2;
$opt_user = $1;
if ($opt_user =~ /^(.+)\/(.*)$/) {
$opt_password = $2;
$opt_user = $1;
}
}
#***********************************************************************
# Process command-line switches
#***********************************************************************
if ( defined $opt_version ) {
print STDERR "oraqry2csv v$VERSION\n";
exit 0;
}
if ( !$rc || defined $opt_help || !defined $opt_database ) {
print STDERR $help;
exit 1;
}
# Queries check
if ( scalar @opt_queries < 1 ) {
print STDERR "ERROR: no queries defined\n";
exit 2;
}
# Define $opt_user if it wasn't set
if ( !defined $opt_user ) {
($opt_user) = getpwuid($<);
}
# Alter $opt_owner
if ( defined $opt_owner ) {
$opt_owner = uc $opt_owner;
}
# Read password into $opt_password if it wasn't set
if ( !defined $opt_password || $opt_password eq "" ) {
print STDERR "Username: $opt_user\n";
ReadMode 'noecho';
print STDERR "Password: ";
chop( $opt_password = <STDIN> );
print STDERR "\n";
ReadMode 'restore';
}
#***********************************************************************
# Connect to the database with $dbh
#***********************************************************************
$dbh = DBI->connect( "dbi:Oracle:$opt_database",
$opt_user,
$opt_password )
|| die "Cannot connect to database: $DBI::errstr";
#***********************************************************************
# Turn autocommit *off* and set transaction to *read only*
#***********************************************************************
$dbh->{AutoCommit} = 0;
$dbh->do("set transaction read only")
|| die "Cannot set transaction to read only: $DBI::errstr";
#
# Preparing NLS_DATE_FORMAT
#
$sth = $dbh->prepare('ALTER SESSION SET NLS_DATE_FORMAT = \'YYYY-MM-DD HH24:MI:SS\'')
or die "DBI::prepare(): $DBI::errstr";
#
# Altering session
#
$sth->execute() or die "DBI::execute(): " . $sth->errstr;
$sth->finish();
#***********************************************************************
# Iterative over all queries, printing out requested information
#***********************************************************************
foreach $query (@opt_queries)
{
my @row;
$sth = $dbh->prepare($query);
$sth->execute() || die $DBI::errstr;
while ( @row = $sth->fetchrow_array() ) {
print join( ',', map( escape_data( 'CHAR', $row[$_] ), 0 .. $#row ) );
print "\n";
}
$sth->finish();
}
#***********************************************************************
# Buh-bye!
#***********************************************************************
$dbh->disconnect();
exit 0;
#***********************************************************************
# Define local subroutines
#***********************************************************************
sub escape_data($$)
{
my $type = shift;
my $value = shift;
if ( $type eq 'NUMBER' ) {
return length $value > 0 ? $value : "''";
} elsif ( $type eq 'VARCHAR2' or $type eq 'CHAR' ) {
if ( !defined $value ) {
return "NULL";
} else {
$value =~ s/'/\\'/g;
return "'$value'";
}
} elsif ( $type eq 'DATE' ) {
if ( !defined $value ) {
return "NULL";
} else {
return "'$value'";
}
} else {
die "Unknown type: $type\n";
}
}
__END__
=pod
=head1 NAME
oraqry2csv.pl - dumps Oracle query to CSV
=head1 SYNOPSIS
oraqry2csv [options] <database> <query> [<query> ...]
=head1 DESCRIPTION
Dumps query data from Oracle in the CSV format.
=over 4
=item <database>
This is the Oracle SID for the database, not the hostname.
=item <query> [<query> ...]
This is a list of one or more queries.
=back
=head1 TO DO
According to the user feedback.
=head1 SEE ALSO
L<oracledump>, L<perl>
=head1 COPYRIGHT
Copyright (C) 2005 Platon SDG, http://platon.sk/
This library is free software; you can redistribute it and/or
modify it under the same terms as Perl itself.
=head1 AUTHOR
Ondrej Jombik <nepto@platon.sk>
=cut
# vim: ts=4
# vim600: fdm=marker fdl=0 fdc=3
Platon Group <platon@platon.sk> http://platon.sk/
|