ORA-01461: can bind a LONG value only for insert into a LONG column - when inserting into CLOB

Question

I am inserting a large string into a CLOB column. The string is (in this instance) 3190 characters long - but can be much larger.

The string consists of xml data - sometimes the data will commit, sometimes i get the error. The error occurs roughly 50% of the time.

Even string which contain over 5000 characters will sometimes commit with no problem.

Unsure where to go next as i am under the impression that CLOB is the best data type for this data.

I have tried LONG LONG RAW

Someone suggested using XMLTYPE however that does not exist in my version of Oracle (11g - 11.2.0.2.0)

My insert statement:

INSERT INTO MYTABLE(InterfaceId, SourceSystem, Description, Type, Status, StatusNotes, MessageData, CreatedDate, ChangedDate, Id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

MessageData is the CLOB column where the error is occuring, i have tried commiting without this data populated and it works.

Error

ORA-01461: can bind a LONG value only for insert into a LONG column

Oracle versions 3.1? I'm pretty sure version 3 was available in 1983. Is that really the version? — jle
I went on help > about and that's what is says. Also says copywright Oracle 2005-2011.Its oracle sql developer if that helps — DNKROZ
ah right - Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production — DNKROZ

jle jle · Accepted Answer · 2014-07-29T14:28:08

ALTER TABLE MYTABLE 
ADD COLUMN XML_COL XMLTYPE;

AND THEN

SQL> INSERT INTO MYTABLE(..., XML_COL) VALUES  (..., XMLTYPE('<root>example</root>'));

The key is to use XMLTYPE column and then use XMLTYPE() function to convert your string to XMLTYPE.

ORA-01461: can bind a LONG value only for insert into a LONG column - when inserting into CLOB

1 Answers