Monday, September 17, 2012

How do you connect Perl and Oracle on Windows?

#!C:/Perl/bin/perl

use DBI;

$oracle_server = 'localhost';
$oracle_user = '<UserName>';
$oracle_passwd = '<Password>';
$oracle_sid = '<SID>';
$oracle_table = 'EMP';
$oracle_port = '1521';

# Get a database handle by connecting to the database
$dbh = DBI->connect("dbi:Oracle:host=$oracle_server; port=$oracle_port; sid=$oracle_sid", $oracle_user, $oracle_passwd, { RaiseError => 1, AutoCommit => 1}) or die "$DBI::errstr\n";

my $sql = "SELECT E.EMPNO AS EMPLOYEE_ID,
   E.ENAME AS EMPLOYEE_NM,
   M.ENAME AS MNGR_NM
   FROM   $oracle_table E
   LEFT OUTER JOIN $oracle_table M
            ON E.MGR = M.EMPNO
   ORDER  BY E.EMPNO";

my $sth = $dbh->prepare($sql) or die "$DBI::errstr";
$sth->execute() or die "$! $?\n";

my $COL_NM = join("\t", @{$sth->{NAME}});
print "$COL_NM\n";

while (my @row = $sth->fetchrow_array())
{
    print join("\t", @row), "\n";
}

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

No comments :