title: Handle PL-SQL exception and continue loop
body: <p>Next PL-SQL code processes bloob objects, until it finds unusual bloob structure:</p>
  
  <pre><code>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 &gt; 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;
  </code></pre>
  
  <p>When program finds more lines than expected trought subsrt functions , returns</p>
  
  <blockquote>
    <p>6502 : 65000 : java.sql.SQLException: ORA-06502: PL/SQL: numeric or
    value error: character string buffer too small ORA-06512: at line 1</p>
  </blockquote>
  
  <p>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.</p>
  
  <p>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.</p>