| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Oracle RPC Exception |
« View previous topic :: View next topic » |
| Author |
Message
|
| abooddy |
 Posted: Thu Feb 06, 2020 2:21 am Post subject: Oracle RPC Exception |
 |
|
Novice
Joined: 28 May 2019
Posts: 20
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 |
|
 |
| Vitor |
| Posted: Thu Feb 06, 2020 5:48 am Post subject: Re: Oracle RPC Exception |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
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 |
|
|
|
|
|
| |
|
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
|
|
|
|
