Home » Open Source » Programming Interfaces » error in query (oracle10g)
error in query [message #685900] Mon, 18 April 2022 07:55 Go to next message
arun888
Messages: 85
Registered: June 2015
Location: INDIA
Member
Getting an error while reading the numbers from the oracle database.


#**********************************************************
#   Read Number from database.
#**********************************************************
sub get_open_us {
    my ($ret, $week) = @_;
    my %numbers;
    my $dbh = DBI->connect ('dbi:Oracle:bmfp', 'US', 'states', {PrintError => 1, AutoCommit => 0 });
    my $sth = $dbh->prepare(qq{ select ret_no
                                    from euro
                                    where ret_code = ?
                                      and start_week <= ?
                                      and end_week >= ? });
    $sth->execute ($ret, $week, $week);
    $dbh->disconnect;
    return \%numbers;
}


#**********************************************************
#  Main Program.
#**********************************************************

    my $week=1950;
    my $ret='edc';
    my $open = &get_open_us ($ret, $week);
    for my $numbers (sort keys %$open ) {
    if ( defined  $lookup{$numbers} ) {
         print "new number is $number";
    }
    }
	
Error :
DBI::db=HASH(0x405ed8c8)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at

Re: error in query [message #685901 is a reply to message #685900] Mon, 18 April 2022 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As it is said, call $sth->finish() before disconnect.

Re: error in query [message #685905 is a reply to message #685901] Wed, 20 April 2022 05:55 Go to previous message
arun888
Messages: 85
Registered: June 2015
Location: INDIA
Member
I tried the below query to save the numbers in the variable numbers. please confirm whether it is right.


#**********************************************************
#   Read Number from database.
#**********************************************************
sub get_open_us {
    my ($dbh, $ret, $week) = @_;
    my %numbers;
    my $sth = $dbh->prepare(qq{ select ret_no
                                    from euro
                                    where ret_code = ?
                                      and start_week <= ?
                                      and end_week >= ? });
    $sth->execute ($ret, $week, $week);
    $sth->bind_columns(\(my $number));
    while ($sth->fetch) {
    $numbers{$number} = $number;
    }
    $dbh->disconnect;
    return \%numbers;
}

#**********************************************************
#  Main Program.
#**********************************************************
my $dbh = DBI->connect ('dbi:Oracle:bmfp', 'US', 'states', {PrintError => 1, AutoCommit => 0 });
my $week=1950;
    my $ret='edc';
    my $open = &get_open_us ($dbh, $ret, $week);
    for my $numbers (sort keys %$open ) {
    if ( defined  $lookup{$numbers} ) {
         print "new number is $number";
    }
    }
	

Previous Topic: out of memory issue
Next Topic: insert the records into table perl
Goto Forum:
  


Current Time: Thu Dec 08 11:38:33 CST 2022