I have below stored procedure where i am getting error as ORA-01403: no data found . I think this error is coming for Select INTO query clause where NAME value is getting null. The value i am passing from input parameter.
While executing the procedure if i give the IN_NAME parameter value which exist in NAME value column in M_LOG table then my procedure is executing correctly and the first if clause is inserting the value correctly.
But as soon as while executing the procedure if i pass the IN_NAME parameter value which does not exist in NAME value column in M_LOG table then i am getting the error as ORA-01403: no data found and
also the condition which needs to be executed in ELSE clause also failing.
I am not sure how can i handle such conditions as either the IF or ELSE conditions should be executed correctly.
CREATE OR REPLACE PROCEDURE C_CHCK
(IN_NAME IN VARCHAR2)
AS
I_MAR VARCHAR2(40);
BEGIN
Select NAME INTO I_MAR FROM M_LOG WHERE NAME = IN_NAME
and C_DATE = CASE --If the day of the month is the 1st, 2nd or 3rd then it will use the last day of the previous month otherwise it will use the last day of the current month
WHEN EXTRACT( DAY FROM SYSDATE ) <= 3
THEN TRUNC( SYSDATE, 'MM' ) - INTERVAL '1' DAY
ELSE LAST_DAY( TRUNC( SYSDATE ) ) END and rownum=1;
IF (I_MAR IS NOT NULL)
THEN
--Insert using select query to load the record for Monthly Check for Step-1 with STATUS 0(SUCCESSFUL) where ‘Daily_check’ ran for Markets and E_ID
INSERT INTO M_LOG(NAME, E_ID, L_ID, C_DATE, N_CHK, NET, IH, I_PUB, STATUS, P_CHECK)
Select MD.NAME, MD.E_ID, MD.L_ID, C_DATE, MD.N_CHK, MD.NESSOFT, MD.IDH, MD.INT_PUB, 0, 'M_1'
from M_LOG MD
WHERE MD.NAME = IN_NAME and
MD.C_DATE = CASE --If the day of the month is the 1st, 2nd or 3rd then it will use the last day of the previous month otherwise it will use the last day of the current month
WHEN EXTRACT( DAY FROM SYSDATE ) <= 3
THEN TRUNC( SYSDATE, 'MM' ) - INTERVAL '1' DAY
ELSE LAST_DAY( TRUNC( SYSDATE ) ) END;
ELSE
--Insert using select query to load the record for Monthly Check for Step-1 with status 1(WARNING) where ‘Daily_check’ not ran for Markets and E_ID
INSERT INTO M_LOG(NAME, E_ID, C_DATE, STATUS, P_CHK)
Select ML.NAME, ML.E_ID, CASE --If the day of the month is the 1st, 2nd or 3rd then it will use the last day of the previous month otherwise it will use the last day of the current month
WHEN EXTRACT( DAY FROM SYSDATE ) <= 3
THEN TRUNC( SYSDATE, 'MM' ) - INTERVAL '1' DAY
ELSE LAST_DAY( TRUNC( SYSDATE ) ) END, 1, 'M_1' from DIR_LOG ML
WHERE ML.NAME = IN_NAME;
END IF;
END C_CHCK;