Tuesday, September 3, 2013

Converting a varchar2 column to blob or clob

In order to convert a table column from varchar2 to blob or clob do the following :

VARCHAR2 -> BLOB

drop table test purge;

create table test
  (a varchar2(1000)
) ;

insert into test
select table_name from user_tables where rownum < 5;

commit;

select * from test;

alter table test add blob_f blob;

update test set blob_f = utl_raw.cast_to_raw(a);
commit;

alter table test drop column a;

alter table test rename column blob_f to a;

 select utl_raw.cast_to_varchar2(a) from test;

VARCHAR2 -> CLOB

drop table test purge;

create table test
   (a varchar2(1000)
) ;

insert into test
select table_name from user_tables where rownum < 5;
commit;

select * from test;

alter table test add clob_f clob;

update test set clob_f = a;
commit;

alter table test drop column a;

alter table test rename column clob_f to a;


 select a from test;

2 comments:

  1. i want to ask about the blob to varchar2 conversion, you know how to do that?

    ReplyDelete
    Replies
    1. It depends what are the contents of the blob. Assuming you know that there is only text in it (no images , no files , no binary data in general) then :
      If the field is type of CLOB then you can treat it as any text column (char, varchar). Otherwise you must use dbms_lob package or Java to read the contents of the blob and update a varachar2 field.




      Delete