DSXchange

Hi all!

I have job with a Stored Procedure stage and Oracle procedure like
I have some trouble with output from the stored procedure stage

If I set type for output in stored procedure stage as "varchar" the job works.

But I need to return "Clob" (or "xmltype") so I changed type of output to "longvarchar" and this does not work and error is:
Is it a limitation of the Stored Procedure stage ? Or is it a bug ?

Thanks!

Based on the message, I'd go with "limitation". Best to open up a support case if you want an official answer and to see what, if any, options you have.

It isn't supported. I heard they are adding better support for CLOB in 11.7 (mainly for Hadoop), but don't know if it would apply in this case. Customer services should be able to confirm if its in 11.7 or not. However, that doesn't really fix your current issue.

I encountered this once and had to slice up the XML CLOB into manageable "chunks", send the pieces, and then reassemble them afterwards. That wasn't a preferred solution, but it worked.

thanks chulett and asorrell

I encountered this once and had to slice up the XML CLOB into manageable "chunks", send the pieces, and then reassemble them afterwards. That wasn't a preferred solution, but it worked.

nice note

can you please give me some hints on it

did you slice up XML/CLOB by dbms and return "chunks" to datastage and assemble ?


thanks

Yes, that's exactly what he means and something I've done before as well.

i have to shed some light to my previous reply

if dbms stored procedure use only one row with clob value to return then it's ok to slice the clob up

what if dbms stored procedure use cursor to return many rows ( my case ) and so in datastage you'll have many "chunks" of many row's clob and so you have to identify needed "chunks" to assemble into clob/LongVarChar and in this case it's tricky

Not to be funny, but... and?

Yes - you might have to establish some sort of key structure / row count in order to reassemble the chunks.

Just realize - if the resulting data record including the re-assembled CLOB exceeds 128K, then it can't be done in DataStage. On the release you are on, 131,072 bytes is the hard limit on record size.

http://www-01.ibm.com/support/docview.w ... wg21416107

If it is a CLOB is it a conglomeration of some sort that can be split out on a logical sub-record basis?

Andy - they are on 11.x and your support link is for some of the original releases. Is that hard limit still actually an issue?

asorrell, chulett
thanks

i tried following
- created sample table
- create oracle dbms procedure with cursor based on above table
- in datastage add stored procedure stage
- after stored procedure stage was added xml output

for output column
use trigger column - x_control

and result but expected something like please help me in assembling xml

There are plenty of XML prettifiers out there; it's not the task of an ETL tool. The XML produced is valid, and that's all that's important. Format is largely irrelevant in XML.

thanks for note

but there are some moments:
-- i'm talking not about pretty printing
-- it's not well-formed xml

please compare and expectation

<test>
<x>
x as value
</x>
</test>

in last case xml contains one root

and imho it have to be a result of xml output stage ( use trigger column - x_control and sample data in previous reply )

Color me a little lost here. I thought you already had XML in the CLOB and just needed to chop it up into chunks and then reassemble them in the job. If that's true, why involve an XML Output stage? If that's not true, have we moved on to a different topic?

>> to chop it up into chunks
it in dbms
it is returned by stored procedure stage with cursor

>> and then reassemble them in the job
in job, yes
xml output stage with use trigger column - x_control

may be i wrong about xml output to reassemble and may be i need to use something else ?

First off, there's no reason to quote everything you are replying to, there's a simple Reply to topic link at the top and bottom of every page. Quote stuff when it's relevant / needed. Saves me having to clean up things.

So, it's not already well-formed, complete XML stored in its entirety as a single CLOB? Instead it is just the 'core' data elements which have already been cut up and stored as multiple records in a relational table? Meaning you then need to not only put the records back together but also wrap it to make it 'well-formed' as well. If so, we seem to have moved past the original discussion which was a bit more... straight-forward.

What does the "x_control" column represent? I see that they mark each tag but I don't see that being especially useful here. Can you explain how you are attempting to use them, please? I'm assuming Andy meant something more like a simple ascending "chunk number" so that you could properly "assemble" / concatenate back together the long string that is a piece of well-formed XML. And those chunks would literally be chopped up string pieces, all cut to the same size, useless on their own, simply so they fit out the door.

Just want to make sure we're all on the same page with regards to the problem you are trying to solve.