CODEWITHSUNDEEP
sqloracle-databasenhibernateoracle11g
DNKROZ
DNKROZ

Reputation: 2872

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

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

Upvotes: 2

Views: 3108

Answers (1)

jle
jle

Reputation: 9489

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.

Upvotes: 1

Related Questions