将超过 4000 个字符的 XML 插入到 Oracle XMLTYPE 列中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2508987/
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
Insert XML with more than 4000 characters into a Oracle XMLTYPE column
提问by Marco
I have an oracle table with a column from type "SYS.XMLTYPE" and a storage procudure which is doing the insert:
我有一个 oracle 表，其中包含一个类型为“ SYS.XMLTYPE”的列和一个执行插入操作的存储程序：
(Short version):
（精简版）：
PROCEDURE InsertXML
(
pXMLData IN LONG
)
IS
BEGIN
INSERT INTO MY_TABLE (XML_DATA) VALUES(pXMLData);
END InsertXML;
I call this sp from my C# code with type "OracleType.LongVarChar".
我从我的 C# 代码中调用这个 sp 类型为“ OracleType.LongVarChar”。
Now the problem: If the xml has less than 4000 characters everything is working fine, but by using a xml with more than 4000 characters I get the following error:
现在的问题是：如果 xml 少于 4000 个字符，一切正常，但是通过使用超过 4000 个字符的 xml，我会收到以下错误：
ORA-20000: ORA-01461: can bind a LONG value only for insert into a LONG column
How can I handle this? Thx 4 answers
我该如何处理？谢谢 4 个回答
回答by Dan
回答by Jonathan L
You need to convert xml string for more than 4000 charcaters into SQLXML type first.
您需要先将 4000 多个字符的 xml 字符串转换为 SQLXML 类型。
Environment: jpa 2.1.0, eclipselink 2.5.2, oracle db 11gr2
环境：jpa 2.1.0、eclipselink 2.5.2、oracle db 11gr2
SQL:
查询语句：
CREATE TABLE "XMLTEST"
( "ID" NUMBER(10,0) NOT NULL ENABLE,
"DESCRIPTION" VARCHAR2(50 CHAR) NOT NULL ENABLE,
"XML_TXT" "XMLTYPE" NOT NULL ENABLE
);
INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (101, 'XML DATA', '<data>TEST</data>');
COMMIT;
DROP TABLE "XMLTEST";
Java Code
Java代码
String sql = "INSERT INTO XMLTEST (ID, DESCRIPTION, XML_TXT) VALUES (?, ?, ?)";
String xmlDataStr = "<data>test...</data>"; // a long xml string with length > 4000 characters
Connection con = getEntityManager().unwrap(Connection.class);
SQLXML sqlXml = con.createSQLXML();
sqlXml.setString(xmlDataStr);
Java code - use PreparedStatement
Java 代码 - 使用 PreparedStatement
PreparedStatement pstmt = con.prepareStatement(sql);
pstmt.setLong(1, 201);
pstmt.setLong(2, "Long XML Data");
pstmt.setSQLXML(3, sqlXml);
pstmt.execute();
Java code - use native query instead of PreparedStatement
Java 代码 - 使用本机查询而不是 PreparedStatement
Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, 301);
query.setParameter(2, "Long XML Data");
query.setParameter(3, sqlXml);
query.executeUpdate();
回答by Christian
According to Stuart Campbell
根据斯图尔特·坎贝尔的说法
- I inserted a XMLTYPE into a CLOB
- but the statement itself had to be wrapped in a transaction.
- 我在 CLOB 中插入了一个 XMLTYPE
- 但是语句本身必须包含在事务中。
Without transaction, it did not work....
没有交易，它不起作用......
