Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling
 
PerlMonks  

Strange errors with DBD::Oracle and placeholders

by Skeeve (Vicar)
on Mar 28, 2012 at 11:58 UTC ( #962166=perlquestion: print w/ replies, xml ) Need Help??
Skeeve has asked for the wisdom of the Perl Monks concerning the following question:

I'm encountering strange errors when using placeholders in an sql query, using an Oracle database. For example this query gives me

DBD::Oracle::db prepare failed: ORA-00907: missing right parenthesis ( +DBD ERROR: error possibly near <*> indicator at char 114 in ' sele +ct (FROM_TZ( CAST(sysdate as timestamp) , :timezone ) <*>AT TIME ZONE :timezone ) AS MY_RESULT from dual ...

This is the code I use:

use strict; use warnings; use DBI; my $sid='MYDB'; my $user='myuser'; my $pass='mypass'; my $dbh = DBI->connect("dbi:Oracle:$sid", $user, $pass, { AutoCommit => 0, RaiseError => 0, PrintErro +r => 1 } ); my $sth= $dbh->prepare(<<SQL) or die $dbh->errstr; select (FROM_TZ( CAST(sysdate as timestamp) , :TIMEZONE ) AT TIME ZONE :TIMEZONE ) AS MY_RESULT from dual SQL $sth->bind_param(':TIMEZONE', 'Europe/Berlin') or die $dbh->errstr;

When I replace ":TIMEZONE" with the string "'Europe/Berlin'", it works, so it is not an issue of the query itself.

In the real production code I use, the error is even more strange, as it tells me something about illegal relational operators.

Is this behaviour somewhere documented in DBD::Oracle? I couldn't find it.

Thanks for your help.


s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
+.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Comment on Strange errors with DBD::Oracle and placeholders
Select or Download Code
Re: Strange errors with DBD::Oracle and placeholders
by roboticus (Canon) on Mar 28, 2012 at 12:52 UTC

    Skeeve:

    I tried a couple variations, but I get the same error. I even put the statement inside a package function. While the package compiles cleanly, I get a similar error when I run it:

    $ cat .SQL/glark.pks create or replace package glark as function gragnar(t in date, TZ1 in varchar2, TZ2 in varchar2) return timestamp with local time zone; end glark; $ cat .SQL/glark.pkb create or replace package body glark as function gragnar(t in date, TZ1 in varchar2, TZ2 in varchar2) return timestamp with local time zone is retval timestamp with local time zone; begin select (from_tz(cast(t as timestamp), TZ1) at time zone TZ2) i +nto retval from dual; return retval; end gragnar; end glark; $ cat t.pl #/usr/bin/perl use strict; use warnings; use feature ':5.10'; use XYZ::DBASE; use XYZ::Utils qw(dump_query); my $DB = XYZ::DBASE::connect('XYZDEV'); my $ST = $DB->prepare(<<SQL); select glark.gragnar(sysdate, :T, :U) from dual SQL $ST->bind_param(':T', 'Europe/Berlin'); $ST->bind_param(':U', 'Europe/Berlin'); $ST->execute; dump_query($ST); $ perl t.pl DBD::Oracle::st fetchall_arrayref failed: ORA-00923: FROM keyword not +found where expected ORA-06512: at "DEV_ROBO.GLARK", line 7 (DBD ERROR: OCIStmtFetch) [for +Statement "select glark.gragnar(sysdate, :T, :U) from dual " with ParamValues: :t='Europe/Berlin', :u='Europe/Berlin'] at /Work/P +erl/LIB/XYZ/Utils.pm line 342.
    My oracle version:

    select * from v$version BANNER ---------------------------------------------------------------- Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production PL/SQL Release 9.2.0.7.0 - Production CORE9.2.0.7.0Production TNS for Linux: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production 5 rows elapsed time: 0

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.

Re: Strange errors with DBD::Oracle and placeholders
by mje (Deacon) on Mar 28, 2012 at 13:22 UTC

    It appears to be the call:

    OCIStmtExecute(968d238,9699f44,968d2b4,0,0,0,0,mode=DESCRIBE_ONLY,16)= +ERROR

    which fails. It works with only the first parameter. I don't think this is a DBD::Oracle issue but I'm prepared to be proven wrong.

Re: Strange errors with DBD::Oracle and placeholders
by morgon (Deacon) on Mar 28, 2012 at 14:47 UTC
    It may be worth trying a different variable-name.

    "TIMEZONE" could be a built-in that maybe confuses the Oracle-parser.

      Thanks for the suggestion. I already tried it with "XYZ" but it failed in the same way.


      s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
      +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://962166]
Approved by zwon
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (12)
As of 2014-09-22 11:46 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    How do you remember the number of days in each month?











    Results (189 votes), past polls