perl script for extracting csv files on the fly

This may be of some use; I use it all the time (RH E 4; Oracle 10.2.0.1). Save the perl code below and make sure you can execute it. Change the host, sid, port, user and password variables as needed.

If you don't have the Text::CSV and Text::Table modules installed with your perl libraries download it from cpan

To run from the command line :

$./script_name -csv 'select sysdate from dual' > dual.csv

Or if you want clean formatted output:

$./script_name 'select * from some_table'

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<snip>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>

#!/usr/bin/perl

# query_results - put query results into a text table or a CSV format

use strict;

use warnings;

use NMS::DBI;

use Text::CSV;

use Text::Table;

my $csv=0;

if ( @ARGV and $ARGV[0] eq '-csv' ) {

    $csv = 1;

    shift;

}

die "USAGE: query_results [-csv] <sql query> >out.csv\n" unless @ARGV;

    my $dsn = "dbi:Oracle:host=localhost;sid=mysid;port=1521";

    my $user = 'user';

    my $password = 'password';

    my $option_list = { PrintError => 0, RaiseError => 0, AutoCommit
=> 1 };

my $dbh = DBI->connect( $dsn, $user, $password, $option_list )

    or die "No database handle - $DBI::errstr\n";

my $q = join ' ', @ARGV;

my $results = $dbh->selectall_arrayref($q);

if ( $csv ) {

    my $csv = Text::CSV->new();

    foreach my $row ( @$results ) {

        my $status = $csv->combine(@$row);

        my $line = $csv->string();

        print "$line\n";

    }

}

else {

    my $tb = Text::Table->new();

    $tb->load( @$results );

    print $tb;

}

<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<snip>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>