Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Oracle Cursor - Exception handling within a loop

Posted on 2003-11-12
2
Medium Priority
?
17,350 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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.

610 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