Solved

PL/SQL

Posted on 2004-08-26
8
771 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
 
LVL 23

Expert Comment

by:seazodiac
ID: 11903424
no, it will not. it will continue to loop
0
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.

 
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 34

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

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
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.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

705 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

12 Experts available now in Live!

Get 1:1 Help Now