Connection to Oracle database using Perl DBI

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

• DBI (previously called DBperl) is a database independent interface module for Perl. It defines a set of methods, variables and conventions that provide a consistent database interface independent of the actual database being used.

• DBD::Oracle is the Oracle specific module for DBI. It can be downloaded from CPAN (download now).

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:

• available_drivers( ) Lists all of the available DBD drivers including DBD::Oracle
• data_sources( ) Lists all of the databases available to DBD::Oracle
• connect( ) Establishes an Oracle database connection
• disconnect( ) Disconnects a login session from Oracle
• err( ) Returns the relevant Oracle error code
• errstr( ) Supplies an associated Oracle error message
• prepare( ) Prepares a SQL statement for execution
• execute( ) Executes a prepared statement
• do( ) Prepares and executes a single SQL statement all together
• bind_param( ) Binds a value to a prepared statement
• commit( ) Commits a transaction
• rollback( ) Rolls back a transaction
• table_info( ) Fetches metadata information from a table
• fetchrow_arrayref( ) Fetches a row of data into a referenced array
• fetchrow_array( ) Fetches a row of data into an array
• selectrow_array( ) Executes prepare( ), execute( ) and fetchrow_array( ) all in one call

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

• CPAN - The Comprehensive Perl Archive Network
• dbi.perl.org - Perl DBI Home Page
• DBD::Oracle - Perl Database Interface driver for Oracle

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

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 quotes :  Somerset Maugham : Marcus Aurelius : Kurt Vonnegut : Eric Hoffer : Winston Churchill : Napoleon Bonaparte : Ambrose Bierce :  Bernard 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 DOS :  Programming Languages History : PL/1 : Simula 67 : C : History of GCC development :  Scripting 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-Month :  How 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 Technology is dominated by two types of people: those who understand what they do not manage and those who manage what they do not understand ~Archibald Putt. Ph.D

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 Softpanorama society. We do not warrant the correctness of the information provided or its fitness for any purpose. 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.

Last modified: March 12, 2019