rockies1
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;
/
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;
/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
NULL will handle it, but if he doesn't use an inner block, the procedure will terminate without cycling through all the records.
no, it will not. it will continue to loop
>> 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!).
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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('Skip ped value: ' || rec.ban || '. Not unique.');
WHEN OTHERS THEN
dbms_output.put_line('Erro r: ' || rec.ban || sqlerrm);
end; --end of inner block
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Othe r 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!!
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('Skip
WHEN OTHERS THEN
dbms_output.put_line('Erro
end; --end of inner block
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Othe
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!!
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;
/