title: Exception No data found in oracle plsql procedure
body: <p>I have procedure <code>SET_SUCCESSOR_KPI_STATUS</code> for which when i try to run this procedure i am getting exception as <code>Exception NO_DATA_FOUND in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: 1000206</code>. Because of this exception the entire flow is getting disrupted. The procedure <code>SET_SUCCESSOR_KPI_STATUS</code> is being called by another procedure and if this procedure throws an exception the other procedure are generating an issue. I am not sure is there any way to catch this exception. When trying to call with <code>KPI_DEF_ID</code> this procedure might returns values or it might contain null for which i am i am getting error as no data found. If i remove the exception handling from this procedure and run it then it shows an error in some other procedures but the main cause is in this procedure.</p>
  
  <pre><code>PROCEDURE SET_SUCCESSOR_KPI_STATUS
  -- PUBLIC
  (
    IN_KPI_DEF_ID IN NUMBER DEFAULT 0
  , IN_KPI_STATUS IN CHAR DEFAULT 'N'
  , RET OUT Number
  ) IS
  
  EV Number := 0;
  SUCC Number := 0;
  PARENTS_GREEN Number := 1;
  SUCC_KPI_ACTIVE_INITIAL CHAR;
  SUCC_KPI_ACTIVE_CURRENT CHAR;    
  BEGIN   
  
  SELECT KD.EVENT_ID INTO EV FROM RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.KPI_DEF_ID = IN_KPI_DEF_ID;   
  
  BEGIN
  SELECT E.EVENT_SUCCESSOR_ID INTO SUCC FROM RATOR_MONITORING_CONFIGURATION.EVENT_SUCCESSOR E JOIN RATOR_MONITORING_CONFIGURATION.EVENT IN_EVENT ON E.EVENT_ID = IN_EVENT.EVENT_ID WHERE E.EVENT_ID = EV;
  EXCEPTION WHEN NO_DATA_FOUND THEN
  SUCC := 0;
  END;
  
  WHILE SUCC &gt; 0
  LOOP
  SELECT KPI_ACTIVE_CURRENT INTO SUCC_KPI_ACTIVE_CURRENT from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID =  SUCC;
  SELECT KPI_ACTIVE_INITIAL INTO SUCC_KPI_ACTIVE_INITIAL from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD WHERE KD.EVENT_ID =  SUCC;
  
  UPDATE RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
  SET KD.KPI_ACTIVE_CURRENT = CASE WHEN IN_KPI_STATUS = 'Y' AND (SELECT MONITORING.ARE_PARENTS_GREEN(KD.KPI_DEF_ID) FROM DUAL) = 1 AND SUCC_KPI_ACTIVE_CURRENT &lt;&gt; SUCC_KPI_ACTIVE_INITIAL THEN KD.KPI_ACTIVE_INITIAL WHEN IN_KPI_STATUS = 'N' AND (SUCC_KPI_ACTIVE_CURRENT &lt;&gt; SUCC_KPI_ACTIVE_INITIAL) THEN IN_KPI_STATUS ELSE KD.KPI_ACTIVE_CURRENT END,
  KD.LAST_UPDATED_BY = 115,
  KD.LAST_UPDATED_DATE = CURRENT_DATE
  WHERE KD.EVENT_ID =  SUCC;
  
  BEGIN
  SELECT E.EVENT_SUCCESSOR_ID INTO SUCC FROM RATOR_MONITORING_CONFIGURATION.EVENT_SUCCESSOR E JOIN RATOR_MONITORING_CONFIGURATION.EVENT IN_EVENT ON E.EVENT_ID = IN_EVENT.EVENT_ID WHERE E.EVENT_ID = SUCC;
  EXCEPTION WHEN NO_DATA_FOUND THEN
  SUCC := 0;
  END;
  
  END LOOP;
  
  RET := 1;
  
  EXCEPTION WHEN NO_DATA_FOUND THEN
  DBMS_OUTPUT.PUT_LINE('Exception NO_DATA_FOUND in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: '||TO_CHAR(IN_KPI_DEF_ID));
  RET := 1;
  WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('Exception OTHERS in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: '||TO_CHAR(IN_KPI_DEF_ID));
  RET := 0;
  RAISE;
  
  END SET_SUCCESSOR_KPI_STATUS;
  </code></pre>