Home » SQL & PL/SQL » SQL & PL/SQL » Need to skip unique constraint (Oracle 8.0, Pl/SQL)
Need to skip unique constraint [message #297051] Wed, 30 January 2008 00:33 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Guruji,
I need to raise an exception for skipping unique constraint error.
The scenario is as follows:
The data flows from different remote tables to my table. A procedure is doing a process and calculations. Finally it is inserting the data in to a table with the calculation.
A job is firing the procedure daily at once.
Recently what happens, an unique constraint error is raised and the procedure stops.
I am invstigting to find out the root cause of unique constraint error. But, it is taking a long time.

Well. now I need to raise an exception to handle the unique constraint error. That means, even if the unique constraint raises,
1. The process of the procedure should not be stopped. It should carry with the next available data.
2. The data (or row), in which the unique constraint was raised, should be trapped in a new 'Trap_unq_err' table. It will help me to findout which are the rows is the root cause for the error.


Warm Regds,
Lenin.
Re: Need to skip unique constraint [message #297058 is a reply to message #297051] Wed, 30 January 2008 00:45 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If this "computing" is done in a loop, put additional BEGIN-EXCEPTION-END block within the loop. Exception handler should handle exceptions (you may even use WHEN OTHERS as you sad that you'd like to log errors). Something like this:
DECLARE
  l_computed_value NUMBER;
  l_errmsg         VARCHAR2(500);
BEGIN
  FOR cur_r IN (SELECT id, name FROM some_table)
  LOOP
    BEGIN
      l_computed_value := do_some_computing_here;
      INSERT INTO your_table (id, name, computed_value)
      VALUES
      (cur_r.id, cur_r.name, l_computed_value);
    EXCEPTION
      WHEN OTHERS THEN
        l_errmsg := SQLERRM;
        INSERT INTO err_log_table (id, err)
        VALUES
        (cur_r.id, l_errmsg);
    END;
  END LOOP;
END;
Re: Need to skip unique constraint [message #297065 is a reply to message #297058] Wed, 30 January 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
WHEN DUP_VAL_ON_INDEX
NEVER
WHEN OTHERS

Regards
Michel
Re: Need to skip unique constraint [message #297071 is a reply to message #297065] Wed, 30 January 2008 00:58 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I agree; however, as he'd like to log errors (and who knows; perhaps DUP-VAL-ON-INDEX isn't the only one), I thought that trapping all of them in a simple WHEN OTHERS will not do harm in this case.
Re: Need to skip unique constraint [message #297078 is a reply to message #297071] Wed, 30 January 2008 01:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
- The title and post is about skipping unique constraint
- What if the error is something like "unable to extend your_table", has the batch still to continue over and over for each row getting the same error, inserting each row in log table...

Regards
Michel
Re: Need to skip unique constraint [message #297088 is a reply to message #297078] Wed, 30 January 2008 01:58 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
And what happens when your_table and err_log_table are in the same tablespace and you hit the "unable to extend" error Wink

As it is a daily job, a more worked-out error/exception handling would be preferrable
Re: Need to skip unique constraint [message #297095 is a reply to message #297051] Wed, 30 January 2008 02:37 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Gee ... yes, you are right and I am wrong (generally speaking).

However, in my opinion, as
catchme_lenin wrote on Wed, 30 January 2008 07:33
Recently what happens, an unique constraint error is raised and the procedure stops.
I am investigating to find out the root cause of unique constraint error. But, it is taking a long time.
I've suggested a quick and dirty solution to the problem. It is not perfect, but - it really might help this procedure to do the job until the root cause is found.

Both
Quote:
1. The process of the procedure should not be stopped. It should carry with the next available data.
2. The data (or row), in which the unique constraint was raised, should be trapped in a new 'Trap_unq_err' table.
requirements are satisfied. "What if" might happen, or might not. If it happens, I'm sure Lenin will return (just like Batman did) and ask for help. Until then, I'd give (my) WHEN OTHERS a try.
Re: Need to skip unique constraint [message #297105 is a reply to message #297095] Wed, 30 January 2008 02:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Never gives bad code in Newbies forum.
I don't mean wrong code that does not work but code that can hurt if it is copied and forum codes are, without the slightest thought and even more if it works.

Regards
Michel
Re: Need to skip unique constraint [message #297112 is a reply to message #297051] Wed, 30 January 2008 03:21 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi all masters,
Thnk you all.

I tried with Littlefoot code in a sample scenario. Using FOR Loop and Cursor. It meets my requirement
1. The procedure is not get stopped when an unique constraint raised. It is carrying out with next available record.
2. It traps the records, which are raising Unique Constraint error.

I have used WHEN DUP_VAL_ON_INDEX THEN exception to trap the error rows and used WHEN OTHERS THEN for other errors.


Thank you all once again.

-Lenin.

Re: Need to skip unique constraint [message #297122 is a reply to message #297112] Wed, 30 January 2008 03:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
used WHEN OTHERS THEN for other errors

NO.
Never use WHEN OTHERS.
There is no use of it in 99.999999% of cases.
Using it is a code bug.

Regards
Michel
Re: Need to skip unique constraint [message #297127 is a reply to message #297122] Wed, 30 January 2008 04:09 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Ok Michel.

I will remove the WHNE OTHERS THEN.

U mean that u want me to use the appropriate exceptions, like TOO_MANY_ROWS, NO_DATA_FOUND..?


Friendly,
-Lenin.
Re: Need to skip unique constraint [message #297132 is a reply to message #297127] Wed, 30 January 2008 04:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
U mean that u want me to use the appropriate exceptions, like TOO_MANY_ROWS, NO_DATA_FOUND..?

That's right.
Catch the expected exceptions, for which you have something to do.
Let the other ones naturally raise.

Regards
Michel
Re: Need to skip unique constraint [message #297186 is a reply to message #297132] Wed, 30 January 2008 09:21 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 30 January 2008 11:40

Let the other ones naturally raise.


Just in case you (Lenin) didn't understand that sentence, I'd like to say a few words about the issue: WHEN OTHERS can be used only in one-time-run examples (like - in my opinion - this one was). For anything more, it is better to use no exception handler at all than WHEN OTHERS as it successfully obfuscates a real reason why the error happened and, equally important, WHERE it happened (Oracle will show you exact code line).

Here's an example: this code doesn't do anything "smart", it is just used to illustrate what happens when you

a) do NOT use the WHEN OTHERS:
SQL> declare
  2    l_eno number;
  3  begin
  4    select empno into l_eno from emp;
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 4


SQL>
See? You know that there's something wrong there, and you know where it happened - at line 4.

b) use the WHEN OTHERS:
SQL> declare
  2    l_eno number;
  3  begin
  4    select empno into l_eno from emp;
  5  exception
  6    when others then null;
  7  end;
  8  /

PL/SQL procedure successfully completed.
The WHEN OTHERS made it look perfect - no error at all! But, the procedure would fail and you wouldn't know that anything wrong happened.

A conclusion: be careful when you use WHEN OTHERS - it may do more harm than good.
Re: Need to skip unique constraint [message #297196 is a reply to message #297186] Wed, 30 January 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
A conclusion: be careful when you use WHEN OTHERS - it may do more harm than good.

See this other current topic: http://www.orafaq.com/forum/mv/msg/96427/297192/102589/#msg_297192

Regards
Michel
Re: Need to skip unique constraint [message #297270 is a reply to message #297186] Wed, 30 January 2008 23:05 Go to previous message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Littlefoot,
Thank you for giving me a explaination on the usage of WHEN OTHERS THEN exception.
I will do follow as per your instruction.

Friendly,
Lenin.
Previous Topic: new in sql need help with date datatype and java
Next Topic: text files with column names
Goto Forum:
  


Current Time: Fri Dec 02 14:28:27 CST 2016

Total time taken to generate the page: 8.54998 seconds