Re: Connection to Oracle using Perl

Hi Mike,

  I am giving an example in Windows Env.

1. Go to http://aspn.activestate.com/ASPN/Downloads/ActivePerl/ and download MSI for windows (5.6.1.629). Install it. Reboot.
2. Go to http://www.activestate.com/PPMPackages/5.6/ and download DBI.ppd and DBD-Oracle8.ppd to c:\dbi.
3. cd into c:\dbi and issue two commands; ppm install dbi.ppd and ppm install dbd-oracle8.ppd. (These command install the Oracle-Perl drivers for you.
4. You are done..

  To test out the drivers:

1. Create a table by this code:

    use strict;
    use DBI;

    my $dbh = DBI->connect( 'dbi:Oracle:awt',
                            'scott',
                            'tiger',
                          {
                            RaiseError => 1,
                            AutoCommit => 0
                          }
                         ) || die "Database connection not made: $DBI::errstr";

    my $sql = qq{ CREATE TABLE testing ( id INTEGER NOT NULL, 
                                       name VARCHAR2(128), 
                                       title VARCHAR2(128), 
                                       phone CHAR(8) 
                                     ) };

$dbh->disconnect();
    

2. Test insert

use strict;
use DBI qw(:sql_types);

my $dbh = DBI->connect( 'dbi:Oracle:awt',
                        'scott',
                        'tiger',
                        {
                          RaiseError => 1,
                          AutoCommit => 0
                        }
                      ) || die "Database connection not made: $DBI::errstr";

my _at_records = (
                [ 0, "Larry Wall",      "Perl Author",  "555-0101" ],
                [ 1, "Tim Bunce",       "DBI Author",   "555-0202" ],
                [ 2, "Randal Schwartz", "Guy at Large", "555-0303" ],
                [ 3, "Doug MacEachern", "Apache Man",   "555-0404" ] 
              );

my $sql = qq{ INSERT INTO testing VALUES ( ?, ?, ?, ? ) }; my $sth = $dbh->prepare( $sql );

for( _at_records ) {
  eval {
$sth->bind_param( 1, _at_$_->[0], SQL_INTEGER );
$sth->bind_param( 2, _at_$_->[1], SQL_VARCHAR );
$sth->bind_param( 3, _at_$_->[2], SQL_VARCHAR );
$sth->bind_param( 4, _at_$_->[3], SQL_VARCHAR );
$sth->execute();
$dbh->commit();

  };

  if( $_at_ ) {
    warn "Database error: $DBI::errstr\n";
$dbh->rollback(); #just die if rollback is failing
  }
}

$sth->finish();
$dbh->disconnect();
-------------------

Check out also this link http://www.perl.com/lpt/a/1999/10/DBI.html A short Guide to DBI.

Allan W. Tham
DBA Received on Mon Sep 03 2001 - 10:50:23 CEST