aremaref has asked for the wisdom of the Perl Monks concerning the following question:

Hi! I'm trying to write a script to parse a large CSV and upload the contents to an Oracle 10g XE database. I'm running Strawberry Perl on a win32 machine and unfortunately there doesn't seem to be a lot of support for DBI modules. I managed to get DBD::Oracle off CPAN and install it, but when I try to test something as simple as a select call I just get this: DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select npi, entity_type_code from system.physician where npi = 1326041310"] at upload-npi.pl line 31. until I hit ^C. This is my code: 
#!usr/bin/perl

use DBI;
use strict;

my $dsn = 'DBI:Oracle:XE';
my $user = 'scott';
my $pass = 'tiger';
my $dbh;
my $sth;
$dbh = DBI->connect($dsn, $user, $pass) || die $dbh->errstr;

$sth = $dbh->prepare('select npi, entity_type_code from physician wher
+e npi = 1326041310');
$sth->execute();
while( my @line = $sth->fetchrow_array ) {print @line;}

$sth->finish();
$dbh->disconnect();

[download]
The table and the data I'm calling does exist in the database. I have a feeling the code is fine, and the problem has to do with my installation of the DBI module, but I'm not sure. Thanks!

Replies are listed 'Best First'.
Re: Can't execute statements with DBD::Oracle
by ikegami (Patriarch) on Jun 15, 2009 at 18:30 UTC
    I'd start by adding the argument { RaiseError => 1, PrintError => 0 } to the call to connect. It says it hasn't executed the statement, so it stands to reason it returned an error you didn't check. The above will cause subsequent errors to thrown an exception.
[reply]
[d/l]
[select]
      Okay, I just tried that, and now instead of printing it nonstop it just prints the same error once, followed by an uninformative "1", then exits. Not sure what to make of that.
[reply]
        No idea, and I don't see how that "1" is possible. How can the program print anything after it exited? (A print in the destructor of an object that gets destroyed during global destruction could do it, but you think DBI's objects wouldn't do that.)
[reply]
Re: Can't execute statements with DBD::Oracle
by afoken (Chancellor) on Jun 15, 2009 at 19:03 UTC

    I just get this: DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select npi, entity_type_code from system.physician where npi = 1326041310"] at upload-npi.pl line 31. until I hit ^C. This is my code:

    This may be your code, but it is not the code you run. This code has just 18 lines, perl would not report an error in line 31. Show us the real code.

    Alexander

    --
    Today I will gladly share my knowledge and experience, for there are no sweeter words than "I told you so". ;-)
[reply]
[d/l]
      Well, it's the same code, I just took out the comments. Edit: anyway, I reinstalled both DBI and DBD::Oracle and I think it's working now. Thanks. 
      #!usr/bin/perl

use DBI;
use strict;

#$ENV {"ORACLE_HOME"} ="C:\\oraclexe\\app\\oracle\\product\\10.2.0\\";
#my $file = "npi1.txt";

my $dsn = 'DBI:Oracle:XE';
my $user = 'scott';
my $pass = 'tiger';
my $dbh;
my $sth;
$dbh = DBI->connect($dsn, $user, $pass, {RaiseError => 1, PrintError =
+> 0}) || die $dbh->errstr;

#open(IN,$file);
#my $line = <IN>;

#my $count = 0;
#while (<IN>)

#{
#    my $n = 'physician';
#    $sth = $dbh->prepare('insert into :table-name (:values)');

    $sth = $dbh->prepare('select npi, entity_type_code from system.phy
+sician where npi = 1326041310');
#    $sth->bind_param(":table-name", $n);
#    $sth->bind_param(":values", $_);
    $sth->execute();
#    $count++;
    while( my @line = $sth->fetchrow_array ) {print @line;}
#}

#close(IN);

$sth->finish();
$dbh->disconnect();

      [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom