Solved

Oracle Cursor - Exception handling within a loop

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

Join & Write a Comment

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…
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

759 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now