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 ...

[download]

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;

[download]

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

Replies are listed 'Best First'.
Re: Strange errors with DBD::Oracle and placeholders
by roboticus (Chancellor) 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.

    [download]
    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

    [download]

    ...roboticus

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

[reply]
[d/l]
[select]
Re: Strange errors with DBD::Oracle and placeholders
by morgon (Priest) 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.

[reply]

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

      s$$([},&%#}/&/]+}%&{})*;#$&&s&&$^X.($'^"%]=\&(|?*{%
      +.+=%;.#_}\&"^"-+%*).}%:##%}={~=~:.")&e&&s""`$''`"e
[reply]
[d/l]
[select]
Re: Strange errors with DBD::Oracle and placeholders
by mje (Curate) 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

    [download]

    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.

[reply]
[d/l]

Back to Seekers of Perl Wisdom