Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses

Oracle hangs at connect

by DrManhattan (Chaplain)
on Aug 09, 2004 at 14:42 UTC ( #381248=perlquestion: print w/replies, xml ) Need Help??

DrManhattan has asked for the wisdom of the Perl Monks concerning the following question:

I'm having difficulty connecting to an Oracle database with perl. Sqlplus connects immediately, but DBI takes over 3 minutes to establish a connection. Here's some test code (auth info removed):
#!/usr/bin/perl use strict; use DBI; $ENV{ORACLE_HOME} = "/usr/local/oracle9"; my $dbhost = ''; my $database = ''; my $username = ''; my $password = ''; # Build a dsn my $dsn = "DBI:Oracle:host=$dbhost;sid=$database"; # Create a database handle print scalar localtime(), " connecting ...\n"; my $dbh = DBI->connect($dsn, $username, $password, { RaiseError => 0, PrintError => 1, AutoCommit => 0 }) or die $DBI::errstr; print scalar localtime(), " done.\n"; $dbh->disconnect();
Here's the script output:
Mon Aug  9 10:30:00 2004 connecting ...
Mon Aug  9 10:33:09 2004 done.
The same code works fine on another Oracle database I'm using, so there appears to be something different about this server. Since Sqlplus connects immediately, though, I have to think there's something I need to do differently on the client side.


Replies are listed 'Best First'.
Re: Oracle hangs at connect
by etcshadow (Priest) on Aug 09, 2004 at 15:01 UTC
    Can you provide the connect description you give to sqlplus (minus the auth info, of course)? I'm taking a wild guess here, but I don't think that you're using both the host name and the SID... probably just using a TNS name (which maps, via either your $ORACLE_HOME/tnsnames.ora, or via an oranames server listed in your $ORACLE_HOME/sqlnet.ora, to a host, SID, and port number).

    Anyway, my best guess for what to try is: drop the host=$host and the sid=$sid, and instead make your DSN just be: DBI:Oracle:$tnsname. That's what I always do.

    Oh, and as to why this will work with one database and not with another... well, the different databases maybe differently configured on either your oranames server or on the sqlnet client's tnsnames.ora file.

    update: that should be $ORACLE_HOME/network/admin/tnsnames.ora (and sqlnet.ora). Sorry, I typed too fast :-)

    ------------ :Wq Not an editor command: Wq
Re: Oracle hangs at connect
by clscott (Friar) on Aug 09, 2004 at 15:20 UTC

    I find that the form specifying host is prone to taking long times to connect.

    Set up your TNS names file approriately ($ORACLE_HOME/network/admin/tnsnames.ora) and use the form:

    $dsn = "dbi:Oracle:$database";


      I'm following up to myself because I think someone has discovered the cause.

      Unless you specify which port Oracle is listening on it will try port 1526 first, which would explain a long delay in waiting for a response before trying the more standard port of 1521.

      my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=$port");


        this is realy good answer. its worksfine Thanks
Re: Oracle hangs at connect
by hmerrill (Friar) on Aug 09, 2004 at 15:23 UTC
    Unfortunately I don't know the answer to your problem, but I can suggest that you ask your question on the Perl DBI mailing list - with the expert people on that list I'm sure you will get some good helpful information there.

Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://381248]
Approved by sweetblood
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2023-12-11 13:11 GMT
Find Nodes?
    Voting Booth?
    What's your preferred 'use VERSION' for new CPAN modules in 2023?

    Results (41 votes). Check out past polls.