DBI connect without an entry in TNSNAMES.ORA

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; 
                                      }; 
...
[download]

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))))
[download]

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

Comment on DBI connect without an entry in TNSNAMES.ORA Select or Download 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: http://ora-12505.ora-code.com/ PS Consider upgrading DBI and DBD::Oracle if you can. -- Clayton	[reply]
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.	[reply]
Re^2: DBI connect without an entry in TNSNAMES.ORA by ericdp (Novice) on Nov 09, 2006 at 17:09 UTC
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	[reply]
Re^3: DBI connect without an entry in TNSNAMES.ORA by ptum (Priest) on Nov 09, 2006 at 17:16 UTC
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?	[reply]
Re^4: DBI connect without an entry in TNSNAMES.ORA by ericdp (Novice) on Nov 09, 2006 at 17:34 UTC
Re^5: DBI connect without an entry in TNSNAMES.ORA by clscott (Friar) on Nov 09, 2006 at 22:18 UTC
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.	[reply]
Re^2: DBI connect without an entry in TNSNAMES.ORA by ericdp (Novice) on Nov 10, 2006 at 15:11 UTC
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	[reply]
Re^3: DBI connect without an entry in TNSNAMES.ORA by clscott (Friar) on Nov 10, 2006 at 20:29 UTC
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: 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? 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	[reply]