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.

2 comments:

  1. I am Sridevi Koduru, Senior Oracle Apps Trainer at Oracleappstechnical.com With 8 Yrs Exp on Oracle Apps and 13 Yrs IT Exp Providing Online Training on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K. I have Provided Training for 500+ Professionals Most of them are Working in Real Time now.

    Contact for (One to One Personal Online Training) on Oracle Apps Technical, Oracle Apps Financial Functional, Oracle Apps SCM, Oracle Apps HRMS, Oracle Financial for Indian Localization, SQL, PL/SQL and D2K at training@oracleappstechnical.com | sridevikoduru@oracleappstechnical.com | +91-9581017828 | http://www.oracleappstechnical.com

    Linkedin profile - http://in.linkedin.com/pub/sridevi-koduru/8b/76a/9b8/

    ReplyDelete
  2. Thank you very much. The post was lengthy, but I loved to read it till the last word. It was so nice blog and useful to Informatics learners.
    Oracle Fusion HCM Technical Training

    ReplyDelete