Softpanorama

May the source be with you, but remember the KISS principle ;-)
Contents Bulletin Scripting in shell and Perl Network troubleshooting History Humor

Connection to Oracle database using Perl DBI

News Oracle Recommended Links Recommended Papers   Checklists SQL Injection FAQs
Oracle client           Humor Etc

Oracle has a good explanation of connection from Perl scripts at Perl - Oracle FAQ

You need two CPAN modules to enable Oracle connectivity: The first is the main DBI (DB Interface) module, the other is a DBD (DB Driver) module. Several DBD modules are available - to support the different database systems, including Oracle and MySQL. The Oracle module is called DBD::Oracle.

There are two basic approaches to installing those Perl modules traditional ( download, unpack, build, test, install) and the CPAN method.

Perl DBI is a generic application programming interface (API) used to connect to Oracle database from Perl. It is similar in concept to ODBC (Oracle DataBase Connectivity) and JDBC (Java DataBase Connectivity). Oracle uses the DBD::Oracle driver, another Perl module that provides the actual communication to the low-level OCI code. It is OCI that makes the final connection to the Oracle database.

Perl DBI connectivity was first implemnted in 1991 when an Oracle DBA, Kevin Stock, created a database connection program called OraPerl that was released for Perl 4. Over time, similar Perl 4 programs appeared for other databases, such as Michael Peppler's Sybperl. In a parallel development, starting around September of 1992, a Perl-based group was working on a specification for DBPerl, a database-independent specification for Perl 4.  When Perl 5 arrived it was changed to object oriented architecture.

DBI architecture permits to connect to any database, as long as you had the right driver. For Oracle such driver is  DBD::Oracle written by Tim Bunce, the main creator of Perl DBI.  Among the calls used by Perl DBI module:

Setting the Oracle environment

First you need to set env variable in Perl:

my $ORACLE_HOME = "/app/oracle/product/11.2.0/db_1";
my $ORACLE_SID="orcl";

$ENV{ORACLE_HOME}=$ORACLE_HOME;
$ENV{ORACLE_SID}=$ORACLE_SID;
$ENV{PATH}="$ORACLE_HOME/bin";
# $ENV{LD_LIBRARY_PATH}="$ORACLE_HOME/lib";

Connecting

To connect to the Oracle database through DBI you can either set up your enviroment like with sqlplus or you can define everything in the connect routine provided by DBI. I will try to describe the last of the two options.
use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=localhost;sid=ORCL;port=1521', 
               'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 });
This connect string requires that you have the Oracle TNS listener running.

Selecting

With our open database handle we can now begin to select from the database.
my $sth = $dbh->prepare("select table_name from user_tables");
while (my ($table_name) = $sth->fetchrow_array()) {
    print $table_name, "\n";
}
$sth->finish();
There are more ways to select with the DBI interface, the prepare way is particular smart if you have more rows or you need placeholders. There is a method called selectrow_array which is very nice if you only need one row of data.
my $table_count = 
   $dbh->selectrow_array("select count(table_name) from user_tables");

Inserting

As with selecting there are multiple ways to insert rows with DBI. Depending on your needs and wheter you need to insert more than one row there are two different solutions. First an example on inserting a single row.
$dbh->do("insert into table_name (name) values (?)", undef, 'scott');
This example introduced something new called a placeholder. A placeholder is defined as a '?' in your SQL-statement and DBI will need arguments for each of the placeholders you have in your SQL-statement. Using placeholders instead of inserting the variabled directly in the SQL-statement is much better, because you put off the actual insert of the value to after the SQL-statement has been parsed by the Oracle database, which should save you from having to check the variable you're inserting from characters that need to be quoted.
There is an even smarter approach if you need to insert many rows.
my $sth = $dbh->prepare("insert into table_name (name) values (?)");
while (my ($line) = <>) {
    $sth->execute($line);
}
$sth->finish();


Transactions

use DBI;
my $dbh = DBI->connect('dbi:Oracle:host=localhost;sid=ORCL;port=1521', 
               'scott', 'tiger', { RaiseError => 1, AutoCommit => 0 });
eval {
    my $sth = $dbh->prepare("insert into table_name (name) values (?)");
    $sth->execute('flipflop');
    $sth->finish();
    $dbh->commit();
}
if ($@) {
    $dbh->rollback();
    die $@;
}
$dbh->disconnect();

Old News

Connect to Oracle DB from Perl script

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

See

 

Perl: Connecting to an Oracle database WITHOUT having an installed client

I started this post with the intention of having it be a quickie, just showing people how to connect to Oracle with Perl/DBI without having to have Oracle client or even Perl installed, but it's not that simple.  So I decided to rewrite it with detailed instructions.

Here is a list of what I used:

Machine: Sun Solaris SPARC on build and target machines
Oracle: Oracle 10g client (build machine only)
Perl: v. 5.10.0 (build machine only)
Perl modules: DBI (1.607), DBD::Oracle (1.22), PAR (0.983), pp (0.982), PerlIO (1.04) (build machine only)

Oracle 10g PERL Connection Problem

I am running into a issue, which makes the return code of PERL as -1 after making connection to ORACLE thru DBI, evenif the script is successful.  This is happening on recently upgraded 10g version.

The script is quite simple. The script and execution is shown below (commands highlighted).  Please let us know if you have seen something similar and do you know of any way to fix this situation.

/usr/local/wics/salil> echo $ORACLE_SID
w002d

/usr/local/wics/salil> echo $LD_LIBRARY_PATH
/usr/openwin/lib:/opt/SUNWspro/lib:/usr/dt/lib32:/opt/oracle/product/10.2.0.4/lib:/opt/oracle/product/10.2.0.4/lib32

/usr/local/wics/salil> cat  dbi_connection_return_val.pl
#!/usr/local/bin/perl


   eval 'use Oraperl; 1' || die $@ if $] >= 5;

   $ls_output = `ls`;
   print "\n... Return code after simple LS execution:  [$?]\n";

   print "\n... Logging in to ORACLE via DBI\n";
   $log_in = &ora_login("","","")  || die ("login failed : $! \n ");
   print "... Login successful!\n\n";
   &ora_logoff($log_in);

   $ls_output = `ls`;
   print "... Return code after simple LS execution:  [$?]\n\n";

/usr/local/wics/salil>  dbi_connection_return_val.pl

... Return code after simple LS execution:  [0]

... Logging in to ORACLE via DBI
... Login successful!

... Return code after simple LS execution:  [-1]

/usr/local/wics/salil>
===================================
Hi Keshav,

Did we encounter these errors? Could it be because of the Signal issue?

==================================================
_____________________________________________

Hi Macneil,
We are migrating from 9i to 10G and our perl scripts needs to change to use 5.8.7.  We were testing our perl scripts and noticed that to be able to login to our database , we have to make the input in ora_login procedure to be all null (which means we have to rely on the environment variables) .

Looking back on AFS, when you migrated, I remember you encounter the same error. The error we encounter is ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) . Did you encounter same problem?  Can you share your knowledge on this?

Can we also setup a meeting with you regarding the perl migration?

CODE :

#!/opt/perl5.8.7/bin/perl

# MUST Have these eval statments to use DBI !!
eval '$Oraperl::safe = 1'       if $] >= 5;
eval 'use Oraperl; 1' || die $@ if $] >= 5;

$DBHandle=&ConnectDBNoInput;
$DBHandle=&ConnectDBWithInputWorld;
$DBHandle=&ConnectDBWithInput;

# this is fine
sub ConnectDBNoInput {
    printf "DB Input = No Input \n";
    my $dbh=&ora_login("","","")||&CheckOraError;
    printf "This works - no input \n";
    printf "Login Success $dbh \n";
    return $dbh;
}

# this is fine
sub ConnectDBWithInputWorld {
    printf "DB Input = w002d.world \n";
    my $dbh=&ora_login("w002d.world","user","pswd")||&CheckOraError;
    printf "This works - w002d.world \n";
    printf "Login Success $dbh \n";
    return $dbh;
}

# this has ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach)
sub ConnectDBWithInput {
    printf "DB Input = w002d \n";
    my $dbh=&ora_login("w002d","user","pswd")||&CheckOraError;
    printf "This works - w002d \n";
    printf "Login Success $dbh \n";
    return $dbh;
}

sub CheckOraError {
    print "$ora_errstr\n";
    exit 1;
 

Recommended Links

perl.com A Short Guide to DBI

Instructional Oracle Perl DBI Example Script

Thomas Eibner - Perl DBI and the DBDOracle driver

Connecting to Oracle with the Perl DBI

Oracle 10g PERL Connection Problem Oracle 10g Connection, PERL

 



Etc

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available in our efforts to advance understanding of environmental, political, human rights, economic, democracy, scientific, and social justice issues, etc. We believe this constitutes a 'fair use' of any such copyrighted material as provided for in section 107 of the US Copyright Law. In accordance with Title 17 U.S.C. Section 107, the material on this site is distributed without profit exclusivly for research and educational purposes.   If you wish to use copyrighted material from this site for purposes of your own that go beyond 'fair use', you must obtain permission from the copyright owner. 

ABUSE: IPs or network segments from which we detect a stream of probes might be blocked for no less then 90 days. Multiple types of probes increase this period.  

Society

Groupthink : Two Party System as Polyarchy : Corruption of Regulators : Bureaucracies : Understanding Micromanagers and Control Freaks : Toxic Managers :   Harvard Mafia : Diplomatic Communication : Surviving a Bad Performance Review : Insufficient Retirement Funds as Immanent Problem of Neoliberal Regime : PseudoScience : Who Rules America : Neoliberalism  : The Iron Law of Oligarchy : Libertarian Philosophy

Quotes

War and Peace : Skeptical Finance : John Kenneth Galbraith :Talleyrand : Oscar Wilde : Otto Von Bismarck : Keynes : George Carlin : Skeptics : Propaganda  : SE quotes : Language Design and Programming Quotes : Random IT-related quotesSomerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose BierceBernard Shaw : Mark Twain Quotes

Bulletin:

Vol 25, No.12 (December, 2013) Rational Fools vs. Efficient Crooks The efficient markets hypothesis : Political Skeptic Bulletin, 2013 : Unemployment Bulletin, 2010 :  Vol 23, No.10 (October, 2011) An observation about corporate security departments : Slightly Skeptical Euromaydan Chronicles, June 2014 : Greenspan legacy bulletin, 2008 : Vol 25, No.10 (October, 2013) Cryptolocker Trojan (Win32/Crilock.A) : Vol 25, No.08 (August, 2013) Cloud providers as intelligence collection hubs : Financial Humor Bulletin, 2010 : Inequality Bulletin, 2009 : Financial Humor Bulletin, 2008 : Copyleft Problems Bulletin, 2004 : Financial Humor Bulletin, 2011 : Energy Bulletin, 2010 : Malware Protection Bulletin, 2010 : Vol 26, No.1 (January, 2013) Object-Oriented Cult : Political Skeptic Bulletin, 2011 : Vol 23, No.11 (November, 2011) Softpanorama classification of sysadmin horror stories : Vol 25, No.05 (May, 2013) Corporate bullshit as a communication method  : Vol 25, No.06 (June, 2013) A Note on the Relationship of Brooks Law and Conway Law

History:

Fifty glorious years (1950-2000): the triumph of the US computer engineering : Donald Knuth : TAoCP and its Influence of Computer Science : Richard Stallman : Linus Torvalds  : Larry Wall  : John K. Ousterhout : CTSS : Multix OS Unix History : Unix shell history : VI editor : History of pipes concept : Solaris : MS DOSProgramming Languages History : PL/1 : Simula 67 : C : History of GCC developmentScripting Languages : Perl history   : OS History : Mail : DNS : SSH : CPU Instruction Sets : SPARC systems 1987-2006 : Norton Commander : Norton Utilities : Norton Ghost : Frontpage history : Malware Defense History : GNU Screen : OSS early history

Classic books:

The Peter Principle : Parkinson Law : 1984 : The Mythical Man-MonthHow to Solve It by George Polya : The Art of Computer Programming : The Elements of Programming Style : The Unix Haterís Handbook : The Jargon file : The True Believer : Programming Pearls : The Good Soldier Svejk : The Power Elite

Most popular humor pages:

Manifest of the Softpanorama IT Slacker Society : Ten Commandments of the IT Slackers Society : Computer Humor Collection : BSD Logo Story : The Cuckoo's Egg : IT Slang : C++ Humor : ARE YOU A BBS ADDICT? : The Perl Purity Test : Object oriented programmers of all nations : Financial Humor : Financial Humor Bulletin, 2008 : Financial Humor Bulletin, 2010 : The Most Comprehensive Collection of Editor-related Humor : Programming Language Humor : Goldman Sachs related humor : Greenspan humor : C Humor : Scripting Humor : Real Programmers Humor : Web Humor : GPL-related Humor : OFM Humor : Politically Incorrect Humor : IDS Humor : "Linux Sucks" Humor : Russian Musical Humor : Best Russian Programmer Humor : Microsoft plans to buy Catholic Church : Richard Stallman Related Humor : Admin Humor : Perl-related Humor : Linus Torvalds Related humor : PseudoScience Related Humor : Networking Humor : Shell Humor : Financial Humor Bulletin, 2011 : Financial Humor Bulletin, 2012 : Financial Humor Bulletin, 2013 : Java Humor : Software Engineering Humor : Sun Solaris Related Humor : Education Humor : IBM Humor : Assembler-related Humor : VIM Humor : Computer Viruses Humor : Bright tomorrow is rescheduled to a day after tomorrow : Classic Computer Humor

The Last but not Least


Copyright © 1996-2016 by Dr. Nikolai Bezroukov. www.softpanorama.org was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. This document is an industrial compilation designed and created exclusively for educational use and is distributed under the Softpanorama Content License.

The site uses AdSense so you need to be aware of Google privacy policy. You you do not want to be tracked by Google please disable Javascript for this site. This site is perfectly usable without Javascript.

Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

FAIR USE NOTICE This site contains copyrighted material the use of which has not always been specifically authorized by the copyright owner. We are making such material available to advance understanding of computer science, IT technology, economic, scientific, and social issues. We believe this constitutes a 'fair use' of any such copyrighted material as provided by section 107 of the US Copyright Law according to which such material can be distributed without profit exclusively for research and educational purposes.

This is a Spartan WHYFF (We Help You For Free) site written by people for whom English is not a native language. Grammar and spelling errors should be expected. The site contain some broken links as it develops like a living tree...

You can use PayPal to make a contribution, supporting development of this site and speed up access. In case softpanorama.org is down you can use the at softpanorama.info

Disclaimer:

The statements, views and opinions presented on this web page are those of the author (or referenced source) and are not endorsed by, nor do they necessarily reflect, the opinions of the author present and former employers, SDNP or any other organization the author may be associated with. We do not warrant the correctness of the information provided or its fitness for any purpose.

Last modified: June 04, 2016