How to get a csv output file from Perl script querying an Oracle table

I am using Perl version 5.8.8 on Windows.

I have below incomplete perl script to read from a oracle 11g database table.

# This script connects to an oracle database, reads columns from region based on a selection criteria and output records in a csv 
# 
use strict;
use DBI;
use warnings;
use File::Copy;
use Text::CSV;

my $dbh = DBI->connect("dbi:Oracle:$db",$user,$pw)
              or die "Database connection not made: $DBI::errstr";
{
my $sth = $dbh->prepare("SELECT * FROM region");
$sth->execute;
my $names = $sth->{'NAME'};
my $tbl_data = $sth->fetchall_arrayref;
print join "\n", map { join(',', map { "\"$_\""' } @$_ } @$tbl_data;

$dbh->disconnect(); }

Open in new window


I am new to Perl and not sure how to achieve the following:

a. restrict the select statement with a where clause and use the below where clause condition as an input to the script.
b. update statement (as below) once the file is written to a csv.

 The select statement would be as below
select region_date, cntr_name  from region  
where cntr_name in ('US' , 'CAN')  
and flag = 'Y' and part_key =  
(select ('S_' || v.tsched || '_' || v.cgroup) as partition_key   
from rgvars v	 
WHERE v.regdate = -- "input parameter"														and v.cgroup = -- "input parameter" 
and v.dnumber = -- "input parameter ");
--
update region
set code = 'N' -- once the file is written out in a csv format

Open in new window


The desired output will be in a dump of the region table in a csv format with headers.


Thanks,
steve2312Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David FavorLinux/LXD/WordPress/Hosting SavantCommented:
If you're doing a massive amount of i/o then you'd use bind + prepare (refer to DBI docs).

For simple things, just use $dbh->do("SQL"); with no prepare or bind.

DBI + DBD (of your choice) tends to be a bit obscure, to me.

I normally start with $dbh->do() + once that's working, then I switch over to prepare with bind variables.

Best to look for examples using your DBD, which will be DBD::oracle.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
steve2312Author Commented:
Thank you David for your suggestions.  I wrote up the code snippet below, but have yet to test this.  

I will be using parameters to read as below.     Does the code demand any additional tweaks.   Desired output again is a csv file format.

use strict;
use DBI;
use warnings;
use File::Copy;
use Text::CSV;

print ("Initializing ".PROG." ".VERSION."\n");
Date_Init('TZ=US/Central','Internal=1');

# Enable Autoflush
#
$| = 1;


my %opts;
getopts('d:l:h', \%opts);

my $date    = UnixDate(($opts{'d'} || 'today'), '%q');
my $dumpdir = $opts{'l'} || '.';
my $nodata = 0;
my ($db, $user, $pass, @tables, $batchdate, $calcgroup, $runnumber)  = @ARGV;

my $dbh = DBh->connect("dbh:Oracle:$db", $user, $pass)
  or die("Cannot connect to '$db' " . DBh->errstr());

unless($db && $user && $pass && @tables, @batchdate, @calcgroup, @runnumber) {
	print("args = $db, $user, $pass, @tables, @batchdate, @calcgroup, @runnumber\n\n");
    die("\n\n  - ERROR:  The Required number of arguments were not received.  Verify that the AppWorx prompts are correct and reset the module.\n\n");
} 

my $flcnt   = 0;

foreach (@tables) {
    
    my $cols = $dbh->selectall_arrayref(qq( 
        SELECT columnname 
          FROM columnmetadata c, 
               tablemetadata t 
         WHERE t.uidtable = c.uidtable 
           AND t.tablename = ? 
      ORDER BY columnnumber), undef, uc($_)) or die("  - Could not get column names for table '$_' ".$dbh->errstr());
  
    my $sth;
    
    if (/REGION/) {

    $sth = $dbh->prepare(qq(
            SELECT TRADE_DATE,
            	     DUNSNUMBER,
            	     GENSITENAME,
            	     SUBGENCODE,	
            	     PGCNAME,
            	     RENEWOFFSET,
            	     TOTALS
              FROM region
              WHERE STATUS = 'N' and PARTITION_KEY = 
             										(select ('S_' || v.uidstatementsched || '_' || v.calcgroup) as partition_key
																from sched_info v
																WHERE v.batchdate = ?
																and v.calcgroup = ?
																and v.runnumber = ?))) or die("  - Could not get data for table '$_' ".DBh->errstr);
                 
    $cols = ();
    } 
    else {  
        $sth = $dbh->prepare(" SELECT * FROM $_ " ) or die("  - Could not get data for table '$_' ".DBh->errstr);
    }
  
    $sth->execute();
    #
    #
    my @firstrow;
    my @columns;
  
    if (exists($$cols[0])) {
        foreach my $col (@$cols) {
            my ($name) = $$col[0];
            $name =~ s/\s+//g;
            push @columns, $name;
        }
    } else 
    {
        my $rc  = $sth->fetchrow_hashref() or $nodata = 1;
        if ($nodata) {
        	print("  - No data found in table '$_' ".$sth->errstr());
        	exit(0);
        }
        my @row = @{$sth->{NAME}};
        @$cols  = @row;
    
        foreach my $col (@$cols) {
            my $name = $col;
            $name =~ s/\s+//g;
            my $data = $rc->{$name};
            push @firstrow, $data;
            push @columns, $name;
        }
    }
  
    print "  - Creating export file for table '$_'\n";
    my $filename = "$date$_.csv";
    open (DUMP, ">$dumpdir/$filename") or die(" - Could not open $dumpdir/$filename for export. $!");

    if (exists($opts{'h'})) {
        print DUMP "TABLE=$_\n";
        foreach my $col (@columns) { print DUMP "$col,"; };
        print DUMP "\n";
    }


    close(DUMP);
    $flcnt++;
}
  
    
    $dbh->disconnect(); 
    print "  - $flcnt File has been successfuly exported.\n";

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Perl

From novice to tech pro — start learning today.