Perl SQL example

http://www.dba-oracle.com/t_dbi_perl_sql_execution.htm

# HERE is the script that accept SQL command and get return from Oracle DB

#!/usr/bin/perl

# include oracle driver folder
BEGIN {
$ENV{ORACLE_HOME} = "/home/cybertech/app/cybertech/product/12.1.0/client_1";
$ENV{LD_LIBRARY_PATH} = "/home/cybertech/app/cybertech/product/12.1.0/client_1";
}

use lib Bundle;
use Bundle::DBI;
use DBI;
use strict;

use POSIX qw(strftime);

if ($#ARGV > 1)
{
print "\nUsage: perl getSQLdata.pl \"[SQL statement]\"\n\n\Example: perl getSQLdata.pl \"select * from tablename\"";
exit -1;
}

my $now_string = strftime "%Y%b%e_%H%M%S", localtime;
my $script_log= "SQL_log_$now_string.log";
my @sqlReturn = ();
chomp($ARGV[0]);


my $temp = "";

# Perform Oracle connection
    my $dbh = DBI->connect('DBI:Oracle:host=HOSTNAME;sid=SIDNAME;port=1521', 'USERNAME', 'PASS')
                or die "\n\nCouldn't connect to database: " . DBI->errstr;
    
# Forming SQL query
my $SQL_CMD = join("",@ARGV);

my $sth = $dbh->prepare($SQL_CMD)
                or die "\n\nCouldn't prepare statement: " . $dbh->errstr;

# Run SQL command
$sth->execute() || try_again();

# open file handler
open (WF, ">$script_log") || die "\n$!\n";

# Loop each row of data
while(my @row = $sth->fetchrow_array())
{
@sqlReturn = ();
# display each column
foreach(@row)
{
$_ = "\t" if !defined($_);
#print "$_\t";
print WF "$_".",";
push (@sqlReturn, $_.",");
}
print @sqlReturn;
print "\n";
print WF "\n";
$temp = join("",@sqlReturn);
$temp = "";
}

END
{ $dbh->disconnect if defined($dbh); }
exit 1;