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.


1.  DBI->connect  => This is to connect to the Oracle database, where 'xe' is the name of the database, 'scott' is the username, and 'tiger' is the password.  This function returns a database handler object.

2. $dbh->prepare => This command prepares a query provided and creates a statement handler object.

3. $sth->execute() => This will submit the query to Oracle and gets it executed. Keep in mind, this just executes the query, does not return the result back though.

4. $sth->fetchrow_array() => This is the one which fetches the result of the last query executed.  A while loop is used to parse this result just in case the output returns multiple rows, still it can be handled.
   In this case, since the query returns 2 columns, we are collecting it in 2 variables and then printing the values.

5. $sth->finish => This tells Oracle that we are done with fetching the results for the query. If we are reading all the results of the query, then this should not be given. It is needed only in cases when we are reading partial results.

6. $dbh->disconnect => This disconnects the connection with the Oracle. Any subsequent query if needs to be executed, we should connect again. Usually, this is done at the end of the program.

Example 2:
   In this, the query is parameterized where the parameter is passed as part of the execute command:
#!/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 = ?") or
          die "Couldn't prepare statement: " . $dbh->errstr;
$sth->execute("10000");

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();

Example 3:
    In this, instead of collecting the result in 2 scalar variables, it is being assigned to an array, and then we are accessing using the array index positions.
my @data=();
while (@data = $sth->fetchrow_array()){
    printf "First Name : %-10s Last Name : %-20s\n" , $data[0], $data[1];
}

No comments:

Post a Comment