RE: Perl dbi/dbd-oracle/Decode function

[Steve Baldwin]

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:[EMAIL PROTECTED] 
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