Oracle SQL Developer select query blob

Question

Does anyone know how i can select a blob(image in this case) from a column in my table? My query below throws this error:

ORA-00932: inconsistent datatypes: expected - got BLOB
00932. 00000 -  "inconsistent datatypes: expected %s got %s"

SELECT homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft,
       listagg(features.feature_name, ',') WITHIN GROUP (ORDER BY features.feature_name)  features, home_type.type_name, home_photo.photo, home_photo.description
FROM homes
INNER JOIN home_feature
    ON homes.home_id = home_feature.home_id
INNER JOIN home_type
    ON home_type.type_code = homes.type_code
INNER JOIN home_photo
    ON homes.home_id = home_photo.home_id
INNER JOIN features
    ON home_feature.feature_id = features.feature_id
INNER JOIN home_photo
    ON home_photo.home_id = homes.home_id
GROUP BY homes.title, homes.description, homes.living_room_count, homes.bedroom_count, homes.bathroom_count, homes.price, homes.sqft, home_type.type_name, home_photo.photo, home_photo.description;

Thank you

Frank Schmitt · Accepted Answer · 2013-01-16 10:40:07Z

3

You're getting this error because you are trying to use GROUP BY with a BLOB column. This is not possible in Oracle.

You should be able to resolve this by doing the grouping in a subquery and moving the JOIN with home_photo to an outer query:

select v1.*,
       home_photo.photo,
       home_photo.description
  from (SELECT homes.homes_id,
               homes.title,
               homes.description,
               homes.living_room_count,
               homes.bedroom_count,
               homes.bathroom_count,
               homes.price,
               homes.sqft,
               listagg(features.feature_name,
                       ',') WITHIN GROUP(ORDER BY features.feature_name) features,
               home_type.type_name
          FROM homes
         INNER JOIN home_feature
            ON homes.home_id = home_feature.home_id
         INNER JOIN home_type
            ON home_type.type_code = homes.type_code
         INNER JOIN features
            ON home_feature.feature_id = features.feature_id
         GROUP BY homes.homes_id,
                  homes.title,
                  homes.description,
                  homes.living_room_count,
                  homes.bedroom_count,
                  homes.bathroom_count,
                  homes.price,
                  homes.sqft,
                  home_type.type_name) v1
 INNER JOIN home_photo
    ON home_photo.home_id = v1.home_id

edited Jan 16, 2013 at 10:40

answered Jan 16, 2013 at 10:21

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1851487 Over a year ago

Hey thank you very much for the reply, but when i try and use your query i get this error form oracle: ORA-00904: "HOMES"."HOME_ID": invalid identifier

Frank Schmitt Over a year ago

Sorry, I forgot to fix the join condition; you need to replace homes.home_id with v1.home_id (I've updated my answer).

Collectives™ on Stack Overflow

Oracle SQL Developer select query blob

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related