CODEWITHSUNDEEP
plsql
Dekker
Dekker

Reputation: 85

Terminate script immediately

im new with PL_SQL, i want to ask a question. A need to terminate this script when reaches te exit condition, but its failing, it continues. Im not able to terminate the execution of the script when WHENEVER SQLERROR EXIT SQL.SQLCODE is reached. Any suggestions??

 WHILE V_COUNT_MENSAJES>0 
     LOOP
     DELETE
     FROM TB_ICP_ENVIOMENSAJES
     WHERE KEY_MENSAJE = P_KEY_MENSAJE;
     WHENEVER SQLERROR EXIT SQL.SQLCODE;
     END LOOP;



END;
   WHEN others THEN
      dbms_output.put_line('Error en la consulta!');
  END;

-----REST OF CODE

Upvotes: 0

Views: 124

Answers (2)

Avrajit Roy
Avrajit Roy

Reputation: 3303

Try to incorporate the below snippet it will help to terminate the code whenever you reach any exception. Hope this helps.

DECLARE
  p_err_cd PLS_INTEGER;
  p_err_msg VARCHAR2(32767 CHAR);
  V_COUNT_MENSAJES PLS_INTEGER;
  P_KEY_MENSAJE VARCHAR2(1000 CHAR);
BEGIN
  WHILE V_COUNT_MENSAJES > 0
  LOOP
    BEGIN
      DELETE FROM TB_ICP_ENVIOMENSAJES WHERE KEY_MENSAJE = P_KEY_MENSAJE;
    EXCEPTION
    WHEN OTHERS THEN
      RAISE_APPLICATION_ERROR(-20001,SQLERRM,TRUE);
    END;
  END LOOP;
END;

Upvotes: 2

Anju
Anju

Reputation: 87

You don't have to use "WHENEVER SQLERROR EXIT SQL.SQLCODE;" code inside PLSQL block.Whenever error occur control will pass to exception.Please see the below example for the same.

DECLARE
 V_COUNT_MENSAJES NUMBER:=10;
 ERROR_1 EXCEPTION;
  BEGIN
   WHILE V_COUNT_MENSAJES>0 
     LOOP
       RAISE ERROR_1;
     END LOOP;
 EXCEPTION
 WHEN others THEN
  dbms_output.put_line('Error en la consulta!');  
   END;
    /

Also please note when you’re executing a script, there are many cases in which you want an error to cause SQL*Plus to terminate where we use the following command and not inside plsql block: SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

Upvotes: 0

Related Questions