将CLOB字段转储到文件中? [英] Dumping CLOB fields into files?
问题描述
说您有桌子:
Column_name | data_type
Title | Varchar2
Text | CLOB
有一些行:
SomeUnkownMovie | A long time ago in a galaxy far, far away....(long text ahead)
FredMercuryBio | Awesomeness and stuff....(more long text)
有没有一种我可以查询的方式,因此它可以输出类似
Is there a way I could query that so it outputs files like
SomeUnkownMovie.txt
FredMercuryBio.txt
(和ofc,以及其各自的文本在内)
(and ofc, with their respective texts inside)
我认为这应该是一个简单易用的sqlplus脚本..尽管我不是一个:(
I reckon this should be a easy enough sqlplus script.. though I'm just not the one :(
谢谢!
推荐答案
此pl/sql代码应在oracle 11g中运行. 它将Clob的文本转储到标题为文件名的目录中.
This pl/sql code should work in oracle 11g. It dumps the text of the clobs into a directory with the title as filename.
BEGIN
FOR rec IN (
select title, text
from mytable
)
LOOP
DBMS_XSLPROCESSOR.clob2file(rec.text, 'DUMP_SOURCES', rec.title ||'.txt');
END LOOP;
END;
如果没有DBMS_XSLPROCESSOR,则可以使用使用UTL_FILE的过程替换DBMS_XSLPROCESSOR.clob2file.
If DBMS_XSLPROCESSOR isn't available then you could replace DBMS_XSLPROCESSOR.clob2file with a procedure that uses UTL_FILE.
例如:
CREATE OR REPLACE PROCEDURE CLOB2FILE (
clob_in IN CLOB,
directory_name IN VARCHAR2,
file_name IN VARCHAR2
)
IS
file_handle UTL_FILE.FILE_TYPE;
clob_part VARCHAR2(1024);
clob_length NUMBER;
offset NUMBER := 1;
BEGIN
clob_length := LENGTH(clob_in);
file_handle := UTL_FILE.FOPEN(directory_name, file_name, 'W');
LOOP
EXIT WHEN offset >= clob_length;
clob_part := DBMS_LOB.SUBSTR (clob_in, 1024, offset);
UTL_FILE.PUT(file_handle, clob_part);
offset := offset + 1024;
END LOOP;
UTL_FILE.FFLUSH(file_handle);
UTL_FILE.FCLOSE(file_handle);
EXCEPTION
WHEN OTHERS THEN
UTL_FILE.FCLOSE(file_handle);
RAISE;
END;
或者用dbms_advisor.create_file替换DBMS_XSLPROCESSOR.clob2file.
Or perhaps replace DBMS_XSLPROCESSOR.clob2file with dbms_advisor.create_file.
这篇关于将CLOB字段转储到文件中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!