Bug #33873 for DBD-Oracle: DBD Oracle doesn't know how to bind

Fri Mar 07 03:52:46 2008 xiaoou.wu [...] oracle.com - Ticket created

Subject:

DBD Oracle doesn't know how to bind_param with "INTERVAL ? MINUTE"

selectrow_array("SELECT SYSDATE + INTERVAL ? MINUTE FROM DUAL",undef,8); will not be correctly executed, because Oracle DBD will make it as "SELECT SYSDATE + INTERVAL 8 MINUTE FROM DUAL;", but not "SELECT SYSDATE + INTERVAL '8' MINUTE FROM DUAL;"

Fri Mar 07 06:58:57 2008 champoux [...] pythian.com - Taken

Fri Mar 07 06:59:59 2008 champoux [...] pythian.com - Correspondence added

Can you tell me which OS (win, Lunix sun?) and which version of Perl, DBI and DBD you are using?

Fri Mar 07 07:00:01 2008 The RT System itself - Status changed from 'new' to 'open'

Fri Mar 07 07:00:02 2008 champoux [...] pythian.com - Status changed from 'open' to 'stalled'

Fri Mar 07 09:09:45 2008 xiaoou.wu [...] oracle.com - Correspondence added

From:

xiaoou.wu [...] oracle.com

Show quoted text

> Can you tell me which OS (win, Lunix sun?) and which version of Perl, > DBI and DBD you are using?

OS: Linux FC8 i386 Perl: 5.8.8 DBI: v1.58 DBD: v1.19 Oracle: 10g XE

Fri Mar 07 09:09:46 2008 The RT System itself - Status changed from 'stalled' to 'open'

Fri May 23 21:04:10 2008 champoux [...] pythian.com - Correspondence added

From:

PYTHIAN [...] cpan.org

After a little searching in the OCI docs I discovered that OCI (what DBD and anything else that works with ORacle uses) does not allow this sort of bind of what they term a INTERVAL LITERAL. My guess is they want to stop SQL injection attacks. or maybe something else It is a case of 'You can't get there from here' However there is a workaround for binding try the NUMTODSINTERVAL function like this $sth = $dbh->prepare("SELECT SYSDATE + NUMTODSINTERVAL(?, 'MINUTE') FROM DUAL"); $sth->bind_param(1,20); $sth->execute; while (my( $value ) = @{$sth->fetch || []}) { print "time: 20 mins from now it =$value length". "\n"; } This works like a charm. I could force DBD::Oracle to bind in this way however it would be so far off Oracle PSQL spec it would only cause confusion and would not be part of any Standard way of doing things. cheers John Scoles

Fri May 23 21:06:12 2008 champoux [...] pythian.com - Correspondence added

Not a bug working to PSQL and OCI spec

Fri May 23 21:06:14 2008 champoux [...] pythian.com - Status changed from 'open' to 'rejected'

Bug #33873 for DBD-Oracle: DBD Oracle doesn't know how to bind_param with "INTERVAL ? MINUTE"

Preferred bug tracker