convert BLOB to varchar2 for inserting to table
741692Dec 17 2009 — edited Dec 17 2009Hi,
I have a table which has a column with BLOB datatype.
We are expecting the BLOB colunmn may contain data up to 12000 charectors.
I want to convert the BLOB column to VARCHAR2 datatype split the data and insert to three VARCHAR2 columns in a another table.
I tried to use UTL_RAW.CAST_TO_VARCHAR2() and dbms_lob.substr() as below but I could use maximum of 2000 charectors in dbms_lob.substr() function.
I tried the below SQL
select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(column_blob,32767,1)) from table_blob
which throws the error -> "ORA-06502: PL/SQL: Numeric or Value Error: raw variable length too long"
When I tried the below query is working but it is selecting only 2000 charectors.
select UTL_RAW.CAST_TO_VARCHAR2(dbms_lob.substr(column_blob,2000,1)) from table_blob
I read from referance that "the maximum length of a raw to use with UTL_RAW is 2000".
Any help on this regard is greatly appreciated.
Thans
Nikhil