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
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
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 TRUNC(SUM(DBMS_LOB.GETLENGTH(image))/1024/1024,2) image_size
Hope this post helps you on how to convert. comments please.