| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Stored Procedures advice |
« View previous topic :: View next topic » |
| Author |
Message
|
| fjcarretero |
 Posted: Fri Jun 25, 2004 7:50 am Post subject: Stored Procedures advice |
 |
|
Voyager
Joined: 13 Oct 2003
Posts: 88
|
Hi All,
I need to call an Oracle stored procedure passing an XML message. This stored procedure is going to process the XML an update multiple tables.
Looking at the specs for external procedures, I can't use CLOBs or BLOBs as parameters. The other alternative is using VARCHARs but it has a max length of 4K.
Could anybody advice me about alternative ways of doing this?.
TIA
Regards
Felipe |
|
| Back to top |
|
 |
| JT |
| Posted: Fri Jun 25, 2004 8:52 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| You can get around this restriction by calling your Oracle stored procedure using the PASSTHRU command. |
|
| Back to top |
|
|
| djeripo |
| Posted: Fri Jun 25, 2004 3:02 pm Post subject: |
|
|
Master
Joined: 25 Jan 2004
Posts: 225
|
Felipe
Did you mean the length of the parameter(CHAR) you are passing to an Oracle SP is more than 4k .And the constraint is that VARCHAR's max. length is 4k.
JT,
If so , PASSTHRU is going to handle this restriction automatically ??
Sorry,If I misinterpret the question ! |
|
| Back to top |
|
|
| fjcarretero |
| Posted: Mon Jun 28, 2004 12:39 am Post subject: |
|
|
Voyager
Joined: 13 Oct 2003
Posts: 88
|
Hi All,
Thank you for the responses.
I can use passthru, but the documentation says that it only supports input parameters. So how would I know if the call has succeded (I mean business errors). We are using an out parameter to check if everything went fine.
Sorry, I don't know much about DBs.
TIA
Regards
Felipe |
|
| Back to top |
|
|
| djeripo |
| Posted: Mon Jun 28, 2004 7:03 am Post subject: |
|
|
Master
Joined: 25 Jan 2004
Posts: 225
|
I call DB2 StoredProcedures by 'CALL' statement .
Something like ,
CALL CTP1020(PLCY_POLICY_NUMBER,PLCY_INDICATOR,OUTPUT_VAL_FORM_CODE,
OUTPUT_PLAN_CODE,OUTPUT_RETURN_CODE,OUTPUT_MESSAGE);
For me the SP is success only if the OUTPUT_RETURN_CODE = 0(SQL Return Code) .
Else its a failure (Meaning SP is not returning all the Output Parameters) .
This is how the person who has written this SP has developed .
This may not be the same case every where .
CREATE PROCEDURE CTP1020 (
IN IC_AR_POL_ACCT_NUM CHARACTER,
IN IC_SBG_COV_IND CHARACTER,
OUT OC_LF1_VAL_FRM_CDE CHARACTER,
OUT OC_PLAN_NAME CHARACTER,
OUT OI_RETURN_CDE INTEGER ,
OUT OC_MESSAGE CHARACTER
) EXTERNAL NAME "PCTP001.CTP1020"; |
|
| Back to top |
|
|
| fjcarretero |
| Posted: Mon Jun 28, 2004 7:41 am Post subject: |
|
|
Voyager
Joined: 13 Oct 2003
Posts: 88
|
Thanks for the answer.
I was planning to use the same approach, but the person implementing the stored procedure in Oracle 9 told that the max length for a VARCHAR is 4K. We need to send bigger messages (I'm serializing the XML into a CHAR).
If we use passthru then I can use CLOBs and BLOBs, but I can only pass input parameters.
I will give use the PASSTHRU solution and see if we can meet the requirements.
Thanks
Regards
Felipe |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
