LongVarBinary to BLOB column issue

amit.jaiswal_ATL · Post by **amit.jaiswal_ATL** » Thu Mar 05, 2015 10:20 am

Hello All,

We are converting our applications from SeeBeyond to DataStage-9.1/11.3. In one of the Job we have to store XML objects in Oracle BLOB column. We have used LongVarBinary in datastage to map it to BLOB column in Oracle. There is some weird thing happening and we are unable to understand it. DataStage job is able to generate and store XML correctly in BLOB. When we are downloading the content of the BLOB data and displaying it in Textpad it is showing it correctly. But when we are viewing this within Oracle client it is showing one additional space added between each character (Example : < ? x m l v e r s i o n = " 1 . 0 " .....) . When we view the BLOB object created by SeeBeyond we are able to view it correctly even within Oracle client (Example : <?xml version="1.0" .....). Now because of this in our next job which is extracting this BLOB data and generating XML file is NOT able to generate correct XML from the BLOB object data created/updated by Datastage. But the same job is able to generate correct XML from BLOB data created/updated by SeeBeyond.

Brief job design:
First job to Store XML in BLOB:
Oracle connector ==> XML Output (creating XML data block) ==> Transformer stage ==> XML Output (creating outer XML header block) ==> Oracle connector (with BLOB column to store XML object)

Second job to read BLOB and create XML:
Oracle Connector ==> Transformer Stage (RawToString function) ==> Sequential File Stage

Please let me know how to store the XML correctly in first job so that we should not see those one extra space between each character in XML in BLOB column.

Please let me know if you need any additional information.

Thanks in advance!

amit.jaiswal_ATL · Post by **amit.jaiswal_ATL** » Thu Mar 05, 2015 11:51 am

Additional information:

1. Inside job we are using XML Output encoding as UTF8.
2. While storing the XML in BLOB using DataStage it is storing it as PC-Unicode type of file. However, SeeBeyond is storing it as PC-ANSI
3. We have removed 'Unicode' property from the Extended drop down under table definition.

Looks like if we set some property in DataStage so that it can store this XML block in BLOB in PC-ANSI format this issue can be resolved.

Please let me know how to achieve this.

Thanks.

amit.jaiswal_ATL · Post by **amit.jaiswal_ATL** » Thu Mar 05, 2015 1:03 pm

Hello All,

I have used UStringToString function to change this from Unicode to ANSI to resolve this issue. This is now giving me desired result.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Thu Mar 05, 2015 1:37 pm

I was about to suggest exactly that solution!

Well done for solving it yourself!