• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 785
  • Last Modified:

PL/SQL

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
rockies1
Asked:
rockies1
3 Solutions
 
seazodiacCommented:
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
 
bvanderveenCommented:
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
 
bvanderveenCommented:
NULL will handle it, but if he doesn't use an inner block, the procedure will terminate without cycling through all the records.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
seazodiacCommented:
no, it will not. it will continue to loop
0
 
bvanderveenCommented:
>> 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
 
seazodiacCommented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
rockies1Author Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now