CODEWITHSUNDEEP
oracle10g
ipavlic
ipavlic

Reputation: 4966

Copy current CLOB column to new BLOB column in Oracle

I have a table with a CLOB column. I'd like to convert the existing data to a BLOB datatype. That fails with ORA-252858 invalid alteration of datatype.

I though about creating a new BLOB column, copying the existing data to it, and then deleting the existing CLOB column.

How can I copy from a CLOB column to a BLOB column?

Upvotes: 2

Views: 19905

Answers (2)

Roeland Van Heddegem
Roeland Van Heddegem

Reputation: 1735

I have my information from Quest: Oracle: How to convert CLOB to BLOB

Copied code here to avoid dead links:

CREATE OR REPLACE FUNCTION clob_to_blob(p_clob IN CLOB) RETURN BLOB IS
  v_blob BLOB;
  v_offset NUMBER DEFAULT 1;
  v_amount NUMBER DEFAULT 4096;
  v_offsetwrite NUMBER DEFAULT 1;
  v_amountwrite NUMBER;
  v_buffer VARCHAR2(4096 CHAR);
BEGIN
  dbms_lob.createtemporary(v_blob, TRUE);

  Begin
    LOOP
      dbms_lob.READ (lob_loc => p_clob,
                     amount  => v_amount,
                     offset  => v_offset,
                     buffer  => v_buffer);

      v_amountwrite := utl_raw.length (r => utl_raw.cast_to_raw(c => v_buffer));

      dbms_lob.WRITE (lob_loc => v_blob,
                      amount  => v_amountwrite,
                      offset  => v_offsetwrite,
                      buffer  => utl_raw.cast_to_raw(v_buffer));

      v_offsetwrite := v_offsetwrite + v_amountwrite;

      v_offset := v_offset + v_amount;
      v_amount := 4096;
    END LOOP;
  EXCEPTION
    WHEN no_data_found THEN
      NULL;
  End;
  RETURN v_blob;
END clob_to_blob;

This worked for me, even when dbms_lob.converttoclob didn't.

Upvotes: 0

Gaurav Soni
Gaurav Soni

Reputation: 6336

create table temp(col_clob clob,col_blob blob);

insert into temp (col_clob) values('hi i am gaurav soni');
insert into temp (col_clob) values('hi i am gaurav soni');
insert into temp (col_clob) values('hi i am gaurav soni');
insert into temp (col_clob) values('hi i am gaurav soni');
insert into temp (col_clob) values('hi i am gaurav soni');

/you need to create a function which will convert clob to blob as shown below: frankly speaking i have taken this function from another source ,but you'll get confused because there is a discussion going on that forum ,that why i dint mentioned here /

create or replace function CLOB_TO_BLOB (p_clob CLOB) return BLOB
as
 l_blob          blob;
 l_dest_offset   integer := 1;
 l_source_offset integer := 1;
 l_lang_context  integer := DBMS_LOB.DEFAULT_LANG_CTX;
 l_warning       integer := DBMS_LOB.WARN_INCONVERTIBLE_CHAR;
BEGIN

  DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);
  DBMS_LOB.CONVERTTOBLOB
  (
   dest_lob    =>l_blob,
   src_clob    =>p_clob,
   amount      =>DBMS_LOB.LOBMAXSIZE,
   dest_offset =>l_dest_offset,
   src_offset  =>l_source_offset,
   blob_csid   =>DBMS_LOB.DEFAULT_CSID,
   lang_context=>l_lang_context,
   warning     =>l_warning
  );
  return l_blob;
END;

--update the col_blob with the function we have created above 

 update temp set col_blob = clob_to_blob(col_clob);

select * from temp;

OUTPUT

 COL_CLOB                               COL_BLOB           
-------------------------------------- -------------------
hi i am gaurav soni                    hi i am gaurav soni
hi i am gaurav soni                    hi i am gaurav soni
hi i am gaurav soni                    hi i am gaurav soni
hi i am gaurav soni                    hi i am gaurav soni
hi i am gaurav soni                    hi i am gaurav soni

Upvotes: 3

Related Questions