Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

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

Comment on Unable to get data from oracle DB.Using DBI
Download Code
Re: Unable to get data from oracle DB.Using DBI
by MidLifeXis (Prior) 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.

    --MidLifeXis

Re: Unable to get data from oracle DB.Using DBI
by Tux (Monsignor) 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 (Deacon) 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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1058437]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others musing on the Monastery: (7)
As of 2014-07-12 15:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    When choosing user names for websites, I prefer to use:








    Results (240 votes), past polls