Skip to Main Content
  • Questions
  • when other than exception, and raise application error

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, sj.

Asked: August 12, 2001 - 10:07 am UTC

Last updated: August 28, 2013 - 7:31 pm UTC

Version: 8.1.7

Viewed 50K+ times! This question is

You Asked

Tom
1.
I have a procedure, where in I drop partitions every day and add partitions.

My question is about the exception section.

Do I have to give specific exception conditions to handle the erros which are possible while dropping and adding partitions, or should I leave it by just giving the when others then exception, or should I leave it alone by not giving the exception section at all.

what are the possible erros which can occur while trying to create a partition and trying to drop a partition.

2.
The use of pragma exception init is very clear, but Why do you have to use a raise application error. Can I just use a regular exception, and raise it when I require to. I dont see the specific use of raise application error.

what is the advantage of raise application error over a regular exception which you declare in the exception section and use it.
(the only advantage seems to be that you can assign a number(20001 to 20999) to the error.)

correct me if Iam wrong.

and Tom said...

1)
A when others is almost always a BUG unless it is immediately followed by a RAISE.

The point of an exception block is to catch exceptional conditions you are EXPECTING, handle them gracefully and continue.

For example, lets say you have a procedure that will either INSERT a new record or UPDATE an existing one depending on whether or not it exists. You could code:

begin
insert into t ( columns.... ) values ( values ..... );
exception
when dup_val_on_index then -- record already exists, lets update it
update t set .... = .... where ....;
end;


Now, if that was coded:

begin
insert into t ( columns.... ) values ( values ..... );
exception
when dup_val_on_index then -- record already exists, lets update it
update t set .... = .... where ....;
when others then
null;
end;


that would be a bug. The when others would fire upon some spurious -- un-expected error and the record would be neither added nor updated. It would be skipped.

Same with your routines, if you have a when others -- and don't do anything meaningful in it (eg: email yourself a notification that it failed, log a message using utl_file or an autonomous transaction, etc -- it is an error that will go undetected.

I truly wish we didn't even support WHEN OTHERS.

You should only catch the exceptions you are expecting and can do something about. Let the others propagate out so you can detect them (so you see them)



2) the use of pragma exeception init and raise_application_error are not dependent on each other whatsoever.

I use raise_application_error much much more then RAISE <named exception> myself. I like to see:



ops$tkyte@ORA8I.WORLD> begin
2 raise_application_error( -20001, 'Something really bad happened, here is some text about it' );
3 end;
4 /
begin
*
ERROR at line 1:
ORA-20001: Something really bad happened, here is some text about it

ORA-06512: at line 2

over:

ops$tkyte@ORA8I.WORLD> declare
2 something_really_bad exception;
3 begin
4 raise something_really_bad;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at line 4

myself anyday.

The advantage of raise application error is not only the code (which is huge) but the message you specify as well. Much more meaningful then

ORA-06510: PL/SQL: unhandled user-defined exception


So -- if the error is going to propagate OUT OF plsql (back to a client) i like to catch it and re-raise it with raise_application_error (for a good example of this, see my section on UTL_FILE in my book in the appendix on necessary supplied packages. UTL_FILE raises about 5 or 6 different "user defined exceptions" -- i use raise_application_error to turn them into meaningful error messages.

If the error is going to be caught and handled by PLSQL -- leave it as an exception.


followup to comment one

What I'm trying to say there is - if the error is raised, caught and handled (not re-raised to the client) by PLSQL -- and the error was a USER DEFINED EXCEPTION (like the ones thrown by UTL_FILE for example) -- there is obviously no need to use raise_application_error.

On the other hand, if the error is going to be propagated BACK to the client -- I prefer to catch the USER DEFINED EXCEPTIONS by name and use raise application error to turn them into something MEANINGFUL for the client, for example:

exception
when utl_file.invalid_path then
raise_application_error(-20001,
'INVALID_PATH: File location or filename was invalid.');
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,
'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.');
when utl_file.read_error then
raise_application_error(-20004,
'READ_ERROR: An operating system error occurred during
the read operation.');
when utl_file.write_error then
raise_application_error(-20005,
'WRITE_ERROR: An operating system error occurred during
the write operation.');
when utl_file.internal_error then
raise_application_error(-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.');
end;

is a block I use in all of my utl_file code. That gives the CLIENT application something useful (error code/message) not just:

ORA-06510: PL/SQL: unhandled user-defined exception





Rating

  (111 ratings)

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

Comments

good answer, but

A reader, August 12, 2001 - 8:07 pm UTC

"If the error is going to be caught and handled by PLSQL -- leave it as an exception."

what do you mean, by caught and handled by plsql , leave it as an exception, all errors are captured by pl/sql, and we use user defined exceptions or raise application error ,
correct me





MGB

Manoj, August 13, 2001 - 12:06 am UTC

Tom,
I was just thinking what if we code these kind of general exception handling blocks as individual functions , as they will be used in many places , in ur case wherever UTL_FILE is used , and pass the exception as an argument to the function and use this function to display the User friendly Error message.

May be u can categorize this into silly suggestion (:

Thanks

Sound's like a good idea...

Robert, July 12, 2002 - 5:12 pm UTC

Tom,

What do you think of Manoj's suggestion.
I have tried to think of a way to do something like this myself.

Thanks,

Robert.

Tom Kyte
July 13, 2002 - 9:58 am UTC

I didn't really follow what he was getting at:

ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace function foo ( x in exception ) return varchar2
  2  as
  3  begin
  4          return 'x';
  5  end;
  6  /

Warning: Function created with compilation errors.

ops$tkyte@ORA817DEV.US.ORACLE.COM> show err
Errors for FUNCTION FOO:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/21     PLS-00103: Encountered the symbol "EXCEPTION" when expecting one
         of the following:
         out <an identifier> <a double-quoted delimited-identifier>
         LONG_ double ref char time timestamp interval date binary
         national character nchar
         The symbol "<an identifier> was inserted before "EXCEPTION" to
         continue.

you cannot "pass" an exception to a function. 

Could this be it?

Scott, June 19, 2003 - 2:07 am UTC

G'day

I think Manoj was trying to say that if someone has many programs that need the same exception handling for utl_file, instead of copying the same exception code to all the different programs, is there a way to have a generic piece of code to capture the exceptions and treat them the same in each different program.

Capeche?

Is this adequate..

A reader, October 13, 2003 - 9:36 pm UTC

Tom, we are creating a bunch of database triggers. We are planning the following common exception section in all the triggers. Please opine if it is adequate.

Exception

when others then
raise_application_error( -20001,'Error Encountered'||sqlcode||'-'||sqlerrm)

Is there anything else to make it more efficient.


Tom Kyte
October 14, 2003 - 6:32 am UTC



sure, mine would be:


<this space intentionally left blank>




that "common sense" trigger just seems to be extra, confusing, information hiding keystrokes. I would never consider it ever.

A reader, October 14, 2003 - 8:30 am UTC

Are you suggesting that there should be no exception section at all? Can you please support your recommendation with an illustration.

Tom Kyte
October 14, 2003 - 10:54 am UTC

that is exactly what I'm saying -- not even suggesting -- just plain out SAYING.


I hate when others, wish we didn't have it.

A reader, October 14, 2003 - 11:26 am UTC

In that case how can we capture errors occurring in the databaset triggers in nice manner..

Tom Kyte
October 14, 2003 - 11:33 am UTC

how is that "nice"?

and -- how often do you expect the trigger to fail?

and -- for what reasons?

you use exceptions to catch ERRORS YOU EXPECT (eg: no_data_found, you might catch that, deal with it and ignore it



Tell me, which looks "nicer" to you? 

ops$tkyte@ORA920LAP> create table t ( x int primary key );

Table created.

ops$tkyte@ORA920LAP> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create table t1 ( x int );

Table created.

ops$tkyte@ORA920LAP> create table t2 ( x int );

Table created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> create trigger t1_trigger
  2  before insert on t1 for each row
  3  begin
  4     insert into t values (:new.x);
  5  end;
  6  /

Trigger created.

ops$tkyte@ORA920LAP> create trigger t2_trigger
  2  before insert on t2 for each row
  3  begin
  4     insert into t values (:new.x);
  5  exception
  6  when others then
  7  raise_application_error( -20001,'Error Encountered'||sqlcode||'-'||sqlerrm);
  8  end;
  9  /

Trigger created.

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> insert into t1 values ( 1 );
insert into t1 values ( 1 )
            *
ERROR at line 1:
ORA-00001: unique constraint (OPS$TKYTE.SYS_C006890) violated
ORA-06512: at "OPS$TKYTE.T1_TRIGGER", line 2
ORA-04088: error during execution of trigger 'OPS$TKYTE.T1_TRIGGER'


ops$tkyte@ORA920LAP> insert into t2 values ( 1 );
insert into t2 values ( 1 )
            *
ERROR at line 1:
ORA-20001: Error Encountered-1-ORA-00001: unique constraint (OPS$TKYTE.SYS_C006890) violated
ORA-06512: at "OPS$TKYTE.T2_TRIGGER", line 5
ORA-04088: error during execution of trigger 'OPS$TKYTE.T2_TRIGGER'


<b>I, as a developer, would INFINITELY prefer getting ORA-00001 -- that MEANS something. You would give me ORA-20001 -- what the HECK is that.  I'd have to parse the error message to figure it out.</b>


don't use when others. 

A reader, October 14, 2003 - 12:44 pm UTC

Excellent !!

Framework and Exception

Alex V, October 14, 2003 - 1:38 pm UTC

Tom,

I would agree with you points against WHEN OTHERS and
NOT catching basic Oracle's exceptions in your examples.
Still, for high-level components, it make sence. For example, OrderManager component asks OrderShipmentManager
component to send an Order. I do not want getting ORA-00001, I want 'ORA-30004, Order was not sent, reason 4, shipping is on strike :-)'. And considering posible bugs in complex OrderShipmentManager, I would put WHEN-OTHERS
to orderly rollback and inform client that there is some
temporary unavailablilities...

In short, my points: basic exception - on low-level, user-defined exception and
WHEN_OTHERS - for high-level domain and client communication. (and mix in between).

Have a nice day,

Alex V.

Tom Kyte
October 14, 2003 - 5:02 pm UTC

Absolutely -- as coded above -- everything became a MEANINGLESS 20001

I would not use a when others in your example -- i would catch errors i expected logically might happen. if the error was ora-1234 -- would you still tell them "strike"???

If you have a good solid reason for capturing an error you logically think you might get and can either

a) deal with it (eg: not an error, just an exception that can be handled)
b) map it to an infinitely more meaningful error message (or just log it and re RAISE; it )

go for it. otherwise, leave it be.

I wish we didn't have when others not because it should never be used but because it is SO ABUSED that I'd rather not have it at all (at the expense of the times it would be a logical construct to have).

Alex V., October 15, 2003 - 1:52 pm UTC

<quote>
I would not use a when others in your example -- i would catch errors i expected
logically might happen. if the error was ora-1234 -- would you still tell them
"strike"???
</quote>

The fact of life is that OrderShipmentManager
(for example) was coded by another person and not well
documented. Or, ora-doc for this componend discribes 25
exception and I can re-submit (recover) only 3 of them
and all others (cut by when_other) will be "service temporary unavailable"
for a client. The same message will be for possible bugs
(we are in real world, aren't we :-)). Logging is
obligatory with no questions.

In another words, WHEN OTHER is appropriate when you can not trust somebody's else work (ex: high-level complex business domain components) and have ability to post
"service temporary unavailable" to client. I see these points as a big difference between low-level
pl/sql fragments around database and coding big domain components.

Of course, nothing is 100% and implementation depends on situation in hand. :-)

Alex V.

Thank you for "Effective Oracle by design"!

what about logging when others?

Susan, August 09, 2004 - 2:24 pm UTC

We have a lot of trigger that have exception handling like the reader from above

EXCEPTION
WHEN OTHERS THEN
ls_errmess := SUBSTR(SQLERRM,1,200);
raise_application_error(-20007, ls_errmess);
end;

going forward I'd like only to log the when_others something like

EXCEPTION
WHEN OTHERS THEN
smccarthy.excep.log_errors (errlocation => 'TEST.T1_AUDIT_UPD_DEL');
raise;
END;

Is this approach appropriate? Thanks.


Tom Kyte
August 09, 2004 - 2:38 pm UTC

I like your approach -- it reraises the exception so that at least the recieving client has a CLUE as to what went on.

The first approach turns EVERYTHING into "ora-20007", not very useful.

Thanks!

Susan, August 09, 2004 - 2:59 pm UTC


I enjoyed it!

Botev, August 24, 2004 - 5:34 am UTC

I really enjoyed the arguments against WHEN OTHERS. I've come to such conclusions myself but never tried to express them in such a solid way!

raise_application_error true flag

Menon, January 25, 2005 - 9:57 pm UTC

raise_application_error can also be passed  a flag which
preserves the previous stack.
consider:
----
SQL> create or replace procedure p2
  2  is
  3  begin
  4    raise_application_error( -20999, 'my exception', true);
  5  end;
  6  /

Procedure created.

SQL> create or replace procedure p1
  2  is
  3  begin
  4    p2;
  5  end;
  6  /

Procedure created.

SQL> show errors;
No errors.
SQL> create or replace procedure p
  2  is
  3  begin
  4    p1;
  5  end;
  6  /

Procedure created.

SQL> show errors;
No errors.

Now we will use the flag (last parameter) in the
raise_application_error below:

QL> begin
  2    begin
  3      p;
  4    exception
  5      when others then
  6        raise_application_error( -20222, 'caught exception', true ); -- <-------the flag
  7    end;
  8  end;
  9  /
begin
*
ERROR at line 1:
ORA-20222: caught exception
ORA-06512: at line 6
ORA-20999: my exception

If you dont give the flag you get

begin
*
ERROR at line 1:
ORA-20222: caught exception
ORA-06512: at line 6
----

Also a question for Conor Mcdonald if he is reading.
In the book 'Mastering Oracle PL/SQL..'
page 510 - you (or chris beck) say:
"A developer can handle these raised exceptions only
if she uses the EXCEPTION-INIT pragma.."
Is this true? From above I am able to raise/handle the
exceptions even though I did not define a pragma?






 

Tom Kyte
January 26, 2005 - 8:21 am UTC

they probably should have said


"can PROPERLY handle these raised exceptions (since all good programs run away screaming from code that has "when others" sprinkled throughout) by using pragma exception init to turn the -20000 error code into a named exception"

another question on raise_application_error

Menon, January 25, 2005 - 10:13 pm UTC

I thought raise_application_error is an inbuilt 
"procedure" but why don't "desc" or select from 
all_objects work?

SQL> select object_name, object_type from all_objects where object_name like '%RAISE%';

SQL> desc raise_application_error
ERROR:
ORA-04043: object raise_application_error does not exist


no rows selected

 

Tom Kyte
January 26, 2005 - 8:22 am UTC

desc substr
desc decode
desc whatever

same thing. they are builtin's. they are just there, they are not standalone procedures

OK got the answer for the last question

Menon, January 25, 2005 - 10:23 pm UTC

"I thought raise_application_error is an inbuilt
"procedure" but why don't "desc" or select from
all_objects work?"

From PL/SQL doc:
RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.




related q

Menon, January 26, 2005 - 10:24 am UTC

Tom
what do you think of the raise_application_error last
argument? It seems like a good practice to just
pass in the flag as true always. I wondered why the
flag is not true by default and then realized it
must have been added afterwards to the procedure.

And thanx for the answer to my question to Connor...


Tom Kyte
January 26, 2005 - 10:42 am UTC

because when you catch an exception -- it is expected that

a) you were expecting it
b) you can deal with it
c) the reason you caught it is because A and B were true, so the exception that
you caught isn't "an error" but an excepted condition


so, if you are using raise_application_error, it must be a brand new error that happened during the normal processing of an expected exception.


Meaning, catching when others to turn that into a generic "raise application error" -- why? why would you do that? It only serves to hide the root cause, to lose information. it falls into the "when others is evil" category.

i think you misunderstood my last question :)

Menon, January 26, 2005 - 10:50 am UTC

"what do you think of the raise_application_error last
argument? "

I meant the last "parameter" of raise_application_error (to be passed as true - false by default) and the suggestion that it should be always passed as true (to preserve the stack.) Assume that you have the pragma defined for
the user to be able to catch it separately and so it
is no longer a generic exception.

I understood your answer to my question to Connor earlier
the first time.






Tom Kyte
January 26, 2005 - 11:19 am UTC

No, i didn't mis-understand, you misunderstood me.

I think that last parameter is not needed, for if your CAUGHT the exception, it was EXPECTED -- is not an ERROR. So, why tell someone about a thing that is not an error?


and if it were an error, the proper thing would be "raise;", not raise_application_error.


Do you in java code catch an exception simply to turn it into some other exception?

In Defense of WHEN OTHERS

Robert, January 26, 2005 - 11:42 am UTC

Tom,

Sometimes we get unexpected errors, such as bad data, etc.
We cannot always anticipate the errors we will get.
When we have a large program, we capture the location and state into specific variables which we output using WHEN OTHERS (RAISE_APPLICATION_ERROR).
We include SQLERRM in the message string along with the location and state of the program at the time of failure...so we don't lose ANY information.
Also we clone the same basic structure from program unit to program unit.

I assert that this is a good thing! :)

create or replace procedure x (pi_account1 varchar2, pi_account2 varchar2)
as
v_err_proc varchar2(100) := 'x';
v_err_loc varchar2(100);
v_err_id varchar2(100);
begin
v_err_loc := 'begin program';
-- do some stuff
v_err_loc := 'update account1';
v_err_id := pi_account1;
-- do SQL stuff...update x where account_id = pi_account1
v_err_loc := 'doing more stuff';
-- doing more stuff
v_err_loc := 'delete account2';
v_err_id := pi_account2;
-- do SQL stuff... delete from y where account_id = pi_account2...
v_err_loc := 'more stuff...';
--- doing some more stuff.....
exception
when others then
raise_application_error(-20001, 'ERROR:' ||
v_err_proc || ':' ||
v_err_loc || ':' ||
v_err_id || ':' || sqlerrm);
end;

When you have 100's of programs to keep up with, this lets you go straight to the problem when one bombs.
We use table logging and email notification also... but I find RAISE_APPLICATION_ERROR simple, robust, and trustworthy.

Great idea, huh?

Thanks,

Robert.


Tom Kyte
January 26, 2005 - 1:17 pm UTC

How so, now they all have to parse the error message to figure out "what the heck happened"

I mean -- so, you strung the error stuff together, you lost the line number that it actually happened on -- and so on.

Me, I'd rather have in my log the original error stack, complete with the entire calling stack of of the error.

Just like dbms_job logs into the alert log when my job fails.

ok...

Menon, January 26, 2005 - 12:25 pm UTC

So when do you ever use "raise_application_error"?

And you do make some excellent points...
btw, just curious - your catching exception in
utl_file in the version/wrapper you wrote in expert-one-on-one
(dont have the book with me right now) used
raise_application_error if I recall...Was that a
good use case? I think you were taking the generic
user-defined exception that utl_file throws
and turning into a more useful message based
on some internal variable in utl_file (or something
to that effect.)

And (now I am thinking aloud here - so pardon any
"not-so-smart" thoughts)

what about a case where you want to give a more
meaningful (user-friendly) error message from an exception?
e.g. (pseudo code)

begin
empno := find_an_emp;
exception
when no_data_found
raise_application_error( 'Employee not found'...)
end;
/

In your mind, is that not a valid case?

Also you may want to log an exception - for which
you need to catch it. I guess in such cases you
would simply use "raise"..

Thanx!

Tom Kyte
January 26, 2005 - 1:34 pm UTC

that is a case in point of when I use it -- yes. I took the rather obscure "user defined error" that you would otherwise get and turned it into something meaningful.

but to just catch "ora-1 unique constraint ..." and turn that into a ora-20001 with a text message -- not useful.


I use raise_application_error when propagating a fatal error back to the client (which is written in C or Java or some 3gl)

I use user defined exceptions when I'm propagating an exception to the plsql invoker.

which means, I rarely use user defined exceptions, when I hit an "exceptional condition" in the code -- something that "just should not be", I am not expecting the upper layers of PLSQL code to be able to deal with it (I, the author of the code after all was not expecting it!) I'm expecting the upper layers of plsql to totally ignore this exception and my goal is to get it back to the client. they need a meaningful error code and error message. With raise application error, I can do that. With "raise user_defined_exception", they do not.


Your use case above with NO_DATA_FOUND is a subtle case. If you were expecting the employee to always be found, it seems to me that "NO_DATA_FOUND" would be sufficient. If you were expecting sometimes for the employee to NOT be found, this is normal in your application -- then I sort of think that raising an error is not appropriate (it isn't an error, it is an expected condition).


for logging the error, it would be:

when others
then
log_it();
raise;
end;

(and I think logging is a function of the uppermost level -- the client optimally, they have the most detail -- they can log something very meaningful)

ok - posted too early:)

Menon, January 26, 2005 - 12:27 pm UTC

"so, if you are using raise_application_error, it must be a brand new error that
happened during the normal processing of an expected exception."

That answers my question on "when should one
use raise_application_error" according to you.


Not convinced...

Robert, January 26, 2005 - 1:51 pm UTC

Tom,

<QUOTE>
Me, I'd rather have in my log the original error stack, complete with the entire calling stack of of the error.
<QUOTE>

I am assuming you mean using dbms_utility.format_error_stack ?

To me, the IDEA of 'format_error_stack' is wonderful... but the implementation is much less than that....
If your entire application consisted of *individual* procedures/functions, then this could be very useful... but when an application contains packages which are made up of many procedures/functions... then dbms_utility.format_error_stack becomes somewhat useless because ***it doesn't tell you which program unit you were in at the time*** (not to knock Oracle... maybe there just isn't a good way for Oracle to capture this info yet... just stating fact).

Thanks!

Robert.

Tom Kyte
January 26, 2005 - 2:00 pm UTC

No, my clients log the error, the get the entire error stack from the database -- it just "comes to them"

I'm thinking exactly about what dbms_jobs does when your job fails.




ops$tkyte@ORA9IR2> create procedure p5
  2  as
  3  begin
  4          dbms_output.put_line( 1/0 );
  5  end;
  6  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p4 as begin p5; end;
  2  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p3 as begin p4; end;
  2  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p2 as begin p3; end;
  2  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p1 as begin p2; end;
  2  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable n number;
ops$tkyte@ORA9IR2> exec dbms_job.submit( :n, 'p1;' );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> commit;
 
Commit complete.
 
ops$tkyte@ORA9IR2> !alert  (just does tail -f on the alert log...)

Wed Jan 26 13:54:36 2005
Errors in file /home/ora9ir2/admin/ora9ir2/bdump/ora9ir2_j000_27527.trc:
ORA-12012: error on auto execute of job 63
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.P5", line 4
ORA-06512: at "OPS$TKYTE.P4", line 1
ORA-06512: at "OPS$TKYTE.P3", line 1
ORA-06512: at "OPS$TKYTE.P2", line 1
ORA-06512: at "OPS$TKYTE.P1", line 1
ORA-06512: at line 1



I get the entire calling stack, I get the source code name and line number, I get the additional information that perhaps ONLY the top level knows (job=63, process name = j0000_....., full trace in this other file over there, etc)


 

To Robert

Menon, January 26, 2005 - 2:25 pm UTC

Tom thanx for the followup clarifying your thoughts.

Robert,
Take a look at 10g dbms_utility.format_Error_backtrace.
It gives you the entire stack trace (I think that
is what you want?)

Also, I agree with Tom about his comment on raise_application_error - except may be on the point
that "no_data_found" is a good enough indicator of
"no employee found". Perhaps in cases such as these
where you want to turn an "SQL" condition into an application specific error message, raise_application_error may be justified.
In some ways it is similar to how Tom wrapped the
utl_file messages into more meaningful ones.
For example, the no_data_found may indicate
no employee found or in the same procedure
may indicate "no department found"...Perhaps
in such cases raise_application_error can be used
to convert it into a more meaningful message and
a user defined exception?

In such cases (or otherwise) if you do use raise_application_error (the way you are
using it) then you may want to check out the flag
that I mentioned - which preserves the stack trace.
You dont have to save the location of error this way.
That flag has been there for some time (e.g. it
is there in 9i)




Not seeing the benifit for Packages (?).

Robert, January 26, 2005 - 2:25 pm UTC

Tom,

Here is your test, but using a package instead of individual procedures....The alert log output is not very helpful...
I would rather see some good information included in raise_application_error messages telling me which procedure(s) failed....

SQL> create or replace package test_package
  2  as
  3  procedure main;
  4  procedure p1;
  5  procedure p2;
  6  procedure p3;
  7  procedure p4;
  8  procedure p5;
  9  end;
 10  /

Package created.

SQL> 
SQL> show err
No errors.
SQL> 
SQL> create or replace package body test_package
  2  as
  3  
  4  procedure p5 as
  5  begin
  6     dbms_output.put_line(1/0);
  7  end;
  8  
  9  procedure p4 as begin p5; end;
 10  procedure p3 as begin p4; end;
 11  procedure p2 as begin p3; end;
 12  procedure p1 as begin p2; end;
 13  procedure main as begin p1; end;
 14   
 15  end;
 16  /

Package body created.

SQL> 
SQL> show err
No errors.
SQL> 
SQL> variable n number;
SQL> exec dbms_job.submit( :n, 'test_package.main;' );

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> 

Here is what shows up in the trace file....I think a formatted error message using raise_application_error as I suggested, above, would be more helpful...

Errors in file /u01/app/oracle/admin/tatest/bdump/tatest_snp8_7147.trc:
ORA-12012: error on auto execute of job 1958
ORA-01476: divisor is equal to zero
ORA-06512: at "xxxx.TEST_PACKAGE", line 6
ORA-06512: at "xxxx.TEST_PACKAGE", line 9
ORA-06512: at "xxxx.TEST_PACKAGE", line 10
ORA-06512: at "xxxx.TEST_PACKAGE", line 11
ORA-06512: at "xxxx.TEST_PACKAGE", line 12
ORA-06512: at "xxxx.TEST_PACKAGE", line 13
ORA-06512: at line 1

Thanks,

Robert. 

Tom Kyte
January 26, 2005 - 2:31 pm UTC

you cannot figure out from that precisely the calling stack?

I can:

select line, text from user_source where name = 'TEST_PACKAGE'
and type = 'PACKAGE BODY' order by line;

I can tell precisely where I was and what was happening.




To Robert again:)

Menon, January 26, 2005 - 3:04 pm UTC

To figure out where precisely. You can go to
the line number starting at the "create or replace package body".

So if you have line number 245, then that is the line
number in the package file starting at the
"create or replace package..." in your file...


Missing information!

Robert, January 26, 2005 - 3:24 pm UTC

Tom and Menon,

But with 'my' way.... you get all Oracle generated error info PLUS pertinant info about the DATA.... what is the ID of the record I was on at the time the program bombed! ;)

Thanks,

Robert.

Tom Kyte
January 26, 2005 - 4:31 pm UTC

I have the inputs from the top level -- all of the way down.

I've said you can log the error, but a when others that is NOT followed by RAISE is almost always a bug


this, this is why I call them evil. Programmers misuse them, they use them to HIDE errors from the calling procedures -- bugs are silently introduced and man oh man -- are they hard to find (when the error just silently gets ignored)

When used properly, they are safe, however the improper use of them far far far exceeds the proper use of them.

when others
then
log_the_error(....);
RAISE;
end;

is ok, but 99.999% of the times, the RAISE is quite simply "gone missing"


And generally, I write transactional things -- the inputs at the top level (which are incomplete or missing at the lowest level) are what we need to reproduce -- the individual "record id" at the lowest level typically is not enough to figure out "what went wrong" -- a transaction is a series of operations, all intertwined, the record id you were processing might not even exist when the transaction is rolled back, what you need are the original set of inputs, in order to reproduce the issue -- so once again, back to the top (from my experience).


Like with this site -- I need the URL (and posted data) in order to reproduce an issue. That a null was attempted to be inserted into column X in table T is not relevant (for the row didn't get inserted) -- I needed the stuff that makes the transaction go awry.

To Robert

Menon, January 26, 2005 - 3:39 pm UTC

Well, All I am saying is:
1. You were storing location in v_err_loc - you dont need that if you use the techniques above (either
dbms_utility.format_error_backtrace() for 10g or
the flag in raise_application_error.)
2. True, your method does save the data - but look how
complex it is. So there is a trade off.
3. If you do want to save the data then you can
a. catch an exception
b. log the data - whatever you want to log.
c. re raise the exception (use raise; ) like Tom
suggests.



Food for Thought

Robert, January 26, 2005 - 5:13 pm UTC

Tom, Menon,

1) Menon, I've never heard of the 'true' flag in raise_application_error... I think I will experiment with this!

2) Tom, I totally agree... using WHEN OTHERS to hide errors (99.99999% of the time) is an incomprehensibly 'low' form of programming.

3)
<QUOTE>
I have the inputs from the top level -- all of the way down.....And generally, I write transactional things -- the inputs at the top level ..... so once again, back to the top (from my experience).....
<QUOTE>

Tom, forgive me for being dense... and I think I know what you mean by 'top' level of program, etc.... but could you give a quick example of what you are talking about 'inputs at the top level -- all of the way down' .... and what you might do to capture any error info therefrom.

Thanks,

Robert


Tom Kyte
January 27, 2005 - 7:41 am UTC

i have a top level procedure "p"

P calls p1.

P1 in turn calls p2 and p3 sequentially. the outputs from p2 and p3 are fed into p4.


p4 has an error. it is because of data from p2. the data from p2 won't exist after the rollback. p4 doesn't have all the data sent to P -- just its inputs.

P4 cannot log sufficient data to reproduce the issue. P4 doesn't have it.

P on the other hand, P has all of its inputs. P can be run again with those inputs -- call p2, p3, and then send them to P4.

We need the original inputs to the transaction. Not the inputs to some subroutine within the larger transaction. The issue generally won't reproduce at that level alone.


So, when I have an issue on my site -- I want the data in the URL -- the original inputs.

Robert

Menon, January 26, 2005 - 6:50 pm UTC

For an example of the raise_application_error with
the flag as true and false , see the first post of mine above.


This is how i use exceptions

A reader, January 27, 2005 - 2:39 am UTC

Hi Tom and all folks!
I think that EXCEPTION, PRAGMA EXCEPTION_INIT, RAISE, RAISE_APPLICATION_ERROR etc..., they all have some use, I would never say 'this is right' or 'wrong'.
Here my approach, an example from a package:
---------------------------------------------------
CREATE OR REPLACE PACKAGE BODY BK.XXKITSEL as

-- PACKAGE EXCEPTIONS --
e_lang_code exception;
e_explode_bom exception;
e_comp_code exception;
PRAGMA EXCEPTION_INIT(e_lang_code, -20901);
PRAGMA EXCEPTION_INIT(e_explode_bom, -20902);
PRAGMA EXCEPTION_INIT(e_comp_code, -20903);

-- One of the functions and procedures in the package
function f_getlang(p_l_deliver_to number,
p_l_ship_to number,
p_h_deliver_to number,
p_h_ship_to number)
return varchar2 is
BEGIN
<here the code...>
EXCEPTION
when NO_DATA_FOUND then
if vp_debug_flag = 'Y' then -- DEBUG --
raise;
else
vp_warning_flag := 1;
raise_application_error(-20901, 'DELIVER_TO or SHIP_TO not found!',TRUE);
end if;
END;

and so on......
-------------------------------------------------

In this case I don't turn a NO_DATA_FOUND in another error, I simply deal with it in my graceful way. Moreover, I have a coerent way to deal with the exceptions and group them within my packages/applications.
In other cases I have already initialized the exception and, for example, if I raise a E_LANG_CODE exception (declared above), I just raise it (if my debug_flag is off!). An example from Oracle Manual:
--------------------------------------------------------
DECLARE
deadlock_detected EXCEPTION;
PRAGMA EXCEPTION_INIT(deadlock_detected, -60);
BEGIN
...
EXCEPTION
WHEN deadlock_detected THEN
-- handle the error
...
END;
-------------------------------------------------------
If my debug_flag is ON, I always leave the the Oracle exceptions free to propagate without interferences and without renaming them.
Tom, I'm with you about WHEN OTHERS, I use it very very seldom... And that few times, under protection of my debug_flag, so the straight RAISE is always going to happen when needed.

Tom Kyte
January 27, 2005 - 8:49 am UTC

exception init is good, never said anything about it.

catching and handling exceptions properly is good, never said anything otherwise.

WHEN OTHERS -- I call evil because most programmers "don't get it". they do it totally wrong. They feel that "we must capture every exception -- it isn't an error if we catch it". This leads to really insidious, hard to find and fix BUGS. Data integrity is compromised, wrong answers abound -- and all because an error was hidden.


You should catch exceptions you expect (i've said that over and over)... pragma exception init is there to help you do that.


but I still pretty firmly believe "no_data_found" is sufficient.

Top down error handling...

Robert, January 27, 2005 - 10:27 am UTC

Tom,

If I have a program structured like your 'top/down' example, I will save off the parameters passed to each proedure before I call each one, at each level. Then if an error occurs an any level, my WHEN OTEHRS (RAISE_APPLICATION_ERROR) will concatenate these values with SQLERRM, and propagate back for each level, all the way to the top. My error output may be a little confusing to sift through, but I have every bit of info I need (90%) of the time to go right to the cause of the error (location in program and bad record).
It is true that programming 'defensively' like this is a little ugly at times... but I look at it like 'insurance' that when the unexpected happens I will have everything I need to quickly find/fix the problem.

I guess it's a trade off... I would like to do all my hard work while developing, while I am familiar with the code, and then forget about it; letting my code do all the hard work for me if there is a problem..... VS. ...... having 'prettier' code but having to dig into the code and refamiliarize myself with it and do a lot of preliminary investigation if ever there is a problem..... (especially as my memory gets older and older ;)

Thanks,

Robert.


Tom Kyte
January 27, 2005 - 10:35 am UTC

you see -- we are agreeing to a degree here.

You have the top level logging (which I think is the bare minimum you need)

And you follow EVERY "when others" with a RAISE of some sort (that is what people don't do, that is why I would rather not discuss when others, I wish it didn't exist because of the abuse of it)....

When used properly -- it works well.

When used as most people tend to use it, it introduces massive bugs all over the place.

I'm that 'A Reader' of 'This is how i use exceptions'

Franco, January 28, 2005 - 1:56 am UTC

Tom and Robert,
We're on the same line.

p.s.: Tom, just to avoid a lot of unintentional 'A Reader' like my previous followup, what do you think about a little, 'innocent' cookie? :-)

A reader, January 28, 2005 - 5:27 pm UTC

Tom,

This is an application design question. In a DMT environment, suppose that an application does inserts into a table, and if that table exceeds max_extents, how should the application handle? Can you please tell it in different contexts .. OLTP, batch? I know DMT is sick and max_extents should be taken care of but in a situation where there are lots and lots of inserts than usual, is the application responsible to handle the error? Can you please clarify?

Tom Kyte
January 28, 2005 - 7:27 pm UTC

<quote>
how should the application handle?
</quote>

you tell me -- my answer would be along the lines of put up a message

"sorry, we've just run totally out of space and we won't be working until someone gives us more. Please let the right person know..."


The application "cannot" fix this.

A reader, January 28, 2005 - 7:46 pm UTC

Tom,

Thanks. Is it something that should be done from the database? Where should the "sorry .." message originate from? Also, is the application obligated to handle this message and stop further inserts? Or is it free to do inserts inspite of noticing this error (this might lead to undesired consequences).

Tom Kyte
January 28, 2005 - 7:57 pm UTC

the client application -- it is the only thing that can actually do it.

The TRANSACTION would be rolled back.

The END USER is free to hit the submit button over and over again, but the transaction will keep failing until someone fixes it.

If you program "transactions", this isn't a concern, you get an error, you roll back, you tell the end user "sorry". They can continue on (other transactions might work) or just go home.

But -- only your application logic can say what is possible. program good transactions, correct logic, good data flows -- and this is nothing "special". Errors are to be expected, they shouldn't be cause for anything "special".

Say I ran out of space on a log table or something (has happened once or twice in the past). I report the error (then my inbox gets flooded :) to the end user. I fix it -- but anything that "worked" continued to work -- things that didn't, failed with a message.

Alex, May 09, 2005 - 11:31 am UTC

Tom,

I had no idea how you felt about when others, I found this out after I put it in some code. I didn't see the harm in using it combined with sqlerrm so we always know what error was,

exception when others then
dbms_output.put_line(sqlerrm);

In my procedure. The procedure is just a ref cursor selecting some data. Still a bad idea?

Tom Kyte
May 09, 2005 - 11:39 am UTC

absolutely a horribly bad idea.

who ever calls this procedure would have NO IDEA that it failed, none. you are silently ignoring all errors.

Alex, May 09, 2005 - 1:29 pm UTC

Ok glad I asked, guess I won't be doing that. The documentation had what I needed so now I have:

EXCEPTION
WHEN user_defined OR INVALID_NUMBER OR NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (SQLERRM);

I hope that is an improvement over the worst thing ever.

Tom Kyte
May 09, 2005 - 2:50 pm UTC

NO, get rid of that too!!!!!

why do you want to HIDE those errors? You didn't fix anything, so don't catch anything.

Alex, May 09, 2005 - 3:26 pm UTC

I don't understand, so I should raise everything? How do I "fix" them? NO_DATA_FOUND means my query didn't return any results, why freak the client out with an error message instead of just returning no rows?

Tom Kyte
May 09, 2005 - 4:04 pm UTC

you should only catch that what you EXPECT and can deal with (hence the name "exceptions" they are exceptional...)


no_data_found would never be raised by a OPEN refcursor.

NO_DATA_FOUND is only raised by a select into and a select into that fetches no rows might well "expect" no_data_found

UNLESS you are expecting the EXCEPTION and can do something about it -- yes, FREAK out the client (as in "let them know, something un-expected happened here)

but no_data_found is only raised with SELECT INTO type queries. open -- nope.

Alex, May 10, 2005 - 9:35 am UTC

Tom,

I was expecting those errors...I didn't choose them at random. They use select into all over the place in refcursors in our code. I should probably just rip that piece out that will take take of my need for no_data_found.

As for the ORA-01423 and invalid_number, i'll use this example:

open ref for
select...
into..
from
(select min(to_number(col1))
from....
where) alias
where joins...

It is possible that the user can input parameters that will return bad data from the select in the inline view. The user can store whatever they want into that column from a different piece of the application. So it's possible a character value will be returned, thus throwing invalid_number. It is rare that this would happen, but it's possible.

I see your point though, I will raise the two remaining errors that way we can find out what's associated with the bad data, and hopefully how and why it got there.

Tom Kyte
May 10, 2005 - 10:32 am UTC

no they don't (use select into) with ref cursors!  It doesn't make sense.


ops$tkyte@ORA9IR2> declare
  2          ref sys_refcursor;
  3          l_dummy dual.dummy%type;
  4  begin
  5          open ref for
  6          select dummy
  7            into l_dummy
  8            from dual
  9           where 1=0;
 10  end;
 11  /
 
PL/SQL procedure successfully completed.


Now, what did we just do there?

a) confused future generations becaues of line "7" which is a big NO OP
b) did not get no_data_found, because we cannot get it on the opening of a cursor

Even if you FETCH that cursor:

ops$tkyte@ORA9IR2> declare
  2          ref sys_refcursor;
  3          l_dummy  dual.dummy%type;
  4          l_dummy2 dual.dummy%type;
  5  begin
  6          open ref for
  7          select dummy
  8            into l_dummy
  9            from dual
 10           where 1=0;
 11
 12          fetch ref into l_dummy2;
 13  end;
 14  /
 
PL/SQL procedure successfully completed.


there will NOT be a NO DATA FOUND, you get no data found with:

ops$tkyte@ORA9IR2> declare
  2          l_dummy  dual.dummy%type;
  3  begin
  4          select dummy
  5            into l_dummy
  6            from dual
  7           where 1=0;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4

ops$tkyte@ORA9IR2> declare
  2          l_dummy  dual.dummy%type;
  3  begin
  4          execute immediate 'select dummy from dual where 1=0' into l_dummy;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 4


<b>with static SQL or native dynamic sql with the INTO clause.... 

Never never never with a ref cursor like that</b>


<b>It is possible that the user can input parameters that will return bad data from the select in the inline view.</b>

And that is precisely why the WHEN OTHERS and WHEN <exception I wasn't really expecting but will catch and hide anyway>  so *so so so* bad.  


The end user should KNOW what they did failed, not that it doesn't return data, but that it flat out failed.


CATCH only that which you 

a) expect
b) can fix

else follow it by RAISE; to re-raise it. 

Alex, May 10, 2005 - 11:34 am UTC

Of course it doesn't make sense! Do you think I'm making that up? You response this time around differs from when I asked about this specifically(bottom):

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1288401763279 <code>

And those select intos are nothing, there's worse stuff out there. There's a table in our database where we're storing blank spaces as primary keys....The people who created this app had, uh, issues.

And yes you are right I had no reason to expect no_data_found in that situation. I know what I did now to cause that.

So like I said, I will RAISE, you have set me straight. It's tough to learn to do the right things when you are working with bad code, you become desensitized by it. Then I ask you something and you think I'm from a different planet.

Tom Kyte
May 10, 2005 - 1:46 pm UTC

I'm confused?

you said you were expecting no_data_found, from a ref cursor? that is wrong

You said "I now have

EXCEPTION
WHEN user_defined OR INVALID_NUMBER OR NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (SQLERRM);

"

and I said "NO, that is bad too. You are hiding from the invoker that it happened and you are not correcting anything here. "


So, I am very confused myself here. You gave me this example:

open ref for
select...
into..
from
(select min(to_number(col1))
from....
where) alias
where joins...

open ref for select ... INTO ...

??

Alex, May 10, 2005 - 2:25 pm UTC

You seem to have it right to me. I am not developing this code, I trying to fix existing code I inherited.

Yes you corrected the exception not being raised, I will raise it.

You said the select into is nonsense, I will remove it.

This example:

open ref for
select...
into..
from
(select min(to_number(col1))
from....
where) alias
where joins...

Is to show you why I was coding this exception:

EXCEPTION
WHEN user_defined OR INVALID_NUMBER OR NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line (SQLERRM);

Sorry if I was being unclear, this code can be overwhelming. With your help it seems around half of the procedure isn't needed at all. The variables they were selecting into they were passing as OUT parameters too.

I am having one query

A reader, May 24, 2005 - 3:16 am UTC

Hi Tom

Good morning .
Well , your answers helped me a lot . I am having one
query now .
When I am trying to create any database object , if it fails , I want store the error code(sqlcode) and error messege(sqlerrm) into a table . How can i do that (using a trigger ? )

Thanks in advance for your time .


Debashis
Bangalore

Tom Kyte
May 24, 2005 - 7:54 am UTC

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg14evt.htm#1001932 <code>

servererror triggers, you can search for SERVERERROR on this site for examples

Last parameter to RAISE_APPLICATION_INFO

Dan, May 24, 2005 - 9:51 am UTC

Great thread Tom - I just had to rewrite a procedure where the error traps returned -1 to the calling program when there was an error. They did not raise a error; just returned a -1, and the calling progrma had to deal with it that way.

Worked fine until -1 becomes a legitimate value.... In the end, I believe there ended up being NO EXCEPTION blocks - they did not do anything with any of the errors.

My preference is to use the RAISE_APPLICATION_ERROR(-20001, my_error_string, TRUE)

SQL> set serveroutput on size 1000000
SQL> list
  1  declare
  2      procedure p3 is
  3      begin
  4          dbms_output.put_line('In P3');
  5          raise no_data_found;
  6      exception
  7          when no_data_found then
  8              raise_application_error (-20001,'No data found in P3 - looking for XXXXX', true);
  9      end;
 10
 11      procedure p2 is
 12      begin
 13          dbms_output.put_line('In P2');
 14          p3;
 15      end;
 16
 17      procedure p1 is
 18      begin
 19          dbms_output.put_line('In P1');
 20          p2;
 21      end;
 22  begin
 23      p1;
 24* end;
SQL> /
In P1
In P2
In P3
declare
*
ERROR at line 1:
ORA-20001: No data found in P3 - looking for XXXXX
ORA-06512: at line 8
ORA-01403: no data found
ORA-06512: at line 14
ORA-06512: at line 20
ORA-06512: at line 23


SQL> 

With the last argument of "TRUE", my error is added to the standard error message, allowing me to indicate what it was looking for, row numbers, something to help me find out what data caused the problem. I also get the entire error stack, so I know the rest of the call.


 

Tom Kyte
May 24, 2005 - 1:07 pm UTC

excellent thanks

Legitimate use of 'when others'?

AT, May 24, 2005 - 1:59 pm UTC

How about the following? I need to do something with all employees (give them a raise, for instance). However, what is done is fairly complicated (or in code that is beyond my control) and might fail for an individual employee. But failure for a couple of employees should not mean failure for all employees (they would be very disappointed if they didn't get their raise just because I failed to give Joey one), so I log the error and continue. While I agree with you that exceptions should be handled properly, I think that this is the best you can do in this specific situation. By raising the exception at the end, I give the caller of this block the option to catch and commit, or to rollback.

DECLARE
error_Count NUMBER := 0;
BEGIN
FOR rec IN (SELECT * FROM emp) LOOP
BEGIN
SAVEPOINT my_savepoint;
do_something_complicated_with_every(rec.emp_id);
EXCEPTION
WHEN OTHERS THEN -- uh-oh, the w-word!
ROLLBACK TO SAVEPOINT my_savepoint;
log.log_error('Something meaningful');
error_count := error_count + 1;
END;
END LOOP;
IF error_count > 0 THEN
raise_application_error(some_meaningful_exception);
END IF;
END;
/


What do you think of this approach, and how would you solve it?

Tom Kyte
May 24, 2005 - 3:43 pm UTC

seems that you need a batch table to tell you "process these employees" and each one is "the transaction" then.


for rec in batch
loop
begin
do_something_complicated_with_every(rec.emp_id);
delete from batch where emp_id = rec.emp_id;
commit;
exception
when others
then
rollback;
log_error( .... );
error_cnt := error_cnt+1;
end;
end loop;

Here, plsql is the "client" in effect, calling the stored procedure. The "batch" effect makes this restartable so that after you fix whatever error condition happened, you can just rerun (and not give everyone a raise again)

Absolute Legitimate

Joachim Seekopp, May 25, 2005 - 8:21 am UTC

<my two cents>
AT's approach is absolutely legitimate -
in fact, it is exactly the same approach
as SAVE EXCEPTIONS uses

no need to fix something and rerun
</my two cents>

do you see any difference between AT's approach
and SAVE EXCEPTIONS ?

Tom Kyte
May 25, 2005 - 8:30 am UTC

save exception is for bulk statements.

To me this looks like "bulk transactions".

It *appears* to me that do-something-complicated is the transction here, it is the atomic unit of work. It stands alone.

I see save exceptions as being useful for a bulk (batch) insert. insert what you can, put the bad rows over here.

I personally see this as being more of a 'batch' operation and to make it restartable in a trivial fashion.... you use a batch table

If this is true:

<quote>
But
failure for a couple of employees should not mean failure for all employees
(they would be very disappointed if they didn't get their raise just because I
failed to give Joey one)
</quote>

wouldn't this be true:

But failure of the system during processing of the last employee should not mean failure for all employees (they would be very disappointed if they didn't get their raise just because I failed to give the last employee one)



I was reading the intent here -- do something complicated is complicated and long. we don't want to redo it, and each employee having this process done to them is separate from any other employee.


So, while the original approach is like 'save exceptions', It is not the way I would have approached the problem and I just laid out what I would have done.

Never said it wasn't a legitimate approach, said this is how I think it should be handled.

RAISE_APPLICATION_ERROR

Moumen, June 14, 2005 - 8:43 am UTC

Hi,
I use "RAISE_APPLICATION_ERROR" in a trigger. the application display error but not the message that i put in RAISE_APPLICATION_ERROR

Many thanks

Tom Kyte
June 14, 2005 - 10:05 am UTC

that would be a bug/issue in the application. talk to the owner of the application.

raise_application_error: keep error stack

Ewan, June 22, 2005 - 10:35 am UTC

Dan's post highlights the keep_error_stack parameter to raise_application_error. This was exactly what I was looking for, but isn't working for me. Maybe I'm misunderstanding and you could clarify?

Below I have copied the exception output for three cases: no exception handler, handler which raises application exception and handler which does plain raise. The only version which preserves the stack trace is the first.

SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
  2  PROCEDURE p2
  3  IS
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE(1/0);
  6  END p2;
  7  PROCEDURE p1
  8  IS
  9  BEGIN
 10  p2;
 11  END p1;
 12  END pkg;
 13  /

Package body created.

SQL> exec pkg.p1;
BEGIN pkg.p1; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero 
ORA-06512: at "EWAN.PKG", line 5 
ORA-06512: at "EWAN.PKG", line 10 
ORA-06512: at line 1 


SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
  2  PROCEDURE p2
  3  IS
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE(1/0);
  6  END p2;
  7  
  8  PROCEDURE p1
  9  IS
 10  BEGIN
 11  p2;
 12  EXCEPTION
 13  WHEN OTHERS THEN
 14  RAISE_APPLICATION_ERROR(-20001, TRIM(SQLERRM), TRUE);
 15  END p1;
 16  END pkg;
 17  /

Package body created.

SQL> 
SQL> exec pkg.p1;
BEGIN pkg.p1; END;

*
ERROR at line 1:
ORA-20001: ORA-01476: divisor is equal to zero 
ORA-06512: at "EWAN.PKG", line 14 
ORA-01476: divisor is equal to zero 
ORA-06512: at line 1 


SQL> CREATE OR REPLACE PACKAGE BODY pkg AS
  2  PROCEDURE p2
  3  IS
  4  BEGIN
  5  DBMS_OUTPUT.PUT_LINE(1/0);
  6  END p2;
  7  PROCEDURE p1
  8  IS
  9  BEGIN
 10  p2;
 11  EXCEPTION
 12  WHEN OTHERS THEN
 13  RAISE;
 14  END p1;
 15  END pkg;
 16  /

Package body created.

SQL> exec pkg.p1;
BEGIN pkg.p1; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "EWAN.PKG", line 13
ORA-06512: at line 1
 

Tom Kyte
June 22, 2005 - 4:43 pm UTC

can you be more precise as to what is 'missing'? (lost in the context of a big thread here)

keep error stack: clarification

Ewan, June 23, 2005 - 5:05 am UTC

What's missing is the full error context, pinpointing the line number of the root cause.

I want the exception thrown back to the client to include the PL/SQL line number at which the error arose, not just the line where the exception was thrown. In the example, I want to know which line the division by zero was at.

If I don't handle the exception in PL/SQL, that's the result I get. (Which backs up your point of only catching what you expect and can handle, I know.)

But if I have an exception handler then I lose that information. Even if the handler re-raises. Even if the handler does raise_application_error(keep_error_stack). I lose the key information: line number of the root cause.




Tom Kyte
June 23, 2005 - 6:28 pm UTC

until 10g that information is not available -- once you catch the exception, the error "didn't happen" anymore. In 10g, they added the ability to preserve this.

before that, you can not get it.

What about dbms_trace?

Menon, June 24, 2005 - 4:13 pm UTC

Hi Tom,
Correct me if I am wrong, but using dbms_trace.set_plsql_trace( dbms_trace.trace_all_exceptions)
you can get the line number of the original exception even in 9i..
You have examples of it in asktom, I believe...



Tom Kyte
June 24, 2005 - 6:50 pm UTC

yes, but that is not in the error message -- you cannot have it preserved in the stack.

OTHERS Contradiction

Tim Hall, June 25, 2005 - 9:19 am UTC

Tom:

I understand the point you are making, and I think for the most part you are correct, but in your eagerness to promote sensible exception handling you are throwing the baby out with the bathwater.

The loss of the OTHERS exception handler would be a disaster for many data loads. Often, as mentioned previously, large dataloads may fail due to unforseen circumstances on a small number of rows. Should the whole load fail because of two dodgy rows? If my requirement is that it should proceed, then without the OTHERS trap I would be forced to catch all possible exceptions at the top level, including ones where there is no built in exception, which would be a serious pain in the butt. Instead I trap the error with an OTHERS exception handler, log the error and carry on (no raise) to the next row.

The OTHERS exception handler does have value, and I'm sure you use it in similar situations. The reason for posting this message is that a read through this post with statements like "I wish we didn't have them" can be very misleading, even though I understand your reasons for saying it.

Maybe a better way of putting it would be something like, the use of the OTHERS exception handler in the middle of a transaction can be very dangerous. It's main value comes as a catch-all surrounding an entire transaction, where failure of that transaction should not halt program execution.

You've implied this earlier, but when talking about top-level exception handling, but in the context of the thread it could easily be overlooked.

As for RAISE_APPLICATION_ERROR, I'm with you 100% there. Use it to make a meaningless error, meaningful, not a meaningful error meaningless.

Cheers

Tim...

Tom Kyte
June 25, 2005 - 9:51 am UTC

I wish we didn't have them. I'd rather find another way to accomplish my task given the number of errors that programmers introduce into otherwise OK code due to their misuse.

I put them into the category of autonomous transactions.

Use of when others, when not followed by RAISE, is almost always a bug in the developed code.


<quote src=initial statement>
A when others is almost always a BUG unless it is immediately followed by a
RAISE.
</quote>

That was my point, they are so badly abused that I'd be willing to give them (and autonomous transactions) up as a feature just to protect the masses from the abuses of them I see.

I could always put the top level exception handling in the client application (which would get the entire error stack even, pre-10g) :)

I'd find a way to work around its demise -- and lots of people would find that code they have been running for years has been running "wrong" -- errors have been hidden.

I understand what you are saying.

Tim Hall, June 25, 2005 - 1:59 pm UTC

Hi

<quote>
I'd be willing to give them (and autonomous transactions) up as a feature just to protect the masses from the abuses of them I see.
</quote>

Regarding OTHERS, in the context of the quote I agree with you, but in the context of my batch jobs, I feel rather emotional about its loss.

Regarding autonomous transactions, I guess it would be a swift switch back to error messages placed on pipes or queues before a rollback, so the error logging is not rolled back also. Happy days.

During the cull can you get rid of bind variables? I've never seen the point of them myself. Just kidding ;-)

Cheers

Tim...



Alex, August 01, 2005 - 3:00 pm UTC

Tom,

I have stored procedure "A", that calls stored procedure "B" which calls stored procedure "C". I have a record that needs to get created in proc "A" whether or not
it's related record gets created in proc "C".

My problem is that the transaction is rolled back when it is determined that the record in proc "C" cannot be created, thus losing the record in proc "A". The user wants a message raised when the record in "C" already exists. I only see a few undesirable options for what I want to achieve and I just want your advice. I could

1.) Commit in proc "A", definately not good.
2.) Somehow bump the logic to determine if the record in "C" needs to be created in proc "A" which will be nearly impossible.

This is an issue that I am too inexperienced to know whether or not this is due to our horrible design or something that can be easily worked around. Thank you.

Tom Kyte
August 01, 2005 - 3:04 pm UTC

why cannot you

a) call B that calls C
b) catch the exception raised by C (or not)
c) insert record into A
d) return message to user interface

begin
b;
insert into a ...
return 'Ok';
exception
when that_bad_thing
insert into a ...
return 'Bad thing happend'
end;

or

a) insert record into A
b) call B that calls C in a begin block
c) catch the exception raised by C (or not)
d) return message to UI


begin
insert into a ...
message := 'Ok';
begin
b;
exception
when that_bad_thing
then message := 'Bad thing';
end;
return message;
end;


Thank you

Alex, August 02, 2005 - 1:24 pm UTC


A reader, October 03, 2005 - 11:28 am UTC

Tom,

I have written the following procedure


create or replace procedure p (a out number )
as
begin
insert into t values (10,11);
a:=1;
raise_application_error(-20001,' Error ');
a:=0;
null;
--exception when others then
----null;
end;
/

If I run the above, why does the value in variable a NEVER gets set to 1, but the Update actually happens (a Row is inserted).

Is it a requirement that the whole PL/SQL block should complete in success for that to happen?

Tom Kyte
October 03, 2005 - 8:19 pm UTC

the row is not inserted.  A is not modified because OUT parameters are only modified IF the procedure is "successful" and the transaction rules state that the statement is atomic - hence the insert won't happen either:


ops$tkyte@ORA10GR2> create table t ( x int, y int );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create or replace procedure p (a out number )
  2  as
  3  begin
  4  insert into t values (10,11);
  5  a:=1;
  6  raise_application_error(-20001,' Error ');
  7  a:=0;
  8  null;
  9  end;
 10  /

Procedure created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> variable a number
ops$tkyte@ORA10GR2> exec p(:a)
BEGIN p(:a); END;

*
ERROR at line 1:
ORA-20001:  Error
ORA-06512: at "OPS$TKYTE.P", line 6
ORA-06512: at line 1


ops$tkyte@ORA10GR2> select x, y from t;

no rows selected

ops$tkyte@ORA10GR2> print a

         A
----------


ops$tkyte@ORA10GR2>
 

A reader, October 03, 2005 - 11:35 am UTC

Also to note is that if the Exception where HANDLED in the block, the Output value appears to be set to 1. Its almost as if the value gets set but gets ERASED when the Exception happens?

Tom Kyte
October 03, 2005 - 8:30 pm UTC

if you have the exception handler, the error *never happened*, the procedure *was successful*


if you like, I actually cover this in some detail in either Expert one on one Oracle or the new book (see home page)

A reader, October 04, 2005 - 2:45 am UTC

Tom,
You've run exec p(:a);
which translates to

begin p(:a); end;

I've run a .sql script:


declare
a number;
begin
p(a);
dbms_output.put_line(' value of a ' ||a);
exception when others then
dbms_output.put_line(' value of a on fail' ||a);
end;
/

My SCRIPT above inserts a row, but how is it different to exec p(:a), afterall it translates to an anonymous block ANYWAY?




Tom Kyte
October 04, 2005 - 2:13 pm UTC

you caught the error and made it disappear - the error never happened as you chose to ignore it.

If you have access to either of expert one on one or the new Expert Oracle Database Architecture - I go over these concepts (including the bad practice of when others and how it totally changes the MEANING of things...)

Raise Exceptions

A reader, October 10, 2005 - 6:31 pm UTC

Tom, I am trying to raise an exception and send some useful message to client depending on the conditions

eg:-
Begin
if parameter1>0 and parameter2<0
then
--I want to send some useful message to client
elsif parameter1<0 and parameter2>0
then
--I want to send another message to client
..and so on.
End;
The way i was doing it is declare bunch of exceptions and raise them at each if /elseif and at the end of procedure in exception section i am raising them with raise_application_error(..);

Declare
exception1 exception;
exception2 exception
..

pragam exception_init(exception1,-20001);
pragam exception_init(exception2,-20002);
..
Begin
if parameter1>0 and parameter2<0
then
raise exception1;
elsif parameter1<0 and parameter2>0
then
raise exception2;
..and so on.
Exception
when exception1 then
Raise_application_error(-20001,'MESSAGE1');
when exception2 then
Raise_application_error(-20002,'MESSAGE2');
End;

is this the right way to do it or is there any better apporoach to it. I do not want to declare bunch of exception like this.

Thanks






Tom Kyte
October 10, 2005 - 7:38 pm UTC

why not just

if ...
then
raise_application_error( -20001, 'Message1' );
elsif ....
then
raise_application_error( -20002, 'Message2' );
.....



raise_application_error

sat, December 15, 2005 - 10:13 am UTC

HELLO Tom ,
How do we handle the raise_application_error .
Let say we have procedure like below
procedure pkg1.p2(rtn_code number , rtn_msg varchar2)
is
l_count number ;
begin
select count(*) into l_count from table_1
where table_1.qty_id = '1234';
if l_count > 5 then
raise_application_error('-20005','count is more');
end if;

exception
when other then
raise_application_error('-20004','blahahahahahahahahahaha');
when user_defined exception then
RAiSE ; ---- didnot used the raise_application_error
end;

now i am calling this procedure from another packge as given below

procedure pkg2.p1 (rtn_code number , rtn_msg varchar2)
is
begin
p2(rtn_code,rtn_mesg);
--if the procedure p2 raises any exceptions or raise_application_error how do you track thoise errors .
end;


any help is highly appreciated


Tom Kyte
December 15, 2005 - 11:11 am UTC

I would delete this block of code entirely:

exception
when other then
raise_application_error('-20004','blahahahahahahahahahaha');
when user_defined exception then
RAiSE ; ---- didnot used the raise_application_error


why bother? the only thing it does is hide the real line number from people.

WHEN OTHERS ... used in nested blocks with exception handling in each

Anand, January 11, 2006 - 5:17 am UTC

I am wondering if there is any BUG in
WHEN OTHERS

I am using nested blocks like...

-------------------------------
<<X>> BEGIN

<<Y>> BEGIN
my_count := DBMS_SQL.EXECUTE( curs_net );
EXCEPTION
WHEN something ;
WHEN OTHERS ...

END Y

<<Z>> BEGIN

EXCEPTION

END Z

EXCEPTION
WHEN OTHERS ...
END X
-----------------------------------

For example, In the above example...
Suppose an exception (ORA-01422)is raised in block Y, which should normally be caught by WHEN OTHERS of Y
but, It's not caught by Y but infact is caught by WHEN OTHERS of oute block X.
Is it normal ?


Tom Kyte
January 12, 2006 - 10:14 am UTC

examples are always good.  bits/snippets generally are not.


Here is mine, you'll have to modify it to reproduce your observation (which I myself have never observed)


PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> set echo on
ops$tkyte@ORA10GR2> @test
ops$tkyte@ORA10GR2> drop table t;

Table dropped.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create table t ( x int );

Table created.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte@ORA10GR2> commit;

Commit complete.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> declare
  2          l_n     number;
  3  begin
  4          begin
  5                  select x into l_n from t;
  6          exception
  7                  when no_data_found then dbms_output.put_line( 'no data found' );
  8                  when dup_val_on_index then dbms_output.put_line( 'dup val on index' );
  9                  when others then dbms_output.put_line( '#1 ' || sqlcode );
 10          end;
 11  exception
 12          when others then dbms_output.put_line( '#2 ' || sqlcode );
 13  end;
 14  /
#1 -1422

PL/SQL procedure successfully completed.
 

The above review made by me is a bit naive

anand TELLA, January 11, 2006 - 10:10 am UTC

My review above regarding the credibility of PL/SQL exceptions is naive. I am sorry for that.

---------------
<<X>> BEGIN

EXCEPTION

WHEN OTHERS THEN ...

END X
---------------------

WHEN OTHERS, catches well, all the default exceptions

thanq n BBye

Rasie_application_error

A reader, January 26, 2006 - 3:49 pm UTC

Tom,
I have a situation wherein,i have to raise an error [Meaningful message to client] ,but the situation is , Client application is calling my package "A", which inturn calls another Package "B".
I want to raise an error to client if some value in Package "B" is missing. But if I do that ,i am getting this Exception into "when others" exception in Package "A".
How do i throw this directly to client from Package "B"?

Thanks,

Tom Kyte
January 27, 2006 - 8:19 am UTC

If "A" has a when others the only thing you can do is

a) make fun of the developer of package "A"

b) educate the developer of package "A" how exceptions are to actually work, that you only catch exceptions you can actually DEAL with, that when others not followed by RAISE is almost certain a BUG.


I'd go with b). The fact that "A" catches and then ignores this error or otherwise deals with it means by defintion that "A" does not view this exception as being an error.

Alex, February 02, 2006 - 1:42 pm UTC

Tom,

I was wondering, is there any difference between having a "when others then raise" exception and having no exception handling at all? Which is preferred? I'm running into a lot of procedures that have when others raise and I don't see the point if we aren't expecting anything. I'm thinking it's going to "bomb" in the same fashion either way.

Tom Kyte
February 02, 2006 - 2:33 pm UTC

yes,

when others
then
raise;


is

o not good
o a bad idea
o something I would make you erase if it was my code to be responsible for
o not anything I would consider doing
o evil
o a waste of keystrokes......



all you do by that is hide the actual source of the error from the invoker of your function.

Raise_application_error Vs. dbms_output.put_line

A reader, May 11, 2006 - 10:19 am UTC

Hi Tom,
I am trying to output an information message to stdout in a databse logon trigger. dbms_output.put_line doesn't seem to display the message, even after a call to dbms_output.enable. Raise_application_error does display the message however, it errors out, as it should. What is the way (a function similar to Raise_application_warning or Raise_application_info??) that would write to stdout but not produce an error.
Thanks.

Tom Kyte
May 11, 2006 - 7:41 pm UTC

database logon triggers RUN ON THE SERVER. Their stdout would be the console way way over there - on that server.


How did you call dbms_output.enable BEFORE YOU LOGGED IN I am wondering :)


There is no facility to write to "stdout" (a concept that barely exists unfortunately in 2006 - the concept of "stdout" that is) from a logon trigger that runs on a server entirely separate from the client machine.

the client would have to retrieve the message the logon trigger writes into a package variable - just like dbms-output works (sqlplus - the client - calls dbms_output.get_lines to print out the stuff you dbms_output.put-line!)

Here is the logon trig

A reader, May 12, 2006 - 10:23 am UTC

First things first.. Thanks for your reply. Here is what I do:

Create or replace trigger logontrig
after logon on database
DECLARE
begin
dbms_output.enable(1000000);
for x in (SELECT machine, program, module, osuser, schemaname FROM v$session WHERE audsid = userenv('sessionid' ) and lower(machine) = 'client1' and schemaname not in ('SYS','SYSTEM')) loop
case
when x.program is null and x.module is null then
RAISE_APPLICATION_ERROR(-20001, 'Unindentified client from machine ' || x.machine || '. Database connection rejected.');
...
end;
/

The above "works" - it displays the error back to the client
and disallows a connection.
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20001: 'Unindentified client from machine xxx'. Database connection rejected.'

However, I would like to throw a warning instead and allow the connection. Is there an "forgiving equivalent" of raise_application_error that would display the message but not raise an error?

Thanks.




Tom Kyte
May 12, 2006 - 9:12 pm UTC

Not unless the CLIENT application is willing and able to retrieve said message and display it.

Meaning "your client application can, but clients in general - nope"

full error stack

Vadim Krits, August 01, 2006 - 6:35 am UTC

If I need to receive full error stack, I'm using the following code:

procedure P1(p_text varchar2) is
cur integer;
rows_processed integer;

procedure LocalP is
begin
cur := dbms_sql.open_cursor;
dbms_sql.parse(cur, p_text, dbms_sql.native);
...
rows_processed := dbms_sql.execute(cur);
dbms_sql.close_cursor(cur);
end;

begin
if GetDebugMode = 1 then
LocalP;
else
begin
LocalP;
exception
when others then
if dbms_sql.is_open(cur) then
dbms_sql.close_cursor(cur);
end if;
raise;
end;
end if;
end;


Tom Kyte
August 01, 2006 - 10:10 am UTC

"where" and "what version"



full error stack

Vadim Krits, August 07, 2006 - 1:11 am UTC

>>"where" and "what version"

In our banking system, which works on Oracle 7.3.4, 8.1.7 and 9.2.


Tom Kyte
August 07, 2006 - 7:48 am UTC

the where was meant to be "where do you need to capture this, in the client (that can happen), or in plsql (that will not)"

so basically "7.3" since that is the least common denominator.


not going to happen in that release.


In 815 - dbms_trace was introduced (7 releases ago!), if you search for dbms_trace on this site, you'll see examples of how it works and how it could be used to capture the entire error stack.

In 10gR1 - only one release ago, there is a new dbms_utility entry point that can retrieve it for you.

full error stack

Vadim Krits, August 22, 2006 - 9:16 am UTC

Look more detailed code:

SQL> create or replace 
  2  PACKAGE pkg as
  3    procedure p1;
  4    procedure SetDebugMode(p_mode char);
  5  end;
  6  /

Package created.

SQL> show err;
No errors.
SQL> create or replace 
  2  PACKAGE BODY pkg as
  3    pv_debug_mode char(1) := 'N';
  4    function DebugMode return char is
  5    begin
  6      return pv_debug_mode;
  7    end;
  8    procedure SetDebugMode(p_mode char) is
  9    begin
 10      pv_debug_mode := p_mode;
 11    end;
 12    procedure p2 is
 13    begin
 14      dbms_output.put_line(1/0); 
 15    end;
 16    procedure p1 is
 17    begin
 18      if DebugMode = 'Y' then
 19        p2;
 20      else
 21        begin
 22          p2;
 23        exception
 24          when others then
 25            raise;
 26        end;  
 27      end if;
 28    end;
 29  end;
 30  /

Package body created.

SQL> show err;
No errors.
SQL> exec pkg.p1;
BEGIN pkg.p1; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "BANKAF.PKG", line 24
ORA-06512: at line 1


SQL> exec pkg.SetDebugMode('Y');

PL/SQL procedure successfully completed.

SQL> exec pkg.p1;
BEGIN pkg.p1; END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "BANKAF.PKG", line 13
ORA-06512: at "BANKAF.PKG", line 18
ORA-06512: at line 1
 

Tom Kyte
August 27, 2006 - 2:21 pm UTC

that does not help answer the question I asked of you though...

A reader, September 07, 2006 - 4:49 pm UTC

create or replace package body SEODATA_BKP
as
procedure LOG_DATA(v_MSG in VARCHAR2, v_STATUS in VARCHAR2)
is

v_TERMINAL VARCHAR2(200);
v_OSUSER VARCHAR2(15);
n_SEQ NUMBER;
pragma autonomous_transaction;

begin
select
sys_context('userenv','os_user'),
sys_context('userenv','host'),
audit_log_seq.nextval
into
v_OSUSER,
v_TERMINAL,
n_SEQ
from
dual;

insert into AUDIT_LOG
(OPERATION, SOURCE_SCHEMA, AUDIT_ACTION, TIME_STAMP, TERMINAL, OS_USER, SEQ_NO, STATUS
)
values
('CREATE TABLE', USER, v_MSG, SYSDATE, v_TERMINAL, v_OSUSER, n_SEQ,v_STATUS);

commit;
end;


procedure START_ARCHIVE
is
n_START NUMBER := dbms_utility.get_time;
n_CURRYEAR NUMBER := substr(SYSDATE,8,9);
n_CNT NUMBER;
e_RAISE_NO_TABLE EXCEPTION;
n_TABYEAR CHAR(2);
n_SRC1CNT NUMBER;
n_SRC2CNT NUMBER;
v_SELSTMT VARCHAR(1000);

begin
-- For j in ( ) is faster than Open cursor, Fetch cursor.
for j in (select distinct(substr(TIMSTAMP,8,9)) n_YEAR from SEOSDATA where trunc(TIMSTAMP,'YYYY') < trunc(SYSDATE,'YYYY'))
loop

begin

select
count(*)
into
n_CNT
from
DUAL
where exists
(select
TABLE_NAME
from
USER_TABLES
where
TABLE_NAME='SEOSDATA'||j.n_YEAR);

if (n_CNT = 0) then
--execute immediate 'create table SEOSDATA'||j.n_YEAR||' as select * from SEOSDATA where 1=2';
n_TABYEAR := j.n_YEAR;
raise e_RAISE_NO_TABLE;
end if;

-- Insertion of Data according to Year in Backup Table.
execute immediate 'insert into SEOSDATA'||j.n_YEAR||
' select * from SEOSDATA where to_char(substr(TIMSTAMP,8,9))='||j.n_YEAR||' and substr(TIMSTAMP,8,9) < substr(SYSDATE,8,9)';

-- Comparing Counts of 2 Identical Tables before Deletion.
v_SELSTMT := 'select count(SRC1), count(SRC2) from
(select SEOSDATA.*, 1 SRC1, to_number(null) SRC2
from SEOSDATA
where substr(TIMSTAMP,8,9)='||j.n_YEAR||'
union all
select SEOSDATA'||j.n_YEAR||'.*, to_number(null) SRC1, 2 SRC2
from SEOSDATA'||j.n_YEAR||')';

execute immediate v_SELSTMT into n_SRC1CNT, n_SRC2CNT;

if n_SRC1CNT = n_SRC2CNT then
log_data('Table SEOSDATA'||j.n_YEAR||' archived with '||n_SRC2CNT||' rows for Year 20'||j.n_YEAR||' successfully','S');
delete from SEOSDATA where substr(TIMSTAMP,8,9)=j.n_YEAR;
else
log_data('Row Count '||n_SRC1CNT||' of SEOSDATA <> Row count '||n_SRC2CNT||' of SEOSDATA'||j.n_YEAR||'-- Rollback','F');
end if;

exception
when NO_DATA_FOUND then
log_data('No Rows for table SEOSDATA'||j.n_YEAR,'F');
end;
end loop;

dbms_output.put_line('The archiving of SEOSDATA table completed in '||round((dbms_utility.get_time-n_START)/100,2)||' secs');
exception
when e_RAISE_NO_TABLE then
log_data('Table SEOSDATA'||n_TABYEAR||' does not exist!!. Create Manually and Grant Appr Privileges to this schema','F');
raise_application_error(-20001,'The table SEOSDATA'||n_TABYEAR||' does not exist!!! Create Manually and grant appr rights');

end;

end SEODATA_BKP;

In the above code, when no row is found in (for j in...), it directly goes to the end of the program. I was expecting it will fire NO_DATA_FOUND exception so that I can log it. Any thing I am missing in the code?


Tom Kyte
September 08, 2006 - 4:08 pm UTC

for x in ( select ... )
loop
process
end loop

is the same as:


open c
loop
fetch c into record
exit when c%notfound
process
end loop
close c


NEITHER throws no-data-found, the only SELECT that does is:

SELECT
INTO <<<<=== select into's


no other "select" will.


(your example could have been, well, really really small you know - whittled down to the bare essentials to reproduce your question - that is the sign of a good test case!)


You would have to do normal logic here:

l_data_found_in_table := FALSE;
for x in ( select ..... )
loop
l_data_found_in_table := TRUE;
.....
end loop;
if ( NOT l_data_found_in_table )
then
....

A reader, September 08, 2006 - 3:00 pm UTC

Thanks I got it.. Please ignore the query.

Full error stack

Vadim Krits, September 21, 2006 - 9:09 am UTC

>the where was meant to be "where do you need to capture this, in the client
>(that can happen), or in plsql (that will not)"

I'm using this code to get actual line number where the error has happend. Only in pl/sql.

Tom Kyte
September 22, 2006 - 2:01 am UTC

see above, I addressed that (dbms_trace, dbms_utility)

RAISE_APPLICATION_ERROR

Reader, April 02, 2007 - 3:08 pm UTC

I am trying to get information from dbms_output on which statement in the pl/sql block resulted in error.

example:

create table t (i int);


declare
l_error_msg varchar2(1000);
stmt integer := 1;
begin

insert into t values ('abcd');

insert into t values (2);

insert into t values ('efgh');

EXCEPTION
WHEN OTHERS THEN
l_error_msg := 'Problem with block'|| ' ' || SQLERRM;

rollback;

dbms_output.put_line('sqlcode is '||sqlcode);
RAISE_APPLICATION_ERROR(-20001,l_error_msg);
end;

for example, if I run the above, I get the following output:

ERROR at line 1:
ORA-20001: Problem with block ORA-01722: invalid number
ORA-06512: at line 19

Using RAISE_APPLICATION_ERROR, is there a way to get which statement inside pl/sql block resulted in the above error. For example, I want to see the statement insert into t values ('abcd') as well with the error ORA-01722: invalid number.

Thank You.
Tom Kyte
April 03, 2007 - 9:09 pm UTC

why not remove the plsql exception block and let ONLY THE ERRORS YOU CAN FIX be handled???????

then you will get the full error stack easily, including the line number.

eg: outlaw when others.

pre-defined exceptions

Stewart W. Bryson, July 30, 2007 - 5:33 pm UTC

Tom:

Several of the RDBMS-supplied packages have pre-defined exceptions. For instance, UTL_FILE has the preset exceptions INVALID_PATH, etc.

Is there any way to write pervasive, pre-defined exceptions with our PL/SQL code.

For instance, if I had a package.procedure called MY_PACKAGE.MY_PROCEDURE, could I write a packaged exception MY_EXCEPTION that anyone could use when calling my utility. Something like
BEGIN
  my_package.my_procedure;
EXCEPTION
  when my_exception
  then dbms_output.put_line('The usual happened');
end;

Tom Kyte
August 02, 2007 - 9:10 am UTC

You'd have to create an "exceptions" package, a package spec with just exceptions in it.

ops$tkyte%ORA9IR2> create or replace package our_exceptions
  2  as
  3          foo exception;
  4          bar exception;
  5  end;
  6  /

Package created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package my_package
  2  as
  3          procedure my_procedure;
  4  end;
  5  /

Package created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create or replace package body my_package
  2  as
  3          procedure my_procedure
  4          is
  5          begin
  6                  raise our_exceptions.foo;
  7          end;
  8  end;
  9  /

Package body created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2          my_package.my_procedure;
  3  exception
  4          when our_exceptions.foo
  5          then
  6                  dbms_output.put_line( 'The usual happened' );
  7  end;
  8  /
The usual happened

PL/SQL procedure successfully completed.

Perfect!

Stewart W. Bryson, August 02, 2007 - 1:33 pm UTC

Thanks.

summary

drg, August 07, 2007 - 3:10 am UTC

Hi Tom,

I've read through this entire post and have a headache :)

I would like a summary of it all if possible with the following question in mind. 

I understand using when others without raise is a bug. 

Using it is still ugly because if we have:

SQL> create or replace procedure gi_test is
  2  
  3    v_dummy DATE;
  4  
  5  begin
  6  
  7   v_dummy := '6';
  8   
  9  
 10  end;
 11  /

and run we get:

SQL> exec gi_test;
BEGIN gi_test; END;

*
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at "OPS$PAMS.GI_TEST", line 7
ORA-06512: at line 1

we know the line number. but if we have:

SQL> create or replace procedure gi_test is
  2  
  3    v_dummy DATE;
  4  
  5  begin
  6  
  7   v_dummy := '6';
  8   
  9  exception 
 10  
 11  when others then
 12  -- log error
 13  raise; 
 14  
 15  end;
 16  /

and run we get:

SQL> exec gi_test;
BEGIN gi_test; END;

*
ERROR at line 1:
ORA-01840: input value not long enough for date format
ORA-06512: at "OPS$PAMS.GI_TEST", line 13
ORA-06512: at line 1

which doesn't give us the line number where the error occured.

We have users who will never tell us there is a problem, they'll just ignore it and cause other problems so I want to log it, but if I log it with the exception handler I lose the line number. We're using Oracle 8i and so I can't use dbms_utility.format_error_backtrace. My question is: Is there a better solution or do I have to just then find the line number myself using other debugging techniques?

Or do I need read through this entire post again? :)

Regards

drg




Tom Kyte
August 07, 2007 - 1:01 pm UTC

you can use dbms_trace to get the information automagically captured as well ( in a table in the sys schema) in 8i

summary2

drg, August 07, 2007 - 9:19 pm UTC

so can i assume that you agree to using when others when you need to log errors? would that be best practise?


Tom Kyte
August 10, 2007 - 3:09 pm UTC

when others
   DO WHATEVER THE HECK YOU WANT;
   RAISE; <<<<<<<<<<<<<<<===================================
end;



the problem is no one uses the RAISE, so - I say "DO NOT DO IT" hoping that that very simple message gets through

when others not followed by raise or raise_application_error is almost certainly a bug in your code.

period.


$$plsql_line

Steve Hall, August 14, 2007 - 10:29 am UTC

Greetings, O mighty one!

I insert records into a log during program execution, e.g
D_BUG.LOG($$plsql_line,lv_SQL);

The $$plsql_line reserved variable - makes it easier to jump to the right spot in the code.

This log procedure is an autonomous transaction. The log table has advantages over flat file (batch number, timestamps, no struggle with DBA's for file access)

I also use FORMAT_ERROR_BACKTRACE.

For me, the only use of WHEN OTHERS is to log and raise. But I think Dr. Hall has a good point.


when others help

Stan, October 31, 2007 - 2:09 am UTC

So, I have the task of reviewing code on my project. I'm quick to point out obvious abuses of when others, but there are some cases with which I'm struggling to find fault. For example:

procedure p1
begin
... do some stuff ...
p2(return_code, return_message);
if return_code = 'E' then
notify_somebody(return_message);
end if;
end p1;

procedure p2 (return_code out varchar2, return_message out varchar2)
... do some stuff ...
when others then
return_code = 'E';
return_message = sqlerrm;
end p2;

These are mostly background transactions that don't have any immediate visibility to the user. Is this an appropriate usage of when others?

I propose this, but I'm scoffed at:

procedre p1
begin
... do some stuff ...
p2;
when others then
notify_somebody;
raise;
end p1;

procedure p2
... do some stuff ...
end p2;

Are there any differences in these scenairios besides losing line numbers where the error occurred? If it's a background process, who actually sees the raise in the second approach anyway?



Tom Kyte
November 01, 2007 - 3:58 pm UTC

...
procedure p1
begin
  ... do some stuff ...
  p2(return_code, return_message);
  if return_code = 'E' then
    notify_somebody(return_message);
  end if;
end p1;
....


well first, return codes are so 1980's, hate them.

second - your code should just be:

procedure p1
begin
   ... do some stuff ....
end p1;


because apparently, it does not matter if p2 happens OR NOT. Sure, somebody gets notified - but the thing that invoked p1 never knows or cares, so just skip it.

I fail to see how p1 can just 'return' at this point, whatever invoked p1 has no idea what really happened there.

Give me a real world use of this construct to comment on.



Your CORRECT approach does not have to lose line numbers (dbms_utility gets all of that for you)

Your CORRECT approach is easier to code - no return code handling

Your CORRECT approach is a billion times safe. Developer "X" does not have the ability to IGNORE your error, when it bombs, it bombs - and that is a good thing. Better to bomb than to run silently incorrectly.

Commit

A reader, November 13, 2007 - 12:21 am UTC

Hi Tom,
In the initial reply:
For example, lets say you have a procedure that will either INSERT a new record or UPDATE
an existing one depending on whether or not it exists. You could code:

begin
insert into t ( columns.... ) values ( values ..... );
exception
when dup_val_on_index then -- record already exists, lets update it
update t set .... = .... where ....;
end;

Don't we need commit?
What happens to the transactions, when there is no commit in the procedure, and the procedure is successful?
Regards,
Tom Kyte
November 16, 2007 - 2:09 pm UTC

that isn't the transaction - the transaction is something only known to a client.

In my experience, a stored procedure with a commit (or a rollback even) is almost always a bug - shouldn't be there.

Only the CLIENT knows when the transaction is complete. A stored procedure is far too low level to have that understanding.

Say you have a procedure that changes an address.
And another that does the phone number.

The "transaction" is "please change my address and phone number together"

The procedures, had they committed, would prevent this from being possible.


Committing in a procedure is done frequently - but only because people don't get transaction processing and databases.

When others <no raise in sight>;


is done frequently too - doesn't make it right, it is utterly wrong.

Commit in Procedure

A reader, November 16, 2007 - 7:17 pm UTC

Hi Tom,

Thanks.

Could you please let me know, how address and phone change will have problem together (with COMMIT in place)..

Procedure a (p_client.....,p_new_address....)
....
UPDATE tbl_client
SET address=p_new_adresss
WHERE client=p_client
COMMIT; --
EXCEPTION
.......

END a;
Procedure p (p_client.....,p_new_phone....)
....
UPDATE tbl_client
SET phone=p_new_phone
WHERE client=p_client
COMMIT; --
EXCEPTION
.....
END p;

From Client

Call both procedures if address and phone are changed..

Now if we don't put COMMIT.. Will the row locked by first Update will prevent Second Update (being locked by first update)..

Regards,
Tom Kyte
November 21, 2007 - 11:12 am UTC

please read a bit more carefully:

...
Only the CLIENT knows when the transaction is complete. A stored procedure is far too low level to have that understanding.

Say you have a procedure that changes an address.
And another that does the phone number.

The "transaction" is "please change my address and phone number together"
........


and you know what, if you think that YOU locking a row prevents YOU from updating the row again - that is just more reason for me saying that "the best programs in the world do not include insert, update, delete, merge or select - all of that is tucked safely away in stored procedures written by database people"

scary...


do you really believe that if you didn't commit, you would BLOCK YOURSELF in the second stored procedure?

What happens when the second one fails....

David, November 19, 2007 - 10:32 pm UTC

@A reader

With the commits in place; and assuming, as Tom did, that as far as the client is concerned the address and phone updates form a single unit of work; what happens when the first procedure succeeds (and commits) and then the second procedure fails?

As far as the client is concerned, their transaction has failed so they want to roll back. But hang on, that first part has already been committed. So we have to take corrective action to back out the committed change. What happens if someone else has subsequently seen that changed data and acted on it.......the possibilities and permutations of bad things that could happen are endless...

If you don't put the commit then all those bad things can't happen anymore. Both updates are updating the same row but the procedures are being called by the same client and hence in the same transaction so you already own the lock.

It's similar to a synchronized block in Java, it's a reentrant lock. You own the transaction, so you own the lock so you can change the row you've already changed.

Try it....

In Session 1.

create table t (a varchar2(10) primary key);
insert into t values ('DAVID');
commit;
update t set a = 'DAVID2' where a = 'DAVID';

-- DO NOT COMMIT!!!

In another session...

update t set a = 'DAVID0' where a = 'DAVID2'; -- This will not block but will update 0 rows

update t set a = 'DAVID3' where a = 'DAVID'; -- This will block! The other transaction has the row locked.

Back in the first session
update t set a = 'DAVID4' where a = 'DAVID'; -- This will succeed but update no rows, as we've already changed that row in this session.

update t set a = 'DAVID4' where a = 'DAVID2'; -- This will succeed, and update one row.
commit;

You'll now see session 2 unblock but update 0 rows because there is not a row with value 'DAVID' anymore.

Where to log exceptions

Drew, April 09, 2008 - 12:59 pm UTC

Great thread - I hope some of this stuff sinks in (for me).

You said above:

for logging the error, it would be:

when others
then
log_it();
raise;
end;

(and I think logging is a function of the uppermost level -- the client optimally, they have the
most detail -- they can log something very meaningful)

I have two questions:

1) If this is at the "uppermost" level, do you need to "raise" again? Or can we just say, "why not - just in case this isn't really the uppermost level - it doesn't hurt to do it"?

2) Let's say an existing PROC has "when others - log - raise". I create another PROC which calls the first PROC. This new PROC also has "when others - log - raise". In this scenario, any exceptions raised by the existing PROC to the new PROC will get logged twice. It seems like a bad idea to remove the logging from the existing PROC since you don't know what else might be using that PROC. Any thoughts?
Tom Kyte
April 09, 2008 - 3:00 pm UTC

1) if you catch in plsql, you must RAISE again.

2) now you know why I don't think this should be done there....

function

mark, May 09, 2008 - 11:40 pm UTC

Tom:

I have procedure P1 that calls Function f1 within it.

F1 can sometimes raise an application error 20000.

how can i continue processing in P1 even i hit that error.
would it look like this

Procedure P1 ( )

begin

For x in (Select * from Table T)
LOOP
begin
SELECT F1(x.p1) into v_value from dual;
exeption
WHEN OTHERS THEN
null;
end;
---code

END LOOP
Tom Kyte
May 12, 2008 - 12:10 pm UTC

perfect, the code should be:

Procedure P1 ( )

begin

For x in (Select * from Table T)
LOOP

---code

END LOOP

period - if F1 is allowed to fail ONCE or more than ONCE - for some unknown reason - and that is OK with you...

then F1 obviously never needs to be called


I've written that many times, just remove the call to F1, you are done.


If you mean to say "the raise_application_error with 20000 is an error I expect and can deal with" then (please NEVER select from DUAL when a simple assignment will do!!!!)

ops$tkyte%ORA10GR2> create or replace function f1( x in number ) return varchar2
  2  as
  3  begin
  4      if x < 0
  5      then
  6          raise_application_error( -20000, 'X must be >= 0' );
  7      end if;
  8      return 'hello world ' || x;
  9  end;
 10  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1
  2  as
  3      this_one_is_ok exception;
  4      pragma exception_init( this_one_is_ok, -20000 );
  5      l_value varchar2(30);
  6  begin
  7      for i in -1 .. 1
  8      loop
  9          begin
 10              l_value := f1( i );
 11              dbms_output.put_line( 'I got ' || l_value );
 12          exception
 13              when this_one_is_ok then dbms_output.put_line( 'it failed with -20000 but I do not care' );
 14          end;
 15      end loop;
 16  end;
 17  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p1
it failed with -20000 but I do not care
I got hello world 0
I got hello world 1

PL/SQL procedure successfully completed.

expception

A reader, May 14, 2008 - 12:46 am UTC

Tom:

FUNCTION MCL
(p_cust_id IN varchar2,
p_month IN varchar2,
p_year IN varchar2

RETURN NUMBER
IS

BEGIN

cur_month := to_number(to_char(to_Date,p_month,'MONTH'),'MM'));

For k in 1..cur_month
LOOP
begin
SELECT ANNUAL_QTY into AN_QTY FROM CREDITS
WHERE cust_id = p_custid and effective_date =
(SELECT max(effecive_date) from credits
where effective_Date <= to_date('1-'||K||'-'||p_year,'DD-MM-YYYY')
and cust_id=p_custid

exception
WHEN NO_DATA_FOUND
AN_QTY := 0;
end;

SUM_AN_QTY := SUM_AN_QTY + AN_QTY;

END LOOP;

...code
...code

WHEN TOO_MANY_ROWS
raise error( too many records);

WHEN OTHERS
raise error

END;

1. If the query could not find a record for annual credit it assigns a "0" and continue.
If it found too many rows or other error would it ever hit the exception in the main program?
If not, how can i make it do that. DO i add the when too many rows to the inside begin/end and then assign a variable value and then check in the main program for that value and raise an error.
2. Would you change the query to inline view if there are a few records in the table or leave it as is.
3. When do you decide on whether a function should always return a 0 in case no records were not found versus raise an error. Is your decision based on original requirement.

4. Why you do not like to select F1 from dual?
would this always be the same
select f1() into v_f1 from dual;

v_f1 : = f1();


Tom Kyte
May 14, 2008 - 10:51 am UTC

1) it would hit the exception in the main block, yes.

I cannot answer this question, I am not sure what you want to have happen.

I do not know why you would code:

when too_many_rows raise an error;
when others raise an error;

I would code:

[this space intentionally left blank]

what possible reason could you have for catching those - all you would do is

a) lose the actual line number the error happened on
b) make it harder to debug and maintain the code


you gain nothing from it.

2) I would have no procedural code at all - do you see how you might actually make that a single query that does all of the work in one call?

I see many bad practices here - passing month and year as strings!! ugh, give me a DATE please.

I see this slow by slow processing - you are going to call this function for every customer one by one aren't you - the slowest way possible to process data.

procedural code where a single sql statement would have sufficed.

error handling that is not really "good"


Looking at your logic, I'd say it is wrong also.

Suppose I have one record in my customer table:

CUST_ID              EFFECTIVE_DATE        ANNUAL_QTY
------------         ---------------       -------------
1                    01-jan-2008           1000


that is it, just one record. And I call your function with inputs of

cust_id = 1
month = june
year  = 2008


you would return 6,000 - as you would add up jan over and over and over again. Is that what you really intended?



3) ... Is your decision based on
original requirement.
...

umm, well, yeah - what else can it be based on? If the customer has no existing data - what is the expected specified outcome of this function? Should it

a) FAIL
b) succeed and return 0


4) never select from dual (that is DO EXTRA WORK) when you do not have to.

ops$tkyte%ORA10GR2> create or replace function f return number as begin return 42; end;
  2  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec runstats_pkg.rs_start

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_v number;
  3  begin
  4          for i in 1 .. 100000
  5          loop
  6                  select f() into l_v from dual;
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runstats_pkg.rs_middle

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> declare
  2          l_v number;
  3  begin
  4          for i in 1 .. 100000
  5          loop
  6                  l_v := f();
  7          end loop;
  8  end;
  9  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec runstats_pkg.rs_stop(1000)
Run1 ran in 420 hsecs
Run2 ran in 5 hsecs
run 1 ran in 8400% of the time

Name                                  Run1        Run2        Diff
STAT...execute count               100,005           5    -100,000
STAT...calls to get snapshot s     100,001           1    -100,000
STAT...recursive calls             100,004           2    -100,002
LATCH.library cache pin            200,044          38    -200,006
LATCH.library cache                300,107          79    -300,028
STAT...session pga memory          327,680     -65,536    -393,216

Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
500,947         432    -500,515##########%

PL/SQL procedure successfully completed.




and that is 10g where dual is "cheap", in 9i it would even be worse


Answer me this: why would you even consider select f into var from dual instead of the concise, more like a programming language construct of var := f??????


error

mark, May 14, 2008 - 9:48 pm UTC

<<what possible reason could you have for catching those - all you would do is
a) lose the actual line number the error happened on
b) make it harder to debug and maintain the code
you gain nothing from it. >>

1. Are you saying to not use Exception Block unless I need to do something.

2. I use this function in calculations inside a for loop cursor. If a customer does not have a CREDIT record I did nto want to stop processing other customers. The thing is if I get an ERROR it will stop. Shall I return a -1 for that condition and code it so when it sees -1 do nothing.

how do you handle these conditions.

3. the other condition is that a customer must always have one ACTIVE credit limit record. If system found two (too many rows) i should not process this customer and flag him somewhere as an error
Tom Kyte
May 16, 2008 - 12:25 pm UTC

1) correct!!!!!!!!!!


2) sigh, only you can answer this. Probably NULL is the right value to return - as in "we don't know". It totally depends on your REQUIREMENTS (we should spend many more hours on those rather than on code...)

3) you are mixing processes here. You are missing constraints. Your report shouldn't be validating data (that, that should have happened way back when the data was created)

If this query:

SELECT ANNUAL_QTY into AN_QTY FROM CREDITS
WHERE cust_id = p_custid and effective_date =
(SELECT max(effecive_date) from credits
where effective_Date <= to_date('1-'||K||'-'||p_year,'DD-MM-YYYY')
and cust_id=p_custid

were to return two or more rows, then cust_id,effective_date must be duplicated - hence if that is a condition that MAY NOT EXIST - you need to

alter table credits add constraint check_unique unique(cust_id,effective_date);


that'll fix that and then too_many_rows becomes a serious system error that MUST ABORT your process - it is a condition that if it were to occur would mean that mars and pluto have aligned (humor there, if it happened, we have a bug)

and I think the logic is entirely botched here. did you pick up on that?

function

mark, May 15, 2008 - 4:56 pm UTC

Tom:

1. <I see many bad practices here - passing month and year as strings!! ugh, give me a DATE please. >

I am calculating monthly available credit for a given month and year. How would you pass a date instead? do you have to use a "day" for that like "01-MAY-2008".

what i do is:

v_month := TO_NUMBER(TO_CHAR(TO_DATE(p_month,'MONTH'),'MM'));

v_year := to_number(p_year);

is there a better way to do it?

2. Would you use exceptions and raise errors for hte following conditions or not?

You have a FUNCTION A that checks the monthly available credit for a customer.

a. If customer does not have a credit record defined, do you report "-1" or raise error or what. There is nothing to calculate.

b. If credits table has more than one row then it is illegal and you can not calculate. there should be only one active credit defined.

Every month you have a process that scans the ORDERS table and process stuff for customers. I select all customers and then run the function to check available credit. If i hit conditions a and B i do not want to stop processing other customers.

ORDERS
-----------
ORDER_DATE
CUST_ID
BOOK_NO
ORD_QTY


FOR x in (SELECT DISTINCT cust_id,order_date,sum(ord_Qty) from ordered_items)
LOOP
v_mcl := MCL(p_cust_id,p_month,p_year)

IF (sum(ord_Qty) > v_mcl) THEN

--this is my reduction factor function which scans order_items and v_mcl and comes up with a reduction factor
v_rf := RF(....)

UPDATE ORDERED_ITEMS
SET ORD_QTY = GREATEST(v_rf*QRD_QTY,1)
WHERE CUST_ID=V_CUST_ID and ORDER_DATE = '01-MAY-2008'

END IF;

END LOOP;

Tom Kyte
May 19, 2008 - 1:13 pm UTC

1) sure, just pass a date. I don't know why you want month in a variable and year in another variable.

you just put them back again!

where effective_Date <= to_date('1-'||K||'-'||p_year,'DD-MM-YYYY')

2) i would not return a return code. I keep saying "YOU AND ONLY YOU CAN DECIDE WHAT IS RIGHT HERE"

Ok

a) If customer does not have a credit record defined, do you report "-1" or
raise error or what. There is nothing to calculate.


you tell me - if a customer does not have a credit record defined IS THAT AN ERROR. You and only you can answer that.


b) you are missing a constraint then, if it is an error to have two of the same records (cust id,effective date) ADD A CONSTRAINT. and if you hit it at runtime ( a duplicate ) it is now a serious error on the scale of "out of memory", "out of space", "divide by zero", whatever - you fail, something really bad has gone wrong.

error handling of records that need to be updated daily

Jay, August 21, 2008 - 5:43 pm UTC

Tom,

I have a quick question and was hoping to get some advice from you.

I need to write a procedure to do this -

a) Run a query that results in some 50 records and store the records in the table everyday.
b) The query has a timestamp to it.
c) When the records are more than 60 days old, I need to delete the records.
d) I need to store the errors in a separate table that might arise from the procedure.

How do I tackle this situation? I guess I need to use dbms_scheduler to schedule this job everynight.

I know this must be simple. But, I have never done anything like this before. Can you please guide me as to how I need to go about it?


Example create and insert -

drop table testdata;
commit;
create table testdata    
( RUN_DATE DATE,
  SOME_NBR NUMBER,
  SOME_AMT NUMBER
);
commit;

Insert into testdata
(select trunc(sysdate) as rundate,
        level as some_nbr,
        level * 23 as some_amt
   from dual
 connect by level <= 10);
commit;

select *
  from testdata


I believe I would need to delete records when the rundate < 60.


I kind of have a general idea of how to go about it. I am just not sure how to put it all together.

Can you please advice when you get a chance?

Thanks for your time!
Jay


Tom Kyte
August 21, 2008 - 10:26 pm UTC

a) Run a query that results in some 50 records and store the records in the table everyday.
b) The query has a timestamp to it.
c) When the records are more than 60 days old, I need to delete the records.
d) I need to store the errors in a separate table that might arise from the procedure.



it seems you have everything you need?

You have the query right? Just add "sysdate" to it.

Code your procedure, it will do an insert into some_table select <your query>; It will have error handling such that if something fails, you record it into another table, as per your requirement.

You know of the scheduler right? So, schedule it.


I'm not sure what else you need. Your first step is to develop and test the stored procedure that does the "copy" It can probably also do the "delete from some_table where dt > sysdate-60"

Then, you and your dba schedule it.

thank you

Jay, August 22, 2008 - 9:50 am UTC

Your first step is to develop and test the stored procedure that does the "copy" It can probably also do the "delete from some_table where dt > sysdate-60"

Tom,
This is exactly where I needed help with :-)
I will do some more ground work and study about how to do this since you must be much more busy than answer a question for a beginner :-)
Thank you again for your time!!
Jay
Tom Kyte
August 22, 2008 - 2:53 pm UTC

but you seemed to already know how to do that? You had an example above???

thank you

Jay, August 22, 2008 - 5:28 pm UTC

Tom,

Thank you for your time! I came up with the following. It probably is a very bad procedure (or at least I get that feeling), but, I am learning!

Can you advice if this is an okay approach?

create or replace procedure test_report( pDaysToDelete IN NUMBER)
 
is
err varchar(200);
begin
delete from where run_date < trunc(sysdate) - pDaysToDelete;
      
Insert into testdata

<SQL query goes here>

commit;

exception
  when others then 
  rollback;    
  err:= sqlerrm;
  raise_application_error(-20001,'an error was encountered - '||sqlcode||' -error- '||sqlerrm);
  insert 
  into staging_rep_objs.error_data 
       (err_id,
        err_dt,
        descr)
  values (to_char(sysdate,'yyyymmddhhmmss'), 
          sysdate,
          err);
commit;
end congress_report; 



I need to get your books and read it!

Thanks for your time!
Jay

Fatal or not?

Richard, September 03, 2008 - 8:08 pm UTC

Sorry if this is a new question, it's hard to tell.

I recently encountered elsewhere an argument in favor of using WHEN OTHERS with a RAISE pretty much everywhere. While I know this would be grudgingly OK by you, so long as there is that accompanying RAISE, the pattern seems to go against the advice you generally give throughout this thread. So, could I get your take on the following reasoning?

PROCEDURE CALLER IS
BEGIN
-- Do something that might throw an expected
-- standard exception, say NO_DATA_FOUND
--SELECT somefield INTO somevar FROM sometable etc.

-- Then, call another procedure, which might do the same
call_other_which_must_find_data_to_live;
EXCEPTION
WHEN no_data_found THEN
-- Perhaps Log before ignoring, if desired
null;
END;

If call_other_which_must_find_data_to_live raises NO_DATA_FOUND, it would want all processing to stop. But, as currently written, the caller would swallow the exception. While this could of course be fixed, by for example putting the SELECT into an anonymous block, or into its own procedure, it seems to me to be a general hassle in exception handling. And, the point of this post is that the other source I encountered, a 1999 TUSC book, recommended to "Create a Standard User-Defined Exception to Communicate Program Unit Failure", such that the procedure would come out something like this:

PROCEDURE CALLER IS
BEGIN
-- Do something that might throw an expected
-- standard exception, say NO_DATA_FOUND
--SELECT somefield INTO somevar FROM sometable etc.

-- Then, call another procedure, which only throws
-- user_defined_fatal_exception, defined in a package
call_other_which_must_find_data_to_live;
EXCEPTION
WHEN no_data_found THEN
-- Perhaps Log before ignoring, if desired
null;
WHEN user_defined_fatal_exception THEN
-- Do not log in this case, or it would be redundant,
-- since the called proc should do so
RAISE;
WHEN others THEN
-- Log in this case for sure, as logging is done
-- nearest the source
log_error(...);
RAISE user_defined_fatal_exception;
END;

If all procedures only throw user_defined_fatal_exception when they want their exceptions to be fatal, and in particular if call_other_which_must_find_data_to_live does it, then caller would ignore its own NO_DATA_FOUND, propagate a fatal error from its callees, and log any unexpected errors of its own. Sounds great, but it leads away from your general philosophy of not masking exceptions, logging at the top level, using RAISE_APPLICATION_ERROR rather than user-defined exceptions, etc. Can you perhaps give me a good argument against the above? Should a procedure not be able to decide whether its unhandled exceptions are fatal?
Tom Kyte
September 04, 2008 - 8:40 am UTC

...
If call_other_which_must_find_data_to_live raises NO_DATA_FOUND, it would want
all processing to stop. But, as currently written, the caller would swallow
the exception.
.....

then the exception handling was incorrectly written in the first place. Exception blocks should be around small bits of code - just the code that could be affected by the exception itself. If what you say "applies" (is what needs to happen) the only correct code would be:

PROCEDURE CALLER IS
BEGIN
  begin
     -- Do something that might throw an expected
     -- standard exception, say NO_DATA_FOUND
        SELECT somefield INTO somevar FROM sometable etc.
   EXCEPTION
      WHEN no_data_found THEN
         code to deal with no data found goes here
   end;

   -- Then, call another procedure, which might do the same
   call_other_which_must_find_data_to_live;
END;


now if call other which .... fails - raises an exception - so what, it'll just propagate up the stack AS IT SHOULD (that is the only sensible thing)

You should

a) only catch exceptions you can deal with
b) and in the smallest scope possible
c) and never use when others - with the possible exception at the "top of the call stack", the client - the invoker of the entire thing - the process at the top can catch when others, log it and either RAISE IT or return an appropriate "error page" to the end user.

... Should a procedure not be
able to decide whether its unhandled exceptions are fatal? ...


absolutely - there are only two kinds of exceptions however

a) those you can deal with - no data found is a classic one, in many cases YOU EXPECT no data to be found, so you catch it and deal with it.

b) those you didn't expect - they are fatal, you didn't anticipate them when you wrote the code, the code has no way to deal with them, game over player one - you lose.

Here is a good article I just read on this
http://dobbscodetalk.com/index.php?option=com_content&task=view&id=698&Itemid=

I was planning on blogging it soon.


.... talks about having errors basically "shut you down" ....

<quote>
I find these options far preferable than going into an unknown state and praying. Would you engage an airplane autopilot with software with no runtime checking? Would you fire a missile with a flight control computer in it with no double-checks on crazy control outputs? Would you want a defibrillator applied to you with no double checks on the voltage applied? How about that X-ray machine intensity?
</quote>

coder-supplied info

Richard, September 04, 2008 - 5:52 pm UTC

Thanks for your prompt response. I would paraphrase your exception-handling advice as:

a) There are two kinds of exceptions: those a block can deal with (without needing to reraise), and those it cannot.
b) A block should only catch exceptions it can deal with.
c) Such exceptions should be caught in the smallest scope possible.
d) A top-level block might be an exception to 'b'.

Point "c", together with your code example, answered the main question from my first post. There is no need to take the advice, proferred elsewhere, of creating a user-defined exception to indicate that a fatal exception was encountered. Instead, an exception handler should not seek to catch exceptions from multiple sources, which may or may not be fatal depending on the source. Exception handlers should be placed close to the source. (Therefore, the benefit of centralized exception handling at the end of a PL/SQL block is only realized sometimes, for some purposes.)

As a followup, I would like to draw some other conclusions from these points, and from other points in this thread. Please correct me if I go astray.

d) Logging of exceptions that can be dealt with would be carried out where they are caught, since they would not propagate anywhere else.
e) Logging of fatal exceptions would be carried out only at the top level, since these would not be caught anywhere else.
f) If a procedure does multiple things, it likely does not even have an EXCEPTION section, since any exceptions that can be dealt with would likely be caught by exception handlers in subblocks of that procedure, as in your code example.

While I like the idea of not littering code with EXCEPTION blocks, I am stuck at point 'e'. (Although I have read the entries in this thread about logging, I may not have followed them all.) My problem is, at the top level EXCEPTION handler, I can get the package name, the error line number and its call stack (using BACKTRACE), and the error code and description. But, it is harder to get any coder-supplied information, such as the values of parameters of procedures called by the top-level procedure, or a list of unsatisfied preconditions to a procedure.

I can think of several techniques to accomplish this desirable logging of coder-supplied info. Could I ask for your quick review of them?

1) Don't bother, the top-level parameters are most important to debug problems. (Seemed at one point to be advised in this thread, but as I said, I might have misunderstood)
2) Put them into a global package variable, where they can be read out later by the logging procedure.
3) Put them into a database table, where they can be read out later.
4) Put them into the alert log or another file, where they can be read out later.
5) Put in EXCEPTION handlers everywhere after all, and pass coder-supplied info through the text parameter of RAISE_APPLICATION_ERROR. (Advised by others in this thread, not by you)
6) Put in EXCEPTION handlers everywhere after all, and log from within them, afterwards raising a user_defined_fatal_exception so that the calling procedure will know not to log anything. (the technique from my first post)
Tom Kyte
September 05, 2008 - 8:46 am UTC

Thanks for your prompt response. I would paraphrase your exception-handling
advice as:

a) There are two kinds of exceptions: those a block can deal with (without
needing to reraise), and those it cannot.
b) A block should only catch exceptions it can deal with.
c) Such exceptions should be caught in the smallest scope possible.
d) A top-level block might be an exception to 'b'.


exactly.


d) Logging of exceptions that can be dealt with would be carried out where they
are caught, since they would not propagate anywhere else.
e) Logging of fatal exceptions would be carried out only at the top level,
since these would not be caught anywhere else.
f) If a procedure does multiple things, it likely does not even have an
EXCEPTION section, since any exceptions that can be dealt with would likely be
caught by exception handlers in subblocks of that procedure, as in your code
example.


correct.

... But, it is harder to
get any coder-supplied information, such as the values of parameters of
procedures called by the top-level procedure, or a list of unsatisfied
preconditions to a procedure.
...

you have the inputs to main, that is even better - that is what you need. In order to recreate your ERROR, you need to run the code from start to finish.



1) absolutely, you need the inputs that cause the chain of events that lead to the error. Knowing that the number "5" was sent to "low_level_routine_52" and caused a fatal error is not as useful as "if you send this input into 'hire_emp', you'll get the error". You need to know why the bad inputs where generated as well.

2) or what I've seen before is a "push" "pop". Upon entry to a routine, you push a formatted string

create or replace procedure p( p1, p2, p3 )
as
begin
    push( 'p1='||p1||',p2='||p2.... whatever you want);
    ....
    pop()
end;


push/pop just does something in a package like:

ops$tkyte%ORA10GR2> create or replace package log_pkg
  2  as
  3          procedure push( p_str in varchar2 );
  4          procedure pop;
  5
  6          procedure log_error;
  7  end;
  8  /

Package created.

ops$tkyte%ORA10GR2> create or replace package body log_pkg
  2  as
  3          type array is table of long index by binary_integer;
  4
  5          g_array array;
  6
  7          procedure push( p_str in varchar2 )
  8          is
  9          begin
 10                  g_array(g_array.count+1) := p_str;
 11          end;
 12
 13          procedure pop
 14          is
 15          begin
 16                  g_array.delete(g_array.count);
 17          end;
 18
 19          procedure log_error
 20          is
 21          begin
 22                  for i in 1 .. nvl(g_array.count,0)
 23                  loop
 24                          dbms_output.put_line( 'g_array('||i||') = '|| g_array(i) );
 25                  end loop;
 26          end;
 27  end;
 28  /

Package body created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p1( x in number )
  2  is
  3  begin
  4          log_pkg.push( 'p1( x = ' || x || ' )' );
  5          if ( x > 5 )
  6          then
  7                  raise program_error;
  8          end if;
  9          log_pkg.pop();
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p2( x in number )
  2  is
  3  begin
  4          log_pkg.push( 'p2( x = ' || x || ' )' );
  5          p1(x+1);
  6          log_pkg.pop();
  7  end;
  8  /

Procedure created.

ops$tkyte%ORA10GR2> create or replace procedure p3( x in number )
  2  is
  3  begin
  4          log_pkg.push( 'p3( x = ' || x || ' )' );
  5          p2(x+1);
  6          log_pkg.pop();
  7  end;
  8  /

Procedure created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p3(1)

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec log_pkg.log_error;

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec p3(4)
BEGIN p3(4); END;

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


ops$tkyte%ORA10GR2> exec log_pkg.log_error;
g_array(1) = p3( x = 4 )
g_array(2) = p2( x = 5 )
g_array(3) = p1( x = 6 )

PL/SQL procedure successfully completed.




3, 4, 5, 6) no, would not recommend that....


Note: I do not think you need push/pop, it just pacifies those that think you do. push/pop are sort of nice for debugging though - so maybe using conditional compilation they could be there...

How to log line number alongwith SQLCODE and SQLERRM?

sam, December 31, 2008 - 8:50 am UTC

when others
then
log_error( ...... ); -- log error is an autonomous transaction
RAISE;
end;


...if I use it this way, actual line number where error occurred is not revealed.

What is the way I can log actual code-line-number along with SQLCODE and SQLERRM in a log table.
Also, I need to throw the same to the client. Using RAISE throws the line number of RAISE.

Thanks,
Sam

TOAD on WHEN OTHERS THEN NULL; END;

Parthiban Nagarajan, November 10, 2009 - 2:52 am UTC

Hi Tom

Toad is advertised with the statement that "it improves the productivity". I am not sure. The built-in code snippet for anonymous block (Toad version 9.5 -> open an editor tab -> type "anon" -> then type Ctrl+Space bar) is

DECLARE
dummy NUMBER;
BEGIN
SELECT count(*) INTO dummy FROM dual;
;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
NULL;
END;

How it will improve the productivity?
Sure, ignoring the bugs, silently, requires no more code ...
Tom Kyte
November 11, 2009 - 3:04 pm UTC

wow, I'm surprised


Followup on when others then null in Toad

Thomas Kyte, November 12, 2009 - 9:04 am UTC

I talked to Steven Feuerstein over at Quest about this and he is going to put in a recommended change for that, thanks for pointing that out.

Re: Toad / when others then null;

Parthiban Nagarajan, November 14, 2009 - 9:09 am UTC

Hi Tom

You are simply

##### ###### # # # # ### # # ######
# # ## # # # # ## # #
# ### #### # # # # # # # # # ####
# # # # # # # # # # # # #
#### ###### # ## #### ### # ## ######

when others

A reader, November 23, 2009 - 11:33 pm UTC

amazing, steve feurestein and quest did not know about that bug!

no one can be blamed for using it now!

Couldn't agree more!!!

Galen Boyer, May 04, 2010 - 10:37 am UTC

Tom,

Probably a big big issue is that when people read documentation about
EXCEPTION handling in PLSQL, the examples have this bug right in them.
I contacted one author about his example, and he said, well, I was
just trying to be simple about it, but, yeah I see what you are
saying, and then corrected the example. The examples of swallowing
exceptions are everywhere, all over the web. I guess most
egregioius is that some of these examples are in the Oracle
documentation directly.

For example,
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/errors.htm#BABIIHFD
which does exactly what you say it shouldn't, a WHEN OTHERS that does
not RAISE.

Then, in
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/errors.htm#insertedID9
we see Oracle recommending to handle the WHEN OTHERS!!!! How can you
handle something when you don't know what it is, which is exactly what
WHEN OTHERS is. They even go so far as to say, "Use of the OTHERS
handler guarantees that no exception is unhandled." I would assume if
somebody posted that explanation to you about their code, you would
throw an electronic hissy-fit.

Or, if we look at
http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10472/exception_handler.htm#LNPLS01316
we find that Oracle does not even warn about WHEN OTHERS. They just
say, "WHEN OTHERS is optional. If used, it must be the last exception
handler in the exception-handling part of the block."

Seems to me Oracle should let you have at their current documentation
and take all the authors of the documentation sections that don't like
out to the AskTom woodshed.
Tom Kyte
May 06, 2010 - 1:01 pm UTC


Processing Errors versus System Errors

Dave, February 04, 2011 - 10:00 am UTC

I am so happy to see someone with Tom's reputation pointing this out. This is quite possibly my biggest pet-peeve of all time. People using WHEN OTHERS without a RAISE at the end are lazy and simply don't know what their process is doing.

We require explicit exception handlers for ALL expected processing exceptions. We only have 1 WHEN OTHERS exception, at the very end of the program, and we reserve it specifically for system level issues like: Insufficient privileges; Table or view does not exist; Max extents; Dead Lock Detected; Oracle not available when working with DB Linked tables; Immediate Shutdown; etc.

Imagine having a process scheduled to run every night at midnight working fine and runs for 30 minutes, and then someone moves the backup of a remotely linked DB to start at 00:05. Every night your process crashes because Oracle goes down and your WHEN OTHERS handler just logs a messages and keep going - or worse yet - does NULL;

This is especially important when dealing with upgrading systems where tables disappear or the DBA messes up the privileges.

And yes it is annoying when Oracle and Quest and other accpeted experts include code snippets with bad WHEN OTHERS handlers. It would be better if they just left them out.

I could go on.... but

Cheers to you Tom.
Tom Kyte
February 04, 2011 - 10:13 am UTC

... without a RAISE at the end are lazy and simply don't know what their process is
doing. ...

I agree with the latter, but possibly not the former. I believe it is an education/experience matter - not laziness. They are doing what they were TAUGHT in most cases.

We require explicit exception handlers for ALL expected processing exceptions.
We only have 1 WHEN OTHERS exception, at the very end of the program, and we
reserve it specifically for system level issues like: Insufficient privileges;
Table or view does not exist; Max extents; Dead Lock Detected; Oracle not
available when working with DB Linked tables; Immediate Shutdown; etc.


that is *perfect*

When others

A reader, October 18, 2011 - 5:20 pm UTC

Hi Tom,
i am bit new to Oracle (Pl/SQL) programming - learing things.
I have read the thread but still getting confusion.
I have simple 4 doubts - sir

1.When others
Null;

2.When other
Raise;

3.When others
Raise application_error ();

4.I dont use 'when others' - is there still scope a bug ?


When to use each one ? why ? Could you please explain each ?
thank you very much for your time .


Tom Kyte
October 19, 2011 - 7:35 am UTC

1) never use, just never - never ever ever.

basically if you have code such as:

begin
do something_1
do something_2
do something_3
exception when others then null;
end;

you might as well just code:

begin
NULL;
end;


because - the first bit of code says:

if nothing happens - that is ok
if something_1 happens that is ok
if something_1 and something_2 happens that is ok
if something_1 & _2 & _3 happens that is ok

well, if nothing happens is OK, the then best way to code that is "NOTHING EVER HAPPENS" - at least then the caller knows what happened right?


2 & 3) if you have:

when others
then
LOG_ERROR (using an autonomous transaction);
or do something useful - whatever that is

RAISE (or raise_application_error())
end


that is OK - that is good, that is fine - it should be done at a very very very very high level (meaning most code you write would NOT do this). it should be done by the client calling the database - not by the low level stored procedures.

Almost NO CODE should use when others. It is the very exceptional bit of code that does.


4) that is good with me, I approve of code that doesn't have a when others :)

WHEN OTHERS can be useful when used properly

A reader, October 19, 2011 - 4:46 pm UTC

I agree that careless use of WHEN OTHERS is a bad practice, but I have trouble buying into the idea that we only want WHEN OTHERS at a very high level, as has been stated a couple times in this thread by several different people. If a statement fails, I want to know which statement failed and why. Consider the following:



drop table a;

create table a (val number);

create or replace procedure boom
is
begin
insert into a values (1);
insert into a values (2);
insert into a values ('A');
insert into a values (4);
insert into a values (5);
commit;
exception
when others then
-- log exception
rollback;
raise;
end;
/

exec boom();



From the exception being raised, which of my 5 statements failed? Sure, it is easy to see looking at this small procedure what statement has the problem, but what if this was a large procedure calling other procedures inserting/updated dozens of tables with hundreds of columns and firing off triggers that call other stored procedures. Having an exception handler at the end of a large procedure to trap and record my exceptions, I have no way of determining which statement in my process failed. The line number that is passed back to the client is the line of the RAISE in the exception handler, not where the exception actually occurred. The error message is a generic “ORA-01722: invalid number” which could happen with variable assignments, selects, inserts, updates and even deletes if there are triggers on that table.

I have inherited large procedures with multiple paths of execution inserting and updating the same table(s) in multiple places in multiple ways that relied on one big exception handler that catches anything that fails, and when something fails you spend literally days trying to figure out where it broke and why. Trapping the error at the end of the procedure is virtually worthless; you have lost all context of the statement, you don’t know what variables have data that is important for your troubleshooting, you have potentially lost variables that were defined within a block or a call to another procedure, your call stack is gone (if the error occurred inside other called procedures), and who knows how many exception handlers you have already bubbled through having your exception data “tweaked” for whatever purposes that developer felt like tweaking it for. Your log cannot tell you what failed, why it failed, where it failed, or pretty much any bit of useful information.

I do think that you should do your best to trap expected errors appropriately. If you are doing a call that has a high likelihood of a DUP_VAL_ON_INDEX exception, then by all means trap the DUP_VAL_ON_INDEX. But, I don’t think it is beneficial or efficient to trap every possible conceivable exception simply to log that exception for troubleshooting purposes. If I have a statement that if it fails, for whatever reason, I want to log the error and bubble up the exception, I should just use WHEN OTHERS. I should not log and raise for each possible exception; there could be hundreds of reasons why an insert/update would fail, especially when you factor in triggers.

Now, that is not to say that developers should wrap every statement they have with a WHEN OTHERS exception handler so they know when and where an error occurs. But, I don’t think developers should necessarily be steered away from using it in the right scenarios. Using WHEN OTHERS to log issues around critical statements is useful. Using WHEN OTHERS to identify which statement failed out of many similar statements is useful. If you work on some of the old legacy systems from the mid-90s when lots of people thought the best place for business logic was triggers, you will want to be able to tell exactly when, where and why an exception occurred. You will want to be able to store the call stack information at the precise moment of an exception, rather than waiting for it to bubble up to the parent. You will want to have access to all the local variables as they exist for that statement. WHEN OTHERS can be very useful at a low level when used properly.
Tom Kyte
October 19, 2011 - 6:47 pm UTC

you do not need that rollback there

you do not WANT that rollback there.


I've for a long time have wanted to remove:

a) when others (abused constantly)
b) commit and rollback (used inappropriately almost ALL of the time)
c) autonomous transactions (see b)
d) triggers (see a and b)

I estimate bugs would drop even more than this:

http://thegamershub.net/2011/10/uae-announces-40-less-car-crashes-during-blackberry-outage/

Would it be a pain to the people that know how to use when others? Yes. Would it be a pain to people that know when to judiciously use commit/rollback and autonomous transactions? Yes. Would it be a pain for people that know when to use a trigger (hardly ever) and how to use them (understanding multi-user conditions)? Yes.

But - it would reduce bug counts by such a large margin, it would be worthwhile to me.


(also understand that I use hyperbole to scare people away from these constructs - so that they need a really really really good reason to use them...)


This statement you made:

I have no way of determining which
statement in my process failed.


is *entirely false*. It would be TRUE if you code your way. it is FALSE if you wait till the highest level.

If you catch your error, your "log routine" "knows" which line failed - but the invoker will not. The highest level will not.

On the other hand - if you wait till the highest level - then the highest level will know that

p1 called p2 on line 43, p2 called p3 on line 112, p3 called p4 on line 1432, and p4 failed on line 55


It (the highest level) will have

a) all of the inputs needed to reproduce the issue.
b) the entire error stack that caused the error.

That is what goes into the log_error routine AT THE HIGHEST level

The lowest level - not so much. It would have ITS inputs - but it won't know how to get the database into the state that caused the error. It is not enough to know that "my procedure p4 was called with inputs of 5,'hello world' and failed". You need to know what p1 did, what p2 did, what p3 did and so on.




But, I don’t think developers should necessarily be steered away from
using it in the right scenarios.


nor do I, that right scenario however is at the highest level possible.


If you want to get a record of where the error occurred and the inputs needed to reproduce it - you'll log at the highest level, not the lowest level. If you log at the lowest level, you'll need to log at *every* level and then have some way of putting all of the pieces back together (because every when others will shift the error line numbers!)

I'll have to disagree with your approach - catching exceptions that you do not expect at the low level is a wrong approach. You get everything you need at the high level, you do not reproduce the error logging code over and over and over everywhere, you get the entire error stack, you get the inputs needed to reproduce the issue.


the only reason to catch an unexpected exception at the low level is to clean up a resource like an open file handle, or a dbms_sql cursor (other cursors - implicit and explicit - clean themselves up if they are local variables). And then the exception block should cover ONLY the critical area of code (as little code as possible) to localize the issue.

grin, December 20, 2011 - 5:19 am UTC

Hello!
I was reading your article in the last Oracle magazine nov/dec 2011 and I totally agree with you about "When Others then raise" idea.
But what to do with the hight level user interface procedures that is called from PHP or Apex? Most users no need to know technical internal details about our application - so we log them and show to user only "Sorry technical error"!?..
Or you let the php process exception?..
Tom Kyte
December 20, 2011 - 8:10 am UTC

You can certainly do this:


begin
  some_procedure;
exception
  when others
  then
       raise_application_error( -20001, get_some_error_message( sqlcode, sqlerrm ) );
end;


where your procedure 'get some error message' is code you write to translate the oracle error code/error message into something meaningful for the end user. You do this at the highest level of the code - the client would submit a block of code like this.

It could also log the error using an autonomous transaction as well.


In that manner - you will get the error in the client, the error will be your custom error message, and the real error and error stack can be logged in a table somewhere so somewhere is made aware of it.

grin, December 21, 2011 - 3:40 am UTC

Do you think it is wrong that instead of "raise_application_error" we will issue:

procedure CallFrom_PHP(out_code OUT number, out_err_msg OUT varchar2) IS
eMyException exception ;
begin
some_DBprocedure;
COMMIT;
out_code:=0;
out_err_msg:='OK';
exception
when eMyException then
out_code:= -1;
out_err_msg := 'Wrong business logic' ;
when others
then
out_code:= -1;
out_err_msg := get_some_error_message( sqlerrm, dbms_utility.format_error_backtrace ) ;
ROLLBACK;
end;

PHP interface will check out_code for success or failure (not deal with exception -20001) and display Error message that can be wrong business logic or even technical issue!?..
Tom Kyte
December 21, 2011 - 8:44 am UTC

totally, 100%, no doubt about it - that is wrong (to me)


You are defeating the entire purpose of exceptions and statement level atomicity.

I truly wish plsql could NOT commit or rollback - the client is the only one that truly knows when the transaction is done. What if I want to call your procedure AND THEN another procedure - and then commit? I cannot - because you commit and/or rollback. You should not, the client should.

Return codes are so 1980's. They are far too easy to miss. And exception flying out of the database is somewhat more "in your face".


To me that code should be:


begin
some_db_procedure;
exception
when eMyexception then raise_application_error( -20001, 'wrong logic' );
when others then raise_application_error( -20002, get_some_error_msg() );
end;


we will automagically roll back JUST this call (statement level atomicity) when it fails - leaving the database in a known consistent state - and the client application will know "failure happened".

And you would have a bit of client code that would manage your error codes - so your code can decide which are fatal, which are informational and so on.


see "Why You Really Want to Let Exceptions Propagate" in this article:

http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61asktom-512015.html

A reader, December 28, 2011 - 3:33 am UTC

Hello!
I agree with you 100% about atomicity etc. inside Oracle and Exception handling - this is the only way to go. But when it comes to interface procedure with php - I don't want that php dude is the one who decide whether to put Commit or Rollback and drive business logic. Normaly all the business logic is put inside "the last, highest level" procedure and php is informed "post factum" - whether transaction is complete or failed!?.. Normally We have no database code inside php.
Php coders are avare about interface design, colours, beautiful pictures, Ajax etc.!?..
Tom Kyte
December 29, 2011 - 10:51 am UTC

Normaly all the business logic is put inside
"the last, highest level" procedure and php is informed "post factum"


that would be perfectly OK and I would approve.

It is the developer who puts commit and/or rollback in every procedure, usually coupled with a when others, that is the problem.

a

a reviewer, December 29, 2011 - 4:40 am UTC

Well, why not
begin
 some_db_procedure;
 commit;
exception
 when others then
  rollback;
  raise; 
end;

?

Tom Kyte
December 29, 2011 - 11:20 am UTC

give some context here. If you mean what the person above you clearly wrote and said using words to describe their intent. Sure, that is ok.


If you mean just willy nilly anywhere and everywhere, then no - it is not ok.


Since we cannot tell your intent, we'll have to say "no"

So is this as a 'bug"?

Al Ricafort, February 03, 2012 - 1:51 am UTC

How about below use of 'when others' in an anonymous pl/sql? Do you still consider it as a "bug"?

-----
BEGIN
for c1 in my_cursor
begin
savepoint my_save_point
---- do something
exception
when others then
utl_file.put_line(f_error_rpt,SQLCODE||'-'|| SQLERRM);
rollback to my_save_point
end;
end loop;
commit;
END;
----

You suggested to catch exception that you are expecting. But the reality is that the 'do something' maybe a package calling another package calling another. And each of those packages may define their own exception. So you have to dig into each one of them to find out what are the exceptions they are raising. Sometimes that is not practical.

And in the above example I don't want my processing to stop just because a record fails. I just need to log it and continue with the next.




Tom Kyte
February 03, 2012 - 10:07 am UTC

yes, I consider that a really bad practice.

I consider using commit and rollback in PLSQL in general (APEX will be an exception to the 'rule').



... Sometimes that is not practical. ..

it is totally practical, you are expecting SOME exceptions. You catch those and deal with them. For the rest - you may catch them with an OTHERS to clean up resources - but be sure to follow the others with a RAISE or RAISE_APPLICATION_ERROR.


The above code should be:
begin
   ...
   utl_file.fopen
   begin
       --- do something
   exception 
   when others
        then utl_file.fclose();
             RAISE;
   end;
   ....
   utl_file.fclose();
end;



for example, no commit, no rollback, no need to do any transaction management actually. If you let this error propagate back to the client - the work performed by the plsql code will have been undone by itself already.

In this fashion, if you do not use commit/rollback - the client is free to combine together a series of procedures to construct a new transaction of its liking. If YOU commit - they cannot do that. If YOU rollback - you might botch up something the client wanted to do.


And in the above example I don't want my processing to stop just because a
record fails. I just need to log it and continue with the next.


Ok, for that, the code would look something like what you have - but the commit at the bottom should not be there. That would be a valid operation. However, I would look for a way to turn that loop into a single sql statement that used dml error logging - no code (many times you can erase the code and just get a single statement out of it all)

PL/SQL can be the ultimate client

Al Ricafort, February 05, 2012 - 8:13 pm UTC

Dear Tom,

I totally agree with you that named packages should not have any commit or rollback. But for anonymous PL/SQL aren't they the ultimate client or main caller(any other way to run them other than thru sqlplus)? Then like any ultimate client it will decide when to commit or rollback.

but be sure to follow the others with a RAISE or RAISE_APPLICATION_ERROR

If you still raise an application error then it is the shell (for example if you are in a UNIX environment) that will receive the error (i.e. sqplus will return non-zero). You are then treating the UNIX shell as your ultimate client.
Tom Kyte
February 06, 2012 - 1:28 am UTC

the only times plsql is the ultimate client is

a) APEX
b) when used in dbms_job/dbms_scheduler

otherwise, there is some external - outside of the database - bit of code. And the exception should propagate to them and they should decide whether to invoke commit or rollback as appropriate.

SQLPlus was the ultimate client in this regard - not the shell. SQLPlus connected to the database, not the shell. And sqlplus is not a very 'programmable' client - it is very limited. I would rather not use sqlplus for long term production situations - unless it was a very simple read only report. Otherwise, I'd want a real language as the client so I could do things on the client to deal with error conditions (logging, notification, recover from situations, etc)

Wierd behaviour..

Ankit, June 21, 2012 - 8:05 am UTC

Hi Tom
Thanks a lot for sharing your wisdom !

I encountered a weird thing mentioned below.

My db version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

CREATE TABLE "EMP" (
"EMPNO" NUMBER(4,0),
"SAL" NUMBER(7,2), );

I execute 3 blocks.

First is:
===========

declare
a emp.empno%type;
b constant emp.sal%type := 1000;
c b%type;
begin
a := null;
end;
---------------------------------------------

Error starting at line 1 in command:
declare
a emp.empno%type;
b constant emp.sal%type := 1000;
c b%type;
begin
a := null;
end;
Error report:
ORA-06550: line 4, column 3:
PLS-00206: %TYPE must be applied to a variable, column, field or attribute, not to "B"
ORA-06550: line 4, column 3:
PL/SQL: Item ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

Second block (i removed constant KEYWORD)
==========================================
declare
a emp.empno%type;
b emp.sal%type := 1000;
c b%type;
begin
a := null;
end;
anonymous block completed successfully

Third block (i keep CONSTANT keyword, just comment declaration of c)
======================================================================
declare
a emp.empno%type;
b constant emp.sal%type := 1000;
--c b%type;
begin
a := null;
end;
anonymous block completed successfully

Isn't third block in contradiction with error encountered in first block, as I kept CONSTANT keyword in third, but it didn't fail.

Could you please let me understand this (is it a bug ?)

Thanks a lot.

Tom Kyte
June 22, 2012 - 7:03 am UTC

the problem is with the definition of C, not B.

B is fine, C is not when you try to define it on a constant.

I don't see any contradiction - when you comment out the offending declaration of C, it works fine. C is the problem, not B

RAISE EXCEPTION

Ankit, June 23, 2012 - 6:07 am UTC

Hi Tom
Thanks for the reply.

My DB version is:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production

I have following block and a nested block, to my surprise, exception raised in INNER block was not handled in outer block's Exception section, but in outer block's OTHERs section :

declare
a exception;
begin

declare
a exception;
begin
raise a;
end;

exception
when a then
dbms_output.put_line('a raised caught in outer');
when others then
dbms_output.put_line('a raised caught in OTHERS');
end;

Output is ---> a raised caught in OTHERS


But when I executed following block, output was different, it was caught in 'Expected section', not in OTHERs this time :

declare
a exception;
begin
declare
a exception;
begin
raise no_data_found;
end;
exception
when no_data_found then
dbms_output.put_line('exception raised caught in outer');
when others then
dbms_output.put_line('a raised caught in OTHERS');
end;

output is --> exception raised caught in outer

Can you please tell me if I am missing something here ?
Thanks.
Tom Kyte
June 23, 2012 - 1:57 pm UTC

the stuff in the declare block is NOT in the inner block - that inner block doesn't begin UNTIL the "begin" for it happens. the declare block is setting up for the ability of the BEGIN/END block to function - before it is done, it cannot function

any errors in a DECLARE block happen before the subsequent begin/end and 'belong' to the calling block of code.


Variable's Value Not Inherited !!

Ankit, June 25, 2012 - 7:06 am UTC

Hi Tom
Thanks for the reply.

BANNER
=======
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production

As per oracle documentation : ( http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/fundamentals.htm#BEIIGBBF ) 10g Release 2 (10.2)
"The %TYPE attribute provides the datatype of a variable or database column. As shown in Example 2-6, variables declared with %TYPE inherit the datatype of a variable,
**plus default values** and constraints."

But when I executed following block, 'b' didn't inherit value of 'a' :

declare
a varchar(2) :='8';
b a%type ;
begin
dbms_output.put_line ('a='||a);
dbms_output.put_line ('b='||b);
end;

Output
a=8
b=

Can you please help me know why default value of a was not inherited by b ??
Thanks
Tom Kyte
June 25, 2012 - 10:02 am UTC



I believe that to be a documentation bug... do you have access to support?


Good!

A reader, June 25, 2012 - 12:49 pm UTC


Re:VAlue Not Inherited !!

Ankit, June 27, 2012 - 2:30 am UTC

No Tom, I don't have access to support.
Tom Kyte
June 27, 2012 - 9:34 am UTC

it does not appear in the current documentation, must have been found and fixed already.

worst practice or buggy practice

A reader, August 28, 2013 - 8:54 am UTC

Quick funny comment, if I am allowed.
Some folks in a pharmaceutical giant environment in Europe, is trying to convince me that the following code will rollback the changes of the XYZ execution in case an unhandled exception is raised.
Am I missing something from the basics ?
Of course a simple test proves the contrary...
(Please don't hate me for the comment, I'm just frustrated )


FUNCTION import_stuff RETURN VARCHAR2 IS
BEGIN

<some stuff>

BEGIN

<exec procedure XYZ> ;

EXCEPTION

WHEN OTHERS THEN
RETURN 'XYZ :'||SQLERRM;<-------

ROLLBACK ;--????

RAISE; --????
END;


commit;

RETURN 'OK';
EXCEPTION
WHEN OTHERS THEN
RETURN SQLERRM;

ROLLBACK ;
RAISE;
END import_stuff;
Tom Kyte
August 28, 2013 - 7:31 pm UTC

that will not rollback xyz.

the return blows out of this piece of code - before the rollback happens.

their when others blindly turns an error into a NON-ERROR and returns out of the block before they rollback. This is a worst practice

this code should just be:

execute xyz;

period.

proof:

ops$tkyte%ORA11GR2> create table t ( x int );

Table created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure xyz
  2  as
  3  begin
  4          insert into t (x) values ( 1 );
  5          insert into t (x) values ( 1/0 );
  6  end;
  7  /

Procedure created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace function import_stuff return varchar2
  2  as
  3  begin
  4          begin
  5                  xyz;
  6          exception when others -- yuck, stinky code, smelly code
  7          then
  8                  return 'xyz :' || sqlerrm;
  9                  rollback;
 10                  raise;
 11          end;
 12          return 'ok';
 13  exception when others -- bad smell again
 14  then
 15          return sqlerrm;
 16          rollback;
 17          raise;
 18  end import_stuff;
 19  /

Function created.

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_output.put_line( import_stuff );
xyz :ORA-01476: divisor is equal to zero

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR2> select * from t;

         X
----------
         1

ops$tkyte%ORA11GR2>



tell them to recompile their code with warnings and

a) we'll tell them when others not followed by raise is 'bad'
b) they have unreachable code:

ops$tkyte%ORA11GR2> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

Session altered.

ops$tkyte%ORA11GR2> alter function import_stuff compile;

SP2-0807: Function altered with compilation warnings

ops$tkyte%ORA11GR2> show errors function import_stuff
Errors for FUNCTION IMPORT_STUFF:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1/1      PLW-05018: unit IMPORT_STUFF omitted optional AUTHID clause;
         default value DEFINER used

6/17     PLW-06009: procedure "IMPORT_STUFF" OTHERS handler does not end
         in RAISE or RAISE_APPLICATION_ERROR

9/3      PLW-06002: Unreachable code
13/16    PLW-06009: procedure "IMPORT_STUFF" OTHERS handler does not end
         in RAISE or RAISE_APPLICATION_ERROR

16/2     PLW-06002: Unreachable code
ops$tkyte%ORA11GR2>



if they would JUST WRITE LESS CODE - the rollback will take care of itself, the client will get a better error message, life will be good:

ops$tkyte%ORA11GR2> truncate table t;

Table truncated.

ops$tkyte%ORA11GR2> create or replace function import_stuff return varchar2
  2  as
  3  begin
  4          xyz;
  5  end import_stuff;
  6  /

SP2-0806: Function created with compilation warnings

ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_output.put_line( import_stuff );
BEGIN dbms_output.put_line( import_stuff ); END;

*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.XYZ", line 5
ORA-06512: at "OPS$TKYTE.IMPORT_STUFF", line 4
ORA-06512: at line 1


ops$tkyte%ORA11GR2> select * from t;

no rows selected

an exception handling framework

A reader, September 19, 2013 - 9:08 am UTC

can you tell me oracle exception handling framework and what is the best framework for error handling in DBMSs?

thanks

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