ORA-06502: PL/SQL: numeric or value error

[Rajkartha]

Please help me

CREATE OR REPLACE PROCEDURE BDTUpdate IS
rdt date;
bdt date;
YrNo number(6);
DtChar VARCHAR2(10);
CURSOR CVL_cur IS
select a.PATIENTID , a.AGE
from tbpatient a
where Birthdt IS NULL
AND PATIENTID IN (SELECT DISTINCT PATIENTID FROM TBSAMPLE)
AND AGE IS NOT NULL;
begin
FOR CVLRec IN CVL_cur
LOOP
SELECT TO_CHAR(RECEIVEDDT,'dd-mm-yyyy') INTO DtChar
FROM TBShipment
WHERE SHIPMENTNO = (SELECT MIN(SHIPMENTNO) FROM TBSAMPLE WHERE PATIENTID = CVLRec.PATIENTID)
AND RECEIVEDDT IS NOT NULL;
YrNo := substr(DtChar,7,4);
IF cvlrec.age < 1 THEN
DtChar := '01-JAN-' || to_char(YrNo-1);
ELSIF cvlrec.age < round(cvlrec.age)+.5 THEN
DtChar := '01-JAN-' || to_char(YrNo-(round(cvlrec.age) + 1));
ELSE
DtChar := '01-JAN-' || to_char(YrNo-ROUND(cvlrec.age,1));
END IF;
update TBPAtient
set BIRTHDT=DtChar
WHERE PATIENTID = CVLRec.PATIENTID;
END LOOP;
END BDTUpdate;

This procedure is giving me error

3 11-01-2001
3 2001
BEGIN BDTUpdate; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "AMGENTB21DBO.BDTUPDATE", line 25
ORA-06512: at line 1

[jmodic]

DtChar VARCHAR2(10);

DtChar := '01-JAN-' || to_char(YrNo-1);

'01-JAN-' occupies 7 characters, so you are having only 3 characters left on DtChar for the appending string. It seems to me that you are trying to append 4-digit string (YrNo := substr(DtChar,7,4); ), which the variable DtChar can not accept.

[balajiyes]

the error is coz the length of the variable DtChar is insufficient to hold a 11-digit string. the error occurs here -

DtChar := '01-JAN-' || to_char(YrNo-1);

[Rajkartha]

ThanX a lot. Problem solved !!!!!