Perl dbi/dbd-oracle/Decode function

--0-377600609-1137784641=:31929
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

Hi list
Without decode the following script works fine but with decode it is giving errors, is there a way to make decode work?

Script follows:

#!/usr/bin/perl
DBI->trace( 2, 'dbitrace.log' );
use warnings;
use FileHandle;
use DBI;
use strict;
use DBI qw(:sql_types);
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK => 60001;
my $dbh1 = DBI->connect("dbi:Oracle:test.com", "test", "test123" ) or die "Can't make 1st database connect: $DBI::errstr\n";

$dbh1->{RowCacheSize} = 100;

my $sth = $dbh1->prepare( "
select
app.UNDERWRITER_LOGIN \"Underwriter Login\",
u.last_name \"Last Name\",
u.first_name \"First Name\",
c.company_name \"Company Name\",
alog.row_mod_by \"Status Mod By\",
alog.row_mod_date \"Modifed Date\",
codes.STATUS_CODE \"Status Code\",
app.SUBMIT_id \"Submit ID\"
from
tg_online_app app ,
tg_online_app_audit_log alog ,
tg_online_app_codes codes ,
tg_company c ,
all_user u
where
app.SUBMIT_ID = alog.SUBMIT_ID and
alog.STATUS_ID = codes.STATUS_ID and
c.id = app.COMPANY_ID and
app.submit_id!='9600015' and
app.STATUS_ID = alog.STATUS_ID and
u.login (+) = app.UNDERWRITER_PORTAL_LOGIN
order by c.COMPANY_NAME , alog.AUDIT_ID
" );
$sth->execute();
print "Creating Workbook\n";

my $workbook = Spreadsheet::WriteExcel->new(newWorkBookName());
die "unable to create workbook - $!\n" unless $workbook;
$workbook->set_tempdir('c:/pc/lc');
my $worksheet = $workbook->addworksheet();

my $colNames = $sth->{NAME_uc};

print "Fetching data\n";

my $rowCount=0;
my $lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
print "\n";

while( my $ary = $sth->fetchrow_arrayref ) {

print "." unless $rowCount++%1000;

if ( ++$lineCount >= LINES_PER_BOOK ) {
$workbook->close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel->new($workBookName);
die "unable to create workbook - $!\n" unless $workbook;
$worksheet = $workbook->addworksheet();
$lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
$lineCount=1;
print "\nNew Workbook: $workBookName\n";
}
$worksheet->write_row($lineCount,0,$ary);
}

print "\n";

$workbook->close;
$sth->finish;
$dbh1->disconnect;

{

my $workBookNumber = 0;
sub newWorkBookName {
return "Inactive_Models_" . ++$workBookNumber . ".xls";
}
}

Thanks in advance



---------------------------------

What are the most popular cars? Find out at Yahoo! Autos
--0-377600609-1137784641=:31929--

RE: Perl dbi/dbd-oracle/Decode function

Hi Robert,

Firstly, it may be just me, but I don't see any DECODE other than in your
opening question. Secondly, the actual error text would be helpful in
diagnosing the problem.

I have used decode many times with DBI and never had a problem.

Steve

-----Original Message-----
From: Robert [mailto:rgoud@yahoo.com]
Sent: Saturday, 21 January 2006 6:17 AM
To: dbi-users@perl.org
Subject: Perl dbi/dbd-oracle/Decode function

Hi list
Without decode the following script works fine but with decode it is
giving errors, is there a way to make decode work?

Script follows:

#!/usr/bin/perl
DBI->trace( 2, 'dbitrace.log' );
use warnings;
use FileHandle;
use DBI;
use strict;
use DBI qw(:sql_types);
use Spreadsheet::WriteExcel;
#use Spreadsheet::WriteExcel::Big;
use Getopt::Long;
use constant LINES_PER_BOOK => 60001;
my $dbh1 = DBI->connect("dbi:Oracle:test.com", "test", "test123" ) or die
"Can't make 1st database connect: $DBI::errstr\n";

$dbh1->{RowCacheSize} = 100;

my $sth = $dbh1->prepare( "
select
app.UNDERWRITER_LOGIN \"Underwriter Login\",
u.last_name \"Last Name\",
u.first_name \"First Name\",
c.company_name \"Company Name\",
alog.row_mod_by \"Status Mod By\",
alog.row_mod_date \"Modifed Date\",
codes.STATUS_CODE \"Status Code\",
app.SUBMIT_id \"Submit ID\"
from
tg_online_app app ,
tg_online_app_audit_log alog ,
tg_online_app_codes codes ,
tg_company c ,
all_user u
where
app.SUBMIT_ID = alog.SUBMIT_ID and
alog.STATUS_ID = codes.STATUS_ID and
c.id = app.COMPANY_ID and
app.submit_id!='9600015' and
app.STATUS_ID = alog.STATUS_ID and
u.login (+) = app.UNDERWRITER_PORTAL_LOGIN
order by c.COMPANY_NAME , alog.AUDIT_ID
" );
$sth->execute();
print "Creating Workbook\n";

my $workbook = Spreadsheet::WriteExcel->new(newWorkBookName());
die "unable to create workbook - $!\n" unless $workbook;
$workbook->set_tempdir('c:/pc/lc');
my $worksheet = $workbook->addworksheet();

my $colNames = $sth->{NAME_uc};

print "Fetching data\n";

my $rowCount=0;
my $lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
print "\n";

while( my $ary = $sth->fetchrow_arrayref ) {

print "." unless $rowCount++%1000;

if ( ++$lineCount >= LINES_PER_BOOK ) {
$workbook->close;
my $workBookName = newWorkBookName();
$workbook =
Spreadsheet::WriteExcel->new($workBookName);
die "unable to create workbook - $!\n" unless $workbook;
$worksheet = $workbook->addworksheet();
$lineCount=0;
$worksheet->write_row($lineCount,0,$colNames);
$lineCount=1;
print "\nNew Workbook: $workBookName\n";
}
$worksheet->write_row($lineCount,0,$ary);
}

print "\n";

$workbook->close;
$sth->finish;
$dbh1->disconnect;

{

my $workBookNumber = 0;
sub newWorkBookName {
return "Inactive_Models_" . ++$workBookNumber . ".xls";
}
}

Thanks in advance



---------------------------------

What are the most popular cars? Find out at Yahoo! Autos