Insert XML with more than 4000 characters into a Oracle XMLTYPE column
I have an oracle table with a column from type "SYS.XMLTYPE
" and a storage procudure which is doing the insert:
(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.LongVarCh开发者_开发问答ar
".
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:
ORA-20000: ORA-01461: can bind a LONG value only for insert into a LONG column
How can I handle this? Thx 4 answers
Check the Oracle docs about XMLType
Also, I believe the datatype should be a CLOB (Character Large Object).
You need to convert xml string for more than 4000 charcaters into SQLXML type first.
Environment: 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
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
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
Query query = getEntityManager().createNativeQuery(sql);
query.setParameter(1, 301);
query.setParameter(2, "Long XML Data");
query.setParameter(3, sqlXml);
query.executeUpdate();
According to Stuart Campbell
- I inserted a XMLTYPE into a CLOB
- but the statement itself had to be wrapped in a transaction.
Without transaction, it did not work....
精彩评论