idea database 解析不了oracle11g 的blob 文本?

Question

本来是xml大文本的格式, blob格式的, 但是在idea解析不了这样的大文本格式, 怎个怎么解决

Answer 1

BLOB是二进制数据，要转换成CLOB类型才能显示文本信息
在12c以上版本，可以使用TO_CLOB函数将blob转换成clob,但要指定字符集及解析类型，参考官方文档

TO_CLOB (bfile|blob) https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/TO_CLOB-bfile-blob.html#GUID-FD7D58FE-B97C-4B75-85A9-5F82FB1DE96A

Example
The following hypothetical example returns the CLOB of a BFILE column value docu in table media_tab, which uses the character set with ID 873. It sets the MIME type to text/xml for the resulting CLOB.

SELECT TO_CLOB(docu, 873, 'text/xml') FROM media_tab;

如果是11g及以前的版本，只能自建函数进行转换了

create or replace function blob2clob(b       blob,
                                     charset varchar2 default 'UTF8')
  return clob as
  v_dest_offset  integer := 1;
  v_src_offset   integer := 1;
  v_lang_context integer := 0;
  v_warning      integer := 0;
  c              clob;
begin
  dbms_lob.createtemporary(c, false, dbms_lob.call);
  dbms_lob.converttoclob(dest_lob     => c,
                         src_blob     => b,
                         amount       => dbms_lob.LOBMAXSIZE,
                         dest_offset  => v_dest_offset,
                         src_offset   => v_src_offset,
                         blob_csid    => nls_charset_id(charset),
                         lang_context => v_lang_context,
                         warning      => v_warning);
  return c;
end;
/

select blob2clob(a) from  blob_test;