I have written this SQL but am getting the following error:
ORA-00932: inconsistent datatypes: expected - got BLOB
Here is SQL :
SELECT DISTINCT
file_name,
JPEG_IMG
FROM
table_repo
WHERE
file_name = 'K1A98'
AND JPEG_IMG IS NOT NULL;
It would have helped if you posted test case.
Sample table: note that it has a primary key column.
SQL> create table test
2 (id number primary key,
3 file_name varchar2(20),
4 jpeg_img blob
5 );
Table created.
Query which looks like yours, i.e. selects file name and the image (contents of the BLOB column) doesn't work, as you already know:
SQL> select distinct
2 file_name, jpeg_img
3 from test;
file_name, jpeg_img
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected - got BLOB
But, if you include the primary key, it works:
SQL> select distinct
2 id, file_name, jpeg_img
3 from test;
no rows selected
Of course, it doesn't make much sense because primary key must be unique anyway, so - if you applied such a code - you'd get all rows from the table.
Another option is to calculate BLOB column's length (if that's good enough for youe):
SQL> select distinct
2 file_name, dbms_lob.getlength(jpeg_img) jpeg
3 from test;
no rows selected
A better option is to calculate hash:
SQL> select distinct
2 file_name, dbms_crypto.hash(jpeg_img, 2) jpeg
3 from test;
no rows selected
SQL>
(Of course, nothing gets selected in my examples as test
table is empty; you should get some results).