LONG RAW to BLOB Conversion

We all know there's a function called TO_LOB which converts LONG to BLOB object.

But we might experience this rare case LONG_RAW to BLOB. Yes i encountered this problem when i want to execute this query in oracle apps 11i Oracle 9 database.

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2)||' MB'  bytes

FROM   per_images

Error:  ORA-00997: illegal use of LONG datatype

I was wondering how come the query is executing in R12 not in 11i..then i cross checked it

In oracle apps R12 the IMAGE column data type in PER_IMAGES table is  BLOB

whereas In oracle apps 11i the IMAGE column data type in PER_IMAGES table is LONG RAW

So the above query will not work in 11i..so i want to convert this long_raw to blob data type.

I tried this

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(TO_LOB(image)))/1024/1024,2)||' MB'  bytes

FROM   per_images

ORA-00932: inconsistent datatypes: expected - got BINARY

Note: If the size is less than 32k you will get the value.

Solution to get the length of the image column is:

CREATE TABLE XX_PER_IMAGES_TEMP (IMAGE BLOB);

INSERT INTO XX_PER_IMAGES_TEMP

                                 SELECT

                                   TO_LOB(IMAGE)

                                 FROM

                                    PER_IMAGES;

SELECT TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2) image_size

FROM

XX_PER_IMAGES_TEMP;

Hope this post helps you on how to convert. comments please.