Category Archives: Perl

Use the Oracle 11g version of Perl to connect to databases in AIX

Posted on by
Reply

I just got my connection from AIX into Oracle through Perl working, using DBD::Oracle. This is good news because it opens up the potential to do processing in our robust shared AIX environment on an LPAR licensed as an Oracle client, not a server. Instead of embedding stuff into Oracle’s extended SQL language just to get it to run on the server, we could now simply write in Perl. This could end up saving us quite a bit of money on Oracle licensing.

The configuration may seem trivial, but I ran into so many gotchas on this, that I just hacked into some Oracle scripts and essentially picked up their environment to run. It was compiled with an @INC path that didn’t exist, so I just created it and moved in all of their compiled Perl. It’s Perl 5.8.3 compiled in single thread mode which is different from the Perl that is compiled with Base AIX.

First, find your perl library wherever Oracle ended up putting it and move it to the location hardcoded in @INC:

(find where the Oracle lib directory is)
tar -cvf /tmp/oraperl.tar lib
mkdir -p /ade/aime_perl_build_latest/perl58/bin/AIX64
cd /ade/aime_perl_build_latest/perl58/bin/AIX64
tar -xvf /tmp/oraperl.tar

Next, find and copy the actual perl executable into /ade/aime_perl_build_latest/perl58/bin/AIX64.

I pick this path, simply because it is what @INC is hardcoded to for me:

# echo "print @INC" | ./perl
/ade/aime_perl_build_latest/perl58/bin/AIX64/Opt/lib/5.8.3/aix-thread-multi-64all/ade/aime_perl_build_latest/perl58/bin/AIX64/Opt/lib/5.8.3/ade/aime_perl_build_latest/perl58/bin/AIX64/Opt/lib/site_perl/5.8.3/aix-thread-multi-64all/ade/aime_perl_build_latest/perl58/bin/AIX64/Opt/lib/site_perl/5.8.3/ade/aime_perl_build_latest/perl58/bin/AIX64/Opt/lib/site_perl.#

Next, I just made a simple test script:

#!/ade/aime_perl_build_latest/perl58/bin/AIX64/perl

use DBI qw(:sql_types);

my $dbh;
my $address = "
                (DESCRIPTION=
                        (ADDRESS=
                                (PROTOCOL=TCP)
                                (HOST= yourhostnamehere)
                                (PORT=1521))
                        (CONNECT_DATA=(SERVICE_NAME= service.yourinc.com))
                )";
my $mode = 0;

$dbh= DBI->connect('dbi:Oracle:', "username@".$address , "password",
                {ora_session_mode => $mode})
                or die "Could not connect\n";

print "Connect failed \n" unless $dbh;
$dbh->{RowCacheSize} = 100;

my $sth = $dbh->prepare("select * from tab",{ora_check_sql => 0 });
$sth->execute or die "\nError: Sql failed: \n";

my $returned="";
while (my @row = $sth->fetchrow_array)
    {
                $returned = $row[0];
                print (" $row[0]\n");
    }

$sth->finish;
$dbh->disconnect or die ("disconnect failed", $DBI::err);

That’s all there is to it, this connects and works just fine. You can probably do something similar at 10g, but don’t try with 9i, it looks like a pretty old version of Perl.

Having now conquered Perl, php is next on my wishlist:

PHP Development Center at Oracle
pecl oci8

Spreadsheet::WriteExcel

Posted on by
Reply

This is a fabulous implementation of perl:
cpan WriteExcel page

Below is one of Mr. McNamara’s scripts with a few changes, I include is just as an example:

#!/usr/bin/perl -w

##############################################################################
#
# A simple example of converting some Unicode text to an Excel file using
# Spreadsheet::WriteExcel and perl 5.8.
#
# This example generates some Thai from a file with ISO-8859-11 encoded text.
#
#
# reverse('©'), September 2004, John McNamara, jmcnamara@cpan.org
#



# Perl 5.8 or later is required for proper utf8 handling. For older perl
# versions you should use UTF16 and the write_unicode() method.
# See the write_unicode section of the Spreadsheet::WriteExcel docs.
#
require 5.008;

use strict;
use Spreadsheet::WriteExcel;


my $workbook  = Spreadsheet::WriteExcel->new("/buxs/htdocs/sample.xls");
my $worksheet = $workbook->add_worksheet('info');
#   $worksheet->set_column('A:A', 20);


my $file = 'excel_test.txt';

open FH, '<:encoding(iso-8859-11)', $file  or die "Couldn't open $file: $!\n";

my $row = 0;

while () {
    next if /^#/; # Ignore the comments in the sample file.
    chomp;
    my $line = $_;
    my @fields = split /\t/, $line;
    my $col = 0;
      foreach(@fields)
        {
        $worksheet->write($row, $col++, $_);
        }
   $row++;
}

close FH;

open FHT, '<:encoding(iso-8859-11)', 'excel_test2.txt'  or die "Couldn't open $file: $!\n";

$worksheet = $workbook->add_worksheet('info2');

$row = 0;

while () {
    next if /^#/; # Ignore the comments in the sample file.
    chomp;
    my $line = $_;
    my @fields = split /\t/, $line;
    my $col = 0;
      foreach(@fields)
        {
        $worksheet->write($row, $col++, $_);
        }
   $row++;
}

close FHT;

daysago.pl

Posted on by
Reply

#!/usr/local/bin/perl

# yesterday = time-86400

# Given a number, it will count back that many days and print the date

$offset = 86400 * @ARGV[0];

@months = qw(Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec);
@weekDays = qw(Sun Mon Tue Wed Thu Fri Sat Sun);
($second, $minute, $hour, $dayOfMonth, $month, $yearOffset, $dayOfWeek, $dayOfYear, $daylightSavings) = localtime(time – $offset);
$year = 1900 + $yearOffset;
$theTime = “$hour:$minute:$second, $weekDays[$dayOfWeek] $months[$month] $dayOfMonth, $year”;
$dayOfMonth=sprintf(“%02d”,$dayOfMonth);
$monthno=sprintf(“%02d”,$month+1);
printf “$monthno $dayOfMonth $year\n”;