-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Hi,
How to check that invalid value and make this procedure to execute successfully?
Thanks...
-
What's your dateofbirth column datatype? Is it DATE?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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 ...
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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...
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
|