Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
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
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=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 drinking their drinks and smoking their pipes about the Monastery: (10)
As of 2015-07-31 10:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (276 votes), past polls