PLSQL exception handling problem
RogB...@gmail.com
I have some PLSQL that is looping through a recordset and I am
attempting to update a field.
This could possibly fail because I have another session which could
be updating the same field, but has not commited.
I am trying to trap the error Resource Busy, which I do, but then
procedure exits without processing more records. I know that somehow,
I must move the error trapping inside a block within the loop, but I
just cannot seem to get it right.
Anyone got any hints for me.? Thanks
(Oracle 10g)
DECLARE
v_ErrorCode NUMBER(15);
v_ErrorText VARCHAR2(200);
e_resource_busy exception;
pragma exception_init(e_resource_busy,-54);
CURSOR cursor1 IS
select employeeid,dentalempcost from dental where employeeid in
('8641','372','4245') for update of dentalempcost NOWAIT;
BEGIN
For myrecord in cursor1 LOOP
update lindac.dentalroger set DENTALEMPCOST=20 where current of
cursor1;
dbms_output.put_line('change made');
END LOOP;
commit;
EXCEPTION
WHEN e_resource_busy THEN dbms_output.put_line('busy');
WHEN OTHERS THEN
BEGIN
v_ErrorCode:=SQLCODE;
v_ErrorText:=SUBSTR(SQLERRM,1,200);
dbms_output.put_line(v_ErrorCode);
dbms_output.put_line(v_ErrorText);
END;
END;
cleveridea
There's more than a few problems with your block.
#1 Since your cursor is FOR UPDATE NOWAIT, it's an all or nothing deal
on the row level processing. If it cannot grab all the rows specified
in the query, it simply throws the exception and that's that. If you
want to process all the rows that have no row level lock (from another
session), eliminate the FOR UPDATE NOWAIT and put the NOWAIT into your
row level processing UPDATE statement.
#2 Wrap you new and improved UPDATE statement in its own
begin..exception...end where you catch your exception, instead of the
outermost block as you do now.
#3 Only bad things are going to happen by having the COMMIT in your
block. Let the entity calling the block commit or not.
#4 Generally, the "other" code that is generating row level locks
should be improved to not hang onto data those locks longer than it
has to, and this block of code should do the waiting it needs to do
the row level processing you want done in the first place rather than
just skip work it is supposed to do.
I hope that helps.
RogB...@gmail.com
> on the row level processing. If it cannot grab all the rows specified
> in the query, it simply throws the exception and that's that. If you
> want to process all the rows that have no row level lock (from another
> session), eliminate the FOR UPDATE NOWAIT and put the NOWAIT into your
> row level processing UPDATE statement.
>
> #2 Wrap you new and improved UPDATE statement in its own
> begin..exception...end where you catch your exception, instead of the
> outermost block as you do now.
Thanks for your response, Cleveridea. The "Other Code" should
definitely be improved. Unfortunately, it is an application that was
developed rather hastily by someone else that allows users to make
changes to their information. Management does not want to shut it down
for some maintenance that we need to do, let alone bring it down to
improve upon it. The maintenace that we have to do is modify certain
fields in people's data. My idea was use the FOR UPDATE NOWAIT when I
do a loop through each record in the table, that way if someone had
their own record locked while they were making a change, it would
generate the wait error for me, then I could insert that user's id
into a table of "users that could not be modified" then the loop would
continue and try to modify the next record. Otherwise, without the
NOWAIT, our update would wait for everyone's change to be committed,
and I suppose that would mean when no one is in the system which might
be an hour or a week.
DA Morgan
Several things don't make sense to me about your block not pointed
out by cleveridea.
1. Your cursor is on one table, "DENTAL", and your FOR UPDATE OF
refers to a column in that table. Then you update "LINDAC.DENTALROGER."
How is this supposed to work?
2. What purpose is served by a cursor and a loop. A simple UPDATE
statement should suffice unless this isn't your real code.
--
Daniel A. Morgan
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
cleveridea
I understand your position; I think we've all been there.
I would like to encourage you to pursue your line of thought where you
set aside the work that is skipped into a separate queue of pending
work.
Simply throwing a dbms_output() as the outcome of a WHEN OTHERS THEN
is a critical flaw. When catching errors (especially WHEN OTHERS) you
should be looking towards only two outcomes: #1: rethrow the error or
another error or #2: definitively follow an alternate (even more
bulletproof) processing path.
Whatever you planned to do manually when you receive this "skip"
dbms_output feedback, you should automate into your procedure itself
instead. Such would fulfill outcome variety #2 above.
RogB...@gmail.com
1. You correct DA. Sometimes I try to simply things for posting. the
table is actually LINDAC.DENTALROGER. I just tried to replace it with
DENTAL in the post to make it easier on the eyes. Sorry for missing
one.
2. I was trying to determine each one that would fail, identify that
record, but update the ones that I could.
DA Morgan
SELECT FOR UPDATE
UPDATE
COMMIT
Everything else is superfluous.