Beefy Boxes and Bandwidth Generously Provided by pair Networks
more useful options

DBI connect without an entry in TNSNAMES.ORA

by ericdp (Novice)
on Nov 09, 2006 at 16:23 UTC ( #583145=perlquestion: print w/replies, xml ) Need Help??
ericdp has asked for the wisdom of the Perl Monks concerning the following question:

Is it possible to do a connect to a database when there is no entry in the local machines TNSNAMES.ORA file? perl, v5.6.1 built for sun4-solaris $DBI::VERSION = "1.20"; $DBD::Oracle::VERSION = '1.12'; I've tried the following, but to no avail.
... my $conn = "dbi:Oracle:HOST=$host;SID=$sid;port=1521"; my $db_h = DBI->connect ( $conn, $user, $pwd, { AutoCommit => 0, # 0 = false RaiseError => 0, PrintError => 0, ChopBlanks => 1 # CHAR chopped } ) || BEGIN { print color 'red on_black'; warn "\n" . "x" x 70 . "\n" . "\t--> Connecting to " . uc ( $sid ) . " as " . uc ( $user ) . " <--\n\n" + . $DBI::errstr . "\n" . "x" x 70 . "\n"; print color 'reset'; print "\n"; return undef; }; ...
I get the following errors
********************************************************************** +* Fatal NI connect error 12541, connecting to: (DESCRIPTION=(ADDRESS=( +NNECT­_DATA=(SID=aaa)(CID=( (HOST=aaa)(USER=uuuuu)))) ********************************************************************** +* Fatal NI connect error 12535, connecting to: (DESCRIPTION=(ADDRESS=( +NNECT­_DATA=(SID=aaa)(CID=( (HOST=aaa)(USER=uuuuu)))) ********************************************************************** +* Fatal NI connect error 12505, connecting to: (DESCRIPTION=(ADDRESS=( +NNECT­_DATA=(SID=aaa)(CID=( (HOST=aaa)(USER=uuuuu))))

Edited (davorg): Replace <pre> tags with <code>

Replies are listed 'Best First'.
Re: DBI connect without an entry in TNSNAMES.ORA
by clscott (Friar) on Nov 09, 2006 at 16:34 UTC

    Does it work with SQLplus?

    ORA-12505 means the database doesn't understand the SID that you provided i.e. "There's no database wih that name hosted here"

    Lookup the error codes in the vendor (or related ) documentation:

    PS Consider upgrading DBI and DBD::Oracle if you can.

Re: DBI connect without an entry in TNSNAMES.ORA
by ptum (Priest) on Nov 09, 2006 at 16:47 UTC

    Um, this may seem a dumb question, but why not simply add the appropriate tnsnames.ora entry for the SID in question so your connection can be resolved? That file is usually owned by whatever passes for 'oracle' on your host, in some directory like /orasw/app/oracle/product/10.0.2/network/admin.


      I'd love to upgrade DBI & DBD and make changes to the TNSNAMES.ORA file. But the SAs and DBAs have those so locked down, it takes an act-of-god to get any changes in.

      At one time, I thought I'd do it on my own pc, but I'm not allowed to install "unaproved" modules. Of which DBI is one. Every oncein a while inventory is done of machines and those finding unapproved software are re-imaged.

      Yeah, not the most helpful place to work. They have really caught up on the idea of similar installations make for easier support/maintenance.


        Hmmmm. Well, according to DBD::Oracle, you are bypassing the tnsnames.ora on your local machine, anyway. The error you are receiving seems to indicate that the listener on the remote machine doesn't recognize the SID you are passing it. Are you sure you have the SID right?

Re: DBI connect without an entry in TNSNAMES.ORA
by chanakya (Friar) on Nov 10, 2006 at 07:31 UTC

    I guess its not possible to connect to SID without the entry in TNSNAMES.ORA. I've tried it before but it just doesn't work, because the listener on the remote machine will search for the SID defined in TNSNAMES.ORA

    Hope that helps.
      I was afraid of that. I was hoping giving a fully qualified connection string would be able to bypass the need for an entry in this file. I guess that Oracle has hooks into this file in order for the local client to connect.


        You absolutely can connect from a client with no tnsnames.ora file, I do it all of the time, with the same syntax that you are using.

        You have obvoiusly not been using valuable tools that are available to you: the documentation for DBD::Oracle and oracle client tools

        The documentation for DBD::Oracle has a huge amount of documentation regarding connections and you can view it on the CPAN

        You have a bad SID/host combination in your script or in your environment. Read the DBD::Oracle documentation on connections and which piece of configuration data (DBI connection string; DBD environment variables; DBI environmant variables; Oracle client files and environment variables) interact. Especially the precedence.

        Here are some more options on how you could attempt to debug the issues:
        1. Verify all of your assumptions:
          • does the database instance actually reside on that server?
          • does the environment contain/lack all of the informaitn you expected
          • does the tnsnames.ora file exist and does it contain any entry that looks similar to the one to which you are trying to connect?
        2. Check for software bugs:
          • You know you are running old version of modules, check the Changelog for bugs related to connections for each version between the one you are using and the current version.

        Finally, when people are trying to help you and ask you questions, answer the questions.


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://583145]
Approved by davorg
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2017-01-20 22:19 GMT
Find Nodes?
    Voting Booth?
    Do you watch meteor showers?

    Results (178 votes). Check out past polls.