Re: DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes

On Fri, 06 Aug 2010 09:56:36 +0100, Charles Jardine <[email protected]>
wrote:

> On 05/08/10 12:49, H.Merijn Brand wrote:
> 
> Snip
> 
> > $dbh->do (qq;
> >     create global temporary table t_foo (
> >         h_key	blob,
> >         h_value	blob
> >         );
> >     );
> > 
> > my $sth = $dbh->prepare ("select h_value from t_foo where h_key = ?");
> > $ perl test.pl
> > DBD::Oracle::db prepare failed: ORA-00932: inconsistent datatypes: expected - got BLOB (DBD ERROR: error possibly near <*> indicator at char 32 in 'select h_value from t_foo where <*>h_key = :p1') [for Statement "select h_value from t_foo where h_key = ?"] at test.pl line 23.
> 
> This is nothing to do with perl. You get this message from Oracle if you
> attempt to compare a blob with anything, including itself.

How ultimately useless. Yet another reason to hate Oracle.
Thanks for the heads-up though.

> Here is a simple demonstration using SQLPlus.
> 
>  Connected to:
>  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
>  SQL> create table t_foo (h_key   blob);
>  Table created.
>  SQL> select h_key from t_foo where h_key = h_key;
>  select h_key from t_foo where h_key = h_key
>                                *
>  ERROR at line 1:
>  ORA-00932: inconsistent datatypes: expected - got BLOB
> 
> It is a bad error message, but the rule is clear. This is
> from the section on Comparison Conditions in the Oracle 10.2
> SQL Reference Manual
> 
> > Large objects (LOBs) are not supported in comparison conditions.
> > However, you can use PL/SQL programs for comparisons on CLOB data.

-- 
H.Merijn Brand  http://tux.nl      Perl Monger  http://amsterdam.pm.org/
using 5.00307 through 5.12 and porting perl5.13.x on HP-UX 10.20, 11.00,
11.11, 11.23, and 11.31, OpenSuSE 10.3, 11.0, and 11.1, AIX 5.2 and 5.3.
http://mirrors.develooper.com/hpux/           http://www.test-smoke.org/
http://qa.perl.org      http://www.goldmark.org/jeff/stupid-disclaimers/