ORA-01403: no data found -- Exception handling not working

Question

BEGIN
FOR v_LoadRec IN c_Load LOOP
    SELECT   count(1) INTO v_NO_OF_DAYS_RESP
    from     DIM_DATE
    where    DIM_DATE.TRADING_DAY_FLAG = 'Y' and
             DIM_DATE_KEY <= TO_NUMBER(TO_CHAR(v_LoadRec.RESPONSE_DATE,'YYYYMMDD')) and
             DIM_DATE_KEY >= TO_NUMBER(TO_CHAR(v_LoadRec.OPEN_DATE, 'YYYYMMDD'))
    group by  v_LoadRec.CALL_NUMBER;
    IF SQL%NOTFOUND THEN
     v_NO_OF_DAYS_RESP :='';
    END IF;

    SELECT   count(1) INTO v_NO_OF_DAYS_RESO
    from     DIM_DATE
    where    DIM_DATE.TRADING_DAY_FLAG = 'Y' and
         DIM_DATE_KEY <= TO_NUMBER(TO_CHAR(v_LoadRec.RESOLVE_DATE,'YYYYMMDD')) and
         DIM_DATE_KEY >= TO_NUMBER(TO_CHAR(v_LoadRec.OPEN_DATE, 'YYYYMMDD'))
    group by  v_LoadRec.CALL_NUMBER;
    IF SQL%NOTFOUND THEN
     v_NO_OF_DAYS_RESO :='';
    END IF;

END LOOP;

I have this block of SQL in my update procedure which gathers the count of trading days for each record and then inserts it into an integer variable named "v_NO_OF_DAYS_RESP" e.g. count of days between the open and response date of a call.

This works well except for when there is a null "RESPONSE_DATE" where it fails with the "ORA-01403: no data found" error. I understand why it's failing (because it of course has no record to insert) but I can't seem to figure out a way to get around it.

In these circumstances where the "RESPONSE_DATE" is found to be NULL, I would like the "v_NO_OF_DAYS_RESP" var to be set to NULL too (or even somehow have the SQL statement nested within an "IF" to possibly completely avoid running the calculation (SQL statement) when the "RESPONSE_DATE" is NULL).

*To put it really simple, I want the following:.. If the call does not yet have a response date, either don't run the SQL statement (calculation) or just set the var to Null

Any ideas or suggestions would be greatly appreciated.

Thanks - Kelvin

Answer 1

 if (v_LoadRec.RESPONSE_DATE) is null Then
     v_NO_OF_DAYS_RESP:='';
 else
    SELECT      count(1) INTO v_NO_OF_DAYS_RESP
    from    DIM_DATE
    where   DIM_DATE.TRADING_DAY_FLAG = 'Y' and
        DIM_DATE_KEY <= TO_NUMBER(TO_CHAR(v_LoadRec.RESPONSE_DATE, 'YYYYMMDD')) and
        DIM_DATE_KEY >= TO_NUMBER(TO_CHAR(v_LoadRec.OPEN_DATE, 'YYYYMMDD'))
    group by  v_LoadRec.CALL_NUMBER;
    IF SQL%NOTFOUND THEN
       v_NO_OF_DAYS_RESP :='';
    END IF;
 end if;