CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
zephyrus
zephyrus

Reputation: 261

How to catch error in cursor declaration?

I have a need to write PL SQL that fetches data from another database over a DB link and perform some actions on it.

DECLARE
   CURSOR cur_progs
   IS
      SELECT *
        FROM allen.table_dummy@db_link;

BEGIN
    ... 
    ...
END;
/

In the above example, I faced two issues.

When the link is not created, below error message is thrown

ORA-04054: database link db_link does not exist

when the link is present but the password of the user with which the link is created is now expired, below error message is thrown

ORA-04052: error occurred when looking up remote object allen.table_dummy@db_link ORA-00604: error occurred at recursive SQL level 1 ORA-01017: invalid username/password; logon denied ORA-02063: preceding line from db_link

Is there a way to catch both these errors? Something like writing PLSQL block within PLSQL block.

Upvotes: 0

Views: 1056

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60262

One option to consider:

Pick a time when the database link is functioning ok, then compile a stored procedure (preferably in a package). That way, you know that the procedure is in a valid compiled state.

Then, write a simple stored procedure that uses dynamic SQL to query DUAL@db_link. Call this at runtime to test the link before running your procedure. This procedure won't be marked invalid even if the link is down.

If the link goes down temporarily, your main stored procedure won't be marked invalid unless you try to compile it.

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

The errors you are getting are compilation errors. You can't catch a compilation error (this is true in any language not just PL/SQL).

If you're really determined, you could change your code to use dynamic SQL so that you move the error from being a compilation error to becoming a runtime error. That would mean that you could catch the error in an exception handler. But doing so seems like a really bad idea.

First off, whether the error is thrown at runtime or at compilation time, the error would appear to be fatal. It seems highly unlikely that you could do something useful in an exception handler if a database link doesn't exist or if the password is invalid. Given that you should only be catching exceptions that you expect and that you can do something useful with, it seems highly unlikely that catching these errors would be useful.

Second, moving from static SQL to dynamic SQL generally makes your code harder to write and harder to maintain. Plus it frequently makes the code less efficient and less secure.

Upvotes: 3

Related Questions