clob类型值转blob类型并存入数据库中 oracle

– 第一步：新建CLOB的临时表，将BLOB字段的类型改成CLOB

CREATE TABLE CFS_TABLE_V2_19
( ID VARCHAR2(38) NOT NULL ,
FILE_BLOB CLOB NOT NULL
);

– 第二步：自定义函数：c2b函数

CREATE OR REPLACE FUNCTION C2B (b IN CLOB default empty_clob())
RETURN BLOB
– typecasts BLOB to CLOB (binary conversion)
IS
res BLOB;
b_len number := dbms_lob.getlength(b) ;
dest_offset1 NUMBER := 1;
src_offset1 NUMBER := 1;
amount_c INTEGER := DBMS_LOB.lobmaxsize;
blob_csid NUMBER := DBMS_LOB.default_csid;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
warning INTEGER;
BEGIN

if b_len > 0 then
DBMS_LOB.createtemporary (res, TRUE);
DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob (res,
b,
amount_c,
dest_offset1,
src_offset1,
blob_csid,
lang_ctx,
warning
);
else
select empty_blob() into res from dual ;
end if ;
RETURN res; – res is OPEN here
END C2B;

– 第三步：通过存储过程将数据放到临时表中

declare
v_clob_FILE_BLOB clob :=TO_CLOB(‘0M8R4KGxGuEAAAAAAAAAAAAAAAAAAAAAPgADAP7/CQAGAAAAAAAAAAAAAAABAAAAPwAAAAAAAAAAEAAAQQAAAAEAAAD+AAAAAD4AAADspcEAUwAJBAAA8FK/AAAAAAAAEAAAAAAACAAAUCYAAA4AYmpiahSuFK4AAAAAAAAAAAAAAAAAAAAAAAAECBYAMjAAAHbEdGl2xHRp9ggAAAAAAAArAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAD//w8AAAAAAAAAAAD//w8AAAAAAAAAAAD//w8AAAAAAAAAAAAAAAAAAAAAALcAAAAAAHoJAAAAAAAAegkAABYXAAAAAAAAFhcAAAAAAAAWFwAAAAAAABYXAAAAAAAAFhcAABQAAAAAAAAAAAAAAP8AAAAAKhcAAAAAAAAqFwAA’) || TO_CLOB(‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA==’);
begin
INSERT INTO CFS_GFA_REPORT_FILE_V2_19 (FILE_ID, SET_YEAR, MOF_DIV_CODE, MOF_DIV_NAME, REC_DIV_CODE, REC_DIV_NAME, REPORT_TYPE, FILE_NAME, FILE_BLOB, PARA_VERSION, PARA_VERSION_NAME, UPDATE_TIME, IS_DELETED, CREATE_TIME, IS_LAST_INST, SEND_DATE) VALUES (‘3ffe14e0f9c0d0f40fe8966081981995’, ‘2023’, ‘109900000’, ‘全国代码’, NULL, NULL, ‘09’, ‘2023年度部门决算分析报告撰写提纲（基层单位版）.doc’, v_clob_FILE_BLOB, ‘1’, NULL, to_date(‘2024-01-09 13:46:33’,‘yyyy-mm-dd hh24:mi:ss’), 2, to_date(‘2024-01-09 13:46:33’,‘yyyy-mm-dd hh24:mi:ss’), 1, to_date(‘2024-01-09 13:46:33’,‘yyyy-mm-dd hh24:mi:ss’));
end;

注意：若是该脚本执行报错：ORA-04030：进程中内存不足
则执行以下命令：
1》alter session set sort_area_size = 104857600;
2》alter system set sort_area_size = 104856700 scope=spfile;
3》create pfile from spfile;
4》若是执行以上三个命令中其中一个提示权限不足，则还有最后一个笨办法：直接将需要执行的大字段的值复制到txt中，然后再粘贴到数据库中对应的数据行中即可

– 第四步：将临时表中的数据经过c2b函数转换，放到真实的BLOB表中

INSERT INTO CFS_TABLE_V2 SELECT ID, c2b(FILE_BLOB) FROM CFS_TABLE_V2_19;