Solved

PL/SQL

Posted on 2004-08-26
8
776 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
[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
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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 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.

729 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