In the previous post I described how to install the Oracle Database 10g Express Edition on Ubuntu and to add some data. In this article I will show how to access that data from a Perl script.
I assume you already have Perl installed, together with the DBI framework for generic database support. We will need to add DBD::Oracle which is available from CPAN. To install this module, run this as root:
# perl -MCPAN -e shell cpan> install DBD::Oracle
Two things are worth noting. First, the root user must have the same Oracle environment set as described in the previous article. Just issue ‘source /usr/lib/oracle/xe/app/oracle/product/10.2.0/server/bin/oracle_env.sh’ to fix that. The second thing to note is that you will most likely get errors during testing of the newly built module which will prevent it from being installed. To override that you will need to add ‘force’ before the command, i.e.:
cpan> force install DBD::Oracle
Now, the groundwork is done and we just need to write the script. Save this in oracle_read.pl and make it executable.
#!/usr/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect( 'dbi:Oracle:xe',
'scott',
'tiger',
) || die "Database connection not made: $DBI::errstr";
my $sql = qq{ SELECT id,name,age FROM persons };
my $sth = $dbh->prepare($sql);
$sth->execute();
my($id, $name, $age);
$sth->bind_columns(\$id, \$name, \$age);
print "List of persons:\n";
while( $sth->fetch() ) {
print "$name [$age]\n";
}
$sth->finish();
$dbh->disconnect;
