Help needed in connecting my perl script to oracle

Hi

I have installed DBI, DBD:Oracle in order to make perl to connect to oracle.

my ORACLE_SID=orcl
my ORACLE_HOME = /opt/oracle/product/10.2.0/Db_1/
my TNS_ADMIN = /opt/oracle/product/10.2.0/Db_1/

I checked the following:

root@d1ncic-test01/05:01 PM(/)#tnsping orcl

TNS Ping Utility for Solaris: Version 10.2.0.1.0 - Production on 03-MAR-2009 17:01:56

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
/opt/oracle/product/10.2.0/Db_1//network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = d1ncic-test01)(PORT = 1523)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)))
OK (0 msec)

ORACLE_HOME

root@d1ncic-test01/05:01 PM(/)#echo $ORACLE_HOME
/opt/oracle/product/10.2.0/Db_1/

ORACLE_SID

root@d1ncic-test01/05:02 PM(/)#echo $ORACLE_SID
orcl

my TNSNAMES.ORA


# tnsnames.ora Network Configuration File: /opt/oracle/product/10.2.0/Db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = d1ncic-test01)(PORT = 1523))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )


my SQLNET.ORA

# sqlnet.ora Network Configuration File: /opt/oracle/product/10.2.0/Db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


my LISTENER.ORA


# listener.ora Network Configuration File: /opt/oracle/product/10.2.0/Db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /opt/oracle/product/10.2.0/Db_1)
      (PROGRAM = extproc)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
      (ADDRESS = (PROTOCOL = TCP)(HOST = d1ncic-test01)(PORT = 1523))
    )
  )


When I execute my perl script I get the following error:

perl con.pl
DBI connect('152.152.0.10','scott',...) failed: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach) at con.pl line 4
ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach)

root@d1ncic-test01/04:59 PM(/)#vi con.pl
"con.pl" 36 lines, 969 characters
#!/usr/bin/perl -w
use strict;
use DBI;
my $db = DBI->connect( "dbi:Oracle:152.152.0.10","scott","tiger" )
    || die( $DBI::errstr . "\n" );
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 16;
my $SEL = "SELECT * FROM emp";
my $sth = $db->prepare($SEL);
$sth->execute();
my $nf = $sth->{NUM_OF_FIELDS};
print "This statement returns $nf fields\n";
 
for ( my $i = 0; $i < $nf; $i++ ) {
    my $name = $sth->{NAME}[$i];
    my $type = $sth->{TYPE}[$i];
    my $prec = $sth->{PRECISION}[$i];
    my $scle = $sth->{SCALE}[$i];
    my $tn=$db->type_info($type)->{TYPE_NAME};
    print
        "Field number $i: name $name of type $tn with precision $prec,$scle\n";
}
while ( my @row = $sth->fetchrow_array() ) {
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n";
}
print "\n\nThis query returned " . $sth->rows . " rows.\n";
 
END {
    $db->disconnect if defined($db);
}

Select allOpen in new window