Home » SQL & PL/SQL » SQL & PL/SQL » Resolving ORA-01841 error
Resolving ORA-01841 error [message #239062] Mon, 21 May 2007 07:01 Go to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
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..
Re: Resolving ORA-01841 error [message #239069 is a reply to message #239062] Mon, 21 May 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Which line is line 13?

This expression "To_Date((dateofbirth + 30),'DD/MM/YYYY')" is wrong. The first parameter must be a string.

Regards
Michel
Re: Resolving ORA-01841 error [message #239078 is a reply to message #239069] Mon, 21 May 2007 07:39 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member
Hi Michel,

Yes the error is on this line only,

To_Date((dateofbirth + 30),'DD/MM/YYYY')

Please tell me,how can I change the this line,so that procedure will execute successfully.

Thanks...

Re: Resolving ORA-01841 error [message #239084 is a reply to message #239078] Mon, 21 May 2007 08:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not just "dateofbirth = dateofbirth + 30"?

Regards
Michel
Re: Resolving ORA-01841 error [message #239308 is a reply to message #239062] Tue, 22 May 2007 00:34 Go to previous messageGo to next message
gajini
Messages: 259
Registered: January 2006
Senior Member


Thanks Michel,

I changed the line but still I'm getting the same,how to resolve it...
Re: Resolving ORA-01841 error [message #239311 is a reply to message #239308] Tue, 22 May 2007 00:42 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Copy and paste here your screen, compilation (with line number) and execution with a "describe" of your table.

Regards
Michel
Previous Topic: SQL Notes
Next Topic: Last Analyzed from USER_INDEXES
Goto Forum:
  


Current Time: Thu Dec 08 20:10:23 CST 2016

Total time taken to generate the page: 0.08019 seconds