DSXchange

Hi,
Im trying to read XML data from Oracle XMLType column and insert into another Oracle table column of XMLType using ODBC Connector stage in datastage 8.1.

During insert the below error is thrown:-
ODBC_Connector: ODBC function "SQLFetch" reported: SQLSTATE = HYC00: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle driver]Driver does not support selecting a column of type XMLType. You must cast columns of type XMLType to a CLOB by using the cast function.

How to overcome this error?

Query used to read data :-
select x.REPORT_XML.getclobval() as REPORT_XML,CR_RPT_NUM ,PAGE_IND from temp x;

Insert Statement in ODBC Connector:-

INSERT INTO temp1(REPORT_XML,CR_RPT_NUM,PAGE_IND) VALUES(sys.xmltype.createxml(ORCHESTRATE.REPORT_XML),ORCHESTRATE.CR_RPT_NUM,ORCHESTRATE.PAGE_IND)

I thought the Connectors were updated to handle the XML Type, but you are on 8.1, which is really old.

Anyone else tried it in a newer release?

Ernie

Just an idea, since you're on a very old version: can you handle the XML as a VarChar (or Long VarChar) data type within your job design? Ignore (or demote) any metadata mismatch alerts.

Thanks for the responses.

When I tried to insert as CLOB the below error is given
ODBC_Connector_15,2: ODBC function "SQLFetch" reported: SQLSTATE = HYC00: Native Error Code = 0: Msg = [IBM(DataDirect OEM)][ODBC Oracle driver]Driver does not support selecting a column of type XMLType. You must cast columns of type XMLType to a CLOB by using the cast function. (CC_OdbcInputStream::open, file CC_OdbcInputStream.cpp, line 173)

Below is the insert statement and REPORT_XML is a CLOB in target table
Insert into temp2(CR_RPT_NUM,PAGE_IND,REPORT_XML) values (orchestrate.CRNUM,orchestrate.PAGE_IND,orchestrate.REPORT_XML);

We have XML_VALUE column as BLOB in Oracle Table and we are using LongVarBinary data type for this column in DataStage which is working fine. You may want to try this option. FYI, we are using Oracle Connector Stage in DS 9.1 and 11.3.