StuckOnceAgain
asked on
Oracle Cursor - Exception handling within a loop
I have a procedure that I have written that works. Unfortunately some of the data that it returns is 'Null' and hence the corresponding update fails. I want to write exception handling that will output the offending line and continue procesing the next set of data, but I am having problems doing that.
declare
cursor prodid_cursor is
Select distinct product_id from product_relation where (relation_type = 5 or relation_type = 6 );
cursor_row prodid_cursor%rowtype;
begin
for cursor_row in prodid_cursor
loop
Update Product_relation set reserved2 =
(select regioncode from group_product_att gp where gp.product_id = cursor_row.product_id and regioncode is not null) where cursor_row.product_id = Product_relation.product_i d;
end loop;
commit;
end;
This works till the bad update is encountered and then errors out. So i tweaked this to be
declare
cursor prodid_cursor is
Select distinct product_id from product_relation where (relation_type = 5 or relation_type = 6 );
cursor_row prodid_cursor%rowtype;
begin
for cursor_row in prodid_cursor
loop
Update Product_relation set reserved2 =
(select regioncode from group_product_att gp where gp.product_id = cursor_row.product_id and regioncode is not null) where cursor_row.product_id = Product_relation.product_i d;
exception
when others then
dbms_output.put_line('Erro r: '||sqlerrm);
end loop;
commit;
end;
It errors out saying with:
-------------------------- ---------- -------
ORA-06550: line 13, column 2:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
ORA-06550: line 18, column 2:
PLS-00103: Encountered the symbol "COMMIT" when expecting one of the following:
begin function package pragma procedure form
The symbol "begin" was substituted for "COMMIT" to continue.
ORA-06550: line 20, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function package pragma procedure form
-------------------------- ---------
If I pull the exception out of the loop and put it just before the commit, it prints out the exception clause and errors out and stops.
Can someone help me out. Thanks.
declare
cursor prodid_cursor is
Select distinct product_id from product_relation where (relation_type = 5 or relation_type = 6 );
cursor_row prodid_cursor%rowtype;
begin
for cursor_row in prodid_cursor
loop
Update Product_relation set reserved2 =
(select regioncode from group_product_att gp where gp.product_id = cursor_row.product_id and regioncode is not null) where cursor_row.product_id = Product_relation.product_i
end loop;
commit;
end;
This works till the bad update is encountered and then errors out. So i tweaked this to be
declare
cursor prodid_cursor is
Select distinct product_id from product_relation where (relation_type = 5 or relation_type = 6 );
cursor_row prodid_cursor%rowtype;
begin
for cursor_row in prodid_cursor
loop
Update Product_relation set reserved2 =
(select regioncode from group_product_att gp where gp.product_id = cursor_row.product_id and regioncode is not null) where cursor_row.product_id = Product_relation.product_i
exception
when others then
dbms_output.put_line('Erro
end loop;
commit;
end;
It errors out saying with:
--------------------------
ORA-06550: line 13, column 2:
PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
begin declare end exit for goto if loop mod null pragma raise
return select update while <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall
<a single-quoted SQL string>
ORA-06550: line 18, column 2:
PLS-00103: Encountered the symbol "COMMIT" when expecting one of the following:
begin function package pragma procedure form
The symbol "begin" was substituted for "COMMIT" to continue.
ORA-06550: line 20, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
begin function package pragma procedure form
--------------------------
If I pull the exception out of the loop and put it just before the commit, it prints out the exception clause and errors out and stops.
Can someone help me out. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
BEGIN and not BEING :-)
But guess what that did the trick. THanks a lot.