Solved

PL/SQL

Posted on 2004-08-26
8
775 Views
Last Modified: 2012-08-14
I'm writing my first PL/SQL Procedure....

I want to cycle through a cursor and if the value in a field is $10.00 or less I want to insert the associated account number into a table.

I wrote the code just fine, and it works as it should unless there is a unique constraint error on the Insert...

If that occurs, I simply want to ignore the error and continue with the remaining records in the cursor, but I can not figure out how.

Here is my code so far:
CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
  cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ';

  BEGIN
               for rec in TESTING loop
                  if (rec.refund_amount < 10.01) then
                     execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
                     using rec.ban;
                     commit;
                  end if;
            end loop;
  EXCEPTION
    WHEN UNIQUE_CONSTRAINT THEN
      -- I have no idea what to do here to ignore the unique constraint error
      
   end MORGAN_TEST;
/
0
Comment
Question by:rockies1
8 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 11903397
just put NULL in there like this:

CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
 cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ';

 BEGIN
            for rec in TESTING loop
              if (rec.refund_amount < 10.01) then
                 execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
                 using rec.ban;
                 commit;
              end if;
         end loop;
 EXCEPTION
   WHEN UNIQUE_CONSTRAINT THEN
        NULL;    
  end MORGAN_TEST;
/
0
 
LVL 7

Accepted Solution

by:
bvanderveen earned 125 total points
ID: 11903404
You can have multiple begin/exception/end blocks within a procedure.  This will control where an error is caught and handled.  If it is not caught in an inner block, it will "fall" to the next outer block:

CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
  cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ';

  BEGIN
             for rec in TESTING loop
               if (rec.refund_amount < 10.01) then
                  BEGIN  --inner block
                  execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
                  using rec.ban;
                  commit;
               end if;
                  EXCEPTION
                          WHEN UNIQUE_CONSTRAINT THEN
                                 dbms_output.put_line('Skipped value: ' || :1 || '.  Not unique.');
                   end;  --end of inner block
          end loop;
  EXCEPTION
    WHEN OTHERS THEN
          dbms_output.put_line('Other error: ' || sqlerrm);
     
   end MORGAN_TEST;
/


This handles the unique constraint error where it should be, and if another error is raised, the WHEN OTHERS will handle it in the outer block.
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11903413
NULL will handle it, but if he doesn't use an inner block, the procedure will terminate without cycling through all the records.
0
Technology Partners: 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!

 
LVL 23

Expert Comment

by:seazodiac
ID: 11903424
no, it will not. it will continue to loop
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11903639
>> no, it will not. it will continue to loop

I don't think so.  The exception has transferred control outside the loop.  Execution will fall to "END" without an inner block (or a GOTO - urk!).
0
 
LVL 23

Assisted Solution

by:seazodiac
seazodiac earned 125 total points
ID: 11903682
sorry, I did not pay attention to the location of exception block, I thought it is inside the FOR Loop;

this will do it;

CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ';

BEGIN
           for rec in TESTING loop
             if (rec.refund_amount < 10.01) then
                execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
                using rec.ban;
                commit;
              EXCEPTION
              WHEN UNIQUE_CONSTRAINT THEN
                 NULL;    
             end if;
        end loop;
  end MORGAN_TEST;
/
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 50 total points
ID: 11904028
No, it does not continue.  The error handler is outside the loop, so processing goes outside the loop.  It can be proven with a simple test.

The REFUND table contains:

       BAN REFUND_AMOUNT REFUND_STA
---------- ------------- ----------
         1             5 WQ
         1             5 WQ
         2             5 WQ

The procedure is (Changes - order by added to we can prove order of operations, error handler changed (UNIQUE_CONSTRAINT is not a predefined error in 9i)):

  1  CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
  2   cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ' order by ban;
  3   BEGIN
  4              for rec in TESTING loop
  5                if (rec.refund_amount < 10.01) then
  6                   execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
  7                   using rec.ban;
  8                   commit;
  9                end if;
 10           end loop;
 11   EXCEPTION
 12     WHEN DUP_VAL_ON_INDEX THEN
 13          NULL;
 14*   end MORGAN_TEST;
 15  /

After running the procedure, we see the REFUND_TOO_SMALL table contains:

       BAN
----------
         1

The exception needs to be put inside the loop.
0
 

Author Comment

by:rockies1
ID: 11904727
I ended up going with this:

CREATE OR REPLACE PROCEDURE MORGAN_TEST IS
  cursor TESTING is select ban, refund_amount from refund where REFUND_STATUS = 'WQ';

  BEGIN --Outer block
             for rec in TESTING loop
                   BEGIN  --inner block
               if (rec.refund_amount < 10.01) then
                 
                  execute immediate 'insert into REFUND_TOO_SMALL (BAN) VALUES (:1 )'
                  using rec.ban;
                  commit;
               end if;
                  EXCEPTION
                          WHEN DUP_VAL_ON_INDEX THEN
                                 dbms_output.put_line('Skipped value: ' || rec.ban || '.  Not unique.');
                                      WHEN OTHERS THEN            
                                 dbms_output.put_line('Error: ' || rec.ban || sqlerrm);
                   end;  --end of inner block
          end loop;
  EXCEPTION
    WHEN OTHERS THEN
          dbms_output.put_line('Other error: ' || sqlerrm);
     
   end MORGAN_TEST; --End of outer block
/


I appreciate the time everyone put into explaining what the problem was and how to go about fixing it.

I will increase the points to split.

Thanks!!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
This video shows how to recover a database from a user managed backup

749 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