Tuesday, July 25, 2017

Perl - Connect to Oracle database and SELECT

How to connect to a database from a Perl program? Let us see in this article how to connect to Oracle and read from a table.  As a pre-requisite, we need to have the DBI and DBD::Oracle packages installed.
   In this article, we are going to see how to read name of a student from the students table.

Example 1:
#!/usr/bin/perl 
use warnings ;
use strict ;
use DBI;

$\="\n";

print "Connecting to DB..";

my $dbh = DBI->connect('dbi:Oracle:xe',  'scott', 'tiger') or
          die "Cannot connect to DB => " . DBI->errstr;
my $sth = $dbh->prepare("select first_name, last_name from students where id = 10000") or
          die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute();

while (my ($f_name, $l_name) = $sth->fetchrow_array()){
    printf "First Name : %-10s Last Name : %-20s\n" , $f_name,  $l_name;
}
#$sth->finish();
$dbh->disconnect();


The above program when run will print the First Name and last name of the student whose id is 10000.