How can I translate date values from Oracle to Excel using Perl's DBI?

Question

Hi All, I am having trouble with a very simple Perl process. I am basically querying an Oracle database and I want to load it into Excel. I have been able to use DBIx::Dump and it works. However, I need to be able to use a variety of Excel formatting tools. And I think Spreadsheet::WriteExcel is the best module that outputs to Excel that allows me do more formatting.

Below is the code and the error I am getting. I basically query Oracle, fetch the data, load into an array and try to write to Excel. For some reason it is doing some kind of comparison and it does not like the data types. For example, the date is '25-OCT-08'. The SVP is 'S01'. It seems to be saying that they are not numeric.

Error:

Argument "01-NOV-08" isn't numeric in numeric ge <>=> at C:/Perl/site/lib/Spreadsheet/WriteExcel/Worksheet.pm line 3414.
Argument "01-NOV-08" isn't numeric in pack ge <>=> ge <>=> at C:/Perl/site/lib/Spreadsheet/WriteExcel/Worksheet.pm line 2157.

Code:

#!/usr/bin/perl -w 

#Set the Perl Modules
use strict; 
use DBI;
use Spreadsheet::WriteExcel;


# Connect to the oracle database
my $dbh = DBI->connect( 'dbi:Oracle:xxxx',
                        'xxxx',
                        'xxxx',
                      ) || die "Database connection not made: $DBI::errstr";


#Set up Query
my $stmt = "select 
                                   week_end_date, SVP, RD,
                                    DM, store, wtd_smrr_gain,QTD_SMRR_GAIN,
                                   wtd_bor_gain,QTD_BOR_GAIN,
                                   wtd_cust_gain,QTD_CUST_GAIN,
                                   wtd_CARD_CLOSED_OCT25,QTD_AVG_CARD_CL
                    from 
                          bonus_4Q_store
                    order by
                          store"; 

#Prepare Query
my $sth = $dbh->prepare($stmt); 

#Execute Query
$sth->execute() or die $dbh->errstr; 

my( $week_end_date,$SVP,$RD,$DM,$store,
    $wtd_smrr_gain,$QTD_SMRR_GAIN,
    $wtd_bor_gain,$QTD_BOR_GAIN,
    $wtd_cust_gain,$QTD_CUST_GAIN,
    $wtd_CARD_CLOSED_OCT25,$QTD_AVG_CARD_CL);

#binds each column to a scalar reference
$sth->bind_columns(undef,\$week_end_date,\$SVP,\$RD,\$DM,\$store,
                   \$wtd_smrr_gain,\$QTD_SMRR_GAIN,
                   \$wtd_bor_gain,\$QTD_BOR_GAIN,
                   \$wtd_cust_gain,\$QTD_CUST_GAIN,
                   \$wtd_CARD_CLOSED_OCT25,\$QTD_AVG_CARD_CL,);

#create a new instance
my $Excelfile = "/Test_Report.xls"; 
my $excel = Spreadsheet::WriteExcel->new("$Excelfile"); 
my $worksheet = $excel->addworksheet("WOW_SHEET");

#Create array shell
my @data;

#Call data and Write to Excel  
while ( @data = $sth->fetchrow_array()){ 
    my $week_end_date = $data[0]; 
    my $SVP = $data[1]; 
    my $RD = $data[2]; 
    my $DM = $data[3]; 
    my $store = $data[1]; 
    my $wtd_smrr_gain = $data[2]; 
    my $QTD_SMRR_GAIN = $data[3];
    my $wtd_bor_gain = $data[4];
    my $QTD_BOR_GAIN = $data[5];
    my $wtd_cust_gain = $data[6];
    my $QTD_CUST_GAIN = $data[7];
    my $wtd_CARD_CLOSED_OCT25 = $data[8];
    my $QTD_AVG_CARD_CL = $data[9];
    my $row = 0; 
    my $col = 0; 
    foreach my $stmt (@data) 
    { 
        $worksheet->write($row++, @data); 
        last; 
    } 
} 

print "DONE \n"; 
$sth->finish(); 
$dbh->disconnect();

Answer 1

I would guess that it is coming out as a string, and when you try to insert it into the date column, there is no implicit conversion for it.

Try selecting the date like this, and it will turn it into a char that you can use to do compares.

to_char(date, 'YYYY/MM/DD HH24:MI:SS')

then

to_date(date, 'YYYY/MM/DD HH24:MI:SS')

to convert it back to a date on insert. That is generally what you need to do in SQL.

As I recall, perl has a trace facility for DBI that might giver a better picture as to what is going on.

Answer 2

The problem is here:

foreach my $stmt (@data) 
{ 
    $worksheet->write($row++, @data); # !!
    last; 
}

The correct syntax for write() is:

write($row, $column, $token, $format)

You are missing the $column argument, which in this case is probably 0.

If $stmt is an array ref then you can write it in one go as follows:

$worksheet->write($row++, 0, $stmt);