Oracle Database Connection

MJohnson90 has asked for the wisdom of the Perl Monks concerning the following question:

Hi,

New to the website, but looking for some advice!

Recently picked up a small project yesterday, which requires me accessing an Oracle database via Perl, i'm using DBI, which as far as i can tell means that i have to specify a SID in the connect string.. However the databases i wish to connect to have multiple nodes and can have different SID's so hard coding them will not be viable.

I've been advised by our database team, that the best way to connect would to use a Service ID? but even a database name would be ok i believe.

Atm my 'connect' code is:

my $dbhost = "*HOST*";
my $dbport = "1521";
my $dbsid = "*SID*";

my $dbh = DBI->connect("dbi:Oracle:host=$dbhost;sid=$dbsid;port=$dbpor
+t",$username,$password) or die $DBI::errstr;
my $sth = $dbh->prepare("SQL STATEMENT");
[download]

Thanks in advance!

Comment on Oracle Database Connection Download Code

Replies are listed 'Best First'.
Re: Oracle Database Connection by McA (Priest) on Sep 03, 2014 at 08:29 UTC
Hi, what I've done far time ago when working with Oracle is the following: `my $db_name = 'SOMEDATABASE'; my $datasource = "dbi:Oracle:$db_name"; my $dbh = DBI->connect($datasource, $dbuser, $dbpasswd);` [download] and `SOMEDATABASE` is a name in TNSNAMES.ORA. Therein it is defined how and to whom a connection is made when using this name. E.g. with a RAC-Cluster having several listeners and severals nodes all is done via this indirection. I'm pretty sure that the database stuff should have a TNSNAMES configuration for other client software too. When going this way it's transparent to the Perl program where the database is hosted. Regards McA	[reply] [d/l] [select]
Re^2: Oracle Database Connection by MJohnson90 (Initiate) on Sep 03, 2014 at 08:43 UTC
Thanks for the quick response! Will this also work for accessing databases on other servers? as i believe thats why i have to include the "host" or "scan" i think it's officially called.	[reply]
Re^2: Oracle Database Connection by MJohnson90 (Initiate) on Sep 03, 2014 at 08:55 UTC
Nvm should have read into TNSNAMES.ORA before i posted haha, thank you i will try this!	[reply]
Re: Oracle Database Connection by Tux (Canon) on Sep 03, 2014 at 09:30 UTC
When using `tnsnames.ora` as proposed, just set the environment `$TWO_TASK` and be done with it. $ echo $TWO_TASK dbo_foobar $ tnsping $TWO_TASK TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 03-SEP- +2014 11:25:08 Copyright (c) 1997, 2013, Oracle. All rights reserved. Used parameter files: /pro/oracle/v12.1/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact ( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( H +OST = dbo)( PORT = 1521)( PROTOCOL = TCP))) ( CONNECT_DATA = ( SERVIC +E_NAME = foobar))) OK (150 msec) $ env DBI_TRACE=1 perl -MDBI -wE'DBI->connect ("dbi:Oracle:", split m{ +/} => $ENV{ORACLE_USERID})' DBI 1.631-ithread default trace level set to 0x0/1 (pid 9871 pi 1e +ba010) at DBI.pm line 288 via -e line 0 -> DBI->connect(dbi:Oracle:, USER, ****) -> DBI->install_driver(Oracle) for linux perl=5.020000 pid=9871 ru +id=203 euid=203 install_driver: DBD::Oracle version 1.74 loaded from /pro/lib/p +erl5/site_perl/5.20.0/x86_64-linux-thread-multi-ld/DBD/Oracle.pm <- STORE('ShowErrorStatement', 1)= ( 1 ) [1 items] at Oracle.pm li +ne 81 <- install_driver= DBI::dr=HASH(0x2045f30) <- connect('', 'USER', ...)= ( DBI::db=HASH(0x21ec3e8) ) [1 items] + at DBI.pm line 671 <- STORE('PrintError', 1)= ( 1 ) [1 items] at DBI.pm line 723 <- STORE('AutoCommit', 1)= ( 1 ) [1 items] at DBI.pm line 723 <- STORE('Username', 'USER')= ( 1 ) [1 items] at DBI.pm line 726 <- connected('dbi:Oracle:', 'USER', ...)= ( undef ) [1 items] at D +BI.pm line 733 <- connect= DBI::db=HASH(0x21ec3e8) <- STORE('dbi_connect_closure', CODE(0x21ebde8))= ( 1 ) [1 items] +at DBI.pm line 742 <- DESTROY(DBI::db=HASH(0x21ec310))= ( undef ) [1 items] at -e lin +e 1 <- disconnect_all= ( ) [0 items] (not implemented) at DBI.pm line +750 ! <- DESTROY(DBI::dr=HASH(0x2045f30))= ( undef ) [1 items] during gl +obal destruction [download] Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]