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
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.