如何将长文本转换为Oracle 11G中的UTF8？

Question

我有大约20000年的大文字，需要转换为UTF8。

select convert('large text','UTF8') from dual

我有错误：

ora-01704：字符串字面太长

尝试此获取另一个错误，

declare 
    v_hugetxt varchar2(32767);
begin    
    v_hugetxt:='huge text'
    select convert(v_hugetxt,'UTF8') into v_hugetxt
                 from dual;
end;

我会收到错误

ORA-01460：未完成或不合理的转换请求提示· 不兼容的字符集可能会导致ORA-01460

Answer 1

我找到了解决方案，但起作用
参考

create or replace function v_blobtoclob(v_blob_in in blob) return clob is
    
      v_file_clob    clob;
      v_file_size    integer := dbms_lob.lobmaxsize;
      v_dest_offset  integer := 1;
      v_src_offset   integer := 1;
      v_blob_csid    number := dbms_lob.default_csid;
      v_lang_context number := dbms_lob.default_lang_ctx;
      v_warning      integer;
      v_length       number;
    
    begin
    
      dbms_lob.createtemporary(v_file_clob, true);
    
      dbms_lob.converttoclob(v_file_clob,
                             v_blob_in,
                             v_file_size,
                             v_dest_offset,
                             v_src_offset,
                             v_blob_csid,
                             v_lang_context,
                             v_warning);
    
      return v_file_clob;
    
    exception
      when others then
        dbms_output.put_line('Error found');
      
    end;

    CREATE OR REPLACE FUNCTION clob_to_blob (p_clob CLOB, p_charsetname VARCHAR2)
       RETURN BLOB
    AS
       l_lang_ctx      INTEGER := DBMS_LOB.default_lang_ctx;
       l_warning       INTEGER;
       l_dest_offset   NUMBER := 1;
       l_src_offset    NUMBER := 1;
       l_return        BLOB;
    BEGIN
       DBMS_LOB.createtemporary (l_return, FALSE);
       DBMS_LOB.converttoblob (
          l_return,
          p_clob,
          DBMS_LOB.lobmaxsize,
          l_dest_offset,
          l_src_offset,
          CASE WHEN p_charsetname IS NOT NULL THEN NLS_CHARSET_ID (p_charsetname) ELSE DBMS_LOB.default_csid END,
          l_lang_ctx,
          l_warning);
    
       RETURN l_return;
    END;

declare 
    v_hugetxt clob; 
    v_hugetxt2 blob; 
    v_hugetxt3 clob; 
    offset int;
    clobsize number;    
begin    
    v_hugetxt:='huge text';    
    select bnf.clob_to_blob(v_hugetxt,'UTF8') into v_hugetxt2 from dual;   
    select bnf.v_blobtoclob(v_hugetxt2) into v_hugetxt3 from dual;
    dbms_output.put_line('Print CLOB');
    offset:=1;
    loop  
      exit when offset > dbms_lob.getlength(v_hugetxt3);  
      dbms_output.put_line(dbms_lob.substr(v_hugetxt3, 255, offset));  
      offset := offset + 255;  
    end loop;   
    DBMS_OUTPUT.PUT_LINE('clob  out = ' || v_hugetxt3);
end;

仅供