?
Solved

Oracle Cursor - Exception handling within a loop

Posted on 2003-11-12
2
Medium Priority
?
17,275 Views
Last Modified: 2008-08-08
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_id;

      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_id;

      exception
      when others then
            dbms_output.put_line('Error: '||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.
0
Comment
Question by:StuckOnceAgain
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 200 total points
ID: 9733787
Ok, change your script to this:

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
     BEING
    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_id;

    exception
    when others then
         dbms_output.put_line('Error: '||sqlerrm);    
     END;
     end loop;
    commit;
end;
0
 

Author Comment

by:StuckOnceAgain
ID: 9733956
Works. I guess it was a typo.. it should have read

BEGIN and not BEING :-)

But guess what that did the trick. THanks a lot.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question