Stored Procedure Error Handling - Best Practices

[hilfy]

We are in the process of moving a number of processes out of our Delphi application and into Oracle stored procedures and packages. For this move, I'm supposed to put together a white paper containing recommendations for error handling and thought I'd come here and see what others are doing with this subject.

I've got three techniques that we've discussed:

1. An ErrMsg string that gets passed into and out of all procedures and functions so that if there's an error it can be passed back to the calling application.

2. An Event Log. This would be a place where we could put both exceptions and process completion messages. I've got code for a separate procedure using "Pragma AUTONOMOUS_TRANSACTION" that will allow a commit outside of the transaction within the main processing transaction. This way we could add a record and commit it without affecting the transaction handling in the calling procedure.

3. For major errors, send an email to the system operators notifying them of the problem.

I've found ways of implementing all three of these fairly easily, but I'd like to know if there are other types of things that are being done that might be helpful.

Thanks!!
-Dell

 

[sem]

Option 1 needs a lot of discipline from developers as well as any other "convention". Besides this may be easily done by Oracle embedded error handling: just call RAISE_APPLICATION ERROR and catch it in calling module.

I may vote against option 2, namely against using autonomous transactions: you'll have a lot of problems in distributed environment (AT doesn't work via db_links) and AT doesn't see uncommited changes in you main transaction. I'd suggest you to use dbms_pipe and some listeneing/logging job process.



Regards, Dima

http://seminihin.narod.ru

 

[hilfy]

When you say that AT doesn't work via db_links, it raises a question because there are two possible ways of reading that.

- The db_link is in the PL/SQL of the stored procedure that is called to update the event log.

- The stored procedure to update the event log resides in one database and is called from another database. (Is this possible? I haven't tested it.)

Are both situations covered by your statement?

I was looking at dbms_pipe, but one of the other programmers here recommended against it because he'd had lots of problems working with pipes. I will include that back in my research.

Thanks!!!

-Dell

 

[sem]

Does 1 mean that you plan to place log table remotely? I don't think it's a good idea Thus let's assume that it's local and you have a logging module declared with AT. In this case you can not "reliably" call any procedure calling that module via db_link.
BTW what's particularly wron with pipes? If you find them not robust enough then us AQ. General idea is to make logging asynchronous

Regards, Dima

http://seminihin.narod.ru

 

[hilfy]

I don't specifically know what's wrong with pipes other than one of the other programmers was vehemently against them when I mentioned the possibility.

The reason the log table would be remote has to do with the way our databases are set up - the simplified version is that we have a separate "SYS" database for each of our clients and a common database for various common data. (All of the SYS databases have a db_link to the common database but the common database does not have a db_link to each of the SYS databases.) We would want to have the log table in the common database so that there is a single point of access for the information.

Thanks again for the info!

-Dell

 

[sem]

Do you really mean "database", not "schema"?

Regards, Dima

http://seminihin.narod.ru

 

[hilfy]

Actually, it may be a little of both - I'm not one of the DBA's, just a programmer so I don't necessarily know the details behind how all of this works in Oracle. In production (not including Dev and QA which are separate physical databases) we have 25 set up across 3 different servers. Each "instance" has its own entry in TNSNames. We're dealing with financial mortgage data for banks and have to have each customer's data physically separate from every other customer's.

-Dell