Resolving ORA-01841 error

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..

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

Hi,

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

Thanks...

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

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

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

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

Hi,

I executed the procedure by changing the line to,

dateofbirth = dateofbirth + 30

but still I'm getting the same error..

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

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...