Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight
 
PerlMonks  

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

Comment on RFC: Oracle Perl Connection
Select or Download Code
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=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
      :/
        'host=...', not 'database=...'

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlmeditation [id://809180]
Approved by keszler
Front-paged by keszler
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (9)
As of 2014-07-25 11:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite superfluous repetitious redundant duplicative phrase is:









    Results (170 votes), past polls