sql_lookup.cgi.template

#!/usr/bin/perl

use strict;
use warnings;
use DBI;                                # SQL access to Oracle
use DBD::Oracle;

my $aleph_id;
my $debug = 0;
my $patron_id = uc $ARGV[0];
if (!$patron_id) {
	print "NULL";
	exit;
	}
#--------------------------
#      Set variables 
#--------------------------
my $db_user = 'DBUSER';
my $db_password = 'DBPASSWORD';
my $z308_prefix = 'Z308PRE';
my $oracle_host = $ENV{'ORA_HOST'};
my $oracle_sid = $ENV{'ORACLE_SID'};

#-------------------------------------
#       Open connection to Oracle
#-------------------------------------
my $dbh = DBI->connect ("dbi:Oracle:host=$oracle_host;sid=$oracle_sid", $db_user, $db_password,
                       {RaiseError=> 1, AutoCommit=> 0 }) or warn "Unable to connect: $DBI::errstr";

#---------------------------------------------
#      Prepare and execute SQL statement 
#---------------------------------------------
my $search_term = join '', $z308_prefix, $patron_id, '%';
my @sqlsearch = qq{ select  /*+ DYNAMIC_SAMPLING(2) ALL_ROWS */ /*+ ordered */ z308_id
                    from z308
                    where z308_rec_key like \'$search_term\' };
print STDERR "@sqlsearch\n" if $debug;
my $sth = $dbh->prepare(@sqlsearch);
$sth->execute or warn "SQL execution failure: $DBI::errstr";

#--------------------------------
# Retrieve results from query
#--------------------------------
while (my @data = $sth->fetchrow_array()) { $aleph_id = $data[0] }
$aleph_id =~ s/ *$//o;
print "$aleph_id";
#-------------------------------------
#       Disconnect from Oracle
#-------------------------------------
$dbh->disconnect;