无法假脱机行大小为 > oracle 中行大小为 32676 的 clob 列

Question

我尝试过假脱机，也使用dbms_lob。仍然无法导出长度为45000的clob列

下面是代码:

spool文件头

set head off将验证设置为关闭设置回波设置页面0设置长度20000设置长度20000设置第32767行开启微调阀设置反馈设置终止

——util script

SET serveroutput ON
DECLARE
l_file utl_file.file_type;
l_clob CLOB;
l_buffer VARCHAR2(32767);
l_amount binary_integer := 32767;
l_pos INTEGER           := 1;
BEGIN
SELECT mo_xml
INTO l_clob
FROM
( SELECT mo_xml, LENGTH(mo_xml) LEN FROM test_xml ORDER BY LEN ASC
)
WHERE rownum =1;
l_file      := utl_file.fopen('test', 'TEMP.TXT', 'w', 32767);
LOOP
dbms_lob.read (l_clob, l_amount, l_pos, l_buffer);
utl_file.put(l_file, l_buffer);
utl_file.fflush(l_file);
l_pos := l_pos + l_amount;
END LOOP;
EXCEPTION
WHEN no_data_found THEN
-- Expected end.
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
WHEN OTHERS THEN
IF utl_file.is_open(l_file) THEN
utl_file.fclose(l_file);
END IF;
raise;
END;
/

Answer 1

尝试使用DBMS_LOB。CLOB2FILE。

declare
v_clob clob;
begin
select mo_xml
into v_clob
from (select mo_xml from test_xml order by dbms_lob.getlength(mo_xml))
where rownum = 1;
--In 12.2 and above:
dbms_lob.clob2file(v_clob, flocation => 'test', fname => 'TEMP.TXT');
--In 11.2 and below the procedure is in a different package:
--dbms_xslprocessor.clob2file(v_clob, flocation => 'TEMP_DIR', fname => 'TEST.TXT');
end;
/

Answer 2

很抱歉这么说，但即使是官方文档的utl_file。表示缓冲区参数的最大大小(它是第二个参数，其中您的"l_buffer"Goes)是32767字节。我认为你必须先拆分行，然后在

之后将它们逐个刷新到一个文件中。