oracle 将 CLOB 字段转储到文件中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7311084/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Dumping CLOB fields into files?
提问by filippo
Say you have the table:
假设你有一张桌子：
Column_name | data_type
Title | Varchar2
Text | CLOB
with some rows:
有一些行：
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
(and ofc, with their respective texts inside)
（和ofc，里面有各自的文本）
I reckon this should be a easy enough sqlplus script.. though I'm just not the one :(
我认为这应该是一个足够简单的 sqlplus 脚本..虽然我不是那个 :(
thanks!
谢谢！
回答by LukStorms
This pl/sql code should work in oracle 11g. It dumps the text of the clobs into a directorywith the title as filename.
这个 pl/sql 代码应该在 oracle 11g 中工作。它将 clobs 的文本转储到标题为文件名的目录中。
BEGIN
FOR rec IN (
select title, text
from mytable
)
LOOP
DBMS_XSLPROCESSOR.clob2file(rec.text, 'DUMP_SOURCES', rec.title ||'.txt');
END LOOP;
END;
If DBMS_XSLPROCESSOR isn't available then you could replace DBMS_XSLPROCESSOR.clob2file with a procedure that uses UTL_FILE.
如果 DBMS_XSLPROCESSOR 不可用，那么您可以用使用 UTL_FILE 的过程替换 DBMS_XSLPROCESSOR.clob2file。
For example :
例如 ：
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;
Or perhaps replace DBMS_XSLPROCESSOR.clob2file with dbms_advisor.create_file.
或者用 dbms_advisor.create_file 替换 DBMS_XSLPROCESSOR.clob2file。
回答by Justin Cave
Are you trying to generate files on the database server file system? Or on the client file system?
您是否正在尝试在数据库服务器文件系统上生成文件？还是在客户端文件系统上？
If you are trying to generate files on the database server file system, there is an example of exporting a CLOB to a filein another StackOverflow thread that is based on Tim Hall's LOB export examples(Tim's site appears to be down at the moment).
如果您尝试在数据库服务器文件系统上生成文件，有一个示例 将 CLOB 导出到另一个 StackOverflow 线程中的文件，该线程基于 Tim Hall 的LOB 导出示例（Tim 的站点目前似乎已关闭）。
If you're trying to generate files on the client file system, it would involve much more complex SQLPlus scripting. You'd be looking at doing something like querying the table and using the data to dynamically generate one SQLPlus script per file that you wanted to generate and then dynamically calling those scripts. You'd be really pushing SQL*Plus's scripting capabilities so that's not an architecture that I would generally advocate but I believe it could be done.
如果您尝试在客户端文件系统上生成文件，则将涉及更复杂的 SQL Plus 脚本。您可能会考虑执行一些操作，例如查询表并使用数据为您想要生成的每个文件动态生成一个 SQLPlus 脚本，然后动态调用这些脚本。您将真正推动 SQL*Plus 的脚本功能，因此这不是我通常提倡的架构，但我相信它可以实现。
If you do need to generate files on the client file system, I'd generally prefer to use something other than SQLPlus. For example, there is an example of a small Java class that reads and writes CLOB and BLOB data to and from fileson the AskTom site. I'd tend to write a small Java utility that ran on the client and exported the data rather than trying to put too much logic in SQLPlus scripts.
如果您确实需要在客户端文件系统上生成文件，我通常更喜欢使用 SQL Plus以外的东西。例如，有一个小型 Java 类的示例，它在 AskTom 站点上的文件中读取和写入 CLOB 和 BLOB 数据。我倾向于编写一个在客户端上运行并导出数据的小型 Java 实用程序，而不是尝试在 SQLPlus 脚本中放入太多逻辑。
