Oracle Package Errors Design

[LeighRiffel]

I am writing an Oracle package to be used as an interface to several related tables. I am attempting to design the package such that if a particular procedure or set of procedures are called that have expected, non-fatal errors, that all the errors are returned and not just the first one encountered.

My current plan is to create a global temporary errors table and whenever an expected error is encountered insert the error into the error table and continue processing. My primary problem with this is that the calling application has to make sure an exception wasn't returned for an unexpected error and then also has to check the errors table (using a function or procedure) for expected errors.

I am looking for ideas on ways to make this design better either by improving my current plan or through an alternate way to accomplish the same goal.

 

[sem]

Read about DBMS_UTILITY.FORMAT_ERROR_STACK procedure, I suppose you may use it instead of inventing a wheel.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

You should consider downloading and using plvision.

It is freeware and written by one S Feuerstein Esquire, who I suspect has forgotten more about pl/sql than I will ever know.

Dima is right, you are definitely re-inventing the wheel.

Also, if your are expecting lots of errors, then isn't this a functional issue as to what the application should do, and not exception handling per se?

Regards

Tharg

Grinding away at things Oracular

 

[LeighRiffel]

I'll look at DBMS_UTILITY.FORMAT_ERROR_STACK and PL/Vision as well as soon as I get a chance.

Tharg,
Multiple applications will be calling this package and I'd rather program the buisness logic once on the package side rather than multiple times in each application. I suppose you could call them business rule violations rather than errors. In some cases the rule would also be checked on the application side, but to maintain the integrity of the table data I am planing to verify all data before putting it in the DB.

 

[LeighRiffel]

Dima,

I have looked at DBMS_UTILITY.FORMAT_ERROR_STACK and it doesn't look like it will do what I need. There errors I will be detecting are not part of a call chain and therefore will not be stacked.

I could have one procedure that could detect five different error conditions. The first one detected can't throw an exception because if it does the other errors will not be detected. For example, if a procedure accepted three parameters and I needed to check each for a range I would want to return errors for each one that was out of range.

 

[LeighRiffel]

Tharg,

I have downloaded PL/Vision and perused the documentation. It looks like I am trying to write something very similar to their logging functionality. It seems like it would be simpler just to write the small peice I need rather than modify this monolithic infrastructure to handle the additional data I need to save.

 

[Thargy]

Spread,

that may be true, and for what is strictly a one-off, I might agree. However, now that I've seen your other posts, and what they indicate you're trying to achieve, I have some further comment.

You are (IMHO) going about this business in a fundamentally flawed way. Exception handling is not the correct mechanism for detecting and handling business rule violation. You will have programming nightmares trying to make exception handlers do what I believe you want.

Enforcing business rules should be by declarative referential integrity, wherever possible. This is obvious I know, but you are specifically mentioning it. Go to extraordinary lengths to achieve RI by means of check constraints with meaningful names etc. As a dim and distant last resort, use triggers.

To avoid network thrashing solely for validating a couple of fields on a form, it can sometimes be legitimate to code business rules in both the front and back ends. However, you have specifically mentioned that multiple app's will access the data, so you really have no choice but to do back end validation (otherwise you'll have to code multiple copies of your business rules).

I conclude my remarks by reiterating that you should use declarative RI wherever possible, and then restrict access to your data by packages encapsulating select statements etc. with only the bare minimum publicly available in the package specification.

Regards

Tharg

Grinding away at things Oracular

 

[LeighRiffel]

Tharg,

I didn't mean to imply that I planned to handle any business rule violations by throwing exceptions, on the contrary I agree that trying to make this work that way would be a nightmare.

Alas, if only I could use Referential Integrity. We have a nasty primary application that maintains complete control over the schema that holds the data. Our modifications are limited to inserts deletes and updates, so even triggers are off limits. A rewrite is possible, unfortunatly right now we can't for various reasons.

I'm sorry I didn't explain all this earlier. RI would indeed provide a superior solution if it were possible.

I agree with your last statement as well. Package encapsulation is a wonderful thing.

 

[sem]

OK, but when you want to validate a set of parameters some internal dependencies may exist, thus in general you need not only check each parameter individually but also their consistency. But how can you check whether the rule is violated by invalid parameter itself or by its combination with another one? It's possible that wrong parameter in conjunction with correct one may fit the requirement, but correcting it breaks the whole rule. False implies anything.

My conclusion: you should stop processing on the first error, or you may make completely wrong assumptions about parameters validity.

Regards, Dima

http://seminihin.narod.ru

 

[Thargy]

Spread,

glad to hear what you said. For a while there, I thought you had completely lost the plot!

If the primary app has control over the data, and it can't be rewritten, could it be enhanced with a whole bunch of constraints being added? That way you would reduce the need for custom coding.

Is the "nasty" application written in Oracle? If it is, and you cant rewrite it, could you add packages to the nasty app which only permit valid DML?

Regards

Tharg

Grinding away at things Oracular

 

[LeighRiffel]

Dima,

Yes, you are correct. There will be situations in which the interdependancy of the data will require me to immediatly stop processing, but that will not always be the case.

Consider a procedure that accepts two parameters that update different fields in two unrelated tables. If an application calls the procedure with the first value null (invalid for this example), and the second as 5 (out of valid range 1-3) then I would want the procedure to let the application know both the missing parameter error and the out of range error. Imagine the users frustration if they were to only receive the first error and after correcting it another pops up.

The procedure parameters in my example might be different parameters in different procedures. I plan to have three or four procedures being called from within a transaction so errors could be detected in each independantly.

 

[LeighRiffel]

Tharg,

No, I cannot add constraints, indexes, or triggers. What I can do is write views and packages in a seperate schema that access the data in the schema controlled by the original application just as you suggest.

No, the primary application was not written in Oracle. It was written in Linc. Unisys ported the application from a mainframe and although they now store their data in an Oracle DB they don't take advantage of Oracles power (indexes, constraints, etc.), nor do they allow us to add any of that to their schema.

 

[Thargy]

Spread,

if the database is small enough, you might consider making a duplicate of it in a schema you own.

Bolt on every conceivable constraint (to avoid coding) then put triggers in (geck!) to do what constraints can't. Make all your changes in the constrained schema and then periodically synchronise to the master database with simple trigger driven insert/update statements.

This might cost a few bob in mass storage, but that's always cheaper than writing code.

Regards

Tharg

Grinding away at things Oracular

 

[sem]

Actually we use our home-made package that may either log_error or raise_error. In both cases error messages are inserted immediately in autonomous transaction (or sent via pipe in old or distributed systems), but the second one obviously aborts execution. You may develop your own, this is quite easy.

Regards, Dima

http://seminihin.narod.ru

 

[LeighRiffel]

Tharg, the database is too large to make a replication scheme practical not from a storage standpoint, but from an infrustructure/syncronization standpoint.

Dima, I have written something such as what you suggest. I thought about doing it in an autonomous transaction, but hadn't yet decided whether I wanted to go that route or not. Perhaps I will.

 

[sem]

You should remember that Oracle does not support (or at least didn't in 9i) autonomous transactions via db links, thus we still have to support pipe logging on systems that are known to be used in distributed environment. It's also possible to write directly to alert.log file, but we refused this option.

Regards, Dima

http://seminihin.narod.ru