CODEWITHSUNDEEP
oracle-databaseplsqloracle11gexception
TrojanName
TrojanName

Reputation: 5355

Oracle PL/SQL - how do I run the same block of code for ALL exceptions?

Oracle 11g. This seems like it should be stupidly obvious, but I haven't seen an example. I have 2 exceptions which each need to write slightly different log messages, and then they should do the same UPDATE and CONTINUE.

Is there any way to structure the exception so I only need to type the UPDATE and CONTINUE statements once, while keeping the different logging?

FOR my_rec IN my_cursor
LOOP

BEGIN
...do some stuff

EXCEPTION 
  WHEN NO_DATA_FOUND THEN
    log_detail.new('Skipping record - ID not found');

    UPDATE my_table
    SET operation_result = 'Failed'
    WHERE my_id = my_rec.some_id;

    CONTINUE;

 WHEN OTHERS THEN
    log_detail.new('Skipping record - unknown error');

    UPDATE my_table
    SET operation_result = 'Failed'
    WHERE my_id = my_rec.some_id;

    CONTINUE;
END;
END LOOP;

Upvotes: 0

Views: 630

Answers (2)

fgil
fgil

Reputation: 19

You could try:

DECLARE
    vError      VARCHAR2(1);
    vMessage    VARCHAR2(100);
BEGIN
    FOR my_rec IN my_cursor LOOP
        vError := 'N';
        BEGIN
            ...do some stuff
        EXCEPTION 
            WHEN NO_DATA_FOUND THEN
                vError := 'S';
                vMessage := 'Skipping record - ID not found';
          WHEN OTHERS THEN
                vError := 'S';
                vMessage := 'Skipping record - unknown error';
        END;

        IF vError = 'S' THEN
            log_detail.new(vMessage);

            UPDATE my_table
            SET operation_result = 'Failed'
            WHERE my_id = my_rec.some_id;

            CONTINUE;
        END IF;
    END LOOP;
END;

Upvotes: 1

Nicola
Nicola

Reputation: 314

Did you try:

FOR my_rec IN my_cursor
LOOP

    BEGIN
    ...do some stuff

    EXCEPTION 

       WHEN OTHERS THEN
         if sqlcode=-1403 then
           log_detail.new('Skipping record - ID not found');
         else
           log_detail.new('Skipping record - unknown error');
         end if;

       UPDATE my_table
       SET operation_result = 'Failed'
       WHERE my_id = my_rec.some_id;

       CONTINUE;
    END;
END LOOP;

Upvotes: 4

Related Questions