Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked

Unable to get data from oracle DB.Using DBI

by Rahul Gupta (Sexton)
on Oct 16, 2013 at 12:59 UTC ( #1058437=perlquestion: print w/replies, xml ) Need Help??
Rahul Gupta has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: Unable to get data from oracle DB.Using DBI
by MidLifeXis (Monsignor) on Oct 16, 2013 at 13:36 UTC

    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.


Re: Unable to get data from oracle DB.Using DBI
by Tux (Abbot) on Oct 16, 2013 at 13:43 UTC

    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.

    Enjoy, Have FUN! H.Merijn
Re: Unable to get data from oracle DB.Using DBI
by mje (Curate) on Oct 16, 2013 at 14:31 UTC

    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.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1058437]
Approved by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (8)
As of 2018-03-22 15:04 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (278 votes). Check out past polls.