asked on

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;
/

seazodiac

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;
/

ASKER CERTIFIED SOLUTION

bvanderveen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bvanderveen

NULL will handle it, but if he doesn't use an inner block, the procedure will terminate without cycling through all the records.

seazodiac

no, it will not. it will continue to loop

bvanderveen

>> 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!).

SOLUTION

seazodiac

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

johnsone

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rockies1

ASKER

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!!