RFC: Oracle Perl Connection

[sqlpython]

I don't usually visit here. I should change that fact. So I thought I would share a solution that helped me. I apologize if this has publised before as I did not find this here. I am really a MySQL user for many years. Recently I had to deal with Oracle which I had not touched in 9+ years. So I needed a Quick Strategy to Connect to Oracle DB from Perl script. I hope this helps someone else...

"My Thanks to Michael's Blog for this ...

After successful installation of DBD::Oracle it’s time to use it. The connection string is the same as for he rest DB: my $dbi=

DBI->connect("dbi:Oracle:$db_name:$db_host:$db_port", $db_user, $db_pa
+ss);

As result of running code above I got following error:

 Couldn't connect to database db_name: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)

After googling I found that the problem was that. I tried to connect to the remove database but the driver couldn’t do that without special file – tnsnames.ora. It should be placed to the $ORACLE_HOME/network/admin and contain something like that:

 db_name =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db_host)(PORT = db_port))
)
(CONNECT_DATA =
(SERVICE_NAME = db_name)
)
)

And the connection string should be changed to use service name from the tnsnames.ora instead of host:

 my $dbi = DBI->connect("dbi:Oracle:$service_name", $db_user, $db_pass);

Finally we should export variable ORACLE_SID into our environment. Add this command into .bashrc

export ORACLE_SID="orcl"

or set it using Perl variable $ENV:

$ENV{ORACLE_SID} = 'orcl';

[keszler]

To avoid the need for a tnsnames.ora entry, I've been using:

my $dbh = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=1521",$use
+r,$passwd);

This is working with Oracle 10.2.0.[34], DBI 1.602, and DBD::Oracle 1.17.

[Anonymous Monk]

I had to export SHLIB_PATH also .. export SHLIB_PATH=/usr/oracle/product/9ias/lib

[cristianabs]

Well I did all you mentioned, but now I've this in the terminal:

DBI connect('database=10.0.249.18;sid=BDSGCOPP;port=1521','CCARVAJAL',
+...) failed: Can't connect using this syntax without specifying a HOS
+T and one of SID INSTANCE_NAME SERVER SERVICE_NAME

:/

[runrig]

'host=...', not 'database=...'

[Anonymous Monk]

thank you for this solution without tnsnames.ora. It works