ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum IndexWebSphere Message Broker SupportOracle RPC Exception

Post new topicReply to topic
Oracle RPC Exception View previous topic :: View next topic
Author Message
abooddy
PostPosted: Thu Feb 06, 2020 2:21 am Post subject: Oracle RPC Exception Reply with quote

Novice

Joined: 28 May 2019
Posts: 16
Location: Baghdad, Iraq

Greetings,

We have the following setup:

Flow > ODBC > Flow Oracle Database > Oracle DBLINK > Remote Oracle Database

Basically, the flow uses ODBC driver to access its own database by calling a procedure which in turn does an RPC (Remote Procedure Call) on the remote database.

The issue is, when the remote procedure raises an exception, the flow completely hangs and becomes unresponsive. The debugger stops after a while and the flow stops receiving messages.

We've debugged our PLSQL code in the flow database and wrapped the RPC with an exception block to handle it and we've successfully done that. However, this does not stop the message flow hanging.

Code:
PROCEDURE transfer(p_final_ttype t_final_ttype%ROWTYPE) IS
    v_bal             NUMBER;
    v_errcode         NUMBER;
    v_errdesc         VARCHAR2(500);
    v_trans_voucher   NUMBER;
    v_iss_fee_voucher NUMBER;
  BEGIN
    -- DO RPC HERE!
  EXCEPTION
    WHEN OTHERS THEN
      pkg_log.add(p_logmsg => SQLERRM);
      pkg_error.raise('Exception: ' || SQLERRM);
  END;


The above code logs the error message successfully and re-raises the exception, however, it is not detected at flow causing the issue.

Facts:

    1. The issue can be easily fixed by wrapping the remote procedure code with an exception block so that it never raises an exception. However, this solution requires contacting vendor which might take a long time.

    2. The exception risen in the RPC is successfully handled or unhanded when called by applications other than IIB. We've used PL\SQL developer to test both when it was handled and when it wasn't.

    3. There is no reason for the message flow to be unresponsive and it should catch the exception at the nearest previous Try Catch node.


I apologize if the above was too much Oracle and less IIB, it was needed to explain the issue. In summary, why the message flow hangs when an exception risen at a remote Oracle database?

Our ODBC.ini Stanza:

Code:
;# Oracle swcbs
[swcbs]
Driver=/home/xyz/binaries/iib-10.0.0.13/server/ODBC/drivers/lib/UKora95.so
Description=DataDirect ODBC Oracle Wire Protocol
HostName=192.168.2.140
PortNumber=1521
ServiceName=IIB
CatalogOptions=0
EnableStaticCursorsForLongData=0
ApplicationUsingThreads=1
EnableDescribeParam=1
OptimizePrepare=1
WorkArounds=536870912
ProcedureRetResults=1
ColumnSizeAsCharacter=1
LoginTimeout=5
QueryTimeout=10
EnableNcharSupport=0
Back to top
View user's profile Send private message
Vitor
PostPosted: Thu Feb 06, 2020 5:48 am Post subject: Re: Oracle RPC Exception Reply with quote

Grand High Poobah

Joined: 11 Nov 2005
Posts: 25917
Location: Texas, USA

abooddy wrote:
In summary, why the message flow hangs when an exception risen at a remote Oracle database?


I would theorize that when the exception is thrown, by the time it gets from the remote database to the database the flow is connected to, it's either not being recognized as an exception that should be passed back through the ODBC connection or it's not coming through the ODBC connection in a way the flow recognizes.

As always, I'd recommend taking a user trace rather than using the debugger to see what's actually going on inside the broker at the time in question. This may shine some light.

In the end, I think this is a good candiate for a PMR. You might need a patch to the ODBC drivers.
_________________
Honesty is the best policy.
Insanity is the best defence.
Back to top
View user's profile Send private message
Display posts from previous:
Post new topicReply to topic Page 1 of 1

MQSeries.net Forum IndexWebSphere Message Broker SupportOracle RPC Exception
Jump to:



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
Protected by Anti-Spam ACP


Theme by Dustin Baccetti
Powered by phpBB 2001, 2002 phpBB Group

Copyright MQSeries.net. All rights reserved.