This weekend had bad weather. Rain pouring from the sky and a drop of snow.
Such weather is good to undertake something that is absolutely useless like writing a Perl programme on Linux to access a remote Oracle database.
The first hurdle to overcome is to install an Oracle client along with some Perl routines that will allow us to write Perl code to get things done. This hurdle can be overcome with help of http://usestrict.net/2009/07/12/perl-installing-dbdoracle-on-ubuntu-904-and-oracle-instant-client/ . This site gives a nice cookbook to install both the Oracle client and the Perl modules.
It took me about 4 hours to get this done. But then I had the software that I needed. The actual code that I wrote is:
#!/usr/bin/perl -wT
use Oraperl;
print "Content-type: text/html\r\n\r\n";
print "<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN>";
print "<html><head>";
print "<title>Test Perl Script</title>";
print "</head>";
$ENV{"ORACLE_HOME"} = "/usr/lib/oracle/xe/app/oracle/product/10.2.0/server";
$hConnection = &ora_login("xe", "uid", "psw") || die $ora_errstr;
$hQuery = &ora_open($hConnection, "SELECT naam, nummer from scott.klant") || die $ora_errstr;
@asQueryReturn = &ora_fetch($hQuery);
while (@asQueryReturn != 0)
{
print "<p>naam: $asQueryReturn[0] nummer: $asQueryReturn[1]";
@asQueryReturn = &ora_fetch($hQuery);
}
&ora_close($hQuery);
&ora_logoff($hConnection);
exit;
This code is not very well documented on the Internet. The best resource is http://www.tutorialspoint.com/perl/oraperl_manual.htm . Nevertheless I could not get this code right until I saw in another source (http://www.cs.purdue.edu/homes/cs290w/perlLecs/PerlOracle.html ) that you also give the full path to ORACLE_HOME as an environmental variable. This is a bit strange as this variable is also known on Linux, but without it, the code did not run.
I wrote this piece of code as a cgi programme. To retrieve the results, I had to start the output with: print “Content-type: text/html\r\n\r\n”; and print “<!DOCTYPE HTML PUBLIC -//W3C//DTD HTML 4.0 Transitional//EN>”; as to avoid the strings to get lost in the Internet. Only after these strings, the output is interpreted as HTML and it can be displayed in the browser.