| Exception Handling for Update [message #7219] |
Wed, 28 May 2003 09:20  |
RB
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
I have written a function that update a table in the database. I wanted to know some specific exception handling that I can do for the Update statement.
Thanks
RB
|
|
|
|
|
|
|
|
| Re: Exception Handling for Update [message #7222 is a reply to message #7221] |
Wed, 28 May 2003 12:24  |
RB
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
|
If the row is not updated , its ok i dont want to raise it as an exception. but if an update statement fails (due to whatever reason) i want to handle that. Hope i am clear now !
|
|
|
|
| Re: Exception Handling for Update [message #7223 is a reply to message #7222] |
Wed, 28 May 2003 12:55 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
By "handle" it, do you mean raise a generic error message?
create or replace function ...
is
begin
update ...;
exception
when others then
raise_application_error(-20000, 'Error in update');
end; |
|
|
|
| Re: Exception Handling for Update [message #7224 is a reply to message #7223] |
Wed, 28 May 2003 13:20 |
RB
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
|
Thanks Ted, I guess i could not explain what I want. I do have a 'WHEN OTHERS' part in exception handling. I just wanted to handle any Update specific exception which makes the Update statement fail! Is this possible at all ?
|
|
|
|
| Re: Exception Handling for Update [message #7225 is a reply to message #7224] |
Wed, 28 May 2003 13:25 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Ted? How about Todd?
This is the type of thread that develops when the original post is very generic and has no code sample at all.
My earlier statement still applies - there are numerous exceptions that could be raised as the result of an update. As with any exception handling, if you want to handle them specifically, you have to list each one you care about.
|
|
|
|
| Re: Exception Handling for Update [message #7226 is a reply to message #7225] |
Wed, 28 May 2003 13:44 |
RB
Messages: 11
Registered: April 2002
|
Junior Member |
|
|
Am Sorry about the name Todd.
I got what u r saying. I will give an example code and can you tell me if I can add any update specific exception to it ??
declare
cursor c1 is select * from employee
where emp_id <100
for update of desc;
c1_rec c1%ROWTYPE
begin
for c1_rec IN c1
loop
update employee
set desc = 'One of first hundred employees'
where current of c1;
end loop;
exception
when no_data_found then
dbms_output.put_line('no data found ');
when others then
dbms_output.put_line(' others ');
end;
Thanks a lot!
|
|
|
|
| Re: Exception Handling for Update [message #7227 is a reply to message #7226] |
Wed, 28 May 2003 16:11 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Again, there is a long laundry list of possible exceptions. NO_DATA_FOUND will not be one of them - that is only raised on a SELECT INTO statement.
Some exceptions that come to mind (and these are not all pre-defined):
[*]inserted value too large for column
[*]cannot update to null
[*]conversion errors
[*]parent/child (RI) errors
[*]exceptions raised through triggers (validation code)
Maybe if you explain why you want to trap for exceptions (what are you going to do with them after you've trapped them) we could better help with an approach.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
