Softpanorama (slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-) Softpanorama Search

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