奇怪的 Oracle XMLType.getClobVal() 结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13534264/
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
Strange Oracle XMLType.getClobVal() result
提问by Mikosz
I use Oracle 11g (on Red Hat). I have simple regular table with XMLType column:
我使用 Oracle 11g（在 Red Hat 上）。我有一个带有 XMLType 列的简单常规表：
CREATE TABLE PROJECTS
(
PROJECT_ID NUMBER(*, 0) NOT NULL,
PROJECT SYS.XMLTYPE,
);
Using Oracle SQL Developer (on Windows) I do:
使用 Oracle SQL Developer（在 Windows 上）我这样做：
select T1.PROJECT P1 from PROJECTS T1 where PROJECT_ID = '161';
It works. I get one cell. I can double click and download whole XML file.
有用。我得到一个细胞。我可以双击并下载整个 XML 文件。
Then I tried to get result as CLOB:
然后我尝试将结果作为 CLOB：
select T1.PROJECT.getClobVal() P1 from PROJECTS T1 where PROJECT_ID = '161';
It works. I get one cell. I can double click and see whole text and copy it. BUT there is a problem. When I copy it to clipboard I get only first 4000 characters. It seems that there is 0x00 character at position 4000 and the rest of CLOB is not copied.
有用。我得到一个细胞。我可以双击并查看整个文本并复制它。但有一个问题。当我将它复制到剪贴板时，我只得到前 4000 个字符。似乎在位置 4000 处有 0x00 字符，并且没有复制 CLOB 的其余部分。
To confirm this, I wrote check in java:
为了确认这一点，我在java中写了检查：
// ... create projectsStatement
Reader reader = projectsStatement.getResultSet().getCharacterStream( "P1" );
BufferedReader bf = new BufferedReader( reader );
char buffer[] = new char[ 1024 ];
int count = 0;
int globalPos = 0;
while ( ( count = bf.read( buffer, 0, buffer.length ) ) > 0 )
for ( int i = 0; i < count; i++, globalPos++ )
if ( buffer[ i ] == 0 )
throw new Exception( "ZERO at " + Integer.toString(globalPos) );
Reader returns full XML but my exception is thrown because there is null character at position 4000. I could remove this single byte but this would be rather strange workaround.
Reader 返回完整的 XML 但我的异常被抛出，因为在位置 4000 处有空字符。我可以删除这个单个字节，但这将是相当奇怪的解决方法。
I don't use VARCHAR2 there but maybe this problem is related to VARCHAR2 limitation (4000 bytes) somehow ? Any other ideas ? Is this an Oracle bug or am I missing something ?
我不在那里使用 VARCHAR2 但也许这个问题与 VARCHAR2 限制（4000 字节）有关？还有其他想法吗？这是 Oracle 错误还是我遗漏了什么？
-------------------- Edit --------------------
- - - - - - - - - - 编辑 - - - - - - - - - -
Value was inserted using following stored procedure:
使用以下存储过程插入值：
create or replace
procedure addProject( projectId number, projectXml clob ) is
sqlstr varchar2(2000);
begin
sqlstr := 'insert into projects ( PROJECT_ID, PROJECT ) VALUES ( :projectId, :projectData )';
execute immediate sqlstr using projectId, XMLTYPE(projectXml);
end;
Java code used to call it:
用来调用它的Java代码：
try ( CallableStatement cs = connection.prepareCall("{call addProject(?,?)}") )
{
cs.setInt( "projectId", projectId );
cs.setCharacterStream( "projectXml", new StringReader(xmlStr) , xmlStr.length() );
cs.execute();
}
-------------------- Edit. SIMPLE TEST --------------------
- - - - - - - - - - 编辑。简单测试 -----
I will use all I learned from your answers. Create simplest table:
我将使用我从你的答案中学到的一切。创建最简单的表：
create table T1 ( P XMLTYPE );
Prepare two CLOBs with XMLs. First with null character, second without.
准备两个带有 XML 的 CLOB。第一个带有空字符，第二个没有。
declare
P1 clob;
P2 clob;
P3 clob;
begin
P1 := '<a>';
P2 := '<a>';
FOR i IN 1..1000 LOOP
P1 := P1 || '0123456789' || chr(0);
P2 := P2 || '0123456789';
END LOOP;
P1 := P1 || '</a>';
P2 := P2 || '</a>';
Check if null is in the first CLOB and not in the second one:
检查 null 是否在第一个 CLOB 中而不在第二个中：
DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P1, chr(0) ) );
DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P2, chr(0) ) );
We will get as expected:
我们将得到：
14
0
Try to insert first CLOB into XMLTYPE. It will not work. It is not possible to insert such value:
尝试将第一个 CLOB 插入 XMLTYPE。不起作用。不可能插入这样的值：
insert into T1 ( P ) values ( XMLTYPE( P1 ) );
Try to insert second CLOB into XMLTYPE. It will work:
尝试将第二个 CLOB 插入 XMLTYPE。它会起作用：
insert into T1 ( P ) values ( XMLTYPE( P2 ) );
Try to read inserted XML into third CLOB. It will work:
尝试将插入的 XML 读入第三个 CLOB。它会起作用：
select T.P.getClobVal() into P3 from T1 T where rownum = 1;
Check if there is null. There is NO null:
检查是否有空值。没有空值：
DBMS_OUTPUT.put_line( DBMS_LOB.INSTR( P3, chr(0) ) );
It seams that there is no null inside database and as long as we are in the PL/SQL context, there is no null. But when I try to use following SQL in SQL Developer ( on Windows ) or in Java ( on Red Hat EE and Tomcat7 ) I get null character at position 4000 in all returned CLOBs:
看来数据库内部没有空值，只要我们在 PL/SQL 上下文中，就没有空值。但是，当我尝试在 SQL Developer（在 Windows 上）或 Java（在 Red Hat EE 和 Tomcat7 上）中使用以下 SQL 时，我在所有返回的 CLOB 中的位置 4000 处得到空字符：
select T.P.getClobVal() from T1 T;
BR, JM
BR, JM
回答by DazzaL
its not an Oracle bug (it stores and retrieves the \0 just fine. its a client/windows bug (Different clients behave differently in regards to "NUL" as does windows)
它不是 Oracle 错误（它可以很好地存储和检索 \0。它是客户端/Windows 错误（不同客户端在“NUL”方面的行为与 Windows 不同）
chr(0) is not a valid character in non-blobs really (i'm curious how you ever get the XMLType to accept it in the first place as usually it wouldnt parse).
chr(0) 实际上不是非 blob 中的有效字符（我很好奇您是如何让 XMLType 接受它的，因为它通常不会解析）。
\0 is used in C to denote the end of a string (NUL terminator) and some GUIs would stop processing the string at that point. For example:
\0 在 C 中用于表示字符串的结尾（NUL 终止符），并且某些 GUI 会在该点停止处理字符串。例如：
然而蟾蜍在这方面惨败：
sql developer fares better, as you can see it:
正如您所看到的，sql 开发人员的表现更好：
but if you copy it, the clipboard will only copy it up to the nul character. this copy paste error isn't SQL developers fault though, its a problem with windows clipboard not allowing NUL to paste properly.
但是如果你复制它，剪贴板只会将它复制到 nul 字符。这个复制粘贴错误不是 SQL 开发人员的错，它是 Windows 剪贴板的问题，不允许 NUL 正确粘贴。
you should just replace(T1.PROJECT.getClobVal(), chr(0), null)to get round this when using sql developer/windows clipboard.
replace(T1.PROJECT.getClobVal(), chr(0), null)在使用 sql developer/windows 剪贴板时，您应该只是为了解决这个问题。
回答by Mark
I also was experiencing this same issue exactly as described by Mikosz (seeing an extra 'NUL' character around the 4000th character when outputting my XMLType value as a Clob). While playing around in SQLDeveloper I noticed an interesting workaround. I was trying to see the output of my XMLType, but was tired of scrolling to the 4000th character, so I started wrapping the Clob output in a substr(...). Much to my surprise, the issue actually disappeared. I incorporated this into my Java app and confirmed that the issue was no longer present and my Clob could be retrieved without the extra character. I know that this isn't an ideal workaround, and I'm still not sure why it works (would love if someone could explain it to me), but here's an abbreviated example of what I've currently got working:
我也遇到了与 Mikosz 描述的完全相同的问题（在将我的 XMLType 值输出为 Clob 时，在第 4000 个字符周围看到一个额外的“NUL”字符）。在玩 SQLDeveloper 时，我注意到一个有趣的解决方法。我试图查看我的 XMLType 的输出，但厌倦了滚动到第 4000 个字符，所以我开始将 Clob 输出包装在 substr(...) 中。令我惊讶的是，这个问题实际上消失了。我将其合并到我的 Java 应用程序中，并确认问题不再存在，并且可以在没有额外字符的情况下检索我的 Clob。我知道这不是一个理想的解决方法，我仍然不确定它为什么起作用（如果有人能向我解释它会很高兴），但这里有一个我目前正在工作的简短示例：
// Gets the xml contents
String sql = "select substr(x.xml_content.getClobVal(), 0) as xml_content from my_table x";
ps = con.prepareStatement(sql);
if(rs.next()) {
Reader reader = new BufferedReader(rs.getCharacterStream("xml_content"));
...
}
回答by Clear
Bug:14781609 XDB: XMLType.getclobval() returns a temporary LOB when XML is stored in a CLOB. fix in patchset 11.2.0.4
错误：14781609 XDB：当 XML 存储在 CLOB 中时，XMLType.getclobval() 返回临时 LOB。在补丁集 11.2.0.4 中修复
and another solution if read as blob, then no error like
和另一个解决方案，如果读取为 blob，则没有错误
T1.PROJECT.getBlobVal(nls_charset_id('UTF8'))
回答by Ben
Easy enough to verify if it's the .getClobVal()call or not - perform an INSTRtest in PL/SQL (not Java) on your resultant CLOB to see if the CHR(0)exists or not.
很容易验证它是否是.getClobVal()调用 -INSTR在 PL/SQL（不是 Java）中对生成的 CLOB执行测试以查看是否CHR(0)存在。
If it does not, then I would point the finger at your Oracle client install.
如果没有，那么我将指责您的 Oracle 客户端安装。
