Skip Menu |
 

Preferred bug tracker

Please visit the preferred bug tracker to report your issue.

This queue is for tickets about the DBD-Oracle CPAN distribution.

Report information
The Basics
Id: 38749
Status: resolved
Priority: 0/
Queue: DBD-Oracle

People
Owner: Nobody in particular
Requestors: tomas.zemres [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: Normal
Broken in: 1.22
Fixed in: 1.23



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
Download bugtest.out.verbose
application/octet-stream 3.9k

Message body not shown because it is not plain text.

Subject: bugtest.pl
Download bugtest.pl
text/x-perl 1.1k
#!/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
Download bugtest.out
application/octet-stream 534b

Message body not shown because it is not plain text.

Download (untitled) / with headers
text/plain 238b
This is now fixed in Trunk which can be found here http://svn.perl.org/modules/dbd-oracle/trunk I have also added a new handle flag ora_oci_success_warn in case you actually want to know about silent warnings like the one in the ticket_


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.