CODEWITHSUNDEEP
oracle-databasestored-proceduresexception
Marcus
Marcus

Reputation: 3869

Exception No data found in oracle plsql procedure

I have procedure SET_SUCCESSOR_KPI_STATUS for which when i try to run this procedure i am getting exception as Exception NO_DATA_FOUND in SET_SUCCESSOR_KPI_STATUS KPI_DEF_ID: 1000206. Because of this exception the entire flow is getting disrupted. The procedure SET_SUCCESSOR_KPI_STATUS 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 KPI_DEF_ID 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.

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 > 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 <> SUCC_KPI_ACTIVE_INITIAL THEN KD.KPI_ACTIVE_INITIAL WHEN IN_KPI_STATUS = 'N' AND (SUCC_KPI_ACTIVE_CURRENT <> 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;

Upvotes: 1

Views: 5943

Answers (2)

Tony Andrews
Tony Andrews

Reputation: 132570

Some of your select into statements are already handling NO_DATA_FOUND nicely:

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;

But others are not:

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;

If these selects can legitimately return no rows then you need to handle NO_DATA_FOUND for these too:

BEGIN
   SELECT KPI_ACTIVE_CURRENT INTO SUCC_KPI_ACTIVE_CURRENT
   from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
   WHERE KD.EVENT_ID =  SUCC;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     -- Do what?
     NULL;
END;

BEGIN
   SELECT KPI_ACTIVE_INITIAL INTO SUCC_KPI_ACTIVE_INITIAL
   from RATOR_MONITORING_CONFIGURATION.KPI_DEFINITION KD
   WHERE KD.EVENT_ID =  SUCC;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
     -- Do what?
     NULL;
END;

Upvotes: 2

Hooloovoo
Hooloovoo

Reputation: 2181

If there is no data available the assignment of the value will fail. Instead of using the SELECT INTO statement consider using another cursor and setting a default value.

You may also want to check the select statement you are using. It may be that data in the joined table doesn't exist, so won't find any data to return.

I expect to get shot down for this, but I would nest a cursor to get this data using the SUCC value as an argument.

You are also assigning a value to an argument in the query. It would be better to assign the value to another variable and add update it out of the query. This way the query is less likely to trip itself up.

Upvotes: 0

Related Questions