Page 1 of 2
Stored Procedure stage with Longvarchar (CLOB)
Posted: Thu Jan 18, 2018 5:35 am
by and
Hi all!
I have job with a Stored Procedure stage and Oracle procedure like
Code: Select all
PROCEDURE TEST1(p_in1 in varchar2, p_out1 out clob)
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:
Code: Select all
Stored_Procedure_17,0: Error: Datatype not presently supported
Is it a limitation of the Stored Procedure stage ? Or is it a bug ?
Thanks!
Posted: Thu Jan 18, 2018 7:35 am
by chulett
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.
Posted: Thu Jan 18, 2018 5:36 pm
by asorrell
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.
Posted: Thu Jan 18, 2018 11:38 pm
by and
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
Posted: Fri Jan 19, 2018 7:26 am
by chulett
Yes, that's exactly what he means and something I've done before as well.
Posted: Sat Jan 20, 2018 1:12 am
by and
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
Posted: Sat Jan 20, 2018 10:34 am
by chulett
Not to be funny, but... and?
Posted: Tue Jan 23, 2018 3:18 pm
by asorrell
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?
Posted: Tue Jan 23, 2018 3:57 pm
by chulett
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?
Posted: Wed Jan 31, 2018 3:55 am
by and
asorrell, chulett
thanks
i tried following
- created sample table
Code: Select all
x_control x_ret
1 <x>
1 x as value
1 </x>
2 <y>
2 y as value
2 </y>
- 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
Code: Select all
<test>
<x>
</test>
<test>
x as value
</test>
<test>
</x>
</test>
Peek_6,0: X:<test>
<y>
</test>
<test>
y as value
</test>
<test>
</y>
</test>
but expected something like
Code: Select all
<test>
<x>
x as value
</x>
</test>
Peek_6,0: X:<test>
<y>
y as value
</y>
</test>
please help me in assembling xml
Posted: Thu Feb 01, 2018 12:57 am
by ray.wurlod
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.
Posted: Thu Feb 01, 2018 9:22 am
by and
thanks for note
but there are some moments:
-- i'm talking not about pretty printing
-- it's not well-formed xml
please compare
Code: Select all
<test>
<x>
</test>
<test>
x as value
</test>
<test>
</x>
</test>
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 )
Posted: Thu Feb 01, 2018 10:31 am
by chulett
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?
Posted: Thu Feb 01, 2018 10:45 am
by and
>> to chop it up into chunks
it in dbms
Code: Select all
x_control x_ret
1 <x>
1 x as value
1 </x>
2 <y>
2 y as value
2 </y>
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 ?
Posted: Thu Feb 01, 2018 1:22 pm
by chulett
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.