How can I use a database server from a Perl CGI script?

Question

My program works already, I have Perl (GUI Window) where I can input data, data passed to the webpage (using to Tomcat server, JSP) and then saved it to oracle database. What I want is to make search parameter (webapp) that retrieve/extract data from the Oracle database using Perl CGI. Is it possible? Or any suggestions to solve my program? Thanks!:-)

Answer 1

Any specific reason for the mix in technologies? Why not use a servlet/JSP?

If you must use Perl, then you need to choose what web-server will run your Perl script.

Normally, this would be Apache using mod_perl.

But if you only intend to use this for a few admin scripts, then you can run Perl from tomcat as outlined here.

Once you have managed to get a simple perl script running, then I would look into using DBI/DBD::Oracle to access your database?

Hope this helps...

Answer 2

Yes u can by using DBI and DBD::Oracle modules.

However there are some gotchas with Oracle. I remember a few fun and games with Oracle 8 so these may no longer be applicable but it did require setting ENV variables like ORACLE_HOME, ORACLE_BASE & ORACLE_SID in some cases.

The DBD::Oracle doc does go into this and also mentions another ENV variable TWO_TASK. So getting it to work may depend on....

• what version of Oracle u have running
• whether u have listener up (which I think u do need for network access like CGI?)
• what version SQL*Net your using.

Seems daunting but all you will probably need is to add these ENV variables in the webserver (iPlanet was what I was using at that time). Alternatively from the DBD::Oracle doc it gives...

BEGIN {
  $ENV{ORACLE_HOME} = '/home/oracle/product/10.x.x';
  $ENV{TWO_TASK}    = 'DB';
}
$dbh = DBI->connect('dbi:Oracle:','scott', 'tiger');
#  - or -
$dbh = DBI->connect('dbi:Oracle:','scott/tiger');

/I3az/

PS. The above assumes you are running CGI script on same server as Oracle! If not then those ENV variables are superfluous and u can just do this (pulled from old script of mine!)...

my $db = DBI->connect("dbi:Oracle:host=$host;sid=$database", $user, $pass, 
  { RaiseError => 0, PrintError => 0 } )
  or croak( "Unable to connect to DB - $DBI::errstr" );

However I do recall having to tweak something like TNLISTENER.CONF on the Oracle server (this was some years ago so memory fails me a bit!) and I'm pretty sure u need to download some client Oracle library (which u can get from their site).