Oracle Programming with Perl DBI / DBD

最新推荐文章于 2024-08-09 16:34:22 发布

Carryf

最新推荐文章于 2024-08-09 16:34:22 发布

阅读量1.4k

点赞数

分类专栏： Oracle DBA 手册文章标签： oracle perl sql database library insert

Oracle DBA 手册专栏收录该内容

28 篇文章 0 订阅

订阅专栏

http://www.idevelopment.info/data/Oracle/DBA_tips/Programming/PROGRAMMING_2.shtml

Oracle Programming with Perl DBI / DBD

by Jeff Hunter, Sr. Database Administrator

Contents

Overview
Download DBI / DBD
Install DBI / DBD
Sample Perl DBI Script for Oracle
Troubleshooting
About the Author

Overview Download DBI / DBD Install DBI / DBD Sample Perl DBI Script for Oracle Troubleshooting About the Author Oracle ACE. Jeff currently works as a Senior Database Administrator for The DBA Zone, Inc. located in Pittsburgh, Pennsylvania. His work includes advanced performance tuning, Java and PL/SQL programming, capacity planning, database security, and physical / logical database design in a UNIX, Linux, and Windows server environment. Jeff's other interests include mathematical encryption theory, programming language processors (compilers and interpreters) in Java and C, LDAP, writing web-based database administration tools, and of course Linux. He has been a Sr. Database Administrator and Software Engineer for over 16 years and maintains his own website site at: http://www.iDevelopment.info. Jeff graduated from Stanislaus State University in Turlock, California, with a Bachelor's degree in Computer Science.

The Perl Database Intertface (DBI) is a database access Application Programming Interface (API) for the Perl Language. The Perl DBI API specification defines a set of functions, variables and conventions that provide a consistent database interface independant of the actual database being used. This month, I will provide a short article on how to install DBI and DBD on the UNIX platform. Also included in this article is a sample Perl script that demonstrates the use of DBI.

To start, you need to download a copy of DBI and Oracle DBD. (Keep in mind that you only need to download the first two files: DBI and Oracle DBD. The others are links to useful DBI resources.)

Program Download Version Release Date
DBI-1.48.tar.gz Download 1.48 14-Mar-2005
DBD-Oracle-1.16.tar.gz Download 1.16 22-Oct-2004
DBI - Database independent interface for Perl View N/A N/A
A Short Guide to DBI View N/A N/A
dbi.perl.org View N/A N/A
Perl DBI Examples View N/A N/A
Database Interface Modules for Perl5 View N/A N/A
Programming the Perl DBI View N/A N/A
CPAN.org (list all modules) View N/A N/A

Ensure the correct version of Perl is installed
You should be using version 5.004 or higher. To find out the version of Perl you have on your machine, use the following:
% perl -version

This is perl, version 5.004_04 built for sun4-solaris

Copyright 1987-1997, Larry Wall

Perl may be copied only under the terms of either the Artistic 
License or the GNU General Public License, which may be found 
in the Perl 5.0 source kit.
Solaris 8 now comes with perl5 installed. Programs like CGI.pm, DBI/DBD, etc. will not install with the Solaris CC compiler. The reason for this is that the CC compiler on solaris 8 is not a full version.
The full version costs money and is considered an add on product available from Sun. Since the installed perl was compilied with CC you can not compile the other perl libraries with gcc. Attempting to do so, (i.e. installing DBI/DBD), it will complain that your version of cc does not have the:
"optional language software installed"
The default version of perl that comes with Solaris 8 is installed in /usr/bin. To verify the version of CC that was used to compile perl, use the following:
% perl -V:cc
First, do not uninstall the Perl interpreter bundled with Solaris (I believe it is perl 5.003_05). This may break things. My advice is to install both gcc and a newer version of Perl from Sunfreeware.com. First install gcc and then Perl. The new perl executable will be located in /usr/local/bin. Here is an example of how to install both packages after downloading:
# pkgadd -d gcc-3.2-sol8-sparc-local

# pkgadd -d perl-5.8.0-sol8-sparc-local
After installing both packages, make sure that gcc and the new version of perl are in your PATH.
Install DBI
You must install DBI BEFORE installing Oracle DBD !!!!

Simply download, gunzip, and untar the DBI module into a temporary directory and make it. Below is a quick guide to installing DBI. Although I am performing all of the following tasks as the root user account, the only task that truly requires root is "make install".
Login as root
su -
Create a temporary directory
mkdir tempDBI
cd tempDBI
gunzip/untar the DBI module
gunzip DBI-1.48.tar.gz
tar -xvf DBI-1.48.tar
Change to the created directory
cd DBI-1.48
Make/Install DBI
perl Makefile.PL
make
make install
Remove temporary DBI directory and files
cd ../..
rm -rf tempDBI
Install DBD:Oracle
You must install DBI BEFORE installing Oracle DBD !!!!

Simply download, gunzip, and untar the DBD module into a temporary directory and make it. Below is a quick guide to installing DBD:
Login as root
su -
Create a temporary directory
mkdir tempDBD
cd tempDBD
gunzip/untar the DBD module
gunzip DBD-Oracle-1.16.tar.gz
tar -xvf DBD-Oracle-1.16.tar
Change to the created directory
cd DBD-Oracle-1.16
Make/Install DBD
perl Makefile.PL
make
make install
Remove temporary DBD directory and files
cd ../..
rm -rf tempDBD

Sample Oracle DBI Script. testDBDOracle.pl

#!/usr/local/bin/perl

# +----------------------------------------------------------------+
# | FILE         : testDBDOracle.pl                                |
# | AUTHOR       : Jeff Hunter, Senior Database Administrator      |
# | PURPOSE      : This script will test the DBI/DBD installation. |
# | OUTPUT FILES : NONE                                            |
# +----------------------------------------------------------------+

require "ctime.pl";
require "flush.pl";

use DBI;

&declareGlobalVariables;

&printHeader;

$dbh = &getOracleLogin("$ORACLE_SID", "$ORACLE_USERID", "$ORACLE_PASSWORD");
$dbh->{LongReadLen} = 64000;

&performTest;

&logoffOracle($dbh);

&printFooter;

exit;

# +--------------+
# | SUB ROUTINES |
# +--------------+
sub declareGlobalVariables {

  $ORACLE_SID              = "ORCL";
  $ORACLE_USERID           = "system";
  $ORACLE_PASSWORD         = "manager";

  $ENV{'ORACLE_SID'}       = "$ORACLE_SID";
  $ENV{'ORACLE_HOME'}      = "/u01/app/oracle/product/10.2.0/db_1";

}

sub printHeader {

  print "/n";
  print "Running testDBDOracle.pl.../n";
  print "/n";

}

sub printFooter {

  print "Ending testDBDOracle.pl.../n";
  print "/n";

}

sub getOracleLogin {

  local ($oracle_sid, $username, $password) = @_;
  local ($temp_dbh);
  local($tempID, $tempPassword, $tempKey);

  print "  (*) Attempting Oracle Login .../n";


  unless ( $temp_dbh = DBI->connect(  "DBI:Oracle:$oracle_sid"
                                    , "$username"
                                    , $password
                                    , {AutoCommit => 0}) ) {
    &programError(   "Oracle Login Failed as $username"
                   , ""
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    exit;
  }

  print "      OK/n/n";

  return $temp_dbh;

}

sub logoffOracle {

  ($dbh) = @_;

  print "  (*) Attempting Oracle Logoff .../n";

  unless ($dbh->disconnect) {

    # &programError(   "Could not disconnect from Oracle"
    #                , ""
    #                , "$DBI::errstr"
    #                , "dba-mail"
    #                , "dba-pager");
    # exit;

    # Commented out this section because of
    # the errors we get: ORA-02050
    # (some remote DBs may be in doubt
    # (DBD: disconnect error)

    1;
  }

  print "      OK/n/n";

}

sub performTest {

  local ($rows1, $rows2, $rows3, $rows4);
  local ($test_dbi_intr_no, $test_dbi_name);
  local ($user, $sysdate);

  # +-----------------------+
  # | CREATE TABLE test_dbi |
  # +-----------------------+

  print "  (*) Creating table TEST_DBI .../n";

  $sql_statement = "
    CREATE TABLE test_dbi (
        test_dbi_intr_no    NUMBER(15)
      , test_dbi_name       VARCHAR2(100)
    )
  ";
  unless ($rows = $dbh->do("$sql_statement")) {
    &programError(   "Could not create table TEST_DBI"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      OK/n/n";

  # +----------------------------+
  # | INSERT INTO TABLE test_dbi |
  # +----------------------------+

  print "  (*) Insert into TEST_DBI .../n";

  $sql_statement = "
    INSERT INTO test_dbi (
        test_dbi_intr_no
      , test_dbi_name
    ) VALUES (
        1000
      , 'Jeff Hunter'
    ) 
  ";

  unless ($rows1 = $dbh->do("$sql_statement")) {
    &programError(   "Could not do INSERT_TEST_DBI (Jeff) cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      $rows1 rows inserted./n";

  $sql_statement = "
    INSERT INTO test_dbi (
        test_dbi_intr_no
      , test_dbi_name
    ) VALUES (
        1001
      , 'Melody Hunter'
    ) 
  ";

  unless ($rows2 = $dbh->do("$sql_statement")) {
    &programError(   "Could not do INSERT_TEST_DBI (Melody) cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      $rows2 rows inserted./n";

  $sql_statement = "
    INSERT INTO test_dbi (
        test_dbi_intr_no
      , test_dbi_name
    ) VALUES (
        1002
      , 'Alex Hunter'
    ) 
  ";

  unless ($rows3 = $dbh->do("$sql_statement")) {
    &programError(   "Could not do INSERT_TEST_DBI (Alex) cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      $rows3 rows inserted./n";

  unless ($dbh->commit) {
    &programError(   "Could not commit INSERT_TEST_DBI transaction"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      OK/n/n";

  # +----------------------------+
  # | SELECT FROM TABLE test_dbi |
  # +----------------------------+

  print "  (*) Select from TEST_DBI .../n";

  $sql_statement = "
    SELECT
        test_dbi_intr_no
      , test_dbi_name
    FROM
        test_dbi
  ";

  unless ($cursor = $dbh->prepare("$sql_statement")) {
    &programError(   "Could not prepare SELECT_TEST_DBI cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  unless ($cursor->execute) {
   &programError(   "Could not execute SELECT_TEST_DBI cursor"
                  , "$sql_statement"
                  , "$DBI::errstr"
                  , "dba-mail"
                  , "dba-pager");
   $dbh->rollback;
   &logoffOracle($dbh);
   exit;
  }


  while ((   $test_dbi_intr_no
           , $test_dbi_name) = $cursor->fetchrow_array) {

    print "/n";
    print "        --> TEST_DBI_INTR_NO :  $test_dbi_intr_no/n";
    print "        --> TEST_DBI_NAME    :  $test_dbi_name/n";
    print "/n";

  }

  unless ($cursor->finish) {
   &programError(   "Could not finish SELECT_TEST_DBI cursor"
                  , "$sql_statement"
                  , "$DBI::errstr"
                  , "dba-mail"
                  , "dba-pager");
   $dbh->rollback;
   &logoffOracle($dbh);
   exit;
  }


  print "      OK/n/n";


  # +----------------------------+
  # | DELETE FROM TABLE test_dbi |
  # +----------------------------+

  print "  (*) Delete from TEST_DBI .../n";

  $sql_statement = "
    DELETE FROM test_dbi
  ";

  unless ($rows4 = $dbh->do("$sql_statement")) {
    &programError(   "Could not do DELETE_TEST_DBI (All Names) cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      $rows4 rows deleted./n";

  unless ($dbh->commit) {
    &programError(   "Could not commit DELETE_TEST_DBI transaction"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      OK/n/n";


  # +---------------------+
  # | DROP TABLE test_dbi |
  # +---------------------+

  print "  (*) Drop table TEST_DBI .../n";

  $sql_statement = "
    DROP TABLE test_dbi
  ";

  unless ($rows = $dbh->do("$sql_statement")) {
    &programError(   "Could not drop table TEST_DBI"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  print "      OK/n/n";


  # +-------------------+
  # | GET USER and DATE |
  # +-------------------+

  print "  (*) Select USER and SYSTEM .../n";

  $sql_statement = "
    SELECT
        user
      , TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
    FROM
        dual
  ";

  unless ($cursor = $dbh->prepare("$sql_statement")) {
    &programError(   "Could not prepare SELECT_SINGLE cursor"
                   , "$sql_statement"
                   , "$DBI::errstr"
                   , "dba-mail"
                   , "dba-pager");
    $dbh->rollback;
    &logoffOracle($dbh);
    exit;
  }

  unless ($cursor->execute) {
   &programError(   "Could not execute SELECT_SINGLE cursor"
                  , "$sql_statement"
                  , "$DBI::errstr"
                  , "dba-mail"
                  , "dba-pager");
   $dbh->rollback;
   &logoffOracle($dbh);
   exit;
  }


  ($user, $sysdate) = $cursor->fetchrow_array;

  print "/n";
  print "        --> USER             :  $user/n";
  print "        --> SYSDATE          :  $sysdate/n";
  print "/n";

  unless ($cursor->finish) {
   &programError(   "Could not finish SELECT_SINGLE cursor"
                  , "$sql_statement"
                  , "$DBI::errstr"
                  , "dba-mail"
                  , "dba-pager");
   $dbh->rollback;
   &logoffOracle($dbh);
   exit;
  }

  print "      OK/n/n";

}


sub programError {

  local($message, $sql_statement, $ora_errstr, $email_who, $page_who) = @_;

  print "+--------------------------+/n";
  print "| SUB: programError        |/n";
  print "+--------------------------+/n";
  print "/n";

  unless($message) {$message = "No message provided from calling module.";}

  print "+-------------------------------------------------------+/n";
  print "| ******************* PROGRAM ERROR ******************* |/n";
  print "+-------------------------------------------------------+/n";
  print "/n";
  print "/n";
  print "Message:/n";
  print "--------------------------------------------------------/n";
  print "$message/n";
  print "/n";
  if ($sql_statement) {
    print "SQL:/n";
    print "--------------------------------------------------------/n";
    print "$sql_statement/n";
    print "/n";
  }

  if ($ora_errstr) {
    print "Oracle Error:/n";
    print "--------------------------------------------------------/n";
    print "$ora_errstr/n";
  }

  # +-------------------------------------+
  # | SEND THIS OUTPUT TO THE MAIL SYSTEM |
  # +-------------------------------------+

  if ($email_who) {

    $AUTO_MESSAGE = "/n";
    $AUTO_MESSAGE .= "+-----------------------------------------+/n";
    $AUTO_MESSAGE .= "| The following message was automatically |/n";
    $AUTO_MESSAGE .= "| genereated by the Sysmon System.        |/n";
    $AUTO_MESSAGE .= "+-----------------------------------------+/n";
    $AUTO_MESSAGE .= "/n";

    @EMAIL_ARRAY = split(/ /, $email_who);

    foreach (@EMAIL_ARRAY) {

      $TO = $_."/@your_company.com";
      $FROM_FULL = "/"Sysmon Admin/"";
      $FROM = "/"dba/"";
      $Subject = "Sysmon Mail Error";

      open (MAIL,"|/usr/lib/sendmail -f $FROM -F $FROM_FULL $TO");
      print MAIL "To: $TO/n";
      print MAIL "From: $FROM/n";
      print MAIL "Reply-To: $FROM/n";
      print MAIL "Subject: $Subject/n/n";

      print MAIL "$AUTO_MESSAGE";

      print MAIL "+-------------------------------------------------------+/n";
      print MAIL "| ******************* PROGRAM ERROR ******************* |/n";
      print MAIL "+-------------------------------------------------------+/n";
      print MAIL "/n";
      print MAIL "/n";
      print MAIL "Message:/n";
      print MAIL "--------------------------------------------------------/n";
      print MAIL "$message/n";
      print MAIL "/n";
      if ($sql_statement) {
        print MAIL "SQL:/n";
        print MAIL "$sql_statement/n";
        print MAIL "--------------------------------------------------------/n";
        print MAIL "/n";
      }

      if ($ora_errstr) {
        print MAIL "Oracle Error:/n";
        print MAIL "--------------------------------------------------------/n";
        print MAIL "$ora_errstr/n";
      }

      close MAIL;
    }
  }

  if ($page_who) {

    @PAGER_ARRAY = split(/ /, $page_who);

    foreach (@PAGER_ARRAY) {

      $TO = $_."/@your_company.com";
      $FROM_FULL = "/"Sysmon Admin/"";
      $FROM = "/"dba/"";
      $Subject = "Sysmon Program Error";

      open (MAIL,"|/usr/lib/sendmail -f $FROM -F $FROM_FULL $TO");
      print MAIL "To: $TO/n";
      print MAIL "From: $FROM/n";
      print MAIL "Reply-To: $FROM/n";
      print MAIL "Subject: $Subject/n/n";

      print MAIL "$message/n";
      close MAIL;
    }
  }

}

This section contains observations and troubleshooting techniques related to programming and running Perl with the DBD:Oracle driver.
64-bit Oracle
Possibly one of the most common errors - attempting to run a Perl DBI/DBD:Oracle script on the same machine and using a 64-bit Oracle product set. This error occurs not so much with the Oracle 64-bit product set in general, but with the setting of the LD_LIBRARY_PATH to the Oracle 64-bit libraries. In this example, I will attempt to run the testDBDOracle.pl Perl script on a server with an Oracle 64-bit product set and have the LD_LIBRARY_PATH set using it:
$ echo $LD_LIBRARY_PATH
/u01/app/oracle/product/9.2.0/lib

$ ./testDBDOracle.pl

Running testdbi.pl...

  (*) Attempting Oracle Login ...
Error
install_driver(Oracle) failed: Can't load '/usr/local/lib/perl5/site_perl/5.8.6/sun4-solaris/auto/DBD/Oracle/Oracle.so' for module DBD::Oracle: ld.so.1: /usr/local/bin/perl: fatal: /u01/app/oracle/product/9.2.0/lib/libwtc9.so: wrong ELF class: ELFCLASS64 at /usr/local/lib/perl5/5.8.6/sun4-solaris/DynaLoader.pm line 229. at (eval 1) line 3 Compilation failed in require at (eval 1) line 3. Perhaps a required shared library or dll isn't installed where expected at ./testDBDOracle.pl line 70

Unfortunately, the process is attempting to link a 32-bit library / Perl executable against a 64-bit (Oracle) library. The way I resolved this is by setting the LD_LIBRARY_PATH to include the 32-bit Oracle libraries and then running the script:
$ LD_LIBRARY_PATH=/u01/app/oracle/product/9.2.0/lib32
$ export LD_LIBRARY_PATH

$ ./testDBDOracle.pl

Running testdbi.pl...

  (*) Attempting Oracle Login ...
      OK
Another solution I tried was to build DBD:Oracle using the build64 flag in the Makefile configuration step:
$ perl Makefile.PL -r=build64
This however didn't work for me and I still had to properly set the LD_LIBRARY_PATH to Oracle's 32-bit libraries in order for the Perl script to run.

Jeffrey Hunter is an Oracle Certified Professional, Java Development Certified Professional, Author, and an

Carryf

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle Programming with Perl DBI / DBD

http://www.idevelopment.info/data/Oracle/DBA_tips/Programming/PROGRAMMING_2.shtml Oracle Programming with Perl DBI / DBD by Jeff Hunter, Sr. Database Administrator <!-- | ************
复制链接

扫一扫

专栏目录

Program	Download	Version	Release Date
DBI-1.48.tar.gz	Download	1.48	14-Mar-2005
DBD-Oracle-1.16.tar.gz	Download	1.16	22-Oct-2004
DBI - Database independent interface for Perl	View	N/A	N/A
A Short Guide to DBI	View	N/A	N/A
dbi.perl.org	View	N/A	N/A
Perl DBI Examples	View	N/A	N/A
Database Interface Modules for Perl5	View	N/A	N/A
Programming the Perl DBI	View	N/A	N/A
CPAN.org (list all modules)	View	N/A	N/A