Heterogeneous DB error propagating past WHEN OTHERS [message #666035] |
Mon, 09 October 2017 14:34 |
|
EricS13
Messages: 2 Registered: October 2017 Location: DC Area
|
Junior Member |
|
|
Hello,
I have an Oracle 11gR2 database that connects with a DB Link to a SQL Server 2008 DB using Heterogeneous Services.
I have a function compiled within the Oracle DB that calls dbms_hs_passthrough.execute_immediate in the body, passing an update statement to SQL Server.
Directly under that is an exception when others that writes to a log table.
The problem I am having is that in certain situations the calls to dbms_hs_passthrough raises an error on the SQL Server end, which comes back as a very generic DB Link error as shown below. This error seems to be raised in SQL Server due to a date conversion error and is then improperly translated when returned to Oracle as shown below.
ORA-00604: ERROR OCCURRED AT RECURSIVE SQL LEVEL 1
ORA-02019: CONNECTION DESCRIPTION FOR REMOTE DATABASE NOT FOUND
When this error comes back, the exception handler in PL/SQL is skipped (!!!!) and the error propagates past the handler, up the chain to the invoking procedure. Has anyone ever seen an error before that behaves like this, which actually skips the exception handler when returned by a foreign DB?
Second, I'd appreciate any tips on how to capture the actual error that is coming from SQL Server (e.g date conversion..). It is very frustrating that Oracle returns a DB Link error regardless of the problem reported by SQL Server.
Thanks
|
|
|
|
Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666040 is a reply to message #666035] |
Tue, 10 October 2017 01:57 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This is nothing to do with SQL Server, your function is not getting that far (which is why it "skips" as you call it your error handler). An ora-02019 means that the code can't see the database link at all. For example:orclx>
orclx> drop database link l1;
Database link dropped.
orclx> select * from global_name@l1;
select * from global_name@l1
*
ERROR at line 1:
ORA-02019: connection description for remote database not found
orclx> You need to create the link in the correct schema. Or create a public link, if you want to live dangerously.
|
|
|
|
Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666045 is a reply to message #666043] |
Tue, 10 October 2017 06:53 |
ThomasG
Messages: 3212 Registered: April 2005 Location: Heilbronn, Germany
|
Senior Member |
|
|
I also have had my share of SQLServer problems. The thing is, even if you run stuff *internally* on SQL Server sometimes you just get a "the action failed" or some such back, without any clue as to WHAT went wrong.
In most cases I encountered was able to handle (or at least "ignore") the error when I placed an explicit declaration of:
DISTRIBUTED_FAILED EXCEPTION;
REMOTE_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT( DISTRIBUTED_FAILED , -02055 );
PRAGMA EXCEPTION_INIT( REMOTE_ERROR , -28500 );
which I then handled at the end ....
EXCEPTION
WHEN DISTRIBUTED_FAILED THEN
....
WHEN REMOTE_ERROR THEN
....
....
|
|
|
|
|