Script: Run SQL Query and Record Output

use strict;
use DBI;

# SimpleQuery.pl
# David Mann
# http://ba6.us

# Runs and outputs a simple query from an Oracle DB 

die "\n$0\n\n" . 
    "This procedure shows how to do a simple query and output the results to a file.\n\n" . 
    "syntax: perl $0 [SCHEMA_NAME] [SCHEMA_PASSWORD] [TNSNAME] [OUTPUT_FILENAME]\n"
    if @ARGV < 3;


# Get command line arguments
my ( $schema_name, $schema_password, $tnsname, $output_filename) = @ARGV;

# Connect to DB
print "Connecting to DB...\n";
my $dbh = DBI->connect( "dbi:Oracle:".$tnsname,
                        $schema_name,
                        $schema_password,
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";

# Open output file
open(OUT,">$output_filename") || die "cannot create $output_filename $!";

print OUT "-- File generated for $schema_name \@ $tnsname on " . localtime() . "\n\n";

print OUT "SET SERVEROUTPUT ON;\n";


### SQL Query that drives this procedure ###

my $sql = qq{ 
select table_name, num_rows
        FROM user_tables
};


### Main ###

# Prepare and execute SELECT
print "Executing query...\n";
my $sth = $dbh->prepare($sql);
$sth->execute();

# Declare and Bind Columns
my($table_name,$num_rows);
$sth->bind_columns(undef, \$table_name, \$num_rows);

# Fetch rows from DB and process them
print "Processing.";
my $out_line;
while( $sth->fetch() ) {

    # Output status to screen
    print ".";

    # Output data to file
    print OUT $table_name . " " . $num_rows . "\n"; 
	    
}

# Output status to screen
print "\ndone\n";

$sth->finish();                           # Close cursor

$dbh->disconnect() or warn "DB disconnection failed: $DBI::errstr\n";

close(OUT);