One of the most important aspects of Perl is in its ability to integrate seemlessly with Oracle.
In this tutorial, we will show you how to install the Perl Database Interface (DBI) module
along with the Oracle Database Driver. Both are also required before you can use the Nyquest
sqlcommon module available in the downloads section.
Installing these modules on a Unix system usually requires root access.
1. As the root user, start CPAN. You should see a prompt like the following:
# cpan
Terminal does not support AddHistory.
cpan shell -- CPAN exploration and modules installation (v1.9402)
Enter 'h' for help.
cpan[1]>
2.Install the DBI module. Type the following:
cpan[1]> install DBI
you should see a bunch of text fly past the screen. Answer y to any dependency requirements that you may have.
3. Install the DBD::Oracle Module. Type the following:
cpan[2]> install DBD::Oracle
again you should see some text scroll past. Answer y to any dependency requirements that you may have.
Before running this step, make sure the root account has all Oracle environment variables set.
For example $ORACLE_HOME, $LD_LIBRARY_PATH, etc.
Essentially, the root user will need to be able to connect to a database using SQL*PLUS
prior to running the above step.
4. Exit CPAN. You are now done.
5. There are many examples of using DBD::Oracle. A simple net search will give you at least a dozen. A good example
is here.
6. Installing the SQLCOMMON Module
The Nyquest SQLCOMMON module is a simple wrapper that allows easier access to typical Oracle calls from within a Perl
script. After downloading and extracting this package, cd to the package directory and run the following as root:
# perl Makefile.PL
# make
# make install
7. At this point, you should be able to connect to an Oracle database from within a perl script.
You must first connect to a database using connect::db
use sqlcommon;
$dbh = connect::db($login_id,$password,$db_string,1);
$dbh is the Database handle and will be required for all database transactions.
8. The query_db::singlerow function returns a single value from a query:
$the_date = query_db::singlerow($dbh,"select sysdate from dual");
9. For multi-column/row selects, prepare the entire SQL Statement then associate a different variable to each column.
Then Loop through each value with a while statement.
$sql = qq{select spriden_pidm, spriden_id, spriden_last_name from spriden where spriden_change_ind is NULL};
$sth = $dbh->prepare( $sql );
$sth->execute();
$sth->bind_columns( undef, \$spriden_pidm,\$spriden_id,\$spriden_last_name );
while ( $sth->fetch() ) {
print "$spriden_pidm $spriden_id $spriden_last_name\n";
}
10. For all update, delete, insert, and stored plsql statements, use the mod::db function:
$sql = "delete from spriden where spriden_change_ind is not null and spriden_last_name = 'Leversund'";
$mod_result = mod::db($dbh,$sql);