oracle大类型clob、blob导出insert脚本

1、clob类型

使用to_char转为字符

select to_char(clob_type) from dual;

2、blob类型

使用to_blob

blob类型转为string

select 'to_blob(' || '''' || substr(utl_raw.cast_to_raw(utl_raw.cast_to_varchar2(dbms_lob.substr(blob_type))), 0) || '''' || ')' from dual;

得到二进制的字符，将blob_string 转换为blob类型数据

SELECT to_blob(('230D0A23546875204E6F762031372030303A30303A30302043535420323032320D0A63656E74657249643D310D0A75726C3D687474705C3A2F2F73732D696672732D646174616D67722F6A6F622F636865636B5F6C6F672F64656C6574655F636865636B5F6C6F670D0A6A6F624672657175656E63793D440D0A636F6F6B69653D0D0A63726F6E3D3020302030202A202A203F200D0A706172616D3D0D0A66756E63547970653D310D0A75736572436F64653D553030310D0A'))
  FROM dual;