thanks
Freek, January 18, 2003 - 8:48 pm UTC
Thanks for your advise.
I will put the logging in the local exception sections and put a package variable in the logging package.
regards
Freek D'Hooge
Error handling is still a unclear part
Tony, January 20, 2003 - 1:03 am UTC
Tom, Error handling is still a unclear part even for experienced programmers. In general, we store application specific, user defined error codes, error messages in a table.
1.Which is the best way in client sever model as well as in Web model?
2.Which is the best way to handle exceptions in nested block and nested function calls?
3. Is the following approach correct for Web apps?
Ex: FUNCTION fn1
IS
l_retval INTEGER;
BEGIN
--
--
l_retval := fn2(....);
/*
fn2 returns 0 if successfully executed,
else return user defined error code.
*/
IF l_retval <> 0 THEN
RETURN l_retval;
END IF;
--
--
END;
FUNCTION fn2(... )
IS
BEGIN
--
--
--
--
RETURN 0;
EXCEPTION
WHEN no_data_found THEN
RETURN 101;
-- Error description for 101 is stored
-- in a error_message table.
END;
Note: I hope you will cover this topic extensively in your next book on best practices.
January 20, 2003 - 10:36 am UTC
1) there is no difference to me between client server and web. In web, you still have a client, you have a server -- there is a man in the middle. but why would error detection, reporting and handling be any different? As far as the client is concerned -- it is just the same application. they care not how it is physically hosted as long as they can run it.
2) I let the exceptions propagate out to the layer that can handle it. If you cannot handle it - don't catch it. If it bubbles out to the topmost layer -- so be it, catch it, record it, report it to the end user.
I don't like your approach above of "return codes". I would just let the exception wind its way back up the stack (making it IMPOSSIBLE to ignore it -- how many bugs I've seen with return code programming where someone ignores the return code -- exceptions cannot be ignored unless you write explicit code to ignore them)...
3) web apps, client server apps, they are all just apps. No, I am not fond of a return code approach. Unless fn2 is prepared to DEAL with no_data_found (eg: it is an EXPECTED condition), fn2 should not catch it -- should not "hide it" as you are doing.
which errors to log
error logging telecommuter, January 20, 2003 - 11:57 am UTC
Depending on the design of your system, you might want to log the error where it first occurs, re-raise the error, then as it bubbles up log any other useful contextual information at each of the higher levels. E.g., with triggers that cascade multiple levels of inserts/updates, each row-level trigger has an error handler that logs key information that might not be available at other levels. If a particular level can't contribute useful information that isn't found at other levels, then don't bother. I'd rather have too much information in the error log than too little. More information means you can quickly establish the context of the error. It also means that you have a better chance of filling up the disk that contains the error log or blowing out the tablespace that contains the error table.
January 20, 2003 - 12:19 pm UTC
excellent advice.
How can we continue after catch an error?
A Reader, January 27, 2003 - 10:35 am UTC
Hi Tom,
I am writing a batch deleting procedure like the following. The problem I have is after an error is caught, the procedure quits. I want to continue to delete all the records after the error is caught. How can I achive that? Without the exceptions, the procedure will also quit after a fk violation. Thank you for your help.
CREATE OR REPLACE PROCEDURE PRO_DELETE AS
fk_violated EXCEPTION;
PRAGMA EXCEPTION_INIT(fk_violated, -2292);
BEGIN
FOR x in (select id user_ID from t1) LOOP
delete from t2
where id=x.user_id;
COMMIT;
END LOOP;
EXCEPTION
WHEN fk_violated THEN
DBMS_OUTPUT.PUT_LINE('FK_');
-- or insert into a log table;
WHEN OTHERS THEN
ROLLBACK;
raise_application_error(-20002, 'Error deleting');
END;
/
January 27, 2003 - 11:28 am UTC
the code should just be:
delete from t2
where user_id in ( select user_id from t1 )
and not exists ( select fk from child_table where child_table.fk = t2.pk );
no muss, no fuss, no exception, no code...
but, if you persist with writing lots of code -- it'll be
CREATE OR REPLACE PROCEDURE PRO_DELETE AS
fk_violated EXCEPTION;
PRAGMA EXCEPTION_INIT(fk_violated, -2292);
BEGIN
FOR x in (select id user_ID from t1)
LOOP
begin
delete from t2 where id=x.user_id;
exception
when fk_violated then
dbms_output.put_line( .... );
end;
COMMIT;
END LOOP;
END;
loose the when others -- it does nothing for you. the statement was already rolled back and you lose the error message with your approach -- you have no idea what the when others caught!
3-tier Applications
Tony, January 28, 2003 - 7:11 am UTC
Tom, as per your advice, its not a good idea to return user-defined error code. We are developing 3-tier web application using ASP, COM(VB) & Oracle. We want to display custom error messages. So, for example, whenever we try to insert new invoice details, we check for duplication. If invoice already exists, we return an error code to the COM object. Another procedure is executed to get the error message "Duplicate Invoice" from the message table. In this case, we have got to capture the exception in the procedure and return user-defined error code to the COM object.
How will I achieve the same if I don't capture exception and return error code?
Please enlight me.
If we don't handle exceptions and return error code.
January 28, 2003 - 8:00 am UTC
too bad you picked the most proprietary of mechanisms to build an 'open' system. the only thing in that mix that gives you a choice is your database -- everything else locks you into a single vendor, single platform.... oh well.
first of all -- 3 tier, 1 tier, 17 tier -- matters not, error handling is error handling is error handling.
If you want to map the oracle error into a customized error, do it at the VERY VERY VERY top level and re-raise the error, like this:
Have the client submit this block:
begin
procedure;
exception
when others then
raise_application_error(-20001,customized_error_msg);
end;
instead of just
begin
procedure;
end;
that way
o the error can be caught and handled at a lower leve IF IT MAKES SENSE to.
o the error will never be hidden
o you get your "customized" error message in a consistent fashion.
the Oracle Error Message will be customized error msg
OTHERS and the RAISE - lost line number
Tom Best, February 07, 2003 - 2:04 pm UTC
The advise to catch everything at the lowest level so it can be logged and reraised means the line number is meaningless, right? It will always point to the line containing the "RAISE" statement.
February 07, 2003 - 2:08 pm UTC
My point was -- if you are going to use this "log all errors" approach - lower level is best. I would rather have:
begin
....
begin
insert into t values ...
exception
when .... then call_logger;
raise;
......
end;
then
begin
....
insert into t values ....
.....
exception
when .... then call_logger;
raise;
end;
the closer to the error, the better the line number. If you catch the error "at the top level of plsql" -- you have lost that entirely. The closer to the error, the better.
One more question about an exception
Alla Gribov, March 10, 2003 - 10:34 am UTC
You said above, that delete statement should be written like this:
-----------------
delete from t2
where user_id in ( select user_id from t1 )
and not exists ( select fk from child_table where child_table.fk = t2.pk );
no muss, no fuss, no exception, no code....
-----------------
and not just a delete and then catch exception.
My question is: I have quite a few children table for the parent table than I am trying to delete from. What is going to happen to the performance of my procedure if I add at least 4 or 5 "not exists" to the delete statement? When I find only appropriate rows to delete (with extra "not exists") my code will look cleaner, but then Oracle would check for referential integrity anyway. Then why do the same thing twice??? (i.e. check for the existance of data in a delete statement and then let Oracle do the same thing by enforcing a referential integrity)
Thanks for your response
March 10, 2003 - 4:53 pm UTC
try it and SEE....
consider a single delete -- that deletes only that which you want
vs
procedural row by row code.....
a single delete will win out -- consider (i used timing this time but tkprof shows exactly how bad this really is -- using procedural code that is)
ops$tkyte@ORA920> create table p ( x int, data char(80) );
Table created.
ops$tkyte@ORA920> insert into p select rownum, 'x' from all_objects;
30553 rows created.
ops$tkyte@ORA920> alter table p add constraint p_pk primary key(x);
Table altered.
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 for i in 1 .. 5
3 loop
4 execute immediate 'create table c'||i||'( x references p )';
5 execute immediate 'insert into c'||i||'
6 select x
7 from p
8 where mod(x,4) = 0
9 and x between :x1 and :x2'
10 using (6000*(i-1)), 6000*i;
11 execute immediate 'create index c'||i||
'_idx on c'||i||'(x)';
12 end loop;
13 end;
14 /
PL/SQL procedure successfully completed.
ops$tkyte@ORA920>
ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920>
ops$tkyte@ORA920> delete from p
2 where not exists ( select null from c1 where c1.x = p.x
3 union all
4 select null from c2 where c2.x = p.x
5 union all
6 select null from c3 where c3.x = p.x
7 union all
8 select null from c4 where c4.x = p.x
9 union all
10 select null from c5 where c5.x = p.x );
23053 rows deleted.
<b>
Elapsed: 00:00:11.30</b>
ops$tkyte@ORA920>
ops$tkyte@ORA920> rollback;
Rollback complete.
Elapsed: 00:00:05.43
ops$tkyte@ORA920>
ops$tkyte@ORA920> begin
2 for x in ( select rowid rid from p )
3 loop
4 begin
5 delete from p where rowid = x.rid;
6 exception
7 when others then null;
8 end;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
<b>
Elapsed: 00:00:40.67</b>
ops$tkyte@ORA920>
Sorry for the confusion
Alla Gribov, March 11, 2003 - 8:10 am UTC
Dear Tom;
Thanks for your reply. I guess it's my fault, but I did not make myself clear
Using your example, here is what I had in mind:
begin
delete from p
where not exists ( select null from c1 where c1.x = p.x
union all
select null from c2 where c2.x = p.x
union all
select null from c3 where c3.x = p.x
union all
select null from c4 where c4.x = p.x
union all
select null from c5 where c5.x = p.x )
and x = 32;
if sql%rowcount = 0 then
raise_application_error(-20002, 'Did not delete');
end if;
exception
when others then
raise_application_error(-20001, 'Unable to delete');
end;
/
AND
begin
delete from p
where x = 32;
exception
when others then
raise_application_error(-20001, 'Unable to delete');
end;
/
Second code is much smaller and easier to read. As for the performance - they executed in the EXACT SAME number of seconds. So, I guess, there is no harm to do it the "smaller" way and I even can distinguish between not deleted, because there was nothing to delete and not deleted, because of the referential integrity constraint (I catch ORA-2292, not just "others")
Thanks again for your help!!!! As always you are right - just DO AND SEE! Sorry again for taking your time :-)
Sincerely
Alla
March 11, 2003 - 8:41 am UTC
well -- they are apples and toaster ovens entirely!
The second one is obviously the only correct approach. Look at the deletes -- you are assuming that sql%rowcount = 0 means "child key found" -- but maybe it means "x=32" was not found.
My point was -- if you want to "delete all parents that have no child", the single delete is correct. If you are just deleting a single row -- you would not go out of your way to check for child records -- that would be incorrect.
Where can I get list of all exceptions???
A reader, February 19, 2004 - 5:28 am UTC
February 19, 2004 - 11:07 am UTC
When others then...
sonali, June 02, 2004 - 10:31 am UTC
You always say that when others then rollback raise_application_error block will hide the error because of rollback statement. It should be just when others then raise_application_error.
The statement is already rolled back...
What if you have nested transactions..
Proc1 calls proc2 and proc2 gets some oracle error.. you said that statement which gave error will be rolled back anyways.. but what happens to other statements in proc1 and proc2 which were before this error statement, will they get rolled back or commited..
I always get confused with the error checks. This discussion was very helpful.
Do you have this topic discussed in your new book ?
Thanks
as always
June 02, 2004 - 11:22 am UTC
i don't say that? where did I say that.
I say a "WHEN OTHERS" that is not followed by a "RAISE" (raise_application_error is like raise) is 99.999% of the time a bug in the code as it hides the error
I also say "i wish plsql did not have commit and rollback" for they are abused and misused in there. the rollback happens automagically if you let the exception that you cannot handle PROPAGATE.
the 'statement' is not rolled back. That entire TRANSACTION is wiped out (and the caller has no clue).
I discuss this topic in great detail in the "old" book.
bubbling up the error info
Mohini, July 20, 2004 - 12:05 pm UTC
Oracle 9.2.0.5 on HP Unix
Tom,
You said
"
The top
level routines will get an error like "hey, the column was too large to fit" but
they won't have any idea *where* this exception was raised.
"
For a nightly batch process..
If we have a wrapper for all
the procedures that we need to run..and have the error handling only in the wrapper (to log error in a table)
like
procedure wrapper
as
proc a;
proc b;
Exception
when others
log_error_proc;
end wrapper;
**proc a and b won't have any error handlers.
And the exception bubble up to the error handler of the wrapper.
Can DBMS_TRACE or DBMS_STACK get the exact line and the package/procedure name
where the error happened?
If yes..Do you have an example?
Thanks
July 20, 2004 - 8:30 pm UTC
yes and yes. search for dbms_trace on this sight, i already set up examples.
Is there an easy way to differentiate between 'out of space' type errors and other errors
Ian, July 28, 2004 - 10:51 am UTC
Tom
I really don't like using when others but we have a requirement in some batch processing to trap an error (duplicate value on index or whatever) and report it in a table - BUT if it is a storage type error - such as ORA-01653: unable to extend table then we want to raise it as there is no point in continuing.
Is there any easy way to figure out if an error is of this type or do we need to plow through the Errors Manual and test for all the relevant ones.
I had hoped the Exception STORAGE_ERROR would do the trick - but that seems to do with PL/SQL Memory.
Any ideas?
Regards
Ian
July 28, 2004 - 1:18 pm UTC
you would need to categorize the errors you either wanted to track or wanted to ignore - we do not have such a categorization.
A reader, December 07, 2005 - 11:25 am UTC