ORA-01461 when inserting into an Oracle XMLType column
Recently, my colleagues and I were having "fun and games" inserting into an XMLType column on Oracle 10gR2 using the Microsoft Oracle provider (System.Data.OracleClient). It works fine until the XML is larger than 4KB. Over that and you get a rather misleading error:
"ORA-01461: can bind a LONG value only for insert into a LONG column"
The solution has two parts.
First, you need to make sure that the XML is being passed as an OracleType.Clob parameter, e.g.
C#:
-
cmd.CommandText = "INSERT INTO XMLTYPE_TEST (MyData) VALUES (XMLTYPE(:xmlparam))";
-
.
-
.
-
.
-
"xmlparam",
-
OracleType.Clob,
-
xml.Length,
-
ParameterDirection.Input,
-
"MyData",
-
DataRowVersion.Default,
-
true,
-
xml);
The second part of the solution is - and this is a weird one - you MUST wrap the command call in a transaction. Strange, but true. Not exactly sure why a transaction is necessary. But hey, it works.