Oracle DB statement execute failing

Question

I am trying to connect to Oracle DB from a Perl script and fetching output from it. I wrote the below code

#!/usr/local/bin/perl

use DBI;
use warnings;
use strict;

sub retrieve_data {
    my ( $dbh, $rwnum ) = @_;
    my @row;
    my $selstmt = $dbh->prepare("select * from DIT_NOFUTURE_TMP1 where rownum < 5")
            or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";
    $selstmt->execute($rwnum);
    while ( @row = $selstmt->fetchrow_array ) {    # retrieve one row
        print join( ", ", @row ), "\n";
    }
}

my $dsn = "dbi:Oracle:DBInstance";
my $dbh = DBI->connect( $dsn, 'username', 'password', { AutoCommit => 0 } );
my $rno = 5;

unless ($dbh) {
    print "\nError : DBI connect failed: DBI:errstr\n";
    print "Error : Unable to connect to database $dsn\n";
    exit(-1);
}

retrieve_data $dbh , $rno;

exit(0);

Expected Output:

SQL> select * from DIT_NOFUTURE_TMP1 where rownum < 5;

ORDER_ID   ORDER_UNIT CUSTOMER_ID
---------- ---------- --------------
2534       2535       100000046
2560       2561       100000109
2523       2524       100000045
2525       2526       100000045

Output Received:

DBD::Oracle::st execute failed: called with 1 bind variables when 0 are needed [for Statement "select * from DIT_NOFUTURE_TMP1 where rownum<5"] at dbcon.pl line 13.
DBD::Oracle::st fetchrow_array failed: ERROR no statement executing (perhaps you need to call execute first) [for Statement "select * from DIT_NOFUTURE_TMP1 where rownum<5"] at dbcon.pl line 14.
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=135.208.32.97)(PORT= 1521))(CONNECT_DATA =(SERVER=DEDICATED)(SERVICE_NAME=PRDMOMS))) at dbcon.pl line 36.

Please suggest some solution for this.

choroba · Accepted Answer · 2016-05-16 14:21:05Z

5

As the error indicates, your statement handle doesn't expect any arguments (as there are no placeholders in the query).

If the number 5 is supposed to be replaced by the parameter, just change the query to

my $selstmt = $dbh->prepare('select * from DIT_NOFUTURE_TMP1 where rownum < ?')
    or die "Can't prepare SQL statement: ", $dbh->errstr(), "\n";

Otherwise, remove the parameter:

$selstmt->execute;

answered May 16, 2016 at 14:21

choroba

245k27 gold badges221 silver badges304 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Amar Shah Over a year ago

I tried removing the parameter and its working fine...thank you

Collectives™ on Stack Overflow

Oracle DB statement execute failing

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related