| Author |
Message
|
| kudlanka |
 Posted: Fri Mar 20, 2009 5:04 am Post subject: Problem with Oracle CLOB |
 |
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Hello,
we have problem with recieving reult from Oracle stored procedure which in param is CLOB and out param is CLOB too.
Configuration:
MQI v6
Oracle v10
here is the code
PROCEDURE FLIP_OVER
(
ab_xml_in IN CLOB,
ab_xml_out OUT CLOB
) IS
BEGIN
ab_xml_out := ab_xml_in;
END flip_over;
We want to call it from Compute node - code looks like this
create procedure FLIP_OVER(in inp1 CHARACTER, out out1 CHARACTER) external name "FLIP_OVER";
in functuion MAIN
DECLARE vystup CHARACTER;
DECLARE vstup CHARACTER;
set vstup='<XM><ELEM>Test Message</ELEM></XM>
call FLIP_OVER(vstup,vystup);
set OutputRoot.XML.XM.MD.dataOra=vystup;
That's all.
We always get error:
<RecoverableException>
<File>/build/S600_P/src/DataFlowEngine/ImbOdbcParameter.cpp</File>
<Line>1404</Line>
<Function>OdbcParameter::BalancedODBCBuffer::resizeForOUT</Function>
<Type></Type>
<Name></Name>
<Label></Label>
<Catalog>BIPv600</Catalog>
<Severity>3</Severity>
<Number>3221</Number>
<Text>LOB parameter for external stored procedure is too large</Text>
<Insert>
<Type>2</Type>
<Text>2147483650</Text>
</Insert>
</RecoverableException>
We cannot find answer which parameter is too large and how to solve it.
Can anyone help us with solution or point us to any document which can help?
Thank you.
Kudlanka |
|
| Back to top |
|
 |
| Vitor |
| Posted: Fri Mar 20, 2009 5:08 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
Moved to correct section
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Mar 20, 2009 5:10 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
I'm guessing the code sample provided is simply to illustrate the issue? Rather than your requirement being to copy XML using a stored procedure called from a Compute node, instead of manipulating it inside the Compute node?
I ask just for clarification.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 5:25 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Yes, it is sample illustrating our issue.
In real situation we want to send xml message to store procedure which parses xml, does some db operations and retruns xml as result. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Mar 20, 2009 5:50 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kudlanka wrote: |
| In real situation we want to send xml message to store procedure which parses xml, does some db operations and retruns xml as result. |
Fair point.
My question then becomes why are you manipulating the XML within an Oracle sp when you're calling the sp from WMB, noted for it's XML parsing abilities?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 6:12 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Vitor,
our architecture departmenet made this decision... |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Mar 20, 2009 6:18 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kudlanka wrote: |
| our architecture departmenet made this decision... |
So ask them my question. Once they've sobered up and/or put the lid back on the glue pot..... 
Seriously, this is not an obvious architectual choice. You buy something which is sold on it's ability to manipulate XML in an industrial fashion, then use it to pass the document to some database software to pull about. You should push back and question this decision. Possible deciding factors include:
- someone in your architectual department is an ex-Oracle DBA
- your architectual department doesn't understand WMB
- your architectual department thinks WMB is a giant parser that can't update databases
Other less charitable scenarios exist.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 6:22 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Vitor,
yes, we can try it but it is long way run.
Is any solution for our problem - for now it is xml which can be parsed in other way as you mentioned. Next time it could be anything else which is clob type. It could be helpful to know solution. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Mar 20, 2009 6:27 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kudlanka wrote: |
| for now it is xml which can be parsed in other way as you mentioned. Next time it could be anything else which is clob type. |
And you can process that with WMB as well.
| kudlanka wrote: |
| It could be helpful to know solution. |
A brief word with Mr Google and some Oracle types here suggests there's a limit to the parameter size the Oracle ODBC supports. Opinion varies here about what the limit is, but the consensus is you've hit it.
Solutions vary; one suggestion is to use WMB to parse it into smaller chucks, but that sort of defeats your architecture.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 6:35 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
I asked Google many times - probably wrong questions. I will try again.
But insert clob or select clob without stored procedure works fine. We think that it is not obcs limitation. |
|
| Back to top |
|
|
| Vitor |
| Posted: Fri Mar 20, 2009 6:40 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| kudlanka wrote: |
| But insert clob or select clob without stored procedure works fine. We think that it is not obcs limitation. |
Not an Oracle person myself so relying on the advice of my staff on this. (!)
Following logically from your statement, if it works without a sp then it implies it's an issue with parameters in an sp & you should consider re-posting your question on an Oracle forum. You may strike more lucky.
Personally, I still think you're better off manipulating the data in WMB. But I'm biased.. 
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 6:45 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
I will try to post my question to Oracle forum - good idea.
Other non-clob procedures work fine too... so problem is only with clob. |
|
| Back to top |
|
|
| HOMETOWN47 |
| Posted: Fri Mar 20, 2009 6:52 am Post subject: |
|
|
Apprentice
Joined: 25 Mar 2003
Posts: 34
|
Ignoring the politics around the right and wrong way of doing this, I've tried the example that kudlanka has posted and not had a problem - I used the same XML message as him because he said this was causing the same issue as using a larger XML message.
This was my SP defined in Oracle
PROCEDURE FLIP_OVER (AB_XML_IN IN CLOB, AB_XML_OUT OUT CLOB ) AS
BEGIN
AB_XML_OUT := AB_XML_IN;
END FLIP_OVER;
This was the definition in my ESQL
create procedure FLIP_OVER(in inp1 CHARACTER, out out1 CHARACTER)
LANGUAGE DATABASE
external name "ODS_USER.CLM_PJ.FLIP_OVER";
And this was the call of the SP
DECLARE vystup CHARACTER;
DECLARE vstup CHARACTER;
set vstup='<XM><ELEM>Test Message</ELEM></XM>';
call FLIP_OVER(vstup,vystup);
set OutputRoot.XML.XM.MD.dataOra=vystup;
Just debugged the flow and worked OK.
Configuration:-
Windows MB 6.1.0.3
Oracle v10 |
|
| Back to top |
|
|
| kudlanka |
| Posted: Fri Mar 20, 2009 6:57 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Thank you HOMETOWN47,
now we know that our code works and we can look for problem in configuration in odbc, db,...
Thank you, it helped...
Kudlanka |
|
| Back to top |
|
|
| kudlanka |
| Posted: Tue Mar 24, 2009 7:00 am Post subject: |
|
|
Novice
Joined: 19 Mar 2009
Posts: 20
Location: Prague
|
Problem is solved.
Problem was that we called 64bit Oracle from 32bit WMQI
Thanks to all
Kudlanka |
|
| Back to top |
|
|
|
|
