So, ordinarily when I use DBD::Oracle, someone simply gives me the name of the instance to use, and I don't have to worry about it. But suppose I've been remotely logging in to a box somewhere and using Oracle there locally, but now I want to pull that information from my home server, using DBD::Oracle. All I have is the name of the host.
Behind the scenes (in case you didn't know this), when you give Oracle (either through an official Oracle client like SQL*Plus or through another client such as a program using DBD::Oracle) an instance name, a lookup is performed in a file somewhere on your system called TNSNAMES.ORA. Given the right information, an administrator can set up a new instance in TNSNAMES.ORA with any name they like to point to the database on the remote host. But what if you can't or don't want to get your admin to modify this file? Or what if you are the admin and don't know this information?
Turns out you only need a couple of pieces of information. One we already have: the hostname. The other is the SID, and to get that execute this command on the remote machine:
$ORACLE_HOME/bin/lsnrctl stat
Somewhere near the end of all the mess of output is something that looks like this:
Service "somethingorother" has 1 instance(s). Instance "ynxsi", status READY, has 1 handler(s) for this service...
In this case the SID is the string ynxsi. (Yes, I made that up. :) )
Now, you can connect to Oracle like this:
my $host = "hostname";
my $sid = "ynxsi";
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid", ...
If I understand correctly, the port the Oracle server is running on can also vary. DBD::Oracle tries ports 1526 and 1521, in that order. In the case that your server is running on a nonstandard port, I believe you can find it from a line that looks like this:
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yep)(PORT=8080)))
In this case the port is 8080. (Which is more often used for web servers. Yes, I made that up, too. Again, if I understand correctly, you can add a port= option to your connect string, like this:
my $port = 8080;
my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=$port", ...
Don't take my word for that, though, because I haven't tested it and may not even understand correctly.
Update: One last thing, which I forgot to include. This depends on the admin of the remote Oracle database to enable this kind of remote access. If you don't see a line from the output of lsnrctl that includes "(PROTOCOL=tcp)", it probably won't work.