Home » SQL & PL/SQL » SQL & PL/SQL » Heterogeneous DB error propagating past WHEN OTHERS (Oracle 11gR2, SQL Server 2008)
Heterogeneous DB error propagating past WHEN OTHERS [message #666035] Mon, 09 October 2017 14:34 Go to next message
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 #666037 is a reply to message #666035] Mon, 09 October 2017 19:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is frustrating that an error on SQL Server is NOT properly logged & recorded.

Only a poor craftsman blames his tools for substandard results.
Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666040 is a reply to message #666035] Tue, 10 October 2017 01:57 Go to previous messageGo to next message
John Watson
Messages: 8922
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 #666043 is a reply to message #666040] Tue, 10 October 2017 06:31 Go to previous messageGo to next message
EricS13
Messages: 2
Registered: October 2017
Location: DC Area
Junior Member
I appreciate the responses. John, normally your response would be my first conclusion here as well, but it is not the case. I have a DB Link from Oracle to SQL Server. If I run an update statement that has no use of a date datatype in the filter then the update works across the link. If I add a date datatype element to the filter of the update statement, I get the DB Link error I mentioned. The link is clearly there and working in some cases. It's not a grant issue on the SQL Server side because it works without the date in the filter.

If I add the date to the where clause in the update statement, not only does it blow up with a DB Link error, but the error propagates past the when others in the update function. This is the part that is really bothering me, because a when others should catch everything.

I've been doing PL/SQL development in distributed systems for about 12 years and this is my first time encountering something that has completely baffled me. I am considering going to Oracle support about it, but really hope to avoid that.
Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666045 is a reply to message #666043] Tue, 10 October 2017 06:53 Go to previous messageGo to next message
ThomasG
Messages: 3211
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
      ....
....




Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666046 is a reply to message #666043] Tue, 10 October 2017 07:02 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Without seeing your code (the code which works and the code which doesn't) I don't see how anyone can assist.
Re: Heterogeneous DB error propagating past WHEN OTHERS [message #666053 is a reply to message #666043] Tue, 10 October 2017 09:28 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
If it really is bypassing the exception handler then that would be an oracle bug and you should talk to support about it.
Previous Topic: row-to-row navigation query
Next Topic: returning collection from a loop and bulk collect
Goto Forum:
  


Current Time: Thu Mar 28 06:12:12 CDT 2024