APEX-AT-WORK no image

Ignore sql error messages in pl/sql process

Von Tobias Arnhold 11.16.2009
Have you experienced the case that you want to execute a process and when a specified error occurs then it should go on like nothing happened.

In my case I had several pl/sql processes and one was to delete a database link. In my special case there shouldn't be an error if no database link exists.

Here is the code snippet for it:

-- error variable
v_no_link EXCEPTION;
-- Map error number returned by raise_application_error to user-defined exception.
PRAGMA EXCEPTION_INIT(v_no_link, -2024);
-- About the error: http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e1500.htm#sthref1158

-- Drop existing database link
EXECUTE IMMEDIATE 'drop database link ' || UPPER(:P1_I_INSTANCE_NAME);

WHEN v_no_link THEN
-- raise_application_error(SQLCODE, 'SQLERRM');

More information: PL/SQL User's Guide and Reference - Error Handling

8 Comments " Ignore sql error messages in pl/sql process "

Alex Nuijten 16 November, 2009 16:40

Why include the "WHEN OTHERS"? It doesn't add anything to the code...

Martin Giffy D'Souza 16 November, 2009 16:49

Hi Tobias,

I know Tom Kyte isn't a huge fan of using a WHEN ... THEN null; in exception handlers.

Perhaps you could change your code to first check if the database link exists. If it does drop it, if not don't do anything.


Bhavin 16 November, 2009 17:54

Hi Tobias,

I agree with Martin it is not good practice to put null in exception handler.

Only do if link exists so in that sense your code would be compact and no need for exception, other variables etc..


John Scott 16 November, 2009 19:20


I believe Tom usually quotes 'WHEN OTHERS THEN NULL' being evil. In this case however Tobias is capturing a very specific exception (ORA-02024).

Personally I think it depends how you look at this, i.e. do you want to have the 'overhead' of always checking if the DB Link exists before trying to drop it. Or...is it much rarer that the DB Link won't exist (in which case you've potentially deferred that overhead and instead capture the exception if/when it does happen).


Gary Myers 16 November, 2009 22:52

The commit after the drop is unnecessary as a DROP DDL will commit implicitly anyway.
I agree that the WHEN OTHERS doesn't really add anything. The particular exception you are interested is listed, and the WHEN OTHERS with just a RAISE_APPLICATION_ERROR is redundant.
I suspect the most frequent uncaught error will be where the DB_LINK exists but is in use by a transaction and can't be dropped.

Tobias Arnhold 17 November, 2009 08:29

Thanks for all your opinions. This example was more about the fact of knowing about a specified Oracle error and ignoring it in an easy way. Maybe as most of you say it is not the best way. I will think about it.

To Gary: I erased the commit. Thanks for the info.

Kevan Gelling

RAISE_APPLICATION_ERROR will not work as it's restricted to error code between -20000 and -20999.

If you insist on WHEN OTHERS then you should use RAISE. It'll maintain the error stack too.

Patrick Wolf 06 December, 2009 12:19

Hi Tobias,

be very careful when concatenating a page item value to a dynamic SQL statement. That one has a high potential for allowing SQL injections if the page item is one the user can enter. You should have a look at DBMS_ASSERT to check it that it's a valid identifier and doesn't contain any other SQL commands.