Inserting or Updating Xml data into Oracle table

sabeenapeter · Post by **sabeenapeter** » Mon May 04, 2015 10:20 pm

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)

eostic · Post by **eostic** » Tue May 05, 2015 4:51 am

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

ray.wurlod · Post by **ray.wurlod** » Tue May 05, 2015 4:36 pm

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.

sabeenapeter · Post by **sabeenapeter** » Mon May 11, 2015 3:44 pm

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);

amit.jaiswal_ATL · Post by **amit.jaiswal_ATL** » Thu May 14, 2015 11:49 am

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.