Beefy Boxes and Bandwidth Generously Provided by pair Networks
Syntactic Confectionery Delight

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 having an uproarious good time at the Monastery: (5)
As of 2017-12-15 08:46 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (423 votes). Check out past polls.