| Subject: | Warning of a NULL column in an aggregate function |
Problem:
If I use query with COUNT(some_column_with_null_values) in PLSQL
procedure, then I give error message:
DBD::Oracle::st fetchrow_array failed: ORA-24347: Warning of a NULL
column in an aggregate function
COUNT(col) in SELECT expression works fine,
but same query in PLSQL procedure does not work.
This testing procedure works fine in sqlplus, but it does not work in perl.
Attached:
bugtest.pl - testing script for this bug with simple PLSQL procedure
bugtest.out - output from script (with ora_verbose=0)
bugtest.out.verbose - output from script (with ora_verbose=5)
Environment:
DBD::Oracle::VERSION = '1.22'
DBI::VERSION = "1.601"
perl v5.10.0 built for i686-linux
Linux vtsup 2.6.16.21-0.8-xen #1 SMP Mon Jul 3 18:25:39 UTC 2006 i686
GNU/Linux
oracle client lib: instantclient_11_1 (I have same problem with 10.2
client lib)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit
Production
Another user reported problem:
http://www.nntp.perl.org/group/perl.dbi.users/2008/08/msg33156.html
| Subject: | bugtest.out.verbose |
Message body not shown because it is not plain text.
| Subject: | bugtest.pl |
#!/usr/bin/perl -w
use strict;
use warnings;
=head1 NAME
DBD::Oracle bug test in counting not-null values in procedure
=head1 PLSQL-Procedure for this test
CREATE OR REPLACE PROCEDURE bugtest1(
list OUT SYS_REFCURSOR
)
AS
BEGIN
OPEN list FOR
SELECT COUNT(null) FROM dual;
END;
/
=cut
use DBI;
use DBD::Oracle qw(:ora_types);
my $dbh = DBI->connect("dbi:Oracle:test",
"test", "test",
{ AutoCommit => 0, RaiseError => 1, PrintError => 1, ora_verbose => 0 }
);
# Direct fetch from select works fine:
my $sth = $dbh->prepare('SELECT COUNT(null) FROM dual');
$sth->execute;
print "result1:\n";
while (my @row = $sth->fetchrow_array) {
print " row: @row\n";
}
print "\n\n";
# but fetch from cursor opened in procedure does not work
# if SELECT contains COUNT(column_with_null_values) expression
$sth = $dbh->prepare('BEGIN bugtest1(list => :list); END;');
$sth->bind_param_inout(':list', \my $list, 0, {ora_type => ORA_RSET});
$sth->execute;
print "result2:\n";
while ( my @row = $list->fetchrow_array ) {
print " row: @row\n";
}
print "\n\n";
| Subject: | bugtest.out |
Message body not shown because it is not plain text.