将当前的CLOB列复制到Oracle中的新BLOB列 [英] Copy current CLOB column to new BLOB column in Oracle

问题描述

我有一个带有CLOB列的表.我想将现有数据转换为BLOB数据类型. ORA-252858 invalid alteration of datatype失败.

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.

我想创建一个新的BLOB列，将现有数据复制到其中，然后删除现有的CLOB列.

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

如何从CLOB列复制到BLOB列?

推荐答案

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');

/您需要创建一个将clob转换为blob的函数，如下所示: 坦白地说，我已经从另一个来源获取了此功能，但是您会感到困惑，因为该论坛上正在进行讨论，这就是为什么我在这里提到的原因/

/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; 

输出

 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

这篇关于将当前的CLOB列复制到Oracle中的新BLOB列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！