Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

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=(HOST=machineA.com)(PROTOCOL=tcp)(PORT=1521))(CO +NNECT­_DATA=(SID=aaa)(CID=(PROGRAM=t2.pl@bbb) (HOST=aaa)(USER=uuuuu)))) ********************************************************************** +* Fatal NI connect error 12535, connecting to: (DESCRIPTION=(ADDRESS=(HOST=machineA.com)(PROTOCOL=tcp)(PORT=1521))(CO +NNECT­_DATA=(SID=aaa)(CID=(PROGRAM=t2.pl@bbb) (HOST=aaa)(USER=uuuuu)))) ********************************************************************** +* Fatal NI connect error 12505, connecting to: (DESCRIPTION=(ADDRESS=(HOST=machineA.com)(PROTOCOL=tcp)(PORT=1521))(CO +NNECT­_DATA=(SID=aaa)(CID=(PROGRAM=t2.pl@bbb) (HOST=aaa)(USER=uuuuu))))

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

Comment on DBI connect without an entry in TNSNAMES.ORA
Select or Download Code
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: http://ora-12505.ora-code.com/

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

    --
    Clayton
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.

      Hello,

      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.

      Eric

        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
    ericdp

    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.

      Thanks
      Eric

        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 http://search.cpan.org/dist/DBD-Oracle/

        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.

        --
        Clayton

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://583145]
Approved by davorg
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2014-09-23 05:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (210 votes), past polls