perl and oracle connectivity using DBI

Here is the connection info I am trying to use  
my $host='genome.coafes.umn.edu';
        my $port='1521';
        my $dsn='dbi:oracle:SMD_Test.genome.coafes.umn.edu';
        my $user='SMD';
        my $pass='passwd';
        my $dbh=DBI->connect("dbi:Oracle:hostname=$host;port=$port;dsn=$dsn",$user,$pass)
        || die "Error in opening database: $DBI::errstr\n";

The  above stmt is part of Login.pm module
I get following error :
An unexpected error ocurred.
Stack Trace:
Can't call method "prepare" on an undefined value at /home/junej003/Fahrenkrug/smd/lib/Tools/DB/Login.pm line 146.


--------------------------------------------------------------------------------

Last executed DBI statement:
Software error:
Can't modify $DBI::!lasth special variable at /home/junej003/Fahrenkrug/smd/lib/Tools/DB/Session.pm line 796

I tried with both DBI:Proxy and DBI:Oracle , i still get the same error.

Doesn't work  I still get the same error . I modified the script as per your recommendation.Its shown below:

 my $host='genome.coafes.umn.edu';
        my $port='1521';
        my $dbname='SMD_Test';
 
        my $user='SMD';
        my $pass='passwd';
        my $dbh=DBI->connect("dbi:Oracle:$dbname:hostname=$host",$user,$pass) || die "Error in opening database: $DBI::errstr\n";

I tried both dbname i-e SMD_Test as well as SMD_Test.genome.coafes.umn.edu

Pl advice

I am showing last few lines .Login.pm contains a function ConnectToDatabase  which is called from another module session.pm .
I earlier tried to specify the connection paramters in a config file called conf.pm . It didn't work then I tried to specify the connection parameters within this function itself , this doesn't works either .

Here is the complete function.
use DBI;
use CGI qw/:standard :html3/;
use CGI::Carp qw(fatalsToBrowser);
use Config::Config;
use Tools::WWW::PageFormat qw(BeginHTML NewBanner Help Email);

####################################################################################
sub ConnectToDatabase{
####################################################################################
# This will return a database handle to the specified database, which
# must be passed in as the first argument.  If a username and password
# are passed into the function, the it will use those to log on,
# otherwise it will default to webb

# Usage : my $dbh = &ConnectToDatabase($dbInstance, $schemaName, $pass);
#
# all arguments are optional. the dbInstance schemaName and password
# are taken from the conf.pm file. args that are passed in override
# the values in the conf.pm file. thus it is possible to have more
# than one db connection open.
#


    my $dbh;
    my $dbInstance = lc( shift );
    my $schemaName = uc( shift );
    my $pass = shift;
    my $conf = Config::Config->new;
    my $dbConnect;

    # fetch the db instance schemaName and password.  If these data
    # were passed in then use what was passed. otherwise use what is
    # in the config file

    if ( !$dbInstance ) {
        $dbInstance = $conf->dbInstanceId;
    }

    if ( !$schemaName ) {
        $schemaName = $conf->dbSchemaName;
    }

    if ( !$pass ) {
        $pass = $conf->dbPw if ($schemaName eq $conf->dbSchemaName);
        if ( !$pass ) {
            $pass = &GetPassword( $dbInstance, $schemaName );
        }
    }

    &SetEnvironment;
         my $oracle_home = $ENV{"ORACLE_HOME"};
        print "ORACLE_HOME: $oracle_home\n";
    if ( $conf->dbConnect ) {
    }
    else {

        #
        # construct the connect string from the raw material
        # given...
        #

        if ( $conf->dbVendor ) {
            $dbConnect = $conf->dbVendor . ":";
        }
        else {
x
            $dbConnect = "dbi:Oracle:";
        }
        if ( !$conf->dbSchemaName ) {
            &PrintConnectionError( $dbInstance, "Configuration error, you must provide a schema name" );
        }
        if ( !$conf->dbInstanceId ) {
            &PrintConnectionError( $dbInstance, "Configuration error, you must provide an Instance id (SID)" );
        }
        if ( $conf->dbHost ) {
            $dbConnect .= "host=" . $conf->dbHost;
            if ( $conf->dbPort ) {
                $dbConnect .= ";port=" . $conf->dbPort;
            }
            $dbConnect .= ";sid=" . $conf->dbInstanceId;
        }
        else {
            $dbConnect .= $conf->dbInstanceId;
        }
    }

    if ( -e $conf->dbLogPath . "NO_CGI_ACCESS" && $ENV{SCRIPT_FILENAME} =~ /cgi/ ){
        &PrintConnectionError( $dbInstance, "The database is currently unavailable for technical reasons." );
    }
    elsif ( -e $conf->dbLogPath . "NO_ACCESS" ){
        &PrintConnectionError( $dbInstance, "The database is currently unavailable for technical reasons." );
    }
$ENV{ORACLE_HOME} = "/u01/app/oracle/product/10.2.0/db_1/";
$ENV{ORACLE_SID} = "SMD_Test";
$ENV{ORACLE_BASE}="/u01/app/oracle";
$ENV{PATH}="/u01/app/oracle/product/10.2.0/db_1/bin";
$ENV{LD_LIBRARY_PATH}="/u01/app/oracle/product/10.2.0/db_1/lib";
#my $dbname='SMD_Test.genome.coafes.umn.edu';
#my $dbh=DBI->connect("dbi:Oracle:$dbname:hostname=$host",$user,$pass) || die "Error in opening database: $DBI::errstr\n";

    eval {
        my $host='genome.coafes.umn.edu';
        my $port='1521';
        my $dbname='SMD_Test';

        my $dsn='dbi:oracle:SMD_Test.genome.coafes.umn.edu';
        my $user='SMD';
        my $pass='passwd';
        my $dbh=DBI->connect("dbi:Oracle:$dbname:hostname=$host",$user,$pass) || die "Error in opening database: $DBI::errstr\n";

#my $dbh=DBI->connect("dbi:Oracle:hostname=$host;port=$port;dsn=$dsn",$user,$pass)
 #       || die "Error in opening database: $DBI::errstr\n";

#       $dbh = DBI->connect( 'dbi:Oracle:SMD_Test', 'SMD', 'passwd', { RaiseError=>1, AutoCommit=>0, ShowErrorStatement=>1 });
#       $dbh = DBI->connect( $dbConnect, $schemaName, $pass, { RaiseError=>1, AutoCommit=>0, ShowErrorStatement=>1 });
    };
if ($@) {
        my $error = h2("Cannot connect to the oracle server named <font color=red>$dbInstance</font>:<br>$DBI::errstr\n$@\n");
        &PrintConnectionError( $dbInstance, $error );
    }

    # set the nls_date_format as required by SMD software
   my $stmt = $dbh->prepare("alter session set nls_date_format=\"YYYY-MM-DD\"");
  #my $stmt = $dbh->prepare("alter session set nls_date_format=\"MM-DD-YYYY\"");
          $stmt->execute();
eval{
    if ( $conf->traceLevel > 0 ) {
        unlink($conf->traceFile);
        $dbh->trace($conf->traceLevel, $conf->traceFile);
    }
    $DBI::neat_maxlen = 2000;
};
    if ($@) {
        my $error = h2("Cannot connect to the oracle server named <font color=red>$dbInstance</font>:<br>$DBI::errstr\n$@\n");
        &PrintConnectionError( $dbInstance, $error );
 }
    return $dbh;

}

Hey ,
I tried that , but I get following error now .

Stack Trace:
install_driver(Oracle) failed: Can't locate DBD/Oracle.pm in @INC (@INC contains: /home/junej003/Fahrenkrug/smd/lib /usr/lib/perl5/site_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl/5.8.4 /usr/lib/perl5/site_perl/5.8.3 /usr/lib/perl5/site_perl /usr/lib/perl5/vendor_perl/5.8.6/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.5/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.4/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.3/i386-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl/5.8.4 /usr/lib/perl5/vendor_perl/5.8.3 /usr/lib/perl5/vendor_perl /usr/lib/perl5/5.8.6/i386-linux-thread-multi /usr/lib/perl5/5.8.6 .) at (eval 7) line 3.

Perhaps the DBD::Oracle perl module hasn't been fully installed,

or perhaps the capitalisation of 'Oracle' isn't right.

Available drivers: DBM, ExampleP, File, Proxy, Sponge.

at /home/junej003/Fahrenkrug/smd/lib/Tools/DB/Login.pm line 142

Hey is the way that I can connect to oracle without using DBD:module . Seems like it is having some issues on Server side .I just talked top my DBA about this .
Regards
Bhupi

Here is the error my DBA is getting when he tries to install DBD module on server side.
DBD::Oracle: libclntsh.so.10.1: cannot open shared object file: No such file or directory at /usr/lib/perl5/5.8.6/i386-linux-thread-multi/DynaLoader.pm line 230.
Not sure why he is getting this error while installing DBD module.

Any ideas .
Bhupi

Hey , The readme helped me to resolve the install error but now I am getting error while connecting:
 ORA-12162: TNS:net service name is incorrectly specified

I am sure my Tnsnames.ora is correct .

Any suggestions

I followed whats is there in Readme.hlp  however it doesn't helps much . I have checked the location of libclntsh.so.10.1 and its there in $ORACLE_HOME/lib.
So I am not sure what is going wrong here ?