Solved

Oracle Cursor - Exception handling within a loop

Posted on 2003-11-12
2
17,145 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
2 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 50 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

773 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