Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jdavidb (1361)

jdavidb
  (email not shown publicly)
http://voiceofjohn.blogspot.com/

J. David Blackstone has a Bachelor of Science in Computer Science and Engineering and nine years of experience at a wireless telecommunications company, where he learned Perl and never looked back. J. David has an advantage in that he works really hard, he has a passion for writing good software, and he knows many of the world's best Perl programmers.

Journal of jdavidb (1361)

Tuesday August 17, 2004
12:40 PM

Remote Oracle with DBI, bypassing TNSNAMES.ORA

[ #20440 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.