在 Oracle SQL 脚本中按原样打印 CLOB 内容
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1576478/
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
Print CLOB content out as is in Oracle SQL script
提问by Leo
To start with here is the bigger picture of the task I'm trying to do. I need to create a xml file from the results of the particular SQL request and store it in a file on the client computer. For that I have a SQL script that does the DBMS_XMLGen with xslt, which I'm going to run from a command line with sqlplus and pipe the output into a file.
从这里开始是我正在尝试做的任务的更大图景。我需要根据特定 SQL 请求的结果创建一个 xml 文件，并将其存储在客户端计算机上的文件中。为此，我有一个 SQL 脚本，它使用 xslt 执行 DBMS_XMLGen，我将使用 sqlplus 从命令行运行该脚本并将输出通过管道传输到文件中。
The problem I'm having now is that content of the XML code (stored in CLOB) has to be splitted into smaller chunks for DBMS_OUTPUT.PUT_LINE, and every chunk ends up with a new line character, breaking the structure of the XML code. I wonder if there's a way to print the content of a BLOB as is on the screen?
我现在遇到的问题是，必须将 XML 代码的内容（存储在 CLOB 中）拆分为 DBMS_OUTPUT.PUT_LINE 的较小块，并且每个块都以换行符结束，破坏了 XML 代码的结构。我想知道是否有办法在屏幕上打印 BLOB 的内容？
Here's the example of the SQL script:
这是 SQL 脚本的示例：
SET SERVEROUTPUT ON FORMAT WRAPPED;
set feedback off
DECLARE
v_ctx DBMS_XMLGen.ctxHandle;
v_xml CLOB;
v_xslt CLOB;
l_offset number := 1;
BEGIN
v_ctx := DBMS_XMLGen.newContext('SELECT * FROM TABLE');
-- DBMS_XMLGen.setXSLT(v_ctx, v_xslt); --not relevant here
v_xml := BMS_XMLGen(v_ctx);
DBMS_XMLGen.closeContext(v_ctx);
loop exit when l_offset > dbms_lob.getlength(v_xml);
DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 255, l_offset));
l_offset := l_offset + 255;
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
raise;
END;
/
The output I'm getting is correct apart from the new line character after every 255 symbols. And I can't just remove the end of lines later, I need the XML to be readable
除了每 255 个符号后的换行符之外，我得到的输出是正确的。而且我不能稍后删除行尾，我需要 XML 可读
Any ideas?
有任何想法吗？
Cheers, Leo
干杯，狮子座
回答by KAD
You can add a delimiter and print out 254 characters, then in notepad++ (in extended mode ~\r\n) replace this delimiter :
您可以添加一个分隔符并打印出 254 个字符，然后在 notepad++（在扩展模式下~\r\n）替换此分隔符：
loop exit when l_offset > dbms_lob.getlength(v_xml);
DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 254, l_offset) || '~');
l_offset := l_offset + 255;
end loop;
回答by Sergey Stadnik
One possible approach is to do the following:
一种可能的方法是执行以下操作：
- Create a database table with a single column and row of type CLOB.
- On server, insert the produced XML into that table.
On client run the SQL*PLus script like this:
SET WRAP OFF SET HEADING OFF SET ECHO OFF SPOOL file_name.xml SELECT your\_clob\_column FROM your\_table; SPOOL OFF
- 创建一个具有 CLOB 类型的单列和单行的数据库表。
- 在服务器上，将生成的 XML 插入到该表中。
在客户端上运行 SQL*PLus 脚本，如下所示：
SET WRAP OFF SET HEADING OFF SET ECHO OFF SPOOL file_name.xml SELECT your\_clob\_column FROM your\_table; SPOOL OFF
That will dump your XML into file_name.xml After that, you will need to truncate you table by issuing:
这会将您的 XML 转储到 file_name.xml 之后，您需要通过发出以下命令来截断您的表：
TRUNCATE TABLE your\_table DROP STORAGE;
otherwise the table won't shrink even if you delete the line with CLOB.
否则，即使您使用 CLOB 删除该行，该表也不会缩小。
回答by rene707
Check APC's CLOB workaround for dbms_output on http://forums.oracle.com/forums/thread.jspa?threadID=306557
在http://forums.oracle.com/forums/thread.jspa?threadID=306557上检查 APC 的 CLOB 解决方法 dbms_output
回答by Felypp Oliveira
Why don't you use DBMS_OUTPUT.PUTinstead of DBMS_OUTPUT.PUT_LINE? Give it a try. The trick is to add a newline character after printing your content. You can do it by calling DBMS_OUTPUT.NEW_LINE.
你为什么不使用DBMS_OUTPUT.PUT代替DBMS_OUTPUT.PUT_LINE？试一试。诀窍是在打印内容后添加换行符。您可以通过调用来实现DBMS_OUTPUT.NEW_LINE。
