Oracle Datatypes XML vs CLOB

[ShaneMuir]

Hi All

I have a query regarding the difference between the datatypes XML and CLOB as used by Oracle 9.2. Are both of them long datatypes? And how will this interact with the ORAOCI8 stage.

I have an issue where I am trying to capture rejected XML records. When a record is rejected the xml record is inserted into a source_data column which is defined as XML. Usually this is not a problem but when the length of the XML record exceeds 4000 char I get the error

Code: Select all

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

I have searched the forum and have deduced that I should be using longvarchar datatype with the oracle database set to CLOB. We currently have the DB set to XML (as we want the data to be checked that the xml is valid) which i believe is based on CLOB?

I performed a couple of tests but I get varying results:

• OCI stage settings - ErrorMessage
  VarChar (4000) - Inserted value too large for column, row rejected.
  VarChar (8000) - ORA-01461: can bind a LONG value only for insert into a LONG column
  LongVarChar (8000) - Oracle datatype not presently supported

We have been able to get around it in the past by separating the insert xml into multiple varchar columns and then concatenating them into the DB XML field using a user defined SQL. However I am curious to know if there is another way.

Is it just a matter of using the wrong plug-in, or maybe an upgrade to DSv7?
Or even perhaps changing the target datatype to CLOB instead of XML?

Does anybody have any suggestions?

Thanks in advance
Shane