CODEWITHSUNDEEP
databaseoracle-databaseblobdistinct
Sara Selim
Sara Selim

Reputation: 467

Oracle: Blob column throws inconsistent datatypes

I have three oracle environments, and a query that runs successfully in two environments but throws ORA-00932: inconsistent datatypes: expected - got BLOB in the third environment.

The query

select
        distinct mytable.ID as ID1_130_,
        mytable.CODE as CODE2_130_,
        mytable.MY_LOGO as PRIMARY_8_130_,
    from
        MY_TABLE mytable;

The MY_LOGO column is BLOB

Can anyone explain why the query throw the exception only on the third Database or what I should check?

Upvotes: 2

Views: 2279

Answers (1)

wolφi
wolφi

Reputation: 8361

TL;DR: You need to rebuild the primary key in the third environment.

The error message is misleading (as so often with Oracle), it should rather be 'DISTINCT does not work with BLOBs'.

Your hint with Magnus Johansson was correct, it's the status of the primary key. Here is a minimal example:

CREATE TABLE t (id NUMBER CONSTRAINT pk PRIMARY KEY, b BLOB);
INSERT INTO t VALUES (1, EMPTY_BLOB());

SELECT DISTINCT works, as long as the primary key is selected, too. Possibly with the logic that if the primary key is involved, the rest of the columns, including the BLOB, are guaranteed to be distinct, too:

SELECT DISTINCT id, b FROM t;
1 (BLOB)

SELECT DISTINCT b FROM t;
ORA-00932: inconsistent datatypes: expected - got BLOB

This works only as long as the supporting index is healty. If you move (or restructure) the table, so that the index becomes unusable, you get your omnious error message:

ALTER TABLE t MOVE;
SELECT status from USER_INDEXES WHERE index_name='PK';
UNUSABLE

SELECT DISTINCT id, b FROM t;
ORA-00932: inconsistent datatypes: expected - got BLOB

Once you repair the index, it works again:

ALTER INDEX pk REBUILD;
SELECT status from USER_INDEXES WHERE index_name='PK';
VALID

SELECT DISTINCT id, b FROM t;
1 (BLOB)

Weird!

Upvotes: 2

Related Questions