-
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
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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);
Cheers!
OraKid.
-
ThanX a lot. Problem solved !!!!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|