Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical

RFC: Oracle Perl Connection

by sqlpython (Novice)
on Nov 24, 2009 at 19:34 UTC ( #809180=perlmeditation: print w/replies, xml ) Need Help??

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';  

Replies are listed 'Best First'.
Re: RFC: Oracle Perl Connection
by keszler (Priest) on Nov 24, 2009 at 19:44 UTC
    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.
      I had to export SHLIB_PATH also .. export SHLIB_PATH=/usr/oracle/product/9ias/lib
      Well I did all you mentioned, but now I've this in the terminal:
      DBI connect('database=;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
        'host=...', not 'database=...'
      thank you for this solution without tnsnames.ora. It works

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://809180]
Approved by keszler
Front-paged by keszler
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2017-06-25 14:43 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (567 votes). Check out past polls.