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

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

-- 
Charles Jardine - Computing Service, University of Cambridge
[email protected]    Tel: +44 1223 334506, Fax: +44 1223 334679