Perl and Oracle

This document was written by CS 290W TA David Corcoran and was last modified

This section describes how we will be using Perl to communicate and query an Oracle database using Oraperl. We will be using a READ ONLY account to access Oracle. This means we will be able to do queries only.

Using the Oraperl Module

Oraperl is the DBD::Oracle module for use in your Perl scripts that contains all the necessary functions for you to call to implement queries to the Oracle Server. To use this module in your Perl scripts, simply do the following: 
#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib. \n";
&ReadParse;

use Oraperl;
This will make all the DBD::Oracle packages available to you. You will put this line at the top of your programs along with your do "cgi-lib.pl".

Setting the ORACLE_HOME Environment Variable

It is necessary for Oraperl to know the location of the Oracle client software so that it can connect to the remote Oracle Server. To do this you must set the environmental variable ORACLE_HOME. You do this via the following: 
#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib \n";
&ReadParse;

use Oraperl;
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/8.1.5";
It is necessary that you include that at the beginning of your Perl code. Now you are ready to begin communication with the Oracle Server. Oraperl uses some basic functions to communicate with the Oracle Server. Below are a few of these: 
&ora_login
&ora_open
&ora_fetch
&ora_close
&ora_logoff

&ora_login("", $sUsername, $sPassword);

This function takes as parameters a NULL string first parameter, a string which is your username and a string which is your password. ora_login returns what is known as a handle to this connection. A handle is basically an identifier used when opening connections. Remember file handling?
open(ATTENDANCE, ">>page.txt").
ATTENDANCE was the handle to that file.

You will need this handle to perform operations to the connection you just made. Consider the following: 

#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib \n";
&ReadParse;

use Oraperl;
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/8.1.5";

$sUsername = "cs290w\@csdb";
$sPassword = "cs290w";

# We connect, login and get a handle to the connection
# in scalar $hConnection.

$hConnection = &ora_login("", $sUsername, $sPassword);

&ora_open($hConnection, "SELECT fields FROM table WHERE criteria");

This function takes as parameters the handle returned from the function ora_login and the SQL command that you want to execute as a string constant in the second parameter. This function also returns a handle to this query. You will actually do another command (ora_fetch) to receive data from this handle. 

#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib \n";
&ReadParse;

use Oraperl;
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/8.1.5";

$sUsername = "cs290w\@csdb";
$sPassword = "cs290w";

# We connect, login and get a handle to the connection
# in scalar $hConnection.

$hConnection = &ora_login("", $sUsername, $sPassword);

$hQuery = &ora_open($hConnection, "SELECT * FROM StarWars");

&ora_fetch($hQuery);

This function takes only one parameter. That parameter is the handle returned by the query made in the previous &ora_open function. This returns an array of values. Basically each time you call this function it will return an array which is one row of data from the table. When no data exists, the array will be empty. Consider the following code: 

#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib \n";
&ReadParse;

use Oraperl;
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/8.1.5";

$sUsername = "cs290w\@csdb";
$sPassword = "cs290w";

# We connect, login and get a handle to the connection
# in scalar $hConnection.

$hConnection = &ora_login("", $sUsername, $sPassword);

$hQuery = &ora_open($hConnection, "SELECT * FROM StarWars");

print "Content-type: text/html \n\n";

@asQueryReturn = &ora_fetch($hQuery);

while (@asQueryReturn != 0)
{
 print "You may reach $asQueryReturn[0] at $asQueryReturn[1] \n";
 @asQueryReturn = &ora_fetch($hQuery);
}
This example queries the table StarWars and returns every (*) value from the table. It then goes into a loop which will continue until no more rows are left in the table (that is, the size of @asQueryReturn is 0). Let's take an example table and this previous search and determine what will be printed.

StarWars
NameEmail
Hans Solosolo@starwars.net
Luke Skywalkerskywalker@starwars.net
Jabba the Hutjabba@pizzahut.com

The following would be printed: 

You may reach Hans Solo at solo@starwars.net
You may reach Luke Skywalker at skywalker@starwars.net 
You may reach Jabba the Hut at jabba@pizzahut.com

&ora_close($hQuery);
&ora_logoff($hConnection);

These functions only take one parameter. Both close connections to handles that have been previously opened by either the &ora_open or &ora_login functions. When you are finished with the Oracle database, you should close all queries and logoff. These functions are used to do this. So, let's wrap things up. A complete program would be the following: 
#!/usr/local/bin/perl

do "cgi-lib.pl" || die "Cannot implement cgi-lib \n";
&ReadParse;

use Oraperl;
$ENV{"ORACLE_HOME"} = "/opt/oracle/product/8.1.5";

$sUsername = "cs290w\@csdb";
$sPassword = "cs290w";

# We connect, login and get a handle to the connection
# in scalar $hConnection.

$hConnection = &ora_login("", $sUsername, $sPassword);

$hQuery = &ora_open($hConnection, "SELECT Email FROM StarWars");

print "Content-type: text/html \n\n";

@asQueryReturn = &ora_fetch($hQuery);

while (@asQueryReturn != 0)
{
 print "Email Address: $asQueryReturn[0] \n";
 @asQueryReturn = &ora_fetch($hQuery);
} 

# Close the query.
&ora_close($hQuery);

# Logoff and Close Connection.
&ora_logoff($hConnection);
This example basically does the same thing as the previous except the SQL command is different. This time we only want to return the Email field in the table. So only one value will be returned each fetch. This one value will still be stored in the array. You will access it as the zero element of this array ... hence printing out every email address in the table.

[ Back to Main ]