• Status: Solved
  • Priority: High
  • Security: Private
  • Views: 94
  • Last Modified:

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,
0
steve2312
Asked:
steve2312
1 Solution
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now