raise exception within an insert cursor loop

[rivi10]

Hi,

In the following cursor, I need to raise an exception when the Insert in the Loop fails.

My objective is, if all the records retrieved by the cursor are NOT inserted, then abort the insert transaction and do not commit.

Should my EXCEPTION be just before the loop ends or after the loop ends?

DECLARE

CURSOR c_get_employ IS
SELECT EMP.EMPLOYER_ID, EMP.EMPLOYER_NAME, EMP.EMPLOYER_LOCATION
FROM EMP
WHERE EMP.EMPLOYER_COUNTRY = 'USA'
ORDER BY EMP.EMPLOYER_ID;

BEGIN

-- Loop through the cursor and start inserting the data

FOR c_post_employ IN c_get_employ

LOOP

INSERT INTO EMP_OUTPUT
(EMPID, EMPNAME, EMPLOC)
VALUES
(c_post_employ.EMPLOYER_ID,c_post_employ.EMPLOYER_NAME,c_post_employ.EMPLOYER_LOCATION);

END LOOP;

WHEN OTHERS THEN
msg.message_code := 101;
msg.message := 'Unable to insert into EMP_OUTPUT Table for LOT Number-';
RAISE emp_error;

COMMIT;

EXCEPTION
WHEN emp_error THEN
DBMS_OUTPUT.PUT_LINE(msg.message_code, msg.message,SQLCODE,SQLERRM,USER);
END;

 

[Dagon]

To do what that, you would need an inner block around the insert loop e.g.

DECLARE
  
  CURSOR c_get_employ IS
    SELECT EMP.EMPLOYER_ID, EMP.EMPLOYER_NAME, EMP.EMPLOYER_LOCATION
          FROM EMP
            WHERE EMP.EMPLOYER_COUNTRY = 'USA'
    ORDER BY EMP.EMPLOYER_ID;
  emp_error exception;  
BEGIN
    
 -- Loop through the cursor and start inserting the data
 begin
 FOR c_post_employ IN c_get_employ
    
    LOOP

    INSERT INTO EMP_OUTPUT
        (EMPID, EMPNAME, EMPLOC)
    VALUES
        (c_post_employ.EMPLOYER_ID,c_post_employ.EMPLOYER_NAME,c_post_employ.EMPLOYER_LOCATION);
    
    END LOOP;

    exception
    WHEN OTHERS THEN
        msg.message_code := 101;
        msg.message      := 'Unable to insert into EMP_OUTPUT  Table for LOT Number-';
    RAISE emp_error;
    end;    
  
    COMMIT;
      
    EXCEPTION
    WHEN emp_error THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE(msg.message_code, msg.message,SQLCODE,SQLERRM,USER);    
END;

But this is utterly pointless because all the inner exception will do is catch an error raised by the insert and transmit it to the main exception handler. This is no different to what would happen if you had no exception handling at all.

The default behaviour is that, if any insert fails, the procedure will terminate and roll back, which seems to be what your requirement is here. It is one of my great annoyances that people constantly feel they have to put exception handlers in code when half the time they are not required at all.

 

[SantaMufasa]

Dagon,

Please correct me if I am wrong (or if I am missing something), but if you have two EXCEPTION sections, do you not need two BEGIN statements in your sample code, above?

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.

 

[Dagon]

I haven't tested the code, so won't guarantee it works. But there are two begin statements -

...
BEGIN
    
 -- Loop through the cursor and start inserting the data
 begin
...

 

[SantaMufasa]

My bad...I didn't look carefully enough. Goodonya.

Mufasa
(aka Dave of Sandy, Utah, USA)
“People may forget what you say, but they will never forget how you made them feel.