Extracting XML Data Using Stored Procedure

[asvictor]

Hi,

I am using a Stored Procedure to Extract XML Data for my Use. This Stored Procedure will produce one line of Data with 6 to & fields with details and a Field with the XML (line of Items). This Fields is defined as a CLOB in my Procedure. When I use this Procedure in my Job, My job runs but produces the value 0 in the Result. But not the XML data. I used LongVarChar in DS to extract XML Data.

Can someone suggest me which Datatype to use to Extract CLOB data in DS. Or is there any other way to Extract XML data?

[vmcburney]

You could use the XML input stage... Have a look in the real time control folder in your Designer, the XML input, transformer and output stages can be used in real time mode and they can also be used in batch mode. You can read data straight from Oracle and transform it into XML or read it as an XML data source. Any design that lets you take the difficult logic out of the Oracle stored procedure and into a DataStage job should be easier to maintain in the long run.

[trobinson]

Try to_char(<CLOB>) in the derivation of the CLOB column. If I understand you correctly, DS is not liking the CLOB column definition.
We use CLOB fields in Oracle 9.02 ato hold XML too and DS does not recognize this data type. We're lucky that the resulting varchar field from the to_char workaround works and is less then 4000 bytes so to_char will always work on our XML.

[ariear]

DS 7 should support CLOB datatype (see the docs).
maybe it's some platform specific/Oracle version specific bug

[Vipin]

There is no bug.

Value 0 represents that procedure has run successfully.
You are setting a CLOB datatype as an output parameter to show the XML, or it is recordset...??
Anytime using DataStage's XML stage is the best bet.
But you can try out setting an out put parameter of CLOB datatype containing required XML.

Manoj.