I am currently writing a perl module that will be passed queries from other scripts and use DBI to execute them on an Oracle Database. The problem I have is when it comes to the return.
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
my $count = 0;
my $cfmt; #field format/alignment
my @harr; #array to store hypens
my %hash1; #hash to store fieldnumber <-> fieldtypename pairs
my %hash2; #hash to store fieldnumber <-> fieldlength pairs
my $dbh = DBI->connect("dbi:Oracle://orasrv1:1521/orcl",'scott','tiger');
my $SQL = "SELECT * FROM EMP";
my $sth = $dbh->prepare($SQL);
$sth->execute;
my $nof = $sth->{NUM_OF_FIELDS};
for ( my $i = 0; $i < $nof; $i++ ) {
my $name = $sth->{NAME}[$i];
my $type = $sth->{TYPE}[$i];
my $tn=$dbh->type_info($type)->{TYPE_NAME};
my $prec = $sth->{PRECISION}[$i];
# special treatment for field type DATE, because it returns no field length
if ($tn eq "DATE") {
$prec = 10;
} else {
$prec = length($name) if (length($name) > $prec);
}
$hash1{"$i"} = "$tn";
$hash2{"$i"} = "$prec";
# print header, all column names left aligned
$cfmt = "%-".$prec."s";
printf("$cfmt ", $name);
push @harr, '-'x$prec;
}
print "\n";
# print line with hypens under the header
print join(" ", @harr) . "\n";
while ( my @row = $sth->fetchrow_array() ) {
foreach (@row) {
$_ = "NULL" if !defined($_);
# left align if field type is VARCHAR2, else right align
my $ftyp = $hash1{"$count"};
my $flen = $hash2{"$count"};
if ($ftyp eq "VARCHAR2") {
$cfmt = "%-".$flen."s";
} else {
$cfmt = "%".$flen."s";
}
printf("$cfmt ", $_);
$count++;
}
print "\n";
$count=0;
}
$dbh->disconnect if defined($dbh);
DRL - useful perl script, I'm going to see if I can turn it into a package.
junior helper - When I ran your code in a test script I got the following complilation errors:
syntax error at test_DBI.pl line 63, near "condescend
# left align if field type is VARCHAR2, else right align
my "
Global symbol "$ftyp" requires explicit package name at test_DBI.pl line 63.
Global symbol "$ftyp" requires explicit package name at test_DBI.pl line 66.
syntax error at test_DBI.pl line 77, near "}"
test_DBI.pl had compilation errors.