Hi,

I am seeking for your wisdom because I have a strange connectivity problem with DBI and an Oracle 11.2.0.3 server.

First of all, a disclosure: I am not experienced in Oracle. So, here is my problem:

When I try to connect using the usual dsn/user/password format, thus (according to my understanding) basing the connection on the tnsnames.ora conf file, I obtain the following connection error: 

ORA-12505:
    TNS:listener does not currently know of SID 
    given in connect descriptor.

[download]

But when I connect using an alternative parameter format and reproduce the exact entry of the tnsnames.ora file in my code, as a string, then I am able to connect.

By the way, the tnsnames.ora file seems to be ok, as the Oracle SQL Developper client can use it successfully to connect.

Does any person with Oracle experience have any ideas about why this is happening ?

Thank you in advance for your feedback.

Here is the code I am using: 

use v5.12;
use strict;
use warnings;
use diagnostics;

use DBI;

my $dbh;

# This code produces an ORA-12505 error
$dbh = DBI->connect(
    "DBI:Oracle:sid=JUPITER;host=jupitercluster",
    "user",
    "pass",
);
    
# Alternatively, this code, 
# reproducting the content of tnsnames.ora, works !
$dbh = DBI->connect(
    "DBI:Oracle:",
    "user/pass@(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = jupite
+rcluster)(PORT = 1521))(CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_N
+AME = jupiter)))",
);

__END__

# Content of the entry in tnsnames.ora
JUPITER =
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = jupitercluster)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME=jupiter)
  )
)

[download]

In reply to DBI and Oracle ORA-12505 error by jmb

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.