Solved

Oracle Cursor - Exception handling within a loop

Posted on 2003-11-12
2
17,183 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 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

733 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