Handle PL-SQL exception and continue loop

Question

Next PL-SQL code processes bloob objects, until it finds unusual bloob structure:

DECLARE
  CURSOR c_mnu
  IS
    SELECT tb.payment_number,tb.blob_obj,tb.blob_size,tb.blob_id
    FROM XXW_PYMNT_ITM_TRF_STG tb
    where tb.payment_number not in (SELECT PAYMENT_NUMBER FROM XXW_PAYMENTS_F_EXCEPTION);
  test_vc VARCHAR2(32767);
  offset      NUMBER := 1;
  amount      NUMBER := 1;
  LEN         NUMBER := 1;
  idItem       VARCHAR2(100);--id item de pago
  montoAplicar VARCHAR2(100);--monto a aplicar
  billItem VARCHAR2(60);--id de billing
  blob_id_ant  NUMBER ;-- blob id anterior
  seq_num      NUMBER := 0; -- sequencia agrupando por blob id
  pago varchar(50);  
BEGIN
  FOR cu IN c_mnu
  LOOP
    LEN           :=cu.blob_size;--tamaño del blob
    test_vc       := cu.blob_obj;--variable de trabajo del blob
    pago := cu.payment_number;
    WHILE ( LEN > 0)
    LOOP
       -- Verifico si es el mismo blob id al anterior
       IF  blob_id_ant = cu.blob_id THEN
     seq_num := seq_num + 1;
       ELSE  
     seq_num := 1;
       END IF;
       --Segundo LF
       idItem:=substr(test_vc,instr(test_vc, ' ' , 1,10 )+1,(instr(test_vc, ' ' , 1,11)-(instr(test_vc, ' ' , 1,10 )+1)));
    -- Tercer LF
       montoAplicar:=substr(test_vc,instr(test_vc, ' ' , 1,15 )+1,(instr(test_vc,  chr(10) , 1,3)-(instr(test_vc, ' ' , 1,15 )+1)));
       --Cuarto LF
       billItem:=substr(test_vc,instr(test_vc, '"' , 1 )+1,((instr(test_vc,  chr(10) , 1,4 )-1)-(instr(test_vc, '"' , 1 )+1)));
       amount:=instr(test_vc,  chr(10) , 1,4 );
       offset := amount+1;
       --saco el pedazo de blob procesado
       test_vc := substr(test_vc,offset, LEN);
       --inserto los valores en la tabla de payments    
       insert into XXW_PYMNT_ITM_AMNT values (cu.payment_number,to_number(idItem),billItem,to_number(montoAplicar),cu.blob_id, seq_num); 
       --actualizo la longitud de blob
       LEN := LEN - offset;
       blob_id_ant := cu.blob_id;

    END LOOP;
  END LOOP;
  commit;
  EXCEPTION
  WHEN OTHERS THEN
  dbms_output.put_line ( 'Pago con error ' || pago);
END;

When program finds more lines than expected trought subsrt functions , returns

6502 : 65000 : java.sql.SQLException: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ORA-06512: at line 1

Instead of just finding to troubled data and adding it to XXW_PAYMENTS_F_EXCEPTION, id like to handle the exception and continue the loop.

I wonder if there is a "SKIP" implementation I could use. I've tried labeling loops but I got "PLS-00375: illegal GOTO statement; this GOTO cannot branch to label". Any suggestions? Thanks.

You have an exception handling that catches all the errors; are you sure the error is coming from this piece of code? — Aleksej
– Aleksej, Commented Jan 26, 2018 at 19:17
This seems to be a dump of your actual code rather than an MCVE. Simplified examples are easier to work with. idownvotedbecau.se/toomuchcode — William Robertson
– William Robertson, Commented Jan 27, 2018 at 10:52
As you're working with BLOB type you should be using DBMS_LOB.SUBSTR() and related functions. They perform more efficiently with LOBs than SQL substr() etc. — APC
– APC, Commented Jan 28, 2018 at 13:51

Bobby Durrett · Accepted Answer · 2018-01-26 20:57:35Z

6

You could create a new block around the substr lines and catch exceptions there so that you stay in the loop. Here is a simple example of an inner block with an exception handler.

    >DECLARE
  2      CURSOR testc
  3      IS
  4          SELECT table_name from user_tables where rownum < 5;
  5      dummy number;
  6  BEGIN
  7      FOR testr IN testc
  8      LOOP
  9          BEGIN
 10              select 1/0 into dummy from dual;
 11          EXCEPTION
 12          WHEN OTHERS THEN
 13              dbms_output.put_line('Inner exception');
 14          END;
 15      END LOOP;
 16  EXCEPTION
 17  WHEN OTHERS THEN
 18      dbms_output.put_line('Outer exception');
 19  END;
 20  /
Inner exception
Inner exception
Inner exception
Inner exception

PL/SQL procedure successfully completed.

answered Jan 26, 2018 at 20:57

Bobby Durrett

1,30312 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

William Robertson · Accepted Answer · 2018-01-27 11:07:23Z

1

Aside from the question of continuing after exceptions, if you want to skip to the next iteration you can use continue:

begin
    for r in (
        select 'X' as sometext from dual union all
        select 'XY' from dual union all
        select 'XYZ' from dual union all
        select 'Z' from dual
    )
    loop
        if length(r.sometext) > 2 then
            dbms_output.put_line('(Skipping ' || r.sometext ||')');
            continue;
        end if;

        dbms_output.put_line(r.sometext);
    end loop;
end;

Output:

X
XY
(Skipping XYZ)
Z

Or without the if:

begin
    for r in (
        select 'X' as sometext from dual union all
        select 'XY' from dual union all
        select 'XYZ' from dual union all
        select 'Z' from dual
    )
    loop
        continue when length(r.sometext) > 2;

        dbms_output.put_line(r.sometext);
    end loop;
end;

edited Jan 27, 2018 at 11:07

answered Jan 27, 2018 at 11:01

William Robertson

16.1k4 gold badges40 silver badges49 bronze badges

1 Comment

APC Over a year ago

But this doesn't handle any kind of bloob, let alone unusual ones :D

Collectives™ on Stack Overflow

Handle PL-SQL exception and continue loop

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related