Resolving ORA-01841 error

[malru]

Hi,

Please find the procedure below,

create or replace procedure Update1 is

Cursor Cur1 Is
Select NO From MASTER;

RecordCounter Number := 0;

begin

For FM In Cur1
Loop
Update /*+ parallel 8 */ MASTER Set
dateofbirth = To_Date((dateofbirth + 30),'DD/MM/YYYY'),
Where NO = FM.NO;

RecordCounter := RecordCounter + 1;

If RecordCounter >= 10000 then
Commit;
RecordCounter := 0;
End If;
End Loop;

Commit;

end Update1;

while executing this procedure,I'm getting the following error,

ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
ORA-06512: at "UPDATE1", line 13
ORA-06512: at line 1

How to resolve this error?

Thanks..

[PAVB]

My guess?... you have an invalid value in dateofbirth column.

[malru]

Hi,

How to check that invalid value and make this procedure to execute successfully?

Thanks...

[PAVB]

What's your dateofbirth column datatype? Is it DATE?

[malru]

Yes datatype is DATE only, and nls_date_format is DD/MM/RRRR

[PAVB]

OK... assuming you want to update whatever dateofbirth you have there with the same date plus 30 days...

...
Set
dateofbirth = dateofbirth + 30
Where ...

[malru]

Hi,

I executed the procedure by changing the line to,

dateofbirth = dateofbirth + 30

but still I'm getting the same error..

[ixion]

select no, dateofbirth
from MASTER
where to_char(dateofbirth,'YYYY') = 0
or to_char(dateofbirth,'YYYY') <= -4713
or to_char(dateofbirth,'YYYY') >= 9999

[malru]

Hi Ixion,

Sorry for the delay in reply, please find the output for the query given by you,

NO DATEOFBI
---------- ----------
85841 31/12/9999
106319 11/08/9999
85842 31/12/9999

Now tell me What I've to do to resolve this error?

Thanks...