#!/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