Tuesday, April 3, 2012

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.