Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Robert.

Asked: July 17, 2002 - 2:32 pm UTC

Last updated: April 12, 2011 - 11:33 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I am in the process of designing an error handling and failure notification system for use by our all our custom PL/SQL programs.
My plan is that all our custom PL/SQL programs, in the case of a fatal error, will call a single error handling procedure from within it's "WHEN OTHERS" exception clause.

The intent is that we will have a centralized, standard, efficient error handling system, particularly an error notification system whereby the appropriate people will automatically be notified in case their program fails.

This error handling procedure will receive parameters such as: calling program, sqlerrm, key variable values at time of error, etc.

The error handling procedure will then send an e-mail notification to the appropriate person (based on the program that failed), write a formatted error messege to the appropriate location based on the failed program, and bail out.

I have some questions, please regarding this.
1. Do you like this idea philosophically and practically (i.e. having a centralized error handling and notification procedure/system) ? if not why?
2. I am concerned that by calling a procedure inside the exception clause, even though I am passing the current error info
(sqlerrm, sqlcode)... I am afraid I may be losing some error info or masking some error info. I am wondering if I am losing anything by not dumping the original error immediatly, while it is 'fresh', instead of passing the 'sqlerrm' to another procedure... is this a valid concern ? why or why not ?
3. Can I count on 'format_error_stack' to give me the failing program's error or should I insist they pass this (e.g. sqlerrm) in to the error handling procedure?
4. Do you have a better way to implement a centralized error handling and notification system; or do you have any other comments, suggestions, critisisms regarding my idea ?

Thanks for your valuable expertise. Please answer the questions as detailed as possible.

Thanks,

Robert.



and Tom said...

1) no, not really. Here is why.

I believe this common routine would HAVE to call "raise;" to re-raise the error (else the caller of the procedure has NO CLUE that an error happened. Dont say "we'll use return codes -- that defeats the entire purpose of exceptions")

So, if this common routine has to re-raise the exception (and it DOES, 100% on that) - then what happens when you have:

p1 calls p2 calls p3 calls p4

and p4 "fails". Now the when others in p4 logs the error, sends the email and re-raises the error. P3 gets the error and does it all over again. Then p2 then p1. You get 4 notifications -- 4 logs -- for the single error.

I believe this is the responsibility of the CALLER of P1 -- it'll get the error and that is where this error handler belongs.

2) You'll lose the line number of the source codee that actually flung the error.... Only the CALLER of p1 can get that.

3) you can call sqlerr to get the error

4) do it at the TOP level, at the client level (eg: at the app server) not inside the stored procedure.



Rating

  (102 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

We can't put the notification our client apps.

Robert, July 17, 2002 - 4:05 pm UTC

Tom,

We use a 3rd party client app. Our PL/SQL jobs are scheduled from within this client app's scheduler.

So do you think it might work IF an application only called this common error handling procedure once? (e.g.: in the exception clause of the main block) ... all inner calls would have 'raise' within their "when others"...(?).

I would appriciate any other ideas or feedback.

Thanks,

Robert

Tom Kyte
July 18, 2002 - 5:32 am UTC

That falls in line with my "the caller should handle it" however -- if the caller is a PLSQL block -- you will lose the line number information whence the error occurred. This information is what is truly useful -- it would be a shame to lose it.

search this site for dbms_trace for a possible solution in that area.

this approach is already in use!

Alex Rakhalski, July 18, 2002 - 9:51 am UTC

Hi, Tom, hi, Robert!
Robert, you described almost same thing, what we are using in real-life big project. It works quite fine! We have a lot of server-side PL\SQL code - mainly packages and triggers. They calls each other in such complex ways, that we need some way to see exactly where and what is happened (For example, procedure proc1 executes some DML and implicitly fires table trigger T1, it in turn fires table trigger T2, it calls proc P2, it calls proc P3, proc P3 recursively calls himself 5 times, and raise exception). We developed error-handling package (about 1996 year), which successfully works several years in large financial information system. Lets call it PK_TRACE, for example. A few months ago I rewrite this package for 8I (with autonomous transaction package become much more elegant!) for our new project.
Describe briefly main principles.
1. Each procedure, function, trigger and so on begins with service call to pk_trace.pr_begin_routine.
2. Each procedure and ... calls before exit pk_trace.pr_end_routine. If there are several exit points (RETURNs from function, for example), pk_trace.pr_end_routine must be before each of them.
3. Each proc ... has standart WHEN OTHERS handler, with calls pk_trace.pr_except_handler.
4. If developer wants to issue custom error message to user, he also can call pk_trace.pr_except_handler(Error message) from any place in his proc.
5. pk_trace.pr_except_handler saves in background error messages and their call stacks in tables. We can build call stack on previous calls to pr_begin_routine/pr_end_routine, call Oracle for help (dbms_utility.format_call_stack, but you will not see triggers in stack in this case).
6. Standart client-side routines detects call to pk_trace.pr_except_handler and display corresponding error information on special screen form to end user. Error information remains in tables for later uses also.
7. Because we register facts of routines starting/finishing, we can also collect PL/SQL performance metrics in background and save them in tables too. We have some parameters, influencing collection performance statistics.

What about reraising exception? pk_trace.pr_except_handler does some intelligent processing (we saves information to tables, you send e-mail) only once, in first place. All consequent calls to pk_trace.pr_except_handler (while "thrown out" to uppest caller) does nothing, only re-raises exception.



Errors

mo, February 25, 2003 - 6:27 pm UTC

Tom:

I have a web page where user enter several rows in one page and then he saves it. If one colum "storage_code" is not a valid entry, it will raise a foreign key violation nd then I create a page that tells user to go back and edit that storage code.

Now, I want to take all the user-entered records and check all sotrage codes and report them at once instead of one at a time. Is this possible and how would you do it?

Do you go to an exception handler as soon the first foreign key violation happens and then check for all other records in a select statement in teh exceptin handler and keep raising the excpetion again. Can this be done?

Thank you

Tom Kyte
February 25, 2003 - 8:53 pm UTC

dude, you have an array of them -- just check them?

you would:


for i in 1 .. array.count
begin
insert ...
exception
when ...
bad_codes := bad_codes || ',' || array(i);
end;
end loop;
if ( bad_codes is not null )
then
print out error message "these codes are bad, try again " || bad_codes;
ROLLBACK;
else
normal processing...
end if

error

mo, February 25, 2003 - 9:14 pm UTC

Tom:

1. I thought when an exception is raised in pl/sql, processing stops. Is it because the loop it goes back to process the next array value.

Thank you,

Tom Kyte
February 25, 2003 - 10:16 pm UTC

you need to read more about exceptions and error handling in the plsql guide (or in my book -- i cover it there as well)

processing continues at the nearest exception block. processing never "stops", processing just goes somewhere else. You put the exception block in the loop and the loop will "continue"

errors

mo, February 26, 2003 - 11:52 am UTC

Tom:

Thanks, it works nicely. However I declared
bad_Codes varchar2(4000)

What about if there are more than 4000 bytes. How would you handle it then?



Tom Kyte
February 27, 2003 - 7:07 am UTC

try LONG which is a varchar2 32k in plsql

CLOBber it

KP, February 26, 2003 - 1:06 pm UTC

No doubt MO tried to bump it over 4000.
Anyone for LONG RAW?

Me? I would use a CLOB.


No perfect solution for error handling

Milan Adamovic, April 17, 2003 - 1:29 pm UTC

I have been investigating perfect error handling approach in PL/SQL and come up with the conclusion that it cannot be really done.

We have been using approach with standard "when others" exception handling approach. The handler logs the error in the error log table and re-raises the exception. The problem with this approach is that you loose the line number where the error occurred, which could otherwise be obtained by parsing calling stack (dbms_utility.format_call_stack).

Here are the issues, if you do not handle the error until the main calling procedure:
- Main procedure (that is invoked by the client) can very often be at several levels of the calling chain, especially during testing and debugging, as well in the final application.
- As soon as you put exception handler (in the main procedure, for instance), you loose the calling stack and you cannot trace back to the procedure where the error originated. I am not sure why Oracle does it this way. Something like print stack trace from Java would resolve all problems. Note that I haven't tested this behavior in 9i.

To me the first solution is still better, because you at least get the procedure name where the error occurred.


Tom Kyte
April 17, 2003 - 3:07 pm UTC



In developement -- use dbms_trace, problem solved. search this site for dbms_trace.


using when others -- hate it. client logs errors. In 3 tier -- this is trivial. In client server, harder, but tell me -- are you building a client server application?

Useless Error Stack?

Vicky Fan, August 20, 2003 - 8:31 pm UTC

The error stack seems only record error number and message without package/procedure names and line number at which the error occurred, with the exception of trigger. Why wouldn't Oracle provide it?

Your whence function has to be executed before each major step in user procedures in order to capture the line number.


Tom Kyte
August 21, 2003 - 5:38 pm UTC

the package and procedure is there?

what useless error stack do you refer to?



Vicky Fan, August 22, 2003 - 3:12 pm UTC

dbms_utility.format_error_stack

With dbms_utility.format_call_stack, the original line number at which the error occurred is lost in exception block. The format_error_stack records error module name without error line number, at least with 8.1.7.

If exception is required to be logged in the database table, the only option to record the exact error line number is using variable locator or the whence function to mark up each major steps in the source code. Not at all a graceful solution than if the format error stack can track and provide all the error details in the exception block.

Tom Kyte
August 22, 2003 - 8:35 pm UTC

or dbms_trace

it'll do it as well. search this site for that.

A reader, August 25, 2003 - 2:44 pm UTC

My only concern is: Is there any performance impact to use dbms_trace in production environment as error logging or is it only meant for debugging in development environment?

Thanks.



Tom Kyte
August 26, 2003 - 7:43 am UTC



sounds like a prime time to "benchmark"


of course, every single extra line of code adds a Performance impact -- by you simply writing the first line of code, you've introduced a performance impact.

what you need to determine, based on your requirements is:

does the value of having this additional information outweigh the cost of having this additional information.

perhaps you make this "feature" something that is EASILY enabled via a parameter somewhere -- so you can turn it on and off at will. That is what I do. I leave all code fully instrumented and turn it on/off when I want. It is in production you REALLY need this capability as they generally don't allow you to just drop in some debug code then! being able to turn this on is of great benefit.

package excpetion

mo, December 02, 2003 - 12:19 pm UTC

Tom:

I have 6 procedures in a package that use the same exception. I declared the exception in the package body variable section and put the EXCEPTION no-records-Found at the end of the package. However I got an error in compilation.

Is it true that I have to put the expcetion handler in each procedure body? If it is true, then I thought packages are supposed to help you build modular code rather than repeating the same routine in each procedure.

Thank you,

Tom Kyte
December 02, 2003 - 12:37 pm UTC

example, example, example


examples are worth a trillion words.


but if I follow you -- you are asking "does the exception handler actually have to be in executable code -- places where code goes?" the answer is YES, the exception block must be in a scoped section of executable plsql code.


create package body demo_pkg
as
-- no code here!

procedure p
is
begin
-- code here, here we can have an exception handler
end;

-- no code here, no exceptions can be caught here, we don't have any CODE

procedure p2
is
begin
-- a new scope of code, exception away
end;

-- no code here, no exceptions where


begin
-- code here, could have an exception block, but remember SCOPE
-- this block ONLY runs once per session, when the package is first
-- touched
end demo_pkg;
/


You need to think about what exceptions are for

they are for handling EXPECTED errors.

They should surround the TINEST, MOST FOCUSED piece of code possible. EG:


begin
.... code code code.....
begin
select ... into .... from .... where ....;
exception
when no_data_found then ....
end;
.... code code code .....
end;



sounds like you might be trying to be "lazy" and type just a little code with a huge exception block -- but that would be wrong, very bad. exception blocks = small, tight, little.

exceptions

mo, December 03, 2003 - 4:43 pm UTC

Tom:

I am not trying to be lazy but I am trying to write good modular code like you said. I have one exception handler (to handle no records found) in about 6 procedures.

I was thinking there should be a better way to repeat the same code 6 times in each procedure.

Obviously I can't write it in the package body. I guess the only way is to write another procedure for that block of code and call it in the exception handler?

For now I will stick with the handler written 6 times.



Tom Kyte
December 03, 2003 - 6:56 pm UTC

that is not good modular code.

Good modular code would have exception blocks at the very lowest level possible (as close to the source of the suspected error spot as humanly possible) that all call a subroutine <<<==== thats the modular part.


don't repeat the code, put the code into a subroutine, call the subroutine.


you are not "stuck" with anything, you are coding "properly" when you have the 6 well formed, meaningful, clearly defined, close in scope exception blocks.

Don't be mad
Be proud of the good code.

Exception Stack

malcolm, December 04, 2003 - 7:19 am UTC

Tom, I want to get the whole error stack in PL/SQL. dbms_utility.format_error_stack does not give me the entire error stack, but somehow SQL*Plus reports it, so the information must be available. How does SQL*Plus get the line numbers and the error stack?

  1  declare
  2  procedure p(i pls_integer) is
  3  begin
  4     if i > 0 then p(i-1); else p(1/0); end if;
  5  end;
  6  begin
  7     p(5);
  8  exception
  9     when others then
 10        dbms_output.put_line(dbms_utility.format_error_stack);
 11* end;
SQL>/
ORA-01476: divisor is equal to zero


  1  declare
  2  procedure p(i pls_integer) is
  3  begin
  4     if i > 0 then p(i-1); else p(1/0); end if;
  5  end;
  6  begin
  7     p(5);
  8* end;
SQL>/
declare
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 4
ORA-06512: at line 4
ORA-06512: at line 4
ORA-06512: at line 4
ORA-06512: at line 4
ORA-06512: at line 4
ORA-06512: at line 7
 

Tom Kyte
December 04, 2003 - 9:50 am UTC

only the CLIENT can get it. one you "catch" the exception, the error stack is reset, cleared.


search this site for dbms_trace if your goal is to log this information.

Do you think that this is a design bug?

Oleksandr Alesinskyy, March 02, 2004 - 10:09 am UTC

Tom,

Do you agree that inavaliability of such information on a server is a obvious design bug in Oracle and it has to fixed ASAP?

Yours sinkerely,

Oleksandr Alesinskyy

Tom Kyte
March 02, 2004 - 3:58 pm UTC

No, I believe if you have a stored procedure that catches an exception, it is doing so to DEAL WITH IT. Stored procedures should not capture "when others" -- the client will get the error, the client can do with it what it wants (including sending it right back to another procedure to get logged if they like).

You can search this site for dbms_trace to see another method.

but if you do see it as an obvious short coming, as a supported customer, you have the ability to file enhancement requests. You would need only to develop the business case and submit it to support.

10g DBMS_UTILITY.FORMAT_ERROR_BACKTRACE

Gary, March 02, 2004 - 5:49 pm UTC

Oracle 10g seems to have a new function in DBMS_UTILITY called FORMAT_ERROR_BACKTRACE :

This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of the SQLERRM function, but not subject to the same size limitation.

This MIGHT be what you are looking for (though I don't have a db to test this on, but can only see the documentation).

</code> http://download-west.oracle.com/docs/cd/B12037_01/appdev.101/b10802/d_util.htm#1003800 <code>


Tom Kyte
March 02, 2004 - 7:27 pm UTC

very nice, thanks for pointing that out -- it does that.

ops$tkyte@ORA10G> create or replace procedure p3
  2  as
  3  begin
  4          raise_application_error( -20001, 'you lose' );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create or replace procedure p2
  2  as
  3  begin
  4          p3;
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA10G> create or replace procedure p1
  2  as
  3  begin
  4          p2;
  5  end;
  6  /
 
Procedure created.
 

ops$tkyte@ORA10G> begin
  2          p1;
  3  exception
  4          when others then
  5                  dbms_output.put_line( '"' || dbms_utility.FORMAT_ERROR_BACKTRACE || '"' );
  6                  dbms_output.put_line( '"' || sqlcode || ' ' || sqlerrm || '"' );
  7                  raise;
  8  end;
  9  /
"ORA-06512: at "OPS$TKYTE.P3", line 4
ORA-06512: at "OPS$TKYTE.P2", line 4
ORA-06512: at "OPS$TKYTE.P1", line 4
ORA-06512: at line
2
"
"-20001 ORA-20001: you lose"
begin
*
ERROR at line 1:
ORA-20001: you lose
ORA-06512: at line 7
 

Design bug discussion

Oleksandr Alesinsky, March 03, 2004 - 9:27 am UTC

>Followup:
>No, I believe if you have a stored procedure that catches an exception,
>it is doing so to DEAL WITH IT.
>Stored procedures should not capture "when others" --
>the client will get the error,
>the client can do with it what it wants
>(including sending it right back to another procedure to
>get logged if they like).

Do you really believe that such approach is very useful in batch processing - when I shall not stop batch processing, e.g. hundred thousands of records to deal with one erroneous record (or may be not errorneous but not fitting existing program code). And what to do if this batch is performed completely inside server by means of DBMS_JOB? There is no "client" at all.

More or less standard approach in batch processing is to process as much as possible and log all exception for further investigation.

It seems that in 10g problem is resolved, but what to do in prior versions?


Tom Kyte
March 03, 2004 - 10:19 am UTC

the source of the error must catch the error then. you put exceptions where you expect them (eg: at the lowest processing of the row -- you know the row failed, you know why the row failed, you log that the row failed and continue).


I would not even really care that it was line 55 of procedure p3 called from line 1023 of procedure foo called from line 23452 of x -- p3 would have been the "process this record procedure" and p3 would have CAUGHT the expected error (row failed processing for reason X) and p3 would have logged this fact.

Here - you are EXPECTING an error

Error you are expecting "row failed processing"

why did the row fail processing? your code would have told you that (via RAISE <named exception> or raise_application_error).

what if the error is unexpected (eg: out of space) -- do you want to continue ? no, you cannot continue.


So, I'm back to "there are things your code expects, catches, deals with". "there are things your code does not expect and would terminate for"




Why does oracle provide only 1000 custom error numbers

A reader, March 03, 2004 - 10:28 am UTC

Why does oracle provide only 1000 custom error numbers
from 20k to 21K ?

is it very small for mid to large system ?

Design bug discussion - 2

Oleksandr Alesinskyy, March 04, 2004 - 4:09 pm UTC

>I would not even really care that it was line 55 of procedure p3 called from
>line 1023 of procedure foo called from line 23452 of x -- > p3 would have been the ....

>Here - you are EXPECTING an error

>Error you are expecting "row failed processing"

>why did the row fail processing? your code would have told you that (via RAISE
><named exception> or raise_application_error).

Yes, I agree with you almost completely. Definitely, exception has be carched in lowes-level routine. But ... This routine have several SQL operators in it. And it is quite tedious and boring to write begin/exception/end block around each of them. And, BTW, code becomes much less readable. It is much easier to have one exception handler and the procedure end. And if this handler would log error and original line it will be more then enough.

Tom Kyte
March 04, 2004 - 5:26 pm UTC

guess we'll have to agree to disagree -- having a log record that "source code line 532 blew up" is much less useful than "caught exception for sqlcode 1401 while validating column foobar, value of foobar was X"

I prefer code that does the work for me (for the guy monitoring the failure). Can you imagine, you are the DBA, the developer gave you a procedure. You know now that "there was a 1401 on line 532 of procedure foo" -- now what? what field was wrong? what caused the 1401? how does the DBA set about figuring out what the root cause was?

I as the DBA would much rather you as the programmer would have caught the exception (you were expecting) and raised a meaningful error so I can fix it.

I do not find it to make code unreadable -- rather the opposite in fact. Readable, maintainable and generally more usuable by others who do not read code for a living.

Multiple statements in one block

andrew, March 04, 2004 - 7:29 pm UTC

This is a common approach to identify which statement failed in a block if you don't want individual handlers for each statement:

SQL> DECLARE
  2     v_stage   VARCHAR2 (20);
  3     v_dummy   VARCHAR2 (20);
  4  BEGIN
  5     v_stage    := 'stage 1 of 2';
  6     SELECT 'XYZ' INTO v_dummy FROM DUAL WHERE 1 = 1;
  7  
  8     v_stage    := 'stage 2 of 2';
  9     SELECT 'XYZ' INTO v_dummy FROM DUAL WHERE 1 = 0;
 10  EXCEPTION
 11     WHEN NO_DATA_FOUND
 12     THEN
 13        raise_application_error (-20501, 'Whoops: no_data_found at ' || v_stage);
 14  END;
 15  /
DECLARE
*
ERROR at line 1:
ORA-20501: Whoops: no_data_found at stage 2 of 2
ORA-06512: at line 13 

Tom Kyte
March 04, 2004 - 7:44 pm UTC

I'd go one step further if you don't mind :)

change lines 5/8 into:


dbms_application_info.set_client_info( 'stage ....' );


and line 13 into "|| userenv(client_info)"


and you now have not only accomplished the goal of being informative when you have an error, but you let the DBA peek at v$session to see where you are (set action and module too!!!!) for long running things use set session longops, even better.

Design bug discussion - 3

Oleksandr Alesinskyy, March 05, 2004 - 8:19 am UTC

To some extent you are right. But...

1. I don't care about DBA - it is not his resposibilty to resolve the problems with content.

2. Even if would surround each and any SQL statement with his own exception block I may be usable to provide meaningful error. E.g. if I have "ORA-01401 Insrted value to large for column" I have no means to say which column and which value (and this is true for many other error codes as well, but inconsistent error info in Oracle is quite separate story, we have discussed it ~1 year ago). So, in such cases one error handler that log error code and line that has caused an error is as good as lot of error handlers around each statement.

3. Concerning workaround with "stage:=stage+1" - this is exactly what we do, but as any thing that require additional human efforts it is somewhat error-prune.

4. Concerning readability - it is depend heavily on the taste but I hardly can understant how code in which 3/4 of lines are just "overhead" can be more readable then code that contain only essential lines. The very meaning of exception handling machanisms is to separate error processing from normal code flow and it is completely lost if we heavily intemix normal code with exception handling as you suggest.

Tom Kyte
March 05, 2004 - 8:58 am UTC

1) you are kidding right? you have this job for the rest of your life? and you are immortal right -- cause if you get hit by the #14 bus tomorrow -- hmm, ok.


2) back to square one, that insert should have the exception block.

3) and makes the code more maintainable from each and every perspective.

and i vehemently disagree with "somewhat error prone".

4) overhead is in the eye of the beholder. Easily 50% of my code (easily) or more is code that is there to help us out when it "goes wrong".

Change the NO to YES in this URL for example. Every piece of code -- every piece of code -- is instruemented to the n'th degree.

Imagine the database without the "overhead" of V$ tables, sql_trace, etc.

If you EXPECT an exception -- catch it, deal with it, move on.

if you do not -- you cannot MOVE ON (by definition -- your program is in "unknown state foobar" in the most literal of senses). If you expect 'this insert can fail, might fail and if it fails -- it is OK, we log that fact -- rollback to this savepoint, whatever and continue processing over here'. If you never expect that insert to fail (eg: insert into audit_trail) and it does -- bamm, you are dead. stop processing, something really bad has happened.


There are exactly two types of errors in the world

a) expected, catch em, deal with em, move on
b) unexpected, nothing you can do


It is exactly this "ignore errors" sort of stuff that gets garbage in and garbage out.


Again, we are at the "we'll HAVE to agree to disagree" cause I just disagree. I would love it if 90% of code were overhead -- if that code helped me

a) diagnose issues
b) tune
c) audit

code is cheap. fixing it ain't. your code is not your code, it is code someone else will be running some day.

Remember "GPF's", how useful. "program x enountered exception foo at program offset 0xAFBC4323"

great for a programmer (maybe, that is questionable).


Writing bullet proof code takes time, it takes instrumentation. My goal has always been to make it so that I can disappear after writing something and have people that use it be able to figure out "what went wrong"....




Design bug discussion -4

Oleksandr Alesinskyy, March 06, 2004 - 6:07 pm UTC

>1) you are kidding right? you have this job for the rest of your life? and you
>are immortal right -- cause if you get hit by the #14 bus tomorrow -- hmm, ok.

I am not kidding - system is installed on many sites and DBA no nothing about it internals (and don't suppose to know). Support team manage it.


>2) back to square one, that insert should have the exception block.

And how this exception block help to know which value for which column goes wrong? For this error (and many others) such information is not provided by Oracle. So this block is no more useful then single block for a whole procedure.

>3) and makes the code more maintainable from each and every perspective.

>and i vehemently disagree with "somewhat error prone".

If I have to add something each and every time then it is obvious then once I (and I guess you as well) will forget. So it is error prune, If I have this information provided automatically chances to not obtain it are much smaller.


>4) overhead is in the eye of the beholder. Easily 50% of my code (easily) or
>more is code that is there to help us out when it "goes wrong".

I guess you slightly misunderstood me. I meant only that I prefer logically separate normal processing bbranch and exception processing branch and not mix and match them. BTW, have you ever heard about "aspect programmng"? I guess it is one of the greatest ideas in software development. It is pity that it still "in pampers" yet.

>Change the NO to YES in this URL for example. Every piece of code -- every
>piece of code -- is instruemented to the n'th degree.

The only problem that high level of instrumentation can cause high level of bugs in instrumentation itself. Especially if I have "mix and match" this instrumentation into the each new development in 10**n places. It is very ponderable reason to separate normal processing and error handling. In this case it is much easier to implant existing instruments into new development.

>Imagine the database without the "overhead" of V$ tables, sql_trace, etc.

Quite easy. DBase-III :) Really I have absolutely nothing against instrumentation of code - just opposite. Really I complain that inability to access proper line number (as well as all other Oracle RDBMS incosistentcies in providng information about exceptions) is exception handler prevent instrumentation in easy and convinient way and force to make those instrumentation with more efforts then really required.

>If you EXPECT an exception -- catch it, deal with it, move on.

>If you expect 'this
>insert can fail, might fail and if it fails -- it is OK, ...
>If you never expect that insert to fail (eg: insert into audit_trail) and it does --
bamm, you are dead...

!00% agreed. The only thing that during processing on one input row I may do dozen inserts and/or updates and at the moment of processing if ANY of them fails I need to do the same thing - log when was fail, where was fail and which input roew caused it. This I have to be able to do with single exception block - save the fact that this exception block has no access to line number in which fail occured.
To be honest I wish from Oracle with each exception much more - some exceprion description structure with following information (in "machine-readable" form :) :
1. Error code
2. Procedure name (packagage name and procedure name in case of packaged procedure) from that cause this exception
3. SQL statemnt text (if exception in SQL)
4. Position in this text (in case of parse error)
5. Bind variables values (if applicable)
6. Name of database object (view, table and so on) if applicable
7. Column name (if applicable)
8. Name of violated constraint (if applicable)

I understand that to the moment it is only the figment of the fevered imagination.

The most interesting thing that all (or almost all this information is available from client and. to some extent, from the server side code as well, byt one pieces are available from DBMS_SQL, other - from server-side Jave, some - from plain PL/SQL. Later I guess has the least amount of those information and I always wonder - why.

>I would love it if 90% of code were overhead -- if that code helped me

>a) diagnose issues
>b) tune
>c) audit

And I wouldbe absolutely glad if I will be able to do it not with 90% code but with 20% (or 30%). And it will be definitely possible with proper support from database cote.

I am not against instrumentation of code - I against the situation in which for this purpose I have devote 80-90%
of code. In some situations it is unavoidable, but whaen IO need quite simple thing like elementary logging and auditing it is untolerable.

>code is cheap. fixing it ain't. your code is not your >code, it is code someone
?else will be running some day.

Exactly. So it have to be well structured and clean.

> My goal has
> always been to make it so that I can disappear after ?>writing something and have
>people that use it be able to figure out "what went ?wrong"....

My as well (for last 24 years that I'm in professional software development). I just want better support in achiving this goal from tools that I use.




Thiru, May 06, 2004 - 11:22 am UTC

Tom,
Thanks for all the inputs. I have a question about hierarchy in expcetion handling. Need your valuable input.

FUNCTION FIRST CALLS FUNCTION SECOND:
NO_DATA_FOUND EXCEPTION HANDLING DONE HERE IN FUNCTION FIRST

FUNCTION SECOND CALLS FUNCTION THREE.
NO EXCEPTION HANDLING HERE IN FUNCTION SECOND

IN FUNCTION THREE
SELECT INTO ... ( there is no data for this statment)
NO_DATA_FOUND WILL BE RAISED BUT NO EXCEPTION HANDLING DONE HERE.

The issue is:

WHEN FUNCTION FIRST IS CALLED, THERE IS NO INDICATION OF THE
FUNCTION THREE WHERE THE ACTUAL SELECT STATEMENT THROWS THE ERROR.

IS THERE A WAY TO GET THE NAME OF THE LOWEST FUNCTION IN HIERARCHY
WHERE THE SELECT STATEMENT GETS EXECUTED
OR
THE ENTIRE HIERARCHY OF CALLS MADE. IN THIS EXAMPLE:
FUNCTON FIRST
FUNCTIN SECOND
FUNCTION THIRD

----------
when the exception is not at all handled, we can see all the function
names with line number.





Tom Kyte
May 06, 2004 - 2:58 pm UTC

if you handle the exception -- you were EXPECTING the exception -- and hence the exception isn't an exception.

In 10g, you can get the full stack, in 9i and before you cannot (but search for dbms_trace on this site)

A reader, May 06, 2004 - 3:00 pm UTC

Thanks I will look into dbms-trace.

who_called_me

A reader, June 20, 2004 - 3:37 pm UTC

The who_called_me utility you have on this site....that just gives me the calling package. Is there a way it can give me the name of procedure/function within the package that called me?

Thanks

Tom Kyte
June 20, 2004 - 4:28 pm UTC

no -- you get the line number however.

the package is the "thing". the function/procedure could be an internal one (not in the spec). also there could be 50 procedure "p"'s in a package as well.

output parameter for error code?

j., June 22, 2004 - 11:31 am UTC

hi tom,

consider a "server", a stored procedure that gets started via dbms-job. it contains a loop in which "requests" (sent from "clients" through dbms-pipes) are accepted and processed. the only way to exit this loop is a request to do so. exceptions have to get handled inside this loop but they must not be reraised since this would stop the "server". that 's why the "server's" exceptions can't be passed to its "clients" as exceptions ...

but (as you 've mentioned above): using return codes or output parameters instead of exceptions makes it very hard to establish proper error handling on the "client" side. one would have to reraise the exception based on the error code returned by the requested "service".

could you suggest another/better way to achieve both goals:
1st: preventing the "server" from beeing stopped due to runtime errors inside its routines (possibly caused by invalid input provided by its "clients").
2nd: getting runtime errors from "server" side propagated through "client" layers.

any hint/help is appreciated.

thanks in advance.

Tom Kyte
June 22, 2004 - 1:56 pm UTC

use AQ, not dbms_job for what you described. using jobs like that is a problem waiting to happen.

AQ with a stored procedure as the target is the right approach to this problem. Then they can raise exceptions and all -- the client is AQ, not "itself"



sorry, didn't get that

j., June 22, 2004 - 3:13 pm UTC

please, let me explain our solution a little bit further:

the kernel or our "server" locks like that:

loop
get client_request from dbms_pipe;
process client_request (incl. handling exceptions);
send result to client through dbms_pipe;
exit when shut_down_requested;
end loop;

dbms_jobs is just the tool we use to start this code - we could have started it with "begin <server_stored_proc>; end;" instead.
dbms_pipe is used for communication between "clients" and the "server" - we could have used dbms_aq for that.

can you please explain the problems with our approach in a little more detail and how one could avoid these problems (and solve the exception handling issue) by using AQ?

Tom Kyte
June 22, 2004 - 9:32 pm UTC

I know exactly what you did -- i've seen it done before.

it is a really bad idea, it basically defeats the job queue interface, leads to a whole set of problems. doesn't work in the long run.

the natural way to write this would be "pro*c program reading from a pipe, processing request and logging errors as needed, writing responses back".

if all of the logic can be in plsql (at that point, one asks "why bother doing this pipe thing, what is wrong with parameters to a stored procedure and just calling a stored procedure"), then AQ would be a better method. You would enqueue a message from the invoker. AQ (a C program) would dequeue it, invoke the handler (can be a plsql function or anthing really) and log errors for you. The handler can then write back a message using an in memory queue or use pipes to send the response back.



the reasons why ...

j., June 23, 2004 - 12:58 pm UTC

... we 've decided to do it that way: we started with 7.3.x. we had the need for autonomous transactions and for "serialization" of access to certain resources as well.

therefore we couldn't handle the "requests" within the requestor's session, but had to use a "singleton" instead and to establish some cross-session-communication (if we 're right).

switching to AQ would *now* be an option - but only if it improves our exception handling, that is: exceptions get propagated "as is" to the invoker. can this be achieved with AQ (if the handler is pl/sql as well as the invoker)?

Tom Kyte
June 23, 2004 - 1:40 pm UTC

in 7.3, I would have definitely used a pro*c application or sqlplus or anything else -- but not the job queues. the job queues just were not designed to run a job forever.

In 7.0, we wrote a thing called "PLEX" to actually automate all of that code generation - sort of like external procedures before external procedures.
</code> http://asktom.oracle.com/~tkyte/plex/ <code>


but in 8i -- given that you want

a) serialization
b) autonomous transactions

you should

a) use dbms_lock to serialize access to a named resource
b) autonomous transactions




j., June 23, 2004 - 4:05 pm UTC

we use dbms_lock to make our "server" a singleton. we don't lock every single resource, but let the "server" control the access.

and we really didn't know that one should not submit "never-ending jobs". can you please give additional information about the problems caused by doing so? we would then start the "server" within a "normal" session instead.

we don't have pro*c knowledge, but all logic in pl/sql. the "clients" reside in the database as well as the "server".

could you *please* give us an idea on how AQ would support our exception handling requirements in this environment?

how about an extension of what we already have:
- handle exceptions inside the "server's" routines (keep "server" alive).
- return exception's codes to "clients" through output parameter.
- generate anonymous pl/sql blocks on client-side that reraise the exceptions.

(at least it seems to work)

Tom Kyte
June 23, 2004 - 4:11 pm UTC

you break the job queue functionality for anything else (that queue is forever consumed)

you are totally at the mercy of any and all memory leaks....

if you only have a single server, you are as serial as you get. you only need a single lock to emulate what you have now.

j., June 23, 2004 - 6:00 pm UTC

sorry, tom, just to get it right: the job itself is *removed* by the "server" when it starts. only the "server's" session - created by that job - remains as long as the "server" isn't forced to shut down (by a request). does the "memory leak" thing apply to such a session? is it different from a "normal" session which isn't run in background, but blocked by the "server"?

and back to our main question: is our error handling approach described above valid? how could one do it better with AQ?

thanks for your patience and your help.

Tom Kyte
June 24, 2004 - 9:00 am UTC

the job itself cannot be removed while it is running.

I know exactly what you are doing - you have a job in an infinitely loop, it is using one of the snpN processes in 8i and before forever.


I don't think you even need AQ, but if you are interested in playing with AQ, there is an application developers guide for it. You would basically be writing a tiny process outside the database to dequeue message, process message, send response back. Alot like you have now -- the tiny process is what would be logging your error stack in any format you want.

j, June 24, 2004 - 12:14 pm UTC

the 'what' parameter of our call to dbms_job.submit is a stored procedure. its first action is dbms_job.remove (at least the job disappears from job-views). after that our "server" endless loop is started. from our point of view the job is gone - the session it created runs "forever". are we wrong? are we facing the "memory leak" problems?

we already use AQ for other purposes (more complex data structures, transactional communication, ...). but we don't want to replace our well running dbms_job/dbms_pipe stuff without any idea of AQ's advantages for our exception handling goals. *logging* errors is *not* the task we are trying to accomplish - but *propagating* exceptions from inside the message processing routines on the invoker's side. if a request for service XYZ failes inside the "server", the exception it failed with should be present on the client's side.

(still left without a clue)

Tom Kyte
June 24, 2004 - 3:11 pm UTC

your job could (the plsql code itself)
the snpN process could
anything could.


if you already use AQ, you know what happens in the event of an error then?


The AQ dequeue process would get the message, run the procedure with the inputs, get whatever back from it (including an error stack -- as clients can get error stacks) and write back to the requestor whatever it likes.


Bottom line -- you seem to have what you want (your server).
You haven't run into any issues.
If you were starting from scratch I would advise against going this way.
But you've already done so.
And are reasonably OK with it.
So, leave it be I guess.

our conclusion

j., June 24, 2004 - 4:37 pm UTC

we only almost have what we want - we just wanted some proper exception propagation in addition ... (at least we 've found a way to reraise exceptions on client side)

we haven't recognized any issues so far - but maybe we 're just lucky ... (still no idea, more questions than answers)

unfortunately we 're not starting from scratch, so finally your very last advice is the only one we can follow: "don't touch it."

thank you, anyhow

dbms_output.put_line

Binish, November 02, 2004 - 12:56 am UTC

Hi Tom

I am desperately in need to see the dbms_output.put_lines of a previous session (on a previous day). If I can get more details about the session, will I be able to find the dbms_outs of that session. Is it stored somewhere in the server?

Many Thanks
Binish

Tom Kyte
November 02, 2004 - 7:27 am UTC

nope, not a chance. dbms_output puts data into an array (plsql table), sqlplus or whatever client pulls the contents of the array after each statement is executed and prints it -- it is "gone" by then.



Error trapping and parsing

phil, January 04, 2005 - 7:20 pm UTC

error handling best practice:

I have a routine that accepts xml request and returns an xml response based on the details in the request.

I am validating some xml against a registered schema as below.

I know that the only types of errors I need for my return are those I am traping, the rest can be dealt with in the when others.

My fist go at this had me writing a mass of code using extract and existsnode as I wrote it appeared to me that I only ever got one of 4 errors that I need to deal with.

Is it good practice to trap and then parse the error text to get values from it and then issue a more user friendly error?

declare
xmldoc xmltype :=xmltype('<?xml version="1.0" encoding="UTF-8"?>
<Doc:Document xmlns:xsi="</code> http://www.w3.org/2001/XMLSchema-instance" <code>xmlns:Doc="urn:RT:xsd:RT.cashrepv1$getaccount">
<Doc:getaccount>
<Doc:NstrAcctSchCrit>
<Doc:AcctId>
<Doc:DmstAcct>DmstAcct </Doc:DmstAcct>
<Doc:test>this should not be here</Doc:test>
</Doc:AcctId>
<Doc:Ccy>Ccy</Doc:Ccy>
<Doc:Ccy>Ccy1</Doc:Ccy>
<Doc:Ccy>Ccy2</Doc:Ccy>
<Doc:BalValDt>
<Doc:DtTmSch>
<Doc:DtTmRg>
<Doc:FrDtTm>2004-12-03T00:00:00</Doc:FrDtTm>
<Doc:ToDtTm>2004-12-03T00:00:00</Doc:ToDtTm>
</Doc:DtTmRg>
</Doc:DtTmSch>
</Doc:BalValDt>
</Doc:NstrAcctSchCrit>
<Doc:QryPrcg>
<Doc:QryRef>
<Doc:Ref>Ref</Doc:Ref>
</Doc:QryRef>
<Doc:QTp>ALLL</Doc:QTp>
</Doc:QryPrcg>
</Doc:getaccount>
</Doc:Document>
','RT.CASHREPV1$GETACCOUNT_CWRTNSubsetV00R00.XSD'
);
lErrorText varchar2(500);
l_inv varchar2(500);

max_elements_exceeded exception;
invalid_xml exception;
invalid_value exception;
invalid_element exception;

pragma exception_init(max_elements_exceeded, -30936);
pragma exception_init(invalid_element, -30937);
pragma exception_init(invalid_value, -31038);
pragma exception_init(invalid_xml, -31154);

begin

xmldoc.schemavalidate();
if xmldoc.isschemavalidated() = 1 then
dbms_output.put_line('Data is valid');
-- ReturnTransaction(xmldoc);
end if;

exception
when invalid_element then
-- parse the error to get invalid element return
-- user descriptive error and send back in return xml
--
-- ReturnErrorTransaction(30937,lErrorText);
lErrorText:= SQLERRM;
l_inv := substr(substr(lErrorText, instr(lErrorText,'''')+1),0, instr(substr(lErrorText, instr(lErrorText,'''')+1),'''')-1) ;
dbms_output.put_line('Element not suported :' || l_inv);
when max_elements_exceeded then
-- parse the error to get invalid element return
-- user descriptive error and send back in return xml
--
-- ReturnErrorTransaction(30936,lErrorText);
dbms_output.put_line('Maximum elements exceeded');
when invalid_value then
-- parse the error to get invalid value return
-- user descriptive error and send back in return xml
--
-- ReturnErrorTransaction(31038,lErrorText);
dbms_output.put_line('Invalid Enumeration');
when invalid_xml then
-- parse the error to get invalid value return
-- user descriptive error ??? and send back in return xml
--
-- ReturnErrorTransaction(31154,lErrorText);
lErrorText:= SQLERRM;
l_inv := substr(lErrorText,instr(lErrorText,'LSX-'),length(lErrorText));
dbms_output.put_line(l_inv) ;
when others then
raise;
end;
/

thanks

Tom Kyte
January 05, 2005 - 9:15 am UTC

I would erase that entire EXCEPTION block

the only thing it could possibly do for your is let the invoker silently ignore the fact that a heinous error has occurred.

the when others -- lose it entirely, why just "raise"? you don't do anything in there so remove it.

In the other cases -- why would you HIDE the fact that an EXCEPTION occurred from the caller? You don't log the error anywhere, you don't really DO anything about it -- so why? why catch them at all -- at the very least, put a RAISE at the end of each block of code -- so the invoker can TELL that they got AN ERROR.

exception block

phil, January 05, 2005 - 4:04 pm UTC

Thanks tom,

I understand what you are saying, I think that my sample code was probably not enough to go on and is misleading,as my point that i was trying to ask is, if I get the following error for example :

ERROR at line 1:
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00213: only 0 occurrences of particle "AcctId", minimum is 1
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 24

trapped in an exception block as above, I notice that the error has all the information I need to create a "user friendly" message for an end user to be returned in my returntransaction xml. That would be my logging, not the put_lines !

Based on that is it "good practice" for me to be able to parse the errors for information.

Sorry for asking the same thing twice.


Tom Kyte
January 05, 2005 - 7:56 pm UTC

only if you immediately turn around and use it in raise_application_error(...)



thank you

phil, January 05, 2005 - 8:03 pm UTC


Where to place exception handler

Arun Gupta, January 06, 2005 - 9:54 am UTC

Tom,
In answer to the original question, you said,
=======================================
p1 calls p2 calls p3 calls p4

and p4 "fails". Now the when others in p4 logs the error, sends the email and
re-raises the error. P3 gets the error and does it all over again. Then p2
then p1. You get 4 notifications -- 4 logs -- for the single error.
========================================

Suppose there are code paths like:
Path1: Batch Program1->p1->p2->p3->p4. p4 generates exception, code handles the exception in p1.
Path2: Batch Program2->p3->p4. p4 generates exception. Where should I place the exception handler? If I place it in p3, it will handle exception for path1 thus leading to multiple log entries for same exception.

Thanks


Tom Kyte
January 06, 2005 - 11:08 am UTC

top level

batch 1 would execute:


begin
p1;
exception
when others
then
log_it; email_it; RAISE;
end;


batch 2 would execute


begin
p3;
exception
when others
then
log_it; email_it; RAISE;
end;





When others

Steve, February 11, 2005 - 10:20 am UTC

I was looking over some code and found some pl/sql that was used to see if two files are identical or not. The procedure returns true if the files are different and false if they are not. In the block that does the actual check (note: it utilizes utl_file) there is a single exception handler as follows:

EXCEPTION
WHEN OTHERS
THEN
RETURN FALSE;

I am trying to convince the person that this is horrible. Returning false in this case is the same as saying that if there is an error the files must be different. Thats not the case, could be permission problem, could be an access problem, could be a more serious database issue. In this situation an exception could be raised and the files could still be identical.

It is my understanding that a "When OTHERS" is an unknown exception, something that shouldn't happen that cannot be accounted for, something unexpected. Shouldn't a when others always be raised??? Is this horribly wrong or is it just me?? You opinion is greatly appreciated.

Tom Kyte
February 12, 2005 - 7:47 am UTC

you are right, they have a fairly big bug in their code.


UNLESS they expect an exception, catch a specific one and deal with it, it is by definition "unexpected" -- an error.

it does not mean the files are different
if does not mean the files even exist

it means "houston, we have a problem"


when others, not followed by raise, is a bug 99.999999999999999999% of the time and this is one of them


(this is not an opinion, it is provably computer programming fact)

Something scary

Steve G, February 14, 2005 - 12:37 pm UTC

I was recently looking through oracle magazine and found an article on refactoring.

</code> https://asktom.oracle.com/Misc/oramag/on-injecting-and-comparing.html <code>

I found something that is quite troubling:
EXCEPTION
WHEN OTHERS
THEN
cleanup;
RETURN FALSE;

This is almost identical to the problem described above. What is really bad is that this exception handler is placed in the "refactored" code. It seems like a pretty bad thing when the "worlds foremost expert on pl/sql" is writing articles with such odbvious and horrible bugs. In an article about refactoring it seems that this is almost like telling people this is the proper way to handle excpetions. Anyways thanks a lot for all the great info on your site. Hopefully Oracle Magazine will put out a properly "refactored" procedure, and admit the mistake.

Tom Kyte
February 14, 2005 - 3:58 pm UTC

</code> http://www.stevenfeuerstein.com/ <code>

drop him a line and ask him to explain what he was thinking?


I make mistakes (I "patched" my very last article! In an article on SQL Injection, guess what I did -- I had a snippet of code that included, well.... some code subject to SQL Injection! Embarrassing, yes -- but I've actually used it to my "advantage". When I talk about sql injection and how "insidious" it truly is -- I use that small example and how code review by the 450,000 some odd readers of Oracle magazine found it and fixed it).

We all make mistakes.


however, upon reading bit more, he said:

<quote>
Now that I have created a cleanup procedure for eqfiles, it is quite easy for me to take care of No. 1 above. I simply add an exception section to the function, call the cleanup routine, and then reraise the exception:

EXCEPTION
WHEN OTHERS
THEN
cleanup;
RAISE;

Alternatively, I might decide to simply return FALSE instead of propagating the exception, because if I encounter an error, then clearly the two files are not the same.
</quote>


I disagree with the last comment -- the files could be identical -- yet the exception is raised due to a line exceeding 32k for example.







Steve G.

Steve G, February 14, 2005 - 4:42 pm UTC

I agree that we all make mistakes, i just found this one to be very odbvious. In the refactored code an exception is never returned. The other thing to make note of is that the cleanup routine expects that the files are open, when in fact the exception could be do to a problem occuring as a result of the attempt made to open the files. The user is never informed of this. It's not a big thing, I would just expect an article on "refactoring" would be a little less riddled with errors.

DBA and error handling

A reader, June 22, 2005 - 11:39 am UTC

Tom, we need your advice:
1. DB with constraints (FK, UK, PK, etc.)
2. Some of these constraints are deferred due to business logic
3. Whenever any DB error happens (including constraint violation) our DBAs are paged and they are completely unhappy with it
4. Due to #3 we (developers) are forced to eliminate this paging
Application only calls DB packages/procedures - no direct DML access. We tried to implement error handling on DB side by catching all possible exceptions and returning to the client meaningfull message instead of handling exception by the client, but what can we do with defferred constraints? We do not catch them on DB side - client gets an exception as soon as he/she commits.
The only solution that we are thinking of is to implement new generic DB procedure DO_COMMIT that does nothing but commiting and handling all exceptions. Client application will have to call this procedure instead of commiting. Is it the right approach?
Thanks a lot for all you are doing for us!!!

Tom Kyte
June 23, 2005 - 1:21 pm UTC

3) wow, I would quit. That would drive me nuts.

and I do not understand #4 at all?? You can catch the exceptions, but your just be RE RAISING THEM.

the "fix" would be more horrendous than the present situation, you would be proposing to not raise exceptions at all??

seems the "fix" is to page a DBA when a DBA should be paged -- using an ON SERVERERROR trigger that catches the "bad stuff".

DBA and error handling

A reader, June 23, 2005 - 1:56 pm UTC

Unfortunatley there is no way for us to change this (I mean - DBA paging).
The idea behind this generic DO_COMMIT procedure is to catch every exception BEFORE it pages DBA and report this as an error to the client application. And actually I'm talking about "when others" in this procedure. We were handling other types of exceptions that we can resolve from the very beginning. But these defferred constraints we cannot handle - they happen on commit. And DBA is paged :-) And I want to be sure that I'm not going to be killed by them one day :-)

Tom Kyte
June 23, 2005 - 7:03 pm UTC

but -- WHO PAGES THE DBA

it seems it must be "the client"

so, if you raise an error back to "the client", catch-22.

Randy, August 26, 2005 - 1:46 pm UTC

Tom,
Could I use the exception below to end a procedure if errors are returned from our STMP server?

EXCEPTION
WHEN OTHERS THEN -- handles SMTP errors
dbms_output.put_line('SMTP error generated');

The procedure is called from dbms_job. We had a case where the smtp returned an error and the job continued to repeat over and over again. We need the job to end if an error has occurred.

Thanks,

Tom Kyte
August 26, 2005 - 2:04 pm UTC

well, that dbms_output goes to the BIT BUCKET. no where, you would have *no idea* that the job failed.

So no, that would not be good.

You could have the job break itself to avoid repeating -- it would stay in the queue and let you fix it later and un-break it to send the mail.

Or have it set its Next date to something way in the future.

and log a message to an error log table somewhere so you know why it failed.




A reader, August 26, 2005 - 2:16 pm UTC

Tom,
I like the idea of "Or have it set its Next date to something way in the future.

and log a message to an error log table somewhere so you know why it failed." That will work out perfect.


Thanks for the reponse!

Randy
Randy

Generate user-friendly messages from constraints on table fields

Robin, November 05, 2005 - 3:42 pm UTC


I have many constraints (pk, check, fk, not null, etc). We are hitting the database from an application developed in forms and from SQL Plus.

Currently when the users violate the constraints, an oracle generated message is displayed. Most of our users have a hard time deciphering these messages. Even something as simple as a not null constraint sends them to the phone to call our help desk.

I'd like something that can be placed in a procedural unit/trigger on the database that would deliver a user-friendly message no matter what application is being used to access the database.

For example:

***************************************
Error on emp table, no name...I'd like to generate something that clearly states what I'm looking for.

What I get now (which seems pretty clear to me, but not to our field users) is:

ORA-02290: check constraint (SCOTT.EMP_ENAME_NN) violated

What I'd like to do is catch the error by number and error text and change it to something like this:

Name cannot be blank on emp table. Please enter name:

**************************************

I'm just not sure where I would put it (trigger/procedural unit/both) and when it should fire.

Thanks for any help, Robin


Tom Kyte
November 06, 2005 - 8:17 am UTC

one approach, create a table of your own

create table my_msg
( owner varchar2(30),
cname varchar2(30),
msg varchar2(2000),
primary key(owner,cname)
) orginization index;


and insert into their stuff like:

values ( 'SCOTT', 'EMP_ENAME_NN', 'This error is caused by .......' );


and in your code, when you get the error (in an on-error trigger in forms for example), you would substr out the owner.cname - query this table and if a row is found, use that error message.


In sqlplus, this will be more problematic if the users are doing straing SQL (but then again, if they cannot deal with the error messages, there is zero percent chance I would let them use SQL against my tables...). There you would have to do this in stored procedures and let them only use the procedures. The procedures can use raise_application_error from an exception block to do this.

Putting it together

A reader, November 07, 2005 - 7:06 am UTC

Hi Tom,

have been reading this thread with great interest. My current situation is your classic J2EE design with stored procs for db access. The existing method of dealing with db errors is return codes - have read other stuff on your site about this being a poor way, just need to come up with something better!

Bringing the thread back to the original question, how about this for an approach to handling errors??

Layer the db code. So we have 'public' packages. These are the only entry points for external programs to communicate with the db (Java or batch jobs). Lets call it the API.

The API proc/functions are where we place the when others clause (ideally logging the error to a table, then doing a RAISE). The client program needs to handle this RAISE.

So in your example, p1-->p2-->p3-->p4, p1 would be part of the public API to clients. p2,p3,p4 would be lower level code that could not be called other than through the layer above.

If there are any RECOVERABLE errors then thay can be handled wherever they occur and processing continues.

Sound good so far?

R

Tom Kyte
November 07, 2005 - 10:35 am UTC

sounds very reasonable - in 10g they can get the full error stack with line numbers and all at the top level so I'm in aggreement with that approach (the "The API proc/functions are where we place the when others clause (ideally
logging the error to a table, then doing a RAISE)." concept).

In 9i, I might not do that, just because the client was the only thing to get the full error stack. so a utility "handle the database error" routine in the client app might have been called for (to log the error in a uniform fashion)

Clarification

A reader, November 07, 2005 - 11:10 am UTC

Hi Tom,

I am using 9i and didn't understand what you meant by your last statement.

Are you saying that in 9i better not to trap the error in p1 and re-raise because you will lose information to Java than if you had not caught it all and let it propogate back to Java without intervention?

R

Tom Kyte
November 08, 2005 - 9:18 pm UTC

In 9i, you'll lose the error stack (the originating line number) if you catch this in plsql. You would only be able to log that "error X happened", you would not be able to log "error X happened on line Y of package P"

the client could though.

Web Application Error Handling

Mick, November 30, 2005 - 6:13 am UTC

Tom

Having read hundreds of threads on Error Handling in Oracle and so called "best practices" i find myself at a bit of a loss as there are so many contrasting
and approcahes.

I was wondering what your thoughts were for handling errors in Oracle Web Applications.

My current approach is as follows:

1) Set up a package "error_handling" with procedures / functions specific to error handling.

/* SPECIFICATION */
CREATE OR REPLACE PACKAGE error_handling
AS

/* Performs DML to log any unexpected error */
PROCEDURE log_error (pv_sqlerrm IN VARCHAR2);

/* Renders a standard block of HTML for any error generated */
PROCEDURE error_screen (
p_sqlerrm IN VARCHAR2, -- Oracle SQLERRM
p_err_src IN VARCHAR2, -- SubProgram error origanated
p_user_id IN VARCHAR2, -- ID of user who generated exception
p_next_proc IN VARCHAr2 -- Where to go next / backto once message has been displayed
);

/* Called from error_screen and converts SQLERRM into a more meaningfull error msg */
FUNCTION get_friendly_msg (pv_sqlerrm IN VARCHAR2)
RETUNR VARCHAR2;

END error_handling;

/* BODY */
CREATE OR REPLACE PACKAGE BODY error_handling
AS

/* Performs DML to log any unexpected error */
PROCEDURE log_error (pv_sqlerrm IN VARCHAR2)
IS
BEGIN
... some DML to log errors;
END log_error;

/* Renders a standard block of HTML for any error generated */
PROCEDURE error_screen (
p_sqlerrm IN VARCHAR2, -- Oracle SQLERRM
p_err_src IN VARCHAR2, -- SubProgram error origanated
p_user_id IN VARCHAR2, -- ID of user who generated exception
p_next_proc IN VARCHAr2 -- Where to go next / backto once message has been displayed
)
IS

lv_friendly_msg VARHCAR2 (200);
BEGIN
lv_friendly_msg := get_friendly_msg (p_sqlerrm);

... Render HTML and lv_friendly_msg;

... Call (Submit to) next procedure or back to procedure that errored;

END error_screen;

/* Called from error_screen and converts SQLERRM into a more meaningfull error msg */
FUNCTION get_friendly_msg (pv_sqlerrm IN VARCHAR2)
RETUNR VARCHAR2
IS
lv_retval VARCHAR2 (300);
BEGIN
CASE statement on pv_sqlerrm
WHEN 'ORA-1234' THEN
lv_retval := 'Some nice text';
WHEN............THEN
WHEN............THEN
ELSE
log_error (pv_sqlerrm);
END CASE;
RETURN lv_retval;
END get_friendly_msg;

END error_handling;

2) Call error_handling.error_screen from any program unit that errors throughout the application and handle it there.

ie.

PROCEDURE one
IS
BEGIN
... some exception generated
EXCEPTION
WHEN OTHERS THEN
Redirect the URL to error_handling.error_screen (OWA_UTIL.redirect_url) passing in all necessary parameters inclding the
procedure we want to go to or get back to;
END one;



This seems to work ok but I have a concern.

All program units have a WHEN OTHERS exception handler but the
real work is passed to the error_handling package. This is bad practive i know but its the only way i can avoid coding like this:

PROCEDURE one
IS
BEGIN
... some exception generated
EXCEPTION
WHEN NO_DATE_FOUND THEN
Redirect the URL to error_handling.error_screen (OWA_UTIL.redirect_url) with message 'The data requested does not exist' and then
submit to the same procedure that errored;
WHEN TOO_MANY_ROWS THEN
Redirect the URL to error_handling.error_screen (OWA_UTIL.redirect_url) with message 'More than one record exists' and then
submit to the same procedure that errored;
WHEN OTHERS THEN
Redirect the URL to error_handling.error_screen (OWA_UTIL.redirect_url) with SQLERRM and then
submit to the same procedure that errored;
END one;

I would be very interested to hear your opinions regarding how best to handle errors when building Web Applications. Specificatlly how
best to use a generic HTML error screen and how that should be called from the program units that error.

Thanks for any help.

Tom Kyte
November 30, 2005 - 2:39 pm UTC

this is a hard question to answer fully because much of it depends on "what you want" to have happen.


I let the "top level" catch the when others and report the error (you do NOT want to catch a when others "deep down", without following it with a RAISE as you'll end up IGNORING errors by accident). The top level is defined by your programming environment (might be a java client, might be htmldb, whatever).

The top level can then log/report/hide the error/whatever.

In your example, unless you EXPECT no_data_found, I would not CATCH no data found. If you expect no data found - then you would have handled it. Any error you are NOT expecting - well, that is like "end of program, goodbye, unsafe to continue from here". same with too many rows. you only catch named exceptions you are expecting, else you let the "controller" - the top level - deal with it (and then your error logging package could be used "there" at the top.

A reader, December 07, 2005 - 11:09 am UTC


getting table/column name

A reader, December 07, 2005 - 12:22 pm UTC

I apologize in advance if this is a stupid question, but in one of your responses above, you stated --

"and in your code, when you get the error (in an on-error trigger in forms for
example), you would substr out the owner.cname - query this table and if a row
is found, use that error message.
"

How do you substr out the column name so that it can be included in a custom error message. For example, right now I am doing form validation using java, but would like to do it thru my existing pl/sql packages instead, so if a user submits a form with an email address that doesn't have "@" in it and I have a check constraint to catch this, how would I return the related column name so that I can use it to build a custom error message like:

EMAILADDRESS -- You have entered an invalid email address.

I understand how to catch the exception and create a custom error msg, just not sure how to get the column name....

Tom Kyte
December 08, 2005 - 1:33 am UTC

look at the error message, it should be "obvious" how to substr out from it???

ops$tkyte@ORA9IR2> create table t ( x varchar2(30), constraint check_x check( instr(x,'@')>0) );

Table created.

ops$tkyte@ORA9IR2> insert into t values ( 'sssss' );
insert into t values ( 'sssss' )
*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.CHECK_X) violated



look for the word 'constraint (' - that tells you where to begin substring, look for the next ')', that tells you where to end. 

thanks

A reader, December 08, 2005 - 9:41 am UTC

thanks Tom. I get that. Check constraint and unique constraint violations include the constraint name in the error message(just need to include the column name in the constraint name when I create it, which I already do and substr to that part of the constraint name that I named for the column). Not null violations include the table and column name, so I just need to substr to the column name portion of the sqlerrm.

I also wanted to do a custom error, which included the column name for "ORA-01401: inserted value too large for column". I guess I was hoping that there was some kind of function like "show_column_name". This great function would return just the column name related to any column level exception, when it occurs :)


Tom Kyte
December 08, 2005 - 11:49 am UTC

in current releases of the software, the column is provided


ops$tkyte@ORA10GR2> ops$tkyte@ORA10GR2> insert into t values ( 'xxxxxxx' );
insert into t values ( 'xxxxxxx' )
                       *
ERROR at line 1:
ORA-12899: value too large for column "OPS$TKYTE"."T"."X" (actual: 7, maximum:
2)
 

10g rel2

A reader, December 08, 2005 - 12:56 pm UTC

excellent! No I can even pass back the max size of the column with the related column name and error message, all dynamically from the database. this is great.

Some 10.2.0.1 examples

A reader, December 14, 2005 - 11:47 am UTC

Just wanted to post these examples of substringing out the column name, max size and actual size of an ORA-12899 error to save others some time, who are like me, substr and instr challenged.... This was done in 10g release 2 only. I am using this on the sqlerrm in the exception block.

mwagner@MW102> variable x varchar2(100)
mwagner@MW102> exec :x := 'ORA-12899: value too large for column "MYSCHEMA"."MYTABLE"."MYCOLUMN" (actual: 38, maximum: 25)'

mwagner@MW102> select substr(:x,
2 instr(:x,'.',1,2)+2,
3 (instr(:x,' (',1,1)-instr(:x,'.',1,2))-3) COLUMN_NAME
4 from dual;

COLUMN_NAME
-------------
MYCOLUMN


mwagner@MW102> select substr(:x,
2 instr(:x,'(',1,1)+1,
3 (instr(:x,',',1,1)-instr(:x,'(',1,1))-1) SUBMITTED_SIZE_AND_LABEL
4 from dual;

SUBMITTED_SIZE_AND_LABEL
------------------------
actual: 38


mwagner@MW102> select substr(:x,
2 instr(:x,': ',1,2)+2,
3 (instr(:x,',',1,1)-instr(:x,': ',1,2))-2) SUBMITTED_SIZE_ONLY
4 from dual;

SUBMITTED_SIZE_ONLY
-------------------
38


mwagner@MW102> select substr(:x,
2 instr(:x,',',1,1)+2,
3 (instr(:x,')',1,1)-instr(:x,',',1,1))-2) MAX_SIZE_AND_LABEL
4 from dual;

MAX_SIZE_AND_LABEL
------------------
maximum: 25


mwagner@MW102> select substr(:x,
2 instr(:x,': ',1,3)+2,
3 (instr(:x,')',1,1)-instr(:x,': ',1,3))-2) MAX_SIZE_ONLY
4 from dual;

MAX_SIZE_ONLY
-------------
25

To sort out ORA- errors into different categories

jc, May 23, 2006 - 4:03 pm UTC

Tom,

I have a "Mission Impossible IV" task to sort out the ORA- errors into 3 categories for our alarm systems:

1. ORA- errors to send an ALARM
2. ORA errors to send a WARNING
3. ORA errors that can be ignored

(I can imagine what you are thinking now... and I have not quit my job yet.)

My first thought was to have the first level ORA- errors for ALARM, and all sub-level stack errors (like ORA-06512 and ORA-06502) for WARNING. Then, cherry-pick the errors that can be ignored (e.g. O/S specific messages)

I started with the oraus.msg file and the "Error Messages" manual (looks like same contents).

1) Please help me with a guildeline to start with.
2) How do I find out a list of sub-level message stack ORA- errors?
3) Many errors are product-specific and operating system-specific. Do you have a better way to sort them out, other than browsing through sections in the oraus.msg file?

Thanks!!


Tom Kyte
May 24, 2006 - 7:00 am UTC

This is more like "mission futility"

Take the lowly "no data found"

is that

1) an alarm (whatever that means)
2) a warning
3) something to ignore.

I choose

4) an exception, an ORA-xxxxx error I expect in my code and will deal with.

No wait, I choose

5) a heinous exception, one that causes my code to fail.


"it depends on the context".


I put ora-xxxxx into two categories:

a) expected and dealt with by the code (eg: examples are SOMETIMES: no data found, resource busy, dup value on index, deadlock. sometimes these are EXPECTED and we catch them, deal with them and continue on. Sometimes these are NOT expected and they are "fatal errors"

b) non-expected errors, these are all FATAL


Makes it very easy. IF an error is raised to the client, then ERROR was FATAL, else ERROR is not fatal, in fact, error was not an error.


There, you are done!

A reader, May 24, 2006 - 12:06 pm UTC

It is both "mission futility" and "mission impossible", but it is unfortunately a task given from my authority that I have to deal with. (sigh!) I know there's really no solution to this task, but I still have to give it a best try. (Don't shake your head now..)

My task is to pick up the ORA- errors from alert.log file; thus it is not possible to differentiate what errors are issued to client. Well, I may have to toss coins.

Thanks for your advice, anyway.


Tom Kyte
May 25, 2006 - 7:16 am UTC

you cannot do it, it is quite simply not only


NOT POSSIBLE

It should be put onto this site and made fun of:
</code> http://oracle-wtf.blogspot.com/ <code>

all ora-xxxx in the alert log - bad, fatal.

there, we are done again.


how to caprture 2 error mesaages

A reader, May 26, 2006 - 1:00 pm UTC

So how do I get the line number in addition to the error message?

begin

-- just dummy thing that will cause an exception
execute immediate('blah blah blah');

-- exception handler
exception when others then
dbms_output.put_line(sqlerrm);
return;

end;

the errors are

ORA-00900: invalid SQL statement
ORA-06512: at line 4

the above code only captures ora-900 and not ora-6512





Thanks,


Tom Kyte
May 27, 2006 - 9:29 pm UTC

got version?

10g - you can do this
before 10g, you cannot do this

dbms_utility.FORMAT_ERROR_BACKTRACE

how to caprture 2 error mesaages

A reader, May 28, 2006 - 7:03 pm UTC

can you show some example

Tom Kyte
May 28, 2006 - 7:09 pm UTC

do you mean "besides calling the documented function that returns this error stack"?

ops$tkyte@ORA10GR2> begin
  2          if (1/0 > 0)
  3          then
  4                  null;
  5          end if;
  6  exception
  7          when others then
  8                  dbms_output.put_line
  9                  ( 'we printed this ' || dbms_utility.FORMAT_ERROR_BACKTRACE );
 10                  dbms_output.put_line
 11                  ( 'we printed this ' || sqlerrm );
 12                  raise;
 13  end;
 14  /
we printed this ORA-06512: at line 2

we printed this ORA-01476: divisor is equal to zero
begin
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at line 12

 

Error Handling in Nested Blocks

Ram, July 03, 2006 - 11:40 am UTC

Hi Tom,
When should I use NULL or RAISE in a nested block?
Is there any difference in usage of above two?Please advise.
Ex:
Begin
begin
---
exception
when no_data_found then
---
NULL; ---this null is in question.....
end;
---second block
begin
---
excepton
when too_many_rows then
--
end;
END;

First block excecption concludes with NULL indicating that
exception should be ignored. Continue to process second block.
---------------Ex with Raise--------------
Begin
begin
---
exception
when no_data_found then
---
RAISE; ---this raise is in question.....
end;
---second block
begin
---
excepton
when too_many_rows then
--
end;
END;

First block excecption concludes with RAISE - re-raising an exception. If I use RAISE will Oracle process second block? or not.

Thanks so much


Tom Kyte
July 07, 2006 - 7:04 pm UTC

You catch exceptions for one reason:

a) you can deal with them, you know why they happen and expected them to happen. You have a plan to deal with it.


that is all. If "no_data_found" is a "heinous error", then either

a) don't catch it at all!!!!
b) catch it so you can "log it", and then re-raise it.


If "no_data_found" is a "we expected that to happen and when it does we do 'this'" then, catch it, and do that. "when no_data_found then null" is OK in VERY VERY LIMITED CASES (eg: when the variables were already initialized)

for example:

declare
x int; -- defaults safely to NULL
begin
begin
select c into x from t where ...;
exception
when no_data_found then null;
end;
-- is "SAFE" probably because X is initialized to NULL - depends on the
-- LOGIC OF YOUR program of course whether this is TRUE...
... more code....
end;



declare
x int; -- defaults to NULL
begin
for i in 1 .. 10
loop
begin
select c into x from t where c2 = i;
exception
when no_data_found then
NULL; -- probably UNSAFE!!!!!!!
end;
-- it is unsafe because X will contain "something" here, but you
-- have NO CLUE - it could be the last select'ed value of X, might
-- be null if the first through the loop. Unsafe
end loop;
end;

but:

declare
x int; -- defaults to NULL
begin
for i in 1 .. 10
loop
begin
select c into x from t where c2 = i;
exception
when no_data_found then
x := NULL; -- now it is safe, but we are not doing "null"
end;
...
end loop;
end;


Raise

A reader, July 07, 2006 - 10:56 pm UTC

Hi Tom,
I understand what you are saying but can you please advise on second question:
In the following example exception concludes with RAISE - re-raising an exception. If I use
RAISE will Oracle process second block? or not.

Ex...with Raise--------------
Begin
begin
---
exception
when others then
---
RAISE; ---this raise is in question.....
end;
---second block
begin
---
excepton
when too_many_rows then
--
end;
END;





Tom Kyte
July 08, 2006 - 8:51 pm UTC

of course not, that is the point of exceptions.

(easy enough to test isn't it???)

commit in error handler

James, October 12, 2006 - 11:17 pm UTC

I want to log errors in a table. Let me know if you would do this.

Why? I would like to capture the error for reporting but still let the client do something with the oracle error as well.



create table err_log(err_date date,err_desc varchar2(1000));

declare
the_date date;
begin

select sysdate
into the_date
from dual where 1=0;

exception when no_data_found then
insert into err_log(err_date,err_desc) values (sysdate,'no sysdate');
commit;
raise;

end;
/

declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 12

select * From err_log
/

ERR_DATE
---------
ERR_DESC
----------
13-OCT-06
no sysdate




Tom Kyte
October 13, 2006 - 7:03 am UTC

create procedure log_error( ....)
as
pragma autonomous_transaction;
begin
insert into log.....;
commit;
end;
/


Call that and then raise

A reader, October 13, 2006 - 10:00 pm UTC

Tommy - you are the best!!!!

Logging other things

Synnøve, November 30, 2006 - 5:12 am UTC

Our system have quite a few batch jobs where some produce flat files, emails, etc.
The batch scheduler do not capture these things, so I was wondering what you thought of logging details of each batch job and what information you think might be handy to log.
I thought perhaps the start/end times, filenames and locations of files produced, total records processed/added/updated/deleted, status (success/failed) etc.
It would give us developers access to view what goes on at a glance, and I cannot see it being a big overhead.

Tom Kyte
November 30, 2006 - 10:00 am UTC

sure, sounds good to me - log what you think would be

a) useful
b) relevant

Error stack

V, December 12, 2006 - 12:28 pm UTC

Is there anyway to limit the error to only return the raised error? i.e.

ERROR at line 1:
ORA-20001: LDAP Search Error <-- Only return this?
ORA-06512: at "EMPSUG.LDAP_PKG", line 92
ORA-06512: at line 1



Tom Kyte
December 12, 2006 - 10:06 pm UTC

no

exception

Sam, August 07, 2007 - 8:16 pm UTC

TOm:

I have a procedure that simply has an implicit cursor that does a selection with ALL OTHER exception handler. However, I get this when one of my variables is too small. How do you explain that it is not going to the exception handler


Tue, 7 Aug 2007 16:06:41 GMT

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "XXX.GET_BOOK", line 7
ORA-06512: at line 10


WHEN OTHERS THEN
v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,200);
htp.p(' <ERROR_CODE>'||v_err_Code||'</ERROR_CODE>');
htp.p(' <ERROR_MSG>'||v_err_msg||'</ERROR_MSG>');
htp.p('</ROWSET>');

2. For implicit cursors
for x in (Select * from table)
loop

end loop;

do the "NO DATA FOUND" and "ALL OTHERS" and other system defined exceptions get hit. It seems it more applies toward regular SQL

select * in to Var from table;

thanks,
Tom Kyte
August 10, 2007 - 3:08 pm UTC

I don't know, you don't let us see a full working example - so, impossible to say....


Here is my guess:

ops$tkyte%ORA10GR2> create or replace procedure foo( x in varchar2 )
  2  as
  3          y int;
  4          z int;
  5          a int;
  6          b int;
  7          m varchar2(5) DEFAULT X;
  8  BEGIN
  9          null;
 10  exception when others then
 11          dbms_output.put_line( 'caught it' );
 12  end;
 13  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec foo( 'hello world' );
BEGIN foo( 'hello world' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$TKYTE.FOO", line 7
ORA-06512: at line 1



the exception happened before the begin block - in your initialization code..



2) no data found and "too many rows" are specific to select INTO only, they do not apply to a cursor like that - only to select into which says "at least one row and AT MOST one row"

exception

Sam, August 12, 2007 - 1:39 pm UTC

TOm:

1. yes yo uare right, it happened before the begin. So there is no way to handle these errors in oracle?

2. yes sometimes i use implicit cursor for one row output too since it is easier to code. so yo usuggest to change that so i can use the expcetion handler?

3. how do you handle expceptions for implicit cursors. do you use the "when other" normally.

thank you,
Tom Kyte
August 15, 2007 - 10:46 am UTC

1) yes there is - either

a) do not do the initialization in the declare do it in the code, eg - instead of:

.....
is
x number := p_x;
begin
....

where p_x is a varchar passed into the procedure, you would:

....
is
x number;
begin
x := to_number(p_x);
....

b) use a nested declare block


ops$tkyte%ORA10GR2> create or replace procedure p1( p_x in varchar2 )
  2  as
  3          x number := p_x;
  4  begin
  5          null;
  6  exception
  7          when others then dbms_output.put_line( 'caught it' );
  8  end;
  9  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p2( p_x in varchar2 )
  2  as
  3  begin
  4
  5  declare
  6          x number := p_x;
  7  begin
  8          null;
  9  end;
 10
 11  exception
 12          when others then dbms_output.put_line( 'caught it' );
 13  end p2;
 14  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p1( 'x' );
BEGIN p1( 'x' ); END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character to number conversion
error
ORA-06512: at "OPS$TKYTE.P1", line 3
ORA-06512: at line 1


ops$tkyte%ORA10GR2> exec p2( 'x' );
caught it

PL/SQL procedure successfully completed.




2) i suggest nothing here - I'm not sure what you mean.

3) implicit cursor or not - you handle exceptions in all cases the same way:

a) you catch those that you EXPECT and can HANDLE, turning them into "not errors"

b) you ignore all others and let them propagate up the call stack.

exceptiopn

A reader, February 01, 2008 - 6:12 pm UTC

tom:

how do you raise when no rows found when you do

for x in (select * from table)
loop

end loop;

this does seem to raise the NO_ROWS_FOUND. how do you do it with implicit cursors.

2. is there any other limitations with using implicit cursors in terms of handling expcetions.


Tom Kyte
February 04, 2008 - 3:43 pm UTC

why would it?

no data found is specific, exclusive, the sole domain of

select ... INTO ...
from ....


it is a static sql thing with a select into only - it never happens for anything else, ever.


select into is designed such that it

a) returns AT LEAST one row - if not, it raises no data found
b) returns AT MOST one row - if not, it raises too many rows

(but if you BULK collect - then no data found and too many rows do not COUNT again - it is purely for the single row select into and nothing else)

The concept of having to return at least one row does not apply, come into play, exist for any other cursor - not retrieving "at least one row" is NOT an error - it just "is what it is, a query that returns no data". no big deal.



2) this is not a "limitation", this is the way it is supposed to work.

exception

A reader, February 04, 2008 - 11:49 pm UTC

Tom:

So how do you handle these exceptions when you use implicit cursor?

Do you do a count first before the cursor and then raise a user defined exceptin or what?

2. what about "when others". does this get raised in an implicit cursor or nothing.
Tom Kyte
February 05, 2008 - 7:39 am UTC

There ARE NO EXCEPTIONS TO HANDLE.

I cannot say this more plainly:


a query that returns 0 rows is not an error.
a query that returns 1 row is not an error.
a query that returns more than 1 row is not an error.
In short - a query returns 0, 1 or more rows - none of that is an error.



Now, that said, you might have some logic that says "Hey, I was expecting at least one row here, and I did not find any - that is a bummer and according to MY LOGIC, my personal - unique - different from anyone elses logic - that is an error condition for me"


There is NO ERROR (in reference to (2) above with the 'when others'). There is nothing to catch, there is nothing wrong.


You would have to code YOUR LOGIC to do what you need, for example:



ops$tkyte%ORA10GR2> create or replace procedure p( p_x in number )
  2  as
  3      l_found_a_row boolean := false;
  4  begin
  5      for x in (select * from all_users where rownum <= p_x)
  6      loop
  7          dbms_output.put_line( 'process record ' );
  8          l_found_a_row := true;
  9      end loop;
 10
 11      if (NOT l_found_a_row)
 12      then
 13          raise program_error;
 14      end if;
 15  end;
 16  /

Procedure created.

ops$tkyte%ORA10GR2> exec p(2);
process record
process record

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec p(0);
BEGIN p(0); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P", line 13
ORA-06512: at line 1


ops$tkyte%ORA10GR2> create or replace procedure p( p_x in number )
  2  as
  3      cursor c is select * from all_users where rownum <= p_x;
  4      l_rec c%rowtype;
  5  begin
  6      open c;
  7      fetch c into l_rec;
  8      if (c%notfound)
  9      then
 10          raise program_error;
 11      else
 12          loop
 13              dbms_output.put_line( 'process record ' || c%rowcount );
 14              fetch c into l_rec;
 15              exit when c%notfound;
 16          end loop;
 17      end if;
 18  end;
 19  /

Procedure created.

ops$tkyte%ORA10GR2> exec p(2);
process record 1
process record 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec p(0);
BEGIN p(0); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P", line 10
ORA-06512: at line 1


ops$tkyte%ORA10GR2> create or replace procedure p( p_x in number )
  2  as
  3      cursor c is select * from all_users where rownum <= p_x;
  4      type array is table of c%rowtype;
  5      l_data array;
  6  begin
  7      select * bulk collect into l_data
  8        from all_users
  9           where rownum <= p_x;
 10
 11      if ( l_data.count = 0 )
 12      then
 13          raise program_error;
 14      else
 15          for i in 1 .. l_data.count
 16          loop
 17              dbms_output.put_line( 'process record ' || i );
 18          end loop;
 19      end if;
 20  end;
 21  /

Procedure created.

ops$tkyte%ORA10GR2> exec p(2);
process record 1
process record 2

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec p(0);
BEGIN p(0); END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P", line 13
ORA-06512: at line 1



exception

A reader, February 05, 2008 - 12:04 pm UTC

Tom:

These are excellent examples on how to do it using implicit cursor, explicit cursor and bulk collect.

1. i never used that bulk collect thing. Is you preference always Implicit cursors I think. It seems the only advantage of the explicit one I see is that you can display the count before the results?

2. Do I always create user defined exceptions if I want to catch things within cursors. the way i see it is that

SELECT * into Varable from table
is a query

and a cursor is query.

It is that #1 is handled by ORacle and I need to handle things in #2.

3. My question was if I had logic code after the cursor and I hit a runtime other, I assume the "WHEN OTHERS" will be invoked. right.
Tom Kyte
February 05, 2008 - 2:01 pm UTC


1) display the count before the results? No idea what you mean.

the only time I use explicit cursors is when I want to

a) control the array fetch size
b) use a ref cursor


2) I do not understand what you are trying to say here.

when you use INTO variable - that is a "programming thing"

select ... into ... from ....; is simply a PROGRAMMING shorthand for:

cursor c is select ... from ...;
begin
   open c;
   fetch c into ...;
   if (c%notfound) then RAISE NO_DATA_FOUND; end if;
   fetch c into ....;
   if (c%found) then RAISE TOO_MANY_ROWS; end if;
   close c;
end;



nothing more, nothing less. Unless and until you have a "into" (PERFORMED BY THE PROGRAMMING ENVIRONMENT, not by the sql engine - INTO is "not" SQL, it is a programming thing)


3) if you hit any error that does not raise an already defined exception, then when others would catch it.

this is why you would use pragma exception init to map the exceptions YOU EXPECT and handle them explicitly - avoiding the use of the dreaded when others.

exceptions

A reader, February 08, 2008 - 9:41 pm UTC

Tom:

I noticed that people handle exceptions differently. sometimes they raise_application_error, and sometimes they just print it with dbms_output.put_line or htp.p and sometimes they insert it to error table.

are they all valid and what is the best way to do it


WHEN ZERO_DIVIDE THEN
dbms_output.put_line('Company must have had zero earnings.');
pe_ratio := null;


EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');



EXCEPTION
WHEN OTHERS THEN
err_code := SQLCODE;
err_msg := substr(SQLERRM, 1, 200);

INSERT INTO audit_table (error_number, error_message)
VALUES (err_code, err_msg);
END;


Tom Kyte
February 11, 2008 - 11:59 am UTC

There are two types of exceptions in the world:

a) exceptions that are not exceptions, you were expecting them.

eg:

WHEN ZERO_DIVIDE THEN 
      dbms_output.put_line('Company must have had zero earnings.');
      pe_ratio := null;


You were expecting that, it is not actually an error, it was an expected condition. You took the error and made it go away, it is not an error. the dbms_output - useless, shouldn't probably be there, but harmless (since dbms_output is not enabled by default, unless you call it from sqlplus, in which case this dbms_output was probably legacy debug code).


b) exceptions which are true errors, this code, this is a BUG BUG BUG BUG BUG, it needs to be ERASED:

EXCEPTION
   WHEN OTHERS THEN
      err_code := SQLCODE;
      err_msg := substr(SQLERRM, 1, 200);

      INSERT INTO audit_table (error_number, error_message)
      VALUES (err_code, err_msg);
END;


that is the SINGLE WORST PRACTICE on the planet.

it could be:

EXCEPTION
   WHEN OTHERS THEN
      procedure_that_is_AUTONOMOUS_TRANSACTION( sqlcode, sqlerrm );
      RAISE;
END;



that will

a) call that procedure with the sql code and sql message - that procedure can
b1) insert that into a log table
b2) COMMIT (without touching the existing transaction)
c) re-raise the error which CANNOT BE HANDLED - it must make it's way back the client

then the client decides if it should

a) retry the operation (maybe they got a deadlock... retry is an option)
b) rollback the transaction
c) do something else and then commit

but only the CLIENT application is smart enough to make those decisions.



This grey area exception:

EXCEPTION
     WHEN DUP_VAL_ON_INDEX THEN
        raise_application_error (-20001,'You have tried to insert a duplicate 
order_id.');


not a fan of that, all it does is:

a) hides the source code line number from the client
b) hides the actually error message from the client


I would let the ora-xxxxx error code and error message (which includes the specific violation, the constraint name) and give the client application a subroutine that can convert the error name (constraint name) into a message like you have here - but for goodness sake, let the client code understand what happened - the client can display a "user friendly" message - but should also include the option in a debug mode to display the ACTUAL message so a developer/dba could at least gain access to it.

A reader, February 11, 2008 - 7:02 pm UTC

Tom

The raise statement is creating a problem for sending the information to the client in the way I would like to.


My exception handles if a user submits an expired session id or invalid session is number

WHEN INVALID_TOKEN_1 THEN
ROLLBACK;
INVALID_TOKEN(v_message_sent);
UPDATE_MESSAGE_LOG(v_message_no,v_message_sent);


This is what the browser or client gets as a result of calling the stored procedure.


<?xml version="1.0" encoding="ISO-8859-1" ?>
<ROWSET>
<ROW>
<ERR_CODE>001</ERR_CODE>
<ERR_MSG>Token Expired</ERR_MSG>
</ROW>
</ROWSET>



Now when i pust "RAISE;" after the update_message_log statement, I get this.
I think I am raising he exception again when I did handle it by displaying an error code to the client in xml format.



ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "ADMIN.SAVE_MSG", line 187
ORA-06512: at line 64


2. Would it make a difference really for the client if i retutn an error code like
ORA-XXX or 001 or any other number as long as he client knows that this is an error and he should know what do with it.

DO you recommend using ORA-20001 instead of 001 as this would not confuse this with the reserved oracle codes?


3. For some reason, i could not have an exception name with same procedure name i call in the exception. Is this invalid?
I had to add exception_name_1 to the exception name,

4. Is there a way to capture the package and procedure name from a session variable instead of hardcoding it in the procerdure call
update_err_log(v_err_code,v_err_msg,'my_procedure','my_package');


5. Would a user created table of "Error_log" for logging errors conflict with and existing oracle table name or view?




thank you,


error log

A reader, March 03, 2008 - 3:13 pm UTC

Tom:

I want to insert the package name /procedure name into the error log where the error occurs.

is there a way to access this using a session variable or i have to hardcode this into the insert statement.

thanks,
Tom Kyte
March 03, 2008 - 8:52 pm UTC

you can get the line number, but the procedure name (which can be duplicated many times - line number is infinitely more relevant) - not.

error

A reader, March 04, 2008 - 1:13 pm UTC

Tom:

Yes, i need to log in package name, procedure name and line number into the error_log.

How do you do that?


Tom Kyte
March 04, 2008 - 2:04 pm UTC

you cannot get the procedure name.

You'd have to write code to get that - and lots of code, lots of slow code. Suggest you forget about it, when diagnosing the error, all you need is the package and line number, your human being eyes can quickly discern the procedure.

  8       dbms_output.put_line( $$PLSQL_UNIT );
  9       dbms_output.put_line( $$PLSQL_LINE );


that'll be the other stuff

error

A reader, March 05, 2008 - 4:07 pm UTC

Tom:

I am reporting an error to a client and logging it into a table. I do not understand why the SQL code does not match the ORA-xxxx oracle error.

How can i get that ORA-xxxxx and report that as Error Code?
Is not this usually what should be reported??

v_err_code := SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,200);

<ERR_CODE>ORA100</ERR_CODE>
<ERR_MSG>ORA-01403: no data found</ERR_MSG>

thanks,
Tom Kyte
March 06, 2008 - 7:47 am UTC

ora-100 is the no data found returned in 3gl's typically - so, is C involved here or anything. show us the entire set of code we need to see to reproduce this.

error

A reader, March 27, 2008 - 12:14 pm UTC

Tom:

No, iam not using any 3GL. Purely pl/sql stored procedure.
here is the procedure.


PROCEDURE TEST (
p_user IN VARCHAR2 DEFAULT NULL )

AS

BEGIN
SELECT * into v_dual from dual where 1 = 0;

EXCEPTION

WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SUBSTR(SQLERRM,1,200);
PRINT_ERROR(v_error_code,v_error_message,v_message_sent);
UPDATE_ERROR_LOG(v_error_code,v_error_message);




Tom Kyte
March 27, 2008 - 1:57 pm UTC

oh, that is just "no data found" - as said, that is the sqlcode returned for "no data found"

I HATE your exception block

I hate the rollback, because..... I hate the when others - because it is not followed by raise.


if you just logged the error and RE-RAISED IT - I would really like your exception block, but as it is - I would not allow you to use it in my database. It would get a big "fail" during code review as being excessively dangerous and bug ridden

error

A reader, March 27, 2008 - 3:05 pm UTC

Tom:

1. I am confused on what should i log. Is it the sql code or the oracle ORA-XXXX error code.
What is the difference between the two? I think many errors results in that same 100 sql code.

2. I know you want a raise in the "OTHERS" exception handler. You want to force the client to handle it instead of ignoring it.

However, the requirement was when an http client sends an http request to oracle and some error occurs I should return an XML message back to client showing the error code and error message.

<?xml version="1.0" encoding="ISO-8859-1" ?>
<ERR_CODE>ORA100</ERR_CODE>
<ERR_MSG>ORA-01403: no data found</ERR_MSG>

When I stick the "raise", the xml message does not get sent. I get something like this.

ORA-01403: xxxxxx


The http client is supposed to parse the xml message and do whatever it wants do with it.

Do you have a better ideas? How can i send a message to client and raise the error at same time.
Tom Kyte
March 30, 2008 - 8:37 am UTC

1) only you can answer that, which do you want? You are the one with a requirement here.

ops$tkyte%ORA11GR1> exec dbms_output.put_line( sqlerrm( 100 ) );
ORA-01403: no data found

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> exec dbms_output.put_line( sqlerrm( -01403 ) );
ORA-01403: no data found

PL/SQL procedure successfully completed.


ANSI has defined 100 as "no data found" - that is what ANSI says to return (and no data found is not an error really, it is a "state of being, a condition"...)

2) do your 'generic' error handling ONLY AT THE UPPERMOST LEVEL (eg: once, the master input/output routine), not down in the details. Do not catch when others in more than one place and do all of the handling at that level. Feel free to have exception blocks elsewhere (if you can catch an exception you were expecting! and deal with it). Have only ONE when others in all of your code .

error line number

A reader, March 27, 2008 - 3:51 pm UTC

Tom:

When I tried to use the variables above for tracking line number I get an error.

htp.p($$PLSQL_UNIT);
htp.p($$PLSQL_LINE);

(1): PLS-00103: Encountered the symbol "$" when expecting one of the following:

Do you know why? Do i need special access for these.
Tom Kyte
March 30, 2008 - 8:37 am UTC

you need to be in a version that supports this new construct. Are you?

error

A reader, March 30, 2008 - 9:01 pm UTC

1. <do your 'generic' error handling ONLY AT THE UPPERMOST LEVEL etc...>


I did not get that. Can you explain how you rewrite this to support sending what you say.

PROCEDURE TEST (
p_user IN VARCHAR2 DEFAULT NULL )

AS

BEGIN
SELECT * into v_dual from dual where 1 = 0;

EXCEPTION

WHEN OTHERS THEN
ROLLBACK;
v_error_code := SQLCODE;
v_error_message := SUBSTR(SQLERRM,1,200);
PRINT_ERROR(v_error_code,v_error_message,v_message_sent);
UPDATE_ERROR_LOG(v_error_code,v_error_message);


2. I want to tracl the Oracle error code. How can i get that only without message (i.e ORA-01401).

3. I am using oracle 9i release 2. Are those 10g variables for trackinh line number.
Tom Kyte
March 31, 2008 - 9:26 am UTC

at the top level - at the thing that calls everything else, not down in a subroutine at the low level - only at the top level - the thing that is invoked as a web service.


2) 100 is the oracle error code. Track what you want, sqlcode will be 100 for that.

3) correct, they did not exist in 9i - part of conditional compilation, new 10gr2 feature.

exception

A reader, March 31, 2008 - 3:37 pm UTC

Tom:

<do your 'generic' error handling ONLY AT THE UPPERMOST LEVEL >

Can you provide a very small example on how you usually implement this? just to get the basic idea.

Are you talking about creating another "begin/end" sub program in the main program.
Tom Kyte
March 31, 2008 - 4:45 pm UTC

at the top level, not in any subroutine - just at the very 'top', the entry point, the procedure called by the client.

exception

A reader, March 31, 2008 - 5:11 pm UTC

Tom:

How can you do error handling at top level.

You have a program with statements A....Z.

Any one of those can create a runtime error that needs to be handled. The "WHEN OTHERS" has to be in the exception handler section at the end.

Any examples on your site or your book for what you mean here.
Tom Kyte
March 31, 2008 - 5:47 pm UTC

ops$tkyte%ORA10GR2> create or replace procedure p1
  2  as
  3  begin
  4          raise program_error;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p2 as begin p1; end;
  2  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p3 as begin p2; end;
  2  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure TOP_LEVEL
  2  as
  3  begin
  4          p3;
  5  exception
  6  when others
  7  then
  8          -- here is the only "safe" place to have a when others, at the
  9          -- main entry point called by client
 10          rollback;
 11          -- whatever..... here.....
 12  end;
 13  /

Procedure created.


p1, p2, p3 - never ever have when others

only that single top level routine called from the outside world can IN THIS VERY SPECIAL CASE.

normally - plsql would never have a when others not followed by raise, this is a unique special case - one of a few such cases.

exception

A reader, March 31, 2008 - 8:30 pm UTC

Tom:

thank you, a picture is worth a thousand words.

1. You are saying that "TOP_LEVEL" procedure can have a "WHEN OTHERS" exception without "raise".

Is not this the same case I have for procedure "test" above. Or your case is different because
TOP_LEVEL is calling p3 which is calling p2 which is calling p1.

Would p1, p2 and p3 have their own internal exception handlers? when A runs into an exception and it propogates back to top level does it hit the "WHEN others" in top_level.


2. If i do this strucure, that means each current procedure I have will be split to two or more procedures, which means more code and maintenance.

How would the output of this differ from what i had before.
is this going to send an xml message and then raise an internal error?


3. Your major point about this is that client can ignore exceptions if oracle app does not raise errors.
But if the client is asking for an xml error code and it does not handle it, then that is not an application error. The client program screwed up. is not this true?


Tom Kyte
April 01, 2008 - 9:14 am UTC

1) I'll put it in bold this time:

p1, p2, p3 - never ever have when others


2) what? huh? that makes no sense to me whatsoever, you have a top_level procedure invoked by things OUTSIDE THE DATABASE. This is the only thing that is allowed to have the when others - this top_level procedure calls (hopefully, we do implement modular code, small subroutines right....) other routines that ARE NOT CALLED from the outside, they do not have any "when others"

I don't know why you would feel compelled to change your existing code?

but, it would make SENSE perhaps to do so, you know, for modularity - structured programming, ease of maintenance (having two source code files is NOT more code, is NOT more maintenance - in fact, it will be EASIER to maintain, less maintenance)

3) I've already said that you have a special case here, you need to send a message back to the client - so be it.

I don't care if a client program "screwed up", I frankly don't care who screwed up - I want to reduce the number of possible chances for screwing up.

It might not be the databases fault, but it is the databases problem if a client screws up - so let us all work to REDUCE THE OPPORTUNITIES FOR SCREWING UP.

code

A reader, April 08, 2008 - 6:08 pm UTC

Tom:

If i want to report an error code (sql code) the same as the one in error message "ORA-00001" not (ORA-1) what do you track?

is there another code that gives me ORA-00001 or do i need to run a function on the sql code to get that.

<ERR_CODE>ORA-1</ERR_CODE>
<ERR_MSG>ORA-00001: unique constraint (XXX.PK_BOOK_SERVER_STATUS) violated</ERR_MSG>

Tom Kyte
April 09, 2008 - 2:45 pm UTC

just store the number one.

all oracle error messages are formatted with ora-NNNNN - five digits.


A reader, April 09, 2008 - 5:06 pm UTC

Tom:

I can store 1, not a problem.

The issue is the parser on the client side. it expects a standard format like ORA-xxxxx.
Tom Kyte
April 10, 2008 - 10:24 am UTC

so, format it that way

'ORA-' || to_char( 'fm00000', your_sql_code )


???

Combining call stack and error stack

Thomas Bender, April 17, 2008 - 5:59 am UTC

Some time ago I managed to trace exceptions through a chain of procedures by combining information from the error stack and the call stack. Each procedure re-raises the exception by calling raise_application_error, so the error stack grows from the innermost to the outermost procedure, while at the same time the call stack shrinks from the innermost to the outermost procedure. (I overrode the FALSE default for the keeperrorstack parameter in raise_application_error).

Admittedly, I haven't looked at dbms_trace, it may offer a simpler way...

error

A reader, April 21, 2008 - 7:14 pm UTC

Tom:

1. I am still little confused on these codes. Is 100 the ANSI sql error code and ORA-01403 the corresponding oracle error code?


ERROR_CODE ERROR_MESSAGE
-------------------- ---------------------------------------
100 ORA-01403: no data found


2. If i want the program to report the oracle error code "ORA-01403" what variable can i get this from?
Do i have to use substr(sqlerrm,1,10) variable.


3. If you have WHEN OTHERS exception handler is the error always reported without "ORA-"
Adding 'ORA-'||to_char('fm00000',100) might duplicate the ORA- twice.

Tom Kyte
April 23, 2008 - 5:30 pm UTC

1. I am still little confused on these codes. Is 100 the ANSI sql error code
and ORA-01403 the corresponding oracle error code?


YES.

2. If i want the program

do you see the substring of (sqlerrm, 1, 9)?

#3 - huh??? no idea what you mean.

you either print out sqlerrm (which includes the ora-XXXXX or not, and if you don't want it "twice", just use sqlerrm??

error

A reader, April 27, 2008 - 4:20 pm UTC


error

A reader, May 08, 2008 - 5:17 pm UTC

Tom:

I use 9iR2. how can i track the package or procedure name and line number where error occurs. i want to save that into my error log table.

i cant use those

8 dbms_output.put_line( $$PLSQL_UNIT );
9 dbms_output.put_line( $$PLSQL_LINE );


Tom Kyte
May 12, 2008 - 10:03 am UTC

ops$tkyte%ORA9IR2> create or replace procedure p1
  2  as
  3  begin
  4          dbms_output.put_line( '.....' );
  5          raise program_error;
  6          dbms_output.put_line( '.....' );
  7  exception
  8          when others then
  9                  dbms_output.put_line( '================== PRINTING ERROR =================' );
 10                  dbms_output.put_line( dbms_utility.format_call_stack );
 11                  dbms_output.put_line( '================== PRINTING ERROR =================' );
 12                  raise;
 13  end;
 14  /

Procedure created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace procedure p2
  2  as
  3  begin
  4          p1;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA9IR2> exec p2
.....
================== PRINTING ERROR =================
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x6ebabe70        10  procedure OPS$TKYTE.P1
0x6eac4a10         4  procedure OPS$TKYTE.P2
0x6eabe3e0         1  anonymous block

================== PRINTING ERROR =================
BEGIN p2; END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P1", line 12
ORA-06512: at "OPS$TKYTE.P2", line 4
ORA-06512: at line 1


error line number

A reader, May 12, 2008 - 3:40 pm UTC

Tom:

this is great. But, I have many Procedures (inside packages and standalone) (like P10) that can fail.

I log in a record everytime there is a runtime error. But now I want to save the line number for easier debugging.

based on your output, it tells me P10 failed on line 10. But it actually failed on Line 4 when you raised the exception.

How can i get the actual line number that triggerd the expception?

2. To log this do you just simply add this code to exception handler:

v_error_stack := dbms_utility.format_call_stack

insert into error_log(p1,p2,error_stack) values (v_p1,v_p2,v_error_stack)
/

I only need the line number. do i have to save the whole thing.

thanks,


Tom Kyte
May 13, 2008 - 10:10 am UTC

umm, so would:

8 dbms_output.put_line( $$PLSQL_UNIT );
9 dbms_output.put_line( $$PLSQL_LINE );



in 10g you have


ops$tkyte%ORA10GR2> create or replace procedure p1
  2  as
  3  begin
  4          dbms_output.put_line( '.....' );
  5          raise program_error;
  6          dbms_output.put_line( '.....' );
  7  exception
  8          when others then
  9                  dbms_output.put_line( '================== PRINTING ERROR =================' );<b>
 10                  dbms_output.put_line( dbms_utility.FORMAT_ERROR_BACKTRACE );</b>
 11                  dbms_output.put_line( '================== PRINTING ERROR =================' );
 12                  raise;
 13  end;
 14  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p2
  2  as
  3  begin
  4          p1;
  5  end;
  6  /

Procedure created.

ops$tkyte%ORA10GR2> exec p2
.....
================== PRINTING ERROR =================
ORA-06512: at "OPS$TKYTE.P1", line 5

================== PRINTING ERROR =================
BEGIN p2; END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P1", line 12
ORA-06512: at "OPS$TKYTE.P2", line 4
ORA-06512: at line 1


not available on 9i - I gave you the logical equivalent of the conditional compilation stuff yesterday.


you can search this site for DBMS_TRACE as well, maybe you just let us log your exceptions.




2) you can parse a string any which way you like. The data in there is in a predicable format

mark

A reader, May 13, 2008 - 5:20 pm UTC

Tom:

thanks. I guess 9iR2 is limited in error handling. I will also look further into DBMS_TRACE.

Do you like the idea of using application variables that can track Line number or the location in the program and saving that into the error log table?
Tom Kyte
May 14, 2008 - 10:30 am UTC

nope, I do not, too much work.

I like the capture the error at the very top level, period. the underlying code should not do a thing about capturing or processing errors at all - it is the sole responsibility of the top most layer - they know all of the inputs, they can log it, it should not be littered throughout every single little procedure.



error tracking

mark, May 14, 2008 - 11:14 am UTC

Tom:

<at very top level>

not sure what you mean. but let us say you have

procedure P1

begin

SELECT * into ....

SELECT * into ....

SELECT * into ....

EXCEPTION
WHEN NO_DATA_FOUND

END;

Right now, all i know is that program failed. I want my error log to show me exactly (the select statement) where it failed. If i had 10g it would tell me the Line number.
Tom Kyte
May 14, 2008 - 3:46 pm UTC

umm, this code is non sensible.


If you are allowed to have no_data_found for some/all of the queries, the code could ONLY be:


procedure p1
as
begin
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
end;




and the TOP LEVEL CALLER of this function (eg: in my world, that would actually be the client itself - they have access to the entire call stack) would log the error if that was the requirement.


At most, the anonymous block would do it:


begin
p1;
exception
when others then
autonomous_transaction_to_log_error( ... );
RAISE;
end;



I would not litter logging of errors here there and everywhere, it does NOT belong in P1 - it only belongs at the original bit of code that invokes the process.


P1 would catch ONLY exceptions that are NOT ERRORS. Anything P1 did not expect should be ignored in p1, allowed to propagate up the call stack - and at most caught at the very top level OR NOT AT ALL (and if you catch it - be sure to RERAISE it)

10g would not give you the line number with the $$line stuff - the format_error_backtrace would - but that is new to 10g and not available before.


search for dbms_trace on this site, it might be of interest to you as well




but basically, "when others" should not be part of p1, p2, p3 - at most it might be part of your anonymous block used to invoke p1, p2, p3 - but it must be followed by RAISE to reraise the error for the client.

Vaibhav, May 16, 2008 - 6:57 am UTC

Hi,

Looks like everyone is missing out the new function DBMS_UTILITY.FORMAT_ERROR_BACKTRACE..

thsi function can be used only in the Exception block and it will return you the LINE NUMBER where the exception occurred

EG;

DECLARE
a number;

BEGIN

select sal into a from emp where empno = 7934;
select sal into a from emp where empno = 9999999;
select sal into a from emp where empno = 7902;

EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;

Output:
ORA-06512: at line 7 (the 2nd SELECT query)

I hope this helps

@TOM

You are the GOD...Gettting to learn crores of things in Oracle using this site
hats off to you for the great work you are doing
Tom Kyte
May 19, 2008 - 3:01 pm UTC

ctl-f and you'll see we talked about it more than once.

problem is, the person currently talking doesn't use 10g, they use 9i and this didn't exist back then.

Vaibhav, May 16, 2008 - 7:11 am UTC

how stupid of me...

someone has already mentioned DBMS_UTILITY.FORMAT_ERROR_BACKTRACE..

just a revision...hehe

trace

A reader, May 16, 2008 - 5:05 pm UTC

This utility does not seem to exist in 9iR2

Oracle9i Enterprise Edition Release 9.2.0.2.0 - 64bit Production
PL/SQL Release 9.2.0.2.0 - Production

SQL> DECLARE
  2  a number;
  3  
  4  BEGIN
  5  
  6  select sal into a from emp where empno = 7934;
  7  select sal into a from emp where empno = 9999999;
  8  select sal into a from emp where empno = 7902;
  9  
 10  EXCEPTION
 11  
 12  WHEN OTHERS THEN
 13  dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
 14  
 15  END;
 16  /
dbms_output.put_line(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
                                  *
ERROR at line 13:
ORA-06550: line 13, column 35:
PLS-00302: component 'FORMAT_ERROR_BACKTRACE' must be declared
ORA-06550: line 13, column 1:
PL/SQL: Statement ignored

exc

A reader, May 20, 2008 - 5:42 pm UTC

Tom:

This is good. But, if you want program execution to stop after hitting the first select or second select expcetion hwo do i do that. I do not want to continue after the first no_data_found?

Woudl you do something like

when no_data_found then raise no_data_first_select;

and add a main exceptin handler


procedure p1
as
begin
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
begin
select into;
exception when no_data_found then <do whatever, it is OK, not an error>;
end;
end;

Tom Kyte
May 20, 2008 - 9:29 pm UTC

just don't catch the exception - that is, do nothing.

I would:

[this space intentionally left blank]


There would be "no main exception handler", you stated "I want program to stop", to stop program, let exception propagate all of the way up and out.

exc

A reader, May 21, 2008 - 11:14 pm UTC

Tom:

yes I wanted it stop but know which one caused it.

I guess i can use a "v_Stage_no" integer variable and set it before each select statement and then print the value in the main exception handler to know which one.

right.
Tom Kyte
May 22, 2008 - 6:49 am UTC

or just use dbms_utility in 10g to get the error stack and have the line number.

or just

[this space left intentionally blank]

let the error propagate up and out - the error message would have the line number.


meaning: still do nothing.

exception

A reader, May 22, 2008 - 7:52 am UTC

Tom:

It is a 9iR2 so the error stack would not give line number.

You are right about propagation except if you do it you cant log the error in a table before display. Propagation will always show that scary oracle error page which users do not like to see.

am i correct here or missing something.
Tom Kyte
May 23, 2008 - 7:52 am UTC

the client can, well, lets put it this way.

Was the client program written by professional developers? Why would the client program not be responsible for - well - logging errors since it could get errors from the database, from the file system, from here from there from all over the place.

If you ask me, this is the job of the TOP LEVEL THING, the thing that controls everything else. Typically that is the client application.

heck, it could use the database to log all errors even - from where ever they come

error

A reader, May 23, 2008 - 9:56 am UTC

Tom:

If the client is an oracle stored procedure I think what you are saying is

User has a main menu (p1) with list of 10 functions.

P1 calls P2 (1st link)
P1 calls P3 (2nd link)
P1 calls P4 (3rd link)

So you are saying error logging should always be done by P1 and not all the other procedures? would this sort of look like this

procedure P1
is
begin
begin
P2;
exception
when others then log error
end;

begin
P3;
exception
when others then log error
end;

etc.........
end;

Would P2, P3 and all have their own exception handlers too.
Tom Kyte
May 23, 2008 - 4:27 pm UTC

I don't see the point in having all of those handlers, one would do for p1 and p2, p3, p4 would not have any when others.

and your when others would either

a) invoke raise/raise_application_error
b) print a "user friendly error page and return" assuming it was the thing called from the web.

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Atul, June 27, 2008 - 1:34 pm UTC

Hi,

I am getting below errors while executing a package.

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ZNET.CLEANSE_EMPLOYEE_JOB_PKG", line 628
ORA-00018: maximum number of sessions exceeded
ORA-06512: at "ZNET.CLEANSE_EMPLOYEE_JOB_PKG", line 607
ORA-00018: maximum number of sessions exceeded
ORA-06512: at line 1


When I checked LINE-628 and 607 I got following:


SQL> select line,text from dba_source where name='CLEANSE_EMPLOYEE_JOB_PKG' and line=628;

      LINE
----------
TEXT
--------------------------------------------------------------------------------
       628
                 vErrorMessage := SQLERRM;


SQL> select line,text from dba_source where name='CLEANSE_EMPLOYEE_JOB_PKG' and line=607;

      LINE
----------
TEXT
--------------------------------------------------------------------------------
       607
             INSERT INTO ZNET.CLEANSE_ERROR_LOG



We are inserting into table ZNET.CLEANSE_ERROR_LOG if errors or when Others then SQLERRM;.

Column value of the variable and TABLE column is 100.

1)What I am checking is correct?
2)If Yes,How could we resolve this error?

Tom Kyte
June 27, 2008 - 1:44 pm UTC


you are getting a message that says:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

and it happens on this line:

vErrorMessage := SQLERRM;

and... well... ok

ops$tkyte%ORA10GR2> declare
  2          maxl      number := 0;
  3          last_maxl number := 0;
  4          last_err  number;
  5  begin
  6          for i in 1..99999
  7          loop
  8                  maxl := greatest( maxl, length(sqlerrm(-i)) );
  9                  if ( maxl <> last_maxl )
 10                  then
 11                          last_err := i;
 12                          last_maxl := maxl;
 13                  end if;
 14          end loop;
 15          dbms_output.put_line( 'length = ' || last_maxl );
 16          dbms_output.put_line( sqlerrm(-last_err) );
 17  end;
 18  /
length = 402
ORA-33422: (EIFMAKEF04) %a  %d
CAUTION: Due to insufficient disk
space, the extension count of the EIF files(s) could not be recorded. To
avoid any potential errors when the file is imported with the IMPORT
command, please DO NOT move, delete, or rename any of the EIF extension
files. These are files of the form %1p.%3p, where %4p is the extension
number. Please note that there are %2p such files.

PL/SQL procedure successfully completed.


you have a function which returns a varchar2 of unknown size, if you want just the first 100 characters, then

vErrorMessage := substr( sqlerrm,1,100 );


Atul, June 27, 2008 - 3:21 pm UTC

Thank You TOM.

But if you notice we are getting ORA-00018 also.

==
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "ZNET.CLEANSE_EMPLOYEE_JOB_PKG", line 628
ORA-00018: maximum number of sessions exceeded
===

So How do we know..What is triggering what?

is ORA-06502 is triggering ORA-00018? OR VICA-VERSA
Tom Kyte
June 27, 2008 - 3:30 pm UTC

I cannot see how you would be getting max number of sessions - not being able to see all of the code (and definitely NOT wanting too) I can absolutely say your logic on line 628 is *bad*, sqlerrm returns things much larger than 100 characters.

fix that and work from there.

format_backtrace inside a function

sambi, August 13, 2009 - 2:23 pm UTC

Tom,

I have a function that returns format_backtrace and i call the function to log in the audit table in the event of an error.please see the following.

Please sugggest me what happens when we have multiple programs logging different errors simultenously and all using the call to the procedure similar to the following

pkg_error_log.proc_audit('proc2',pkg_error_log.fn_get_error_stack||SQLERRM);

Is there any chance of overlapping the error stack and getting some other procedure's back trace( proc1 in proc2)

I know the following call is safe
pkg_error_log.proc_audit('proc2',DBMS_UTILITY.FORMAT_ERROR_BACKTRACE||SQLERRM);

Basicaly i want to shield the FORMAT_ERROR_BACKTRACE from the developers(if possible). Please suggest.
Thanks in advance...



create table t(c1 number(10) primary key);
create table audit_log(proc_name varchar2(35),error_msg varchar2(255));

insert into t(c1) values(1);

CREATE OR REPLACE PACKAGE pkg_error_log AS

FUNCTION fn_get_error_stack RETURN VARCHAR2;

procedure proc_audit(proc_name IN VARCHAR2,error_msg IN VARCHAR2);

END pkg_error_log;



CREATE OR REPLACE PACKAGE BODY pkg_error_log AS

FUNCTION fn_get_error_stack RETURN VARCHAR2 IS

begin

return DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;

end fn_get_error_stack;

procedure proc_audit(proc_name IN VARCHAR2,error_msg IN VARCHAR2)
IS PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into audit_log(proc_name,error_msg) values(proc_name,substr(error_msg,1,250));

commit;

end proc_audit;

END pkg_error_log;



create or replace procedure proc1 as

begin

insert into t(c1) select c1 from t;

exception when others then

pkg_error_log.proc_audit('proc1',pkg_error_log.fn_get_error_stack||SQLERRM);
RAISE;
end proc1;

select * from audit_log;

proc_name error_msg

proc1 ORA-06512: at "SAM.PROC1", line 5
ORA-00001: unique constraint (SAM.SYS_C0093840) violated


Tom Kyte
August 24, 2009 - 7:08 am UTC

Please sugggest me what happens when we have multiple programs logging
different errors simultenously and all using the call to the procedure similar
to the following


PL/SQL as a language would not work as a programming language if there was.

Each session that is running PLSQL has their own data segment, their own stack segment - they are each independent. A variable modified in session 1 will not affect session 2.


Followup

Sambi, August 17, 2009 - 11:41 am UTC

Tom,

Good morning..Any suggestions on the above approach?.

thank you
Sambi.

Finding PLS-nnnn Error Messages

Praveen Ray, February 01, 2011 - 5:30 am UTC

Hi Tom,

SQLERRM function returns the error message associated with ORA-nnnn. Is there any way to find PLS-nnnn meassages? anything - dictionary/script within database. Examples:

PLS-00323: subprogram or cursor 'x' is declared in a package specification and must be defined in the package body
PLS-00304: cannot compile body of 'x' without its specification

Regards,
Ray
Tom Kyte
February 01, 2011 - 5:09 pm UTC

we'd have to load up the pcmus.msg file from the $ORACLE_HOME/plsql/mesg directory. You can use oerr:

$ oerr pcm 304
304, 0, "cannot compile body of '%s' without its specification"
// MANUAL: cannot compile body of '<MI>name<D>' without its specification
// INDEX:       "package", "package body", "package specification"
// RELEASE: Not in pcc release -- maybe required for sqlforms?
// CAUSE: The compiled package specification required to compile a 
//  package body could not be found. Some possible causes follow:
//  --the package name is misspelled 
//  --the package specification was never compiled 
//  --the compiled package specification is not accessible   
//  The package specification must be compiled before compiling the 
//  package body, and the compiler must have access to the compiled
//  specification.
// ACTION:Check the spelling of the package name. Compile the package
//   specification before compiling the package body. Also, make sure the
//   compiler has access to the compiled specification.


to get the text from the command line - but from within the database - we'd have to have it in a table and query it up. An external table would work...

something like this:

ops$tkyte%ORA11GR2> create or replace directory msg_dir as '/home/ora11gr2/app/ora11gr2/product/11.2.0/dbhome_2/plsql/mesg';

Directory created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create table pcmus_msg
  2  ( line varchar2(4000) )
  3  organization external
  4  ( type oracle_loader
  5    default directory msg_dir
  6    access parameters( fields( line char(4000) ) )
  7    location ( 'pcmus.msg' )
  8  )
  9  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace view pcmus_msg_view
  2  as
  3  select line#, line, last_value( case when line not like '//%' then line# end IGNORE NULLS ) over (order by line#) grp
  4    from (
  5  select rownum line#, line
  6    from pcmus_msg
  7         )
  8  /

View created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> select line from pcmus_msg_view
  2  where grp = (select grp from pcmus_msg_view where line like '304,%')
  3  order by line#
  4  /

LINE
--------------------------------------------------------------------------------
304, 0, "cannot compile body of '%s' without its specification"
// MANUAL: cannot compile body of '<MI>name<D>' without its specification
// INDEX:       "package", "package body", "package specification"
// RELEASE: Not in pcc release -- maybe required for sqlforms?
// CAUSE: The compiled package specification required to compile a
//  package body could not be found. Some possible causes follow:
//  --the package name is misspelled
//  --the package specification was never compiled
//  --the compiled package specification is not accessible
//  The package specification must be compiled before compiling the
//  package body, and the compiler must have access to the compiled
//  specification.
// ACTION:Check the spelling of the package name. Compile the package
//   specification before compiling the package body. Also, make sure the
//   compiler has access to the compiled specification.
//

16 rows selected.

DBMS_UTILITY.NAME_RESOLVE

Rajeshwaran, Jeyabal, April 02, 2011 - 7:35 pm UTC

Tom:

I was reading about DBMS_UTILITY.NAME_RESOLVE from Expert one on one Oracle and tried an example using DBMS_LOCK API and got this error message.

rajesh@10GR2> declare
  2    l_schema varchar2(400);
  3    l_part1  varchar2(400);
  4    l_part2  varchar2(400);
  5    l_dblink varchar2(400);
  6    l_part1_type number;
  7    l_object_number number;
  8  begin
  9    dbms_utility.name_resolve(
 10      name =>'DBMS_LOCK',
 11      context=>1,
 12      schema => l_schema,
 13      part1 => l_part1,
 14      part2 => l_part2,
 15      dblink => l_dblink,
 16      part1_type => l_part1_type,
 17      object_number => l_object_number );
 18
 19      dbms_output.put_line ('l_schema = '|| l_schema);
 20      dbms_output.put_line ('l_part1 = '|| l_part1);
 21      dbms_output.put_line ('l_part2 = '|| l_part2);
 22      dbms_output.put_line ('l_dblink = '|| l_dblink);
 23      dbms_output.put_line ('l_part1_type = '|| l_part1_type);
 24      dbms_output.put_line ('l_object_number = '|| l_object_number);
 25  end;
 26  /
declare
*
ERROR at line 1:
ORA-06564: object DBMS_LOCK does not exist
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at line 9


Elapsed: 00:00:00.34
rajesh@10GR2>


Is that DBMS_LOCK is NOT supported?


Tom Kyte
April 12, 2011 - 11:33 am UTC

or might it be that you do not have access to dbms_lock

that would be a more likely explanation.

ops$tkyte%ORA10GR2> declare
  2    l_schema varchar2(400);
  3    l_part1  varchar2(400);
  4    l_part2  varchar2(400);
  5    l_dblink varchar2(400);
  6    l_part1_type number;
  7    l_object_number number;
  8  begin
  9    dbms_utility.name_resolve(
 10      name =>'DBMS_LOCK',
 11      context=>1,
 12      schema => l_schema,
 13      part1 => l_part1,
 14      part2 => l_part2,
 15      dblink => l_dblink,
 16      part1_type => l_part1_type,
 17      object_number => l_object_number );
 18
 19      dbms_output.put_line ('l_schema = '|| l_schema);
 20      dbms_output.put_line ('l_part1 = '|| l_part1);
 21      dbms_output.put_line ('l_part2 = '|| l_part2);
 22      dbms_output.put_line ('l_dblink = '|| l_dblink);
 23      dbms_output.put_line ('l_part1_type = '|| l_part1_type);
 24      dbms_output.put_line ('l_object_number = '|| l_object_number);
 25  end;
 26  /
l_schema = SYS
l_part1 = DBMS_LOCK
l_part2 =
l_dblink =
l_part1_type = 9
l_object_number = 4265

PL/SQL procedure successfully completed.

A Custom Error Handling System

A reader, September 19, 2013 - 12:41 pm UTC

hi
i want to modify almost all the error messages before they will be displayed to clients but not only in pl/sql code ( in oracle forms screens, sql tool requestds , ...) .it is not possible to modify all of them manually. is there a way to do this? i want to prevent extracting important information about databases from error messages. I know I can turn of displaying error messages in php and … but I want to show error messages to people but first I want to remove important information from error messages and then display them to clients. Any idea?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library