Get value from cursor when exception occur

Question

I have a cursor for loop which will fail when col2 is zero:

declare
    cursor cur_data is
    select id, col1/col2 as mean 
    from my_table;
begin
    for rec_data in cur_data loop
        update my_table set col3=rec_data.mean where id=rec_data.id;
    end loop;
exception when others then
    insert into my_log (id, error_text) values (rec_data.id, SQLERRM);
end; 

I want to get the value of the id column when this error occur. Something like the insert statement in the exception section, which of course will fail because the cursor is closed. Is it possible?

Answer 1

When you do SELECT ID, 1/0 FROM my_table you always get nothing selected, no matter how big your table is, thus you don't have any ID. The exception divisor is equal to zero is raised already while SELECT, not while UPDATE.

This one is working.

CREATE TABLE my_table(
  ID NUMBER,
  nominator NUMBER,
  denominator NUMBER,
  ratio NUMBER CONSTRAINT ratio_check CHECK(ratio <= 1) );


CREATE TABLE my_log (
  ID NUMBER,
  error_text VARCHAR2(2000));


INSERT INTO my_table VALUES (400, 3, 5, NULL);
INSERT INTO my_table VALUES (500, 2, 5, NULL);
INSERT INTO my_table VALUES (300, 4, 5, NULL);
INSERT INTO my_table VALUES (100, 6, 5, NULL);
INSERT INTO my_table VALUES (200, 1, 5, NULL);
INSERT INTO my_table VALUES (600, 1, 0, NULL);
COMMIT;


DECLARE

  CURSOR cur_data IS
  SELECT ID, nominator/denominator AS ratio
  FROM my_table;

BEGIN

  FOR aRow IN cur_data LOOP
  BEGIN
    UPDATE my_table SET ratio = aRow.ratio WHERE ID = aRow.ID;
  EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO my_log VALUES (aRow.ID, DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
  END;
  END LOOP;


EXCEPTION
    WHEN OTHERS THEN
        INSERT INTO my_log VALUES (-1, DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
END;

SELECT * FROM my_log;

ID     ERROR_TEXT
--------------------------------------------------------------------------------
100    ORA-02290: check constraint (MY_USER.RATIO_CHECK) violated                       
       ORA-06512: at line 11                                                           

-1     ORA-01476: divisor is equal to zero                                             
       ORA-06512: at line 9                                                            

2 rows selected.

If you prefer FORALL you can use this one:

DECLARE

   TYPE ratioRecType IS RECORD (ID NUMBER, ratio NUMBER);
   TYPE ratioTable IS TABLE OF ratioRecType;
   r ratioTable;

    DML_ERRORS EXCEPTION;
   PRAGMA EXCEPTION_INIT(DML_ERRORS, -24381);
    recId NUMBER;
    errMsg VARCHAR2(1000);

BEGIN

  SELECT ID, nominator/denominator
  BULK COLLECT INTO r
  FROM my_table
  WHERE denominator <> 0;

  FORALL i IN r.FIRST..r.LAST SAVE EXCEPTIONS
    UPDATE my_table SET ratio = r(i).ratio WHERE ID = r(i).ID;

EXCEPTION
  WHEN DML_ERRORS THEN
    FOR f IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
       recId := r(SQL%BULK_EXCEPTIONS(f).ERROR_INDEX).ID;
       errMsg := SQLERRM(-SQL%BULK_EXCEPTIONS(f).ERROR_CODE);
       INSERT INTO my_log VALUES (recId, errMsg||CHR(13)||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
     END LOOP;
  WHEN OTHERS THEN
     INSERT INTO my_log VALUES (-1, DBMS_UTILITY.FORMAT_ERROR_STACK||DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);

END;

However SAVE EXCEPTIONS provides less detailed error message, i.e

SELECT * FROM my_log;

ID     ERROR_TEXT
--------------------------------------------------------------------------------
100    ORA-02290: check constraint (.) violated                       
       ORA-06512: at line 19                                                         

In order to get all records you can do it also like this:

DECLARE
  CURSOR cur_data IS
  SELECT ID, nominator, denominator
  FROM my_table;

BEGIN

  FOR aRow IN cur_data LOOP
  BEGIN
    UPDATE my_table SET ratio = aRow.nominator / aRow.denominator WHERE ID = aRow.ID;

...

Answer 2

A couple of ways not to have to face this problem in the first place.

1) Define a check constraint on col2 that doesn't allow zero values

2) If zero values are valid for other reasons, simply filter out the zero values in the query that defines the cursor:

cursor cur_data is
select id, col1/col2 as mean 
from my_table
where col2 != 0; -- or "nvl( col2, 0 ) != 0" if nulls allowed

In the meantime, if you want to see the rows where this is happening, simply query where it is zero.

Answer 3

No you would need to declare a variable:

declare
    cursor cur_data is
    select id, col1/col2 as mean 
    from my_table;
    v_id my_table.id%type;
begin
    for rec_data in cur_data loop
        v_id := rec_data.id;
        update my_table set col3=rec_data.mean where id=rec_data.id;
    end loop;
exception when others then
    insert into my_log (id, error_text) values (v_id, SQLERRM);
end; 

You could use rec_data.id if handling the error inside the loop:

declare
    cursor cur_data is
    select id, col1/col2 as mean 
    from my_table;
begin
    for rec_data in cur_data loop
        begin
            update my_table set col3=rec_data.mean where id=rec_data.id;
        exception when others then
            insert into my_log (id, error_text) values (rec_data.id, SQLERRM);
        end;
    end loop;
end; 

In this code, processing would continue from the next row from the cursor rather than aborting. To make it stop looping you could add an exit statement:

declare
    cursor cur_data is
    select id, col1/col2 as mean 
    from my_table;
begin
    for rec_data in cur_data loop
        begin
            update my_table set col3=rec_data.mean where id=rec_data.id;
        exception when others then
            insert into my_log (id, error_text) values (rec_data.id, SQLERRM);
            exit;
        end;
    end loop;
end;