div.b-mobile {display:none;}

Thursday, September 14, 2006

Classic example of why I despise...

WHEN OTHERS.  When not followed by RAISE; to re-raise the error.

What is really bothersome:

I am having this problem since a months (sic)

Ok, they've been stuck in their upgrade (probably cursing 10g in the meanwhile), all because of this stupid "when others" clause.  I despise it, I want it gone.  I know - there are valid uses of it, for example:

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

but you know what, I just don't see it used correctly in most cases, yes MOST cases.  People feel compelled for whatever reason to hide errors from client applications - and always include that fateful when others without the raise;.  I cannot count the number of bugs in existing systems that I've found - let alone the ones that are out there just waiting to spring out and bite someone.


I am just thankful they wrote a function, and forgot to return anything in the when others.  Small things like making two fatal errors sometimes cancel each other out and make for something that works!!!


If I could remove three things from the database - they would be:



  1. Triggers
  2. Autonomous Transactions
  3. When Others (at least if not followed by RAISE or make when others ALWAYS re-raise as part of the language itself!!)

Triggers are so abused - and used so inappropriately, I'd rather live without them.


Autonomous transactions (coupled with triggers of course, to avoid mutant tables or something) are another "bug in the disguise of production code".  Almost universally misapplied.


When Others - 'nuff said.


I don't even know what to say about doing DDL in the function like they do - or the lack of bind variables.  My afternoon is wrecked after seeing this piece of "code".  Utterly destroyed.

POST A COMMENT

47 Comments:

Blogger shrek said....

now Tom, don't hold back let us know how you really feel.;-)

another problem is that since the error doesn't get raised, it doesn't show up in the cron email the DBA goes to check each and every morning. [yes i do have to, and log into eache server to do it. let's not go there, OK?] so when it stops working the DBA has no idea until he gets yelled at for not elling people.

Thu Sep 14, 02:54:00 PM EDT  

Blogger Herod T said....

There there Tom... There are hundreds of people extending their sympathy to you.

Take the afternoon off and go home to the family. That is horrible code. Take tomorrow off too. :)

Shrek, just have to go there.. sorry:

lookup .forward in your flavour of unix.

All mail to the unix user gets forwarded to an email address, or addresses of your choice.

Thu Sep 14, 02:58:00 PM EDT  

Anonymous Sokrates said....

absolutely agree with you

by the way,I nver understood the following (java, but it doesn't matter):

try { Thread.sleep(1000); } catch(InterruptedException e){}

who can this explain to me ?

Thu Sep 14, 03:04:00 PM EDT  

Anonymous RobH said....

Triggers are so abused - and used so inappropriately, I'd rather live without them.

Thats a bold statement Tom. I see a lot of really bad sql too, but I'm not sure I could live without that (SQL that even brings the database to its knees)!

Thu Sep 14, 03:06:00 PM EDT  

Blogger djb said....

I saw this on AskTom and thought to myself, "Wow, that should be on the DailyWTF."

What I don't get is when programmers start writing this really wierd stuff, and they don't ask themselves, "Isn't there any better way to do this?" You know - it takes not a lot of effort to find out how to do it the right way. Just reading 2 or 3 entries in AskTom would have fixed most of the problems!

Thu Sep 14, 03:10:00 PM EDT  

Anonymous Frank said....

When I read this on AskTom I saw you gradually getting madder and madder.
Laughed out loud when I continued to see your blog for today. Guess I kind of knew it could not be about anything else :-)

Thu Sep 14, 03:19:00 PM EDT  

Anonymous Anonymous said....

Another example for the 'worst practices' track in Denmark.
(but you probably already have enough)

Toon

Thu Sep 14, 04:06:00 PM EDT  

Anonymous MojoMark said....

I saw that article in my RSS reader and it got my attention. I like to read the questions and predict what you will say in your response. The issue was obvious (to me) but it definitely was a WTF moment.

I have to say you were exceptionally calm on this one - more than I expected considering the code violated so many "Tom'isms"

Thu Sep 14, 04:36:00 PM EDT  

Blogger shrek said....

Shrek, just have to go there.. sorry:

lookup .forward in your flavour of unix.


oh i know all about .forward, but that only works if the server will send mail outside itself. see i told you not to go there.;-)

Thu Sep 14, 04:57:00 PM EDT  

Anonymous Fred said....

Tom, how's your chest?! I once worked with a contractor who would randomly declare "This code gives me chest pains, it's so bad!", and your post reminded me of him!

(I'm just glad the contractor didn't work on the system I had to touch; a VB developer single-handedly wrote the Oracle side, and ... well, let's just say, there is a function called "bind_variables"... And yes, there is code generated from the pl/sql that is unbound. *shakes head*. I'm a relative newbie, but even I know that you have to work really, really hard (ie. dynamic sql) to get pl/sql not to bind variables automatically!). Madness. He would have had a heart attack...!)

Thank goodness you're here to keep newbies like myself on the straight and narrow, thus avoiding the common errors!

Thu Sep 14, 05:22:00 PM EDT  

Blogger SeanMacGC said....

Total empathy Tom (fellow Prius driver, and good machine it is, after 52K for me). Strikes me that some people (aka 'programmers', after a severely skewed fashion) are concerned only with producing volumes of code, not functionality per se, in that transparent and worthwhile functionality is something to be avoided at all costs.

Thu Sep 14, 05:58:00 PM EDT  

Blogger Gary Myers said....

There's only one thing in the database I would abolish, and that is implicit datatype conversion. It accomplishes NOTHING, EVER, and just allows sloppy thinking.
Autonomous transactions and WHEN OTHERS should be part of an Oracle option supplied free but only to organisations that can demonstrate they support good development practices (code reviews etc). :) Seriously, the 10GR2 PL/SQL compiler has warnings if you can exit a function without a return. Maybe a suitably respected Oracle professional can get them to include a warning about a WHEN OTHERS without a RAISE/RAISE_APPLICATION_ERROR.

The other feature more abused than not is DISTINCT, which normally means "I don't understand how to use a subquery to get from a child table to a parent, so I'll stick all the necessary tables in one FROM and DISTINCT the result"

Thu Sep 14, 06:59:00 PM EDT  

Blogger Thomas Kyte said....

that is implicit datatype conversion.

Ok, you just made my list grow :)

implicit string to date conversions - another big old bug just lurking out there...

at least string to number implicit conversions typically just impact performance (remove ability to use index on a string column compared to a number) - implicit date conversions many times corrupt (change) data or result in incorrect answers.

Thu Sep 14, 07:03:00 PM EDT  

Anonymous RobH said....

besides just implicit conversions, while we're on the topic of dates could we please stop with things like 2 digit year format as well as numeric month?

I recently ran into an issue with some code on Aug 7, 2006.
07-AUG-2006 <-- Easy conversion
08.07.06 <--- um....

Thu Sep 14, 08:49:00 PM EDT  

Anonymous Andrew said....

Well, I would add SELECT * in all its forms -- but I already wrote you an example of that bug.

I am sure there will be a lot of 'me too'folks out there for my situation. And this has happened in more than one shop. I work there as a DBA and part of my job is to advise and consult. Now when I tell them about problems like the one you describe here, they just say some weesel words and keep on doing it. Then they eventually bring in a high $$ consultant to solve their problems and he tells them the same thing and they all march off to fix thier code.

Something like the prophet in his own land...

Arrrrrrrrrrrrrrrrrrrrrg.

Thu Sep 14, 08:58:00 PM EDT  

Blogger Phil said....

Isn't just problematic in PL/SQL:

try {
[some Java code here]
}
catch (Exception e) {
null;
}

I've seen it (and related variations) before, makes me jump up and down and yell really loud at my computer, then pick up the phone and yell really loud at the sod who coded it.

Even when 'null' is actually some error handling code, it is never handled correctly. My favourite was when a handler tried to correct the method inputs (WTF) and called the same method again (even bigger WTF)...ended up in an infinite loop.

Fri Sep 15, 04:01:00 AM EDT  

Blogger Stephen Booth said....

I recently ran into an issue with some code on Aug 7, 2006.
07-AUG-2006 <-- Easy conversion
08.07.06 <--- um....


Unfortunately the only real solution to that would be for the UN or similar body to issue a dictate, to be obeyed on pain of destruction by nuclear attack, that all dates be rendered in a specific logical order.

The obvious candidates are DD-MM-YYYY (highest to lowest precision) or YYYY-MM-DD (Lowest to highest precision. Choice of separator (or indeed whether to have a separator or not) lies with the individual. Personally I prefer YYYYMMDD as it makes sorting easy when you've got a directory full of files where the filename is datestamped in that format.

The only drawback I can see to this plan is that the only major economic power in the world that doesn't use either of those two formats already is also the one with the most nukes.

Stephen

Fri Sep 15, 07:31:00 AM EDT  

Blogger SeanMacGC said....

WHEN OTHERS THEN
NULL;
(or something equally futile like dbms_output...)

is the logical equivalent of:

IF THIS CODE HAS A PROBLEM THEN
DO YOU THINK I OR ANYONE ELSE CARES IF IT WORKS OR NOT?

Fri Sep 15, 08:46:00 AM EDT  

Blogger SeanMacGC said....

This comment has been removed by a blog administrator.

Fri Sep 15, 08:49:00 AM EDT  

Anonymous Andrew said....

I swear on a stack of coding manuals this is true. I saw it, I swear.
BEGIN
[cursor loop to do updates]
EXCEPTION
WHEN OTHERS THEN
insert into err_table values (SQLCODE, SQLERRM);
commit;
END;
COMMIT;

How many things wrong with this error handling can you find???

Fri Sep 15, 09:07:00 AM EDT  

Anonymous John Flack said....

You are exactly right about WHEN OTHERS without error handling or re-raising the error.
But get rid of triggers?
How else am I going to:
Automatically populate a surrogate PK from a sequence, or audit columns from the current user and date.
Automatically change a state code to upper case no matter how the user entered it.
Validate on conditions too complex for a check constraint.
Keep a log of changes to a key table.
Write an updatable view with code to govern how to update the underlying tables.

Get rid of autonomous transactions?
How else am I going to:
Log errors that require me to roll back the current transaction.
Keep a progress log that users can see change while a background job runs - got to commit this occassionally without committing the main work.

Fri Sep 15, 10:29:00 AM EDT  

Blogger Thomas Kyte said....

How else am I going to:
Automatically populate a surrogate PK from a sequence


I hate triggers that supply values in the background like that. They are akin to "magic". They add no value whatsoever and only negatively impact performance and make the entire application harder to maintain. Therefore, I vote this 'feature' of changing values behind the scenes as a 'non-feature', a bad idea.

or audit columns from the current user and date

Use transactional API's, outlaw insert/update/delete/merge in client applications.

Automatically change a state code to upper case no matter how the user entered it.

see first two points - there should quite simply be a check constraint:

check( state = upper(state))

on that column and well formed transactional API's that know how to interact with the data would do the right thing.

Triggers that modify values I SUPPLIED - hate em, bugs waiting to happen, maintanance nightmares.

Validate on conditions too complex for a check constraint.

give me a for example please (although this is perhaps the ONLY valid use of a trigger...)

Keep a log of changes to a key table.

workspace managment... (I know it uses triggers, but if we didn't have triggers - it would be internalized just like advanced replication is!)

Write an updatable view with code to govern how to update the underlying tables.

that is different from a trigger on a table in many senses. However it is also "a trick of the nth degree" - something I tend to avoid for understandability, maintenance, correctness reasons personally.


can triggers be used correctly?

Absolutely

The problem is - the are not in so many cases that I'd be willing to give up any perceived benefit just to get rid of the abuses (and bugs) caused by them.

Are they going away? No, not a chance, it is just a dream I have....


Log errors that require me to roll back the current transaction.

that is the only valid use - and perhaps we should provide just such a function and nothing else. EG: you have an extensible API that lets you log an error to a table of your choice, but that is it - period.

Keep a progress log that users can see change while a background job runs - got to commit this occassionally without committing the main work.

dbms_application_info.set_session_longops

there is no way you would use autonomous transactions for that!!!! never!!!! (see they are evil, you solved a problem using a wrong technique just because they were there :)

Fri Sep 15, 10:42:00 AM EDT  

Anonymous Michael Norbert said....

I think posters are now baiting you. They know what gets you going. Boring day at work, lets send Tom some whacked out code, watch him blow his top and laugh ourselves silly.
Darn, I've outed myself.

Fri Sep 15, 02:41:00 PM EDT  

Blogger Justin said....

While I'm a fan of using

dbms_application_info.set_session_longops

in long-running jobs, I also see some benefit to periodically logging progress using autonomous transactions. That seems to allow you to log a lot more information that can be used to see how code is progressing than DBMS_APPLICATION_INFO can maintain. To me, this is invaluable during development and pretty cheap instrumentation in production. It can also make it a lot easier to predict when things will finish if you can look at historical patterns rather than relying on linear extrapolation. What's the downside of the occasional autonomous transaction to log progress?

Fri Sep 15, 04:28:00 PM EDT  

Blogger Alberto Dell'Era said....

try { Thread.sleep(1000); } catch(InterruptedException e){}

Thread.sleep() may terminate prematurely, if interrupted by another thread, so it may wait for less than requested.

By swallowing the exception, you're saying "I don't mind if I wait for less than 1000 msec, I expect it, it's not an exception in the program flow"; if at the opposite you're not expecting the exception, you should let the exception propagate to the caller (to halt execution more than likely) to let it know that something funny happened, and let the bug be discovered. The same thing you'd do in PL/SQL actually (probably in Java you'll want to throw a RuntimeException instead in the latter case, but that's a minor detail, actually a design decision).

Fri Sep 15, 06:49:00 PM EDT  

Anonymous Robbert said....

The one exception I have on this is when using the wwsec_api to determine whether someone exists as a portal user.

The damn thing allways returns a user defined error if de user doesn't exist. (or if anything else is bothering it for some reason.)

Thus I will usually build somthing like

DECLARE
...
l_user_id number;
tab_user_id wwsec_api.idarray;
counter integer := 1;
BEGIN
...
-- Test user existance in portal
FOR r in (select username from tab_unverified_users)
LOOP
BEGIN
l_user_id := wwsec_ap.ID (r.username);
tab_user_id(counter) := l_user_id;
EXCEPTION
WHEN OTHERS THEN
write2log('user: "'||r.username||'" does not exist in portal.');
END;
END LOOP;
-- Do stuff with existing users.
...
END;

I specifically use when others beacuse I have the hope that one day Oracle will wise up and use an error message that will actually mean something.

Sat Sep 16, 06:56:00 PM EDT  

Blogger Thomas Kyte said....

Robert

Umm, why wouldn't you catch the named exception??? That is what they are there for???


Now, I'm not familar with the portal API - but the exceptions it throws should be documented - and much like utl_file would be caught BY NAME.

This is not a reason to use "when others" - not at all!!!

Sun Sep 17, 03:29:00 AM EDT  

Anonymous Robbert said....

@Thomas,

I agree that the errors it throws should be documented.

Unfortunately the problem is that the wwsec_api like pretty much anything else that plugs into portal, allways returns an ora-20000 user defined exception regardless what's going wrong.

This behaviour is not in line with normal Oracle exception handling, which leads me to the conclusion that this will be corrected in future versions.

To prevent future upgrades from breaking my current code, I just use "when others" to catch a code which isn't properly defined anyway.

Personaly I see this as an ORA-20000 variant of NULL.
"ora-20000" != "ora-20000"

Sun Sep 17, 05:58:00 AM EDT  

Blogger Noons said....

[When Others (at least if not followed by RAISE or make when others ALWAYS re-raise as part of the language itself!!)]

now now, Tom: how else do you expect us to extract the non-corrupt blobs off a 9ir2 table in an ASSM tablespace when the darn thing decides to intersperse random data in the middle of good one? ;-)

Spent quite a few hours doing just that recently. All because of yet another unpublishwed 9i bug! And yes, I used:

when others then
l_count_err := l_count_err + 1;
end;

If I didn't have that escape valve, how else would you expect me to salvage whatever I could off the blessed thing? Like, guess the error codes I'd get for all corrupt data?

So: yes Oracle, get rid of it. But leave an event somewhere that lets us have it back. Or else fix the blob bugs once and for all.

Sun Sep 17, 10:46:00 AM EDT  

Anonymous Peter Nybo Rasmussen said....

Regarding triggers and "Validate on conditions too complex for a check constraint" examples.
Any multi-table check constraint (or ASSERTIONS as defined by ANSI SQL) would apply.
Wonder why Oracle and others (except Rdb/VMS) never implemented this. Since the SQL engines can already evaluate multi-table conditions (e.g. in where-clauses), it should not be too difficult to implement for check constraints ?

Mon Sep 18, 06:12:00 AM EDT  

Blogger Thomas Kyte said....

Regarding triggers and "Validate on conditions too complex for a check constraint" examples.

send me an example you think actually works. Unless it (the trigger) includes LOCK TABLE commands - it is almost certainly WRONG....

Give me an example, I'll tell you where the logic (using triggers) falls apart.

This is a good reason why I don't like triggers!!!! People try to use them for things that CROSS ROWS or CROSS TABLES - and fail to use lock table.

Mon Sep 18, 06:51:00 AM EDT  

Anonymous Keith Jamieson said....

While I agree with most of the comments above, I would rather use the two digit number to represent a month, rather than the 3 character date representation.

If you translate the Month of March into German, in Oracle it is represented as Mär but on NT it is represented as Mäz. Some friends of mine, who are fluent in German, tell me that Mäz is correct.

The issue I encountered was that the to_date conversion was failing, as the VB front-end application was using the format from NT and passing this to Oracle, which was promptly failing with the 'Not a valid Month ' Error.

Of course, looking at the code, the date conversion was correct.
We were very fortunate that the Test Department encountered this error in March, before the system had gone into production.

The solution employed, was to replace all 3 character months with two digit months.

Uness someone can show me how I can get Oracle's dateformat to generate Mäz rather than Mär, I'll stick to keeping my dates entirely in numeric format.

Mon Sep 18, 08:36:00 AM EDT  

Blogger Thomas Kyte said....

Keith-

not sure what you mean - all dates in Oracle are stored in a 7 byte binary format. We do not store anything in a 3 character field.

the MON format is used on output to convert to a string and on input to convert a string into a month.

At no time would we every store a 3 character month in a date field, we store the

century
year
month
date
hour
minute
second

in 7 bytes, in binary (numbers)


The DISPLAY of these three character fields is controls by your NLS_LANG settings entirely.

[tkyte@dellpe ~]$ plus

SQL*Plus: Release 10.2.0.2.0 - Production on Lun. Sept. 18 11:56:00 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> select to_char(add_months(sysdate,-6),'mon') from dual;

TO_CH
-----
mars

ops$tkyte%ORA10GR2> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
[tkyte@dellpe ~]$ export NLS_LANG=German
[tkyte@dellpe ~]$ plus

SQL*Plus: Release 10.2.0.2.0 - Production on Mo Sep 18 11:56:08 2006

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> select to_char(add_months(sysdate,-6),'mon') from dual;

TO_
---
mrz


I can only suppose that you had the NLS_LANG set incorrectly on the client side. It is purely a display/formatting thing and in no way affects the data "stored" in the database.

Mon Sep 18, 12:06:00 PM EDT  

Anonymous John Flack said....

I would LOVE to use a transactional API for many of the jobs that I now use triggers to do. This is how Web PL/SQL applications generated by Oracle Designer do it, and Forms can use this too. However, while applications written in Java and other languages like PHP or Ruby CAN call an API instead of doing DML, most of them have no support or tools for doing it. If you want to call an API, you write it yourself, no drag and drop and you are there. This INCLUDES Oracle's own JDeveloper and ADF. So I MUST write it with triggers so that the table is updated correctly NO MATTER HOW it is updated. There is no guarantee otherwise that the next developer won't bypass the API and just write DML.

Actually, this is why I write views with instead of triggers too. I have to simplify complex relationships sometimes or some developers will inevitably do it wrong, or require me to explain it to them a few times a week. But they all understand how to get their modules to insert a single row into a single table.

Mon Sep 18, 02:19:00 PM EDT  

Anonymous Anonymous said....

Tom, I think you are being too harsh on triggers. I agree with John Flack about all the reasons he cited for using triggers.

Don't you yourself say, applications come and go, but data stays?! Protecting data integrity is paramount, no matter what latest and greatest app/technology comes along. Triggers let us do that, place business rules/validations right next to the data.

Besides, how would you get the :old/:new functionality that a row-level trigger offers you?

Mon Sep 18, 09:30:00 PM EDT  

Blogger Gary Myers said....

Validation conditions too complex for a check constraint.

Comparing a column date against sysdate for those nice "You cannot reserve a room for a date in the past" type messages ?

Mon Sep 18, 10:24:00 PM EDT  

Blogger Thomas Kyte said....

Protecting data integrity is paramount,

indeed it is, that is why triggers are evil :)

Most of the time, "business logic"/"data logic" that is implemented in triggers is done incorrectly (that is what I mean by people abuse triggers).

They hit mutating tables (which indicates your logic is fundementally flawed to begin with) and "work around it" with autonomous transactions. They have multi-user conditions they don't even think about in the trigger logic.

It just makes it too easy for the neophyte to shoot themselves in the head (not the foot, that would just hurt).

I'm not saying it would be painful to live without triggers - just that I'd rather go without then have them for the "greater good".

Comparing a column date against sysdate

transactional API. Don't let applications insert/update/delete :)

I'll keep saying that....

Tue Sep 19, 01:34:00 AM EDT  

Anonymous Peter Nybo Rasmussen said....

Followup on triggers and "Validate on conditions too complex for a check constraint" ...

I was not trying to advocate the use of triggers, on the contrary, I was trying to promote the idea that Oracle and others should provide alternative declarative methods for multi-table check constraints (e.g. ANSI SQL "Assertions").

This subject and a concrete example was also discussed in

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:21389386132607

... where Tony Andrews suggested a clever materialized view "trick", but the idea was never completely pursued/benchmarked.

Another example: We have a system using "logical deletes". Rows are never deleted physically, but a "deleted" flag (Y/N) is set. For a pair of foreign/primary key related rows all combinations are allowed, except (FK.deleted='N' and PK.deleted='Y') which is NOT allowed.
This logic is currently implemented via triggers (will not show you the code ... too ugly, not correct).
I would like to be able to declare something like:

check (not exists (select null
from FK join PK on FK.fkey=PK.pkey
where FK.deleted='N' and PK.deleted='Y'))

Tom, do you agree we actually need declarative multi-table check constraints (to get rid of the triggers)

?

Tue Sep 19, 04:48:00 AM EDT  

Anonymous Keith Jamieson said....

Thanks Tom,
Just what I was looking for.

This happened some years ago, so I can't remember the exact details.

If there is an issue it would be that the Oracle to_date function would be expecting MrZ but NT would be passing in Maz, so thats a mismatch. All the other months in german were translated correctly, so I don't think this was a client-side issue.


Connected to:
Oracle Database 10g Release 10.2.0.1.0 - Production

SQL> select to_date('01-MAZ','DD-MON') from dual;
select to_date('01-MAZ','DD-MON') from dual
*
ERROR at line 1:
ORA-01843: not a valid month


SQL> select to_date('01-MRZ','DD-MON') from dual;

TO_DATE(
--------
01.03.06

SQL>

The issue here was probably that the date was obtained from NT (via Visual Basic/Java) rather than using all dates from Oracle.

Thu Sep 21, 09:50:00 AM EDT  

Anonymous Chris said....

In a past life as a VB developer I used to come accross a lot of code that used a similar funtion to the When others, in vb its called resume next a classic error handler would be.

error_handle:
on error resume next
'Comment: must write error handler before final release.

or the other great idea a default error handler to display failed SQL Inserts/Updates to the user with an option to continue to the next screen anyway.

Fri Sep 22, 08:58:00 AM EDT  

Anonymous Peter Nybo Rasmussen said....

Tom, thanks for talking to you at MiracleDBF.
Could you maybe share your thoughts on the "logical deletes" example above, and multi-table constraints in general ?

Wed Oct 04, 03:02:00 PM EDT  

Anonymous Varsha Gadekar said....

For Triggers: What is the condition where we can not avoid triggers?

What are the pre checks need to take care before designing the triggers(in worst case)?

Tue Oct 17, 03:11:00 AM EDT  

Blogger Thomas Kyte said....

Varsha Gadekar said....

There are no times triggers cannot be avoided. They are purely a convenience that is overused, abused and improperly used.

You would want to make sure there isn't a better way to do something before designing a trigger.

Never design a trigger to do integrity constraint checking that crosses rows in a table (eg: "the average salary by dept cannot exceed X" - that would go across rows) or across tables (eg: referential integrity)

Tue Oct 17, 03:48:00 AM EDT  

Blogger Rahul said....

>>>>The only drawback I can see to this plan is that the only major economic power in the world that doesn't use either of those two formats already is also the one with the most nukes.

Stephen

Fri Sep 15, 07:31:00 AM EDT
>>>

That was funny....

Rahul.

Wed Jan 23, 12:08:00 PM EST  

Anonymous Anonymous said....

Very Good article , this article make some interesting points.
Tactical Flashlights
r c helicopter
video game
Tactical Flashlight

Mon Jun 23, 02:41:00 AM EDT  

Blogger laptop battery said....

Shopping the cheap battery,you can see from here.

Sat Aug 09, 03:17:00 AM EDT  

Anonymous Sam (USA) said....


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

Wed Dec 31, 08:41:00 AM EST  

POST A COMMENT

<< Home