The problem: -----------
When Oracle matches placeholder names passed to OCIBindByName() with the actual placeholders in the SQL or PL/SQL, it uses a case-insensitive comparison. This means that Oracle regards the placeholders :a and :A as the same.
On the other hand, DBD::Oracle uses case-sensitive comparison when matching placeholder names, and regards :a and :A as different.
This has at least two undesirable effects.
1. If the statement contains two named placeholders which
differ only in letter_case, they will each contribute
to the count in $sth->{NUM_OF_PARAMS}, despite the fact
that this is meant to be a count of _distinct_
placeholders.2. The name passed to $sth->bind_param... must match in letter case at least one of the occurences of the placeholder in the statement. This is not what an OCI programmer would expect. Many of the examples in the OCI manual have the names passed to OCIBindByName() in upper case, and those in the PL/SQL in lower case.
Below is a tiny perl program which demonstrates this.
The proposed solution: ---------------------
I suggest that DBD::Oracle's handling of placeholder name comparisons should be changed to be case-insensitive.
Below is a patch to dbdimp.c (version 1.14) which achieves this by lower-casing the name both at preparse time and at bind time.
============================ #!/bin/perl -w use strict; use DBI;
my $dbh = DBI->connect('dbi:Oracle:', 'foo', 'bah',
{RaiseError=>1, PrintError=>0, AutoCommit=>0});
my $sth = $dbh->prepare(
"SELECT decode(:a, :A, 'same', 'different') from dual");# The following prints 2, indicating that DBD::Oracle # thinks that the above has two distinct placeholders
print $sth->{NUM_OF_PARAMS}, "\n";
$sth->bind_param(':a', 'lower');
$sth->bind_param(':A', 'upper');
$sth->execute;# The following prints 'same', indicating that Oracle # can't see the difference between the placeholders
print +($sth->fetch)->[0], "\n";
$sth->finish; $dbh->disconnect; ============================= --- dbdimp.c.orig Fri Oct 17 13:08:17 2003 +++ dbdimp.c Fri Oct 17 13:08:49 2003 @@ -812,7 +812,7 @@
} else if (isALNUM(*src)) { /* ':foo' */
while(isALNUM(*src)) /* includes '_' */
- *dest++ = *src++;
+ *dest++ = toLOWER(*src), src++;
style = ":foo";
} else { /* perhaps ':=' PL/SQL construct */
/* if (src == ':') *dest++ = *src++; XXX? move past '::'? */
@@ -1321,7 +1321,7 @@
SV **phs_svp;
STRLEN name_len;
char *name = Nullch;
- char namebuf[30];
+ char namebuf[32]; /* ':' + 30 char name + '\0' */
phs_t *phs; /* check if placeholder was passed as a number */
@@ -1329,7 +1329,13 @@
if (SvGMAGICAL(ph_namesv)) /* eg if from tainted expression */
mg_get(ph_namesv);
if (!SvNIOKp(ph_namesv)) {
+ int i;
name = SvPV(ph_namesv, name_len);
+ if (name_len>31)
+ croak("Placeholder name too long (%s)", neatsvpv(ph_namesv,0));
+ for (i=0; i<name_len; i++) namebuf[i] = toLOWER(name[i]);
+ namebuf[i] = '\0';
+ name = namebuf;
}
if (SvNIOKp(ph_namesv) || (name && isDIGIT(name[0]))) {
sprintf(namebuf, ":p%d", (int)SvIV(ph_namesv));===================================================
-- Charles Jardine - Computing Service, University of Cambridge [EMAIL PROTECTED] Tel: +44 1223 334506, Fax: +44 1223 334679
