Oracle xmltype insert fails for large xmls

Hi there,

I have a table with a few xmltype columns. One of which stores a large xml. Inserts to the table work as long as the size of the xml is limited like 3k..

But when I try to insert an xml of size 35k I get this error:

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

Is this a Lightspeed bug? How can I get around this issue??

Attached here the SQL log from Lightspeed..

Thanks, Tilak

Hi Tilak,

Could you please try putting in place the SQL logger (or just capturing it with Oracles query profiler) to see what the queries are? There's two things that could be going on here:

1. It's an issue with the insert
2. It's an issue with the select back

I'm picking it's the insert. It would seem strange that it only starts having issues with a 35K blob (I'm assuming it is working happily on smaller sizes?). This leads me to think it's either:

1. A constraint on the table column regarding length
2. A constraint being added by LightSpeed, perhaps where a bug lies (at this time, we are not aware of a bug here however)

Best bet would be to capture the query to understand what's going on here.

Thanks,

John-Daniel Trask

John,

Thanks for the reply.

I have attached the Lightspeed sql log to the original post. And, a little surprised it's building a plain insert sql without parameter bindings.

I don't have any constraints on the column. And, was able to insert this with odp.net parameterized query (using xmltype parameter).

Note: We cannot run the insert from the log directly using sql plus, because the string literal length is too long.

Thanks,

Tilak

We dont specifically set the data type for bound parameters, it is inferred by the ADO.NET provider. In this case I believe it will be inferred to be a CLOB/NCLOB type which is probably the cause of the error being raised by Oracle that you are seeing.

Are you able to convert the column over to CLOB/NCLOB? e.g. http://www.orafaq.com/wiki/LONG

Jeremy