Unable to get data from oracle DB.Using DBI

[Rahul Gupta]

I am unable to fetch data from oracle db using DBI module

#!perl -w
use strict;
use DBI;
use DBD::Oracle;
my $host = "192.X.X.X";
my $sid = "mydb";
my $db = DBI->connect("dbi:Oracle:host=$host;sid=$sid;","SCOTT","tiger
+")
 or die "Cannot connect to server". $DBI::errstr;
$db->{AutoCommit}    = 0;
$db->{RaiseError}    = 1;
$db->{ora_check_sql} = 0;
$db->{RowCacheSize}  = 100; 
my $query = "select * from emp where status ='NEW'";
my $prepared_query = $db->prepare($query);
$prepared_query->execute
or die "SQL Error: $DBI::errstr\n";
while ( my @row = $prepared_query->fetchrow_array() ) {
    #print "@row";
    foreach (@row) {
        $_ = "\t" if !defined($_);
        print "$_\t";
    }
    print "\n";
}
 
END {
    $db->disconnect if defined($db);
}

when i am execuitng the above code it executes successfully without data.

please help me in this issue, because i invested 1 day on this issue without any success.!!

thanks in advance

[MidLifeXis]

Are there any errors? Do you ever get into the while loop? Does that query from something like sqlplus return anything? Help us help you.

[Tux]

Some random points ...

• Does table emp have any records at all?
• use DBD::Oracle; is unneeded;
• Include the attributes in connect
• Add PrintError, PrintWarn and ShowErrorStatement
• or die "SQL Error…" is useless under RaiseError
• disconnect is implicit on lexicals

use strict;
use DBI;
my $host = "192.X.X.X";
my $sid  = "mydb";
my $dbh  = DBI->connect ("dbi:Oracle:host=$host;sid=$sid;", "SCOTT", "
+tiger", {
    AutoCommit         => 0,
    RaiseError         => 1,
    ora_check_sql      => 0,
    RowCacheSize       => 100,
    PrintError         => 1,
    ChopBlanks         => 1,
    ShowErrorStatement => 1,
    FetchHashKeyName   => "NAME_lc",
    }) or die "Cannot connect to server". $DBI::errstr\n;

my $sth = $db->prepare ("select * from emp where status = 'NEW'");
$sth->execute;
while (my @row = $sth->fetchrow_array ()) {
    print join "\t" => map { $_ // "" } @row;
    print "\n";
    }

If you still don't get anything, you could add DBI->trace (5); or so.

[mje]

In addition to what everyone else has said comment out lines 9 (no need to turn off AutoCommit in this example), 11 (changing from default not a good idea when not required here and you are tracking down a problem) and 12 (leave it at the default) and rerun.