Perl program to connect Oracle Database and generate Excel

Perl uses DBI and Excel::Writer::XLSX module to connect Oracle Database and Generate Excel (.xlsx). In this article, let’s see how to use it with an example.

Oracle apps use built-in XML Publisher to generate Excel output file. So you create data definition and template and register it as a concurrent program.

Mainly below Template Type is in use for Excel output.

Excel Template – This excel (.xls) based template which you can use. This generates pure excel, but it supports only 65K records per sheet. You need to design multi-sheet excel to accommodate the data.

RTF Template – You can also use the RTF template to generate Excel output. This supports more than 65k records, but it is not pure excel. It actually generates an HTML file and set meta content to excel. So when you open the file, it gets opened in excel.

When you open the file in Notepad++ or any other text editor, you can clearly see HTML tags in the file. Another problem here is file size, as it is HTML file, the file size becomes huge if you have many records.

So what if you want to generate proper excel output with less size and more than 65k rows?

you can use Perl script to achieve this. Oracle Apps supports Perl based concurrent program. or you can create UNIX host based concurrent program and call the Perl script to generate excel.

Perl Script to generate excel output

perl uses Excel::Writer::XLSX module. it is vey easy to use it.

#!/usr/bin/perl
#use strict;
#use warnings;
 
use DBI;
use Excel::Writer::XLSX;

my $workbook  = Excel::Writer::XLSX->new( 'extract.xlsx' );
$workbook-> set_optimization ();
my $worksheet = $workbook->add_worksheet('abc');

# connect to MySQL...
$driver= "Oracle";

$dbh = DBI->connect('DBI:Oracle:instan_name', "username", "password");

# prepare and execute the SQL statement
$sth = $dbh->prepare("select * From apps.dba_objects");
$sth->execute;

 
my $i=1; 

$worksheet->write_row( 0,0, $sth->{NAME});


while ( my $row = $sth->fetchrow_arrayref() ) {
     
    foreach ($row) {
      
       # print $row;
		$worksheet->write_row( $i,0, $row );
		 $i++;
    }
  
}

 
$workbook->close;

$sth->finish;

$dbh->disconnect;

exit;

This script generates pure .xlsx file which supports more that 65k records with less size.

Share This: