asked on

how to Convert VARCHAR2 to BLOB

convert varchar2 column to BLOB !

Hi,
have few tables each with one or two description columns declared as VARCHAR2 ,which i need to convert to BLOB.
TO_LOB function is for converting LONG or LONG RAW to BLOB that too within a select statement which in turn is inside an INSERT INTO statement..
have to maintain data when i make the change.
Any help appreciated.

Thanks in advance..

ASKER CERTIFIED SOLUTION

grim_toaster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

Since a BLOB deals with uninterpreted data, the RAW type is the scalar type that is being used to write to the LOB using the DBMS_LOB.WRITE, DBMS_LOB.WRITE_APPEND procedure. So to add a VARCHAR2 column to a BLOB this column has to be converted to a RAW. This conversion is done using the UTL_RAW package.

The UTL_RAW.CAST_TO_RAW function converts a VARCHAR2 into a RAW datatype. The data is not modified, only its datatype is recast.

Syntax:
UTL_RAW.CAST_TO_RAW ( v_varIN VARCHAR2) RETURN RAW;

The UTL_RAW.CAST_TO_VARCHAR2 function does the opposite of the above function.
Syntax:
UTL_RAW.CAST_TO_VARCHAR2 (raw_var IN RAW) RETURN VARCHAR2.

After the VARCHAR2 to RAW conversion has been made the DBMS_LOB procedures WRITE and WRITEAPPEND may be used in combination with a BLOB.

skaykay

ASKER

Hi,

Thanks for the reply.
Tried your way by renaming existing column..it says "ORA-14155: missing PARTITION OR SUBPARTITION KEYWORD"
Is column renaming not possible ?

Thanks

dvabrinda

hi,
you will get error "ORA-14155: missing PARTITION OR SUBPARTITION KEYWORD" only when you forget to include column keyword in your query while renaming a column.
try this syntax
alter table table1
rename column col1 to col1

to rename the column

skaykay

ASKER

hi dvabrinda,

Thanks..was a silly thing to do..anyway had corrected and the column name is getting renamed...will try out teh conversion.
Thanks again