| Stored procedure date type compare ORA-01839 [message #420496] |
Sun, 30 August 2009 20:31  |
bbmonster
Messages: 47
Registered: June 2007
Location: UB
|
Member |
|
|
Dear all,
I have something wrong with my stored procedure. It seems to work, but it doesn't work in this case. I can't execute the procedure with following parameter. Red marked line is error throwing line.
begin
SP_ADD2T0101 ('AABB11', 'IMAABB11', TO_DATE('2009-08-30 23:47:21', 'yyyy-MM-dd HH24:mi:ss'));
end;
ORA-01839: date not valid for month specified
ORA-06512: at "INFORMANT.SP_ADD2T0101", line 15
ORA-06512: at line 2
CREATE OR REPLACE PROCEDURE INFORMANT.SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE) IS
DT DATE;
BEGIN
IF D >= SYSDATE + INTERVAL '1' MONTH THEN
What's wrong with me?
Thanks in advance
|
|
|
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420505 is a reply to message #420496] |
Sun, 30 August 2009 23:20  |
 |
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> CREATE OR REPLACE PROCEDURE INFORMANT.SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE) IS
2 DT DATE;
3 BEGIN
4
5 IF D >= SYSDATE + INTERVAL '1' MONTH THEN
6
7 RETURN;
8 END IF;
9
10 end;
11 /
CREATE OR REPLACE PROCEDURE INFORMANT.SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE) IS
*
ERROR at line 1:
ORA-01435: user does not exist
Do NOT put schema or storage information in your code and put a complete code.
SQL> CREATE OR REPLACE PROCEDURE SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE) IS
2 DT DATE;
3 BEGIN
4
5 IF D >= SYSDATE + INTERVAL '1' MONTH THEN
6
7 RETURN;
8 END IF;
9
10 end;
11 /
Procedure created.
SQL> begin
2 SP_ADD2T0101 ('AABB11', 'IMAABB11', TO_DATE('2009-08-30 23:47:21', 'yyyy-MM-dd HH24:mi:ss'));
3 end;
4 /
PL/SQL procedure successfully completed.
Nothing wrong for me.
Use SQL*Plus and copy and paste your session.
Post your date/time related NLS parameters (V$NLS_PARAMETERS).
Regards
Michel
[Updated on: Sun, 30 August 2009 23:22] Report message to a moderator |
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420522 is a reply to message #420496] |
Mon, 31 August 2009 02:05 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
This expected behavior as per ANSI/ISO committee which says: adding 1 month increments the value of the month attribute of the date. If it gives an invalid date, the database should raise an exception..
You can read about the same here.
Interval Arithmetic
You should therefore use add_months function, as Blackswan suggested since it does not have this effect.
|
|
|
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420527 is a reply to message #420524] |
Mon, 31 August 2009 02:13 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
| Quote: | How could this be an invalid date for OP and not for me?
I hope this is not the expected behaviour.
|
What does your sysdate return?
XE@SQL> select sysdate from dual;
SYSDATE
--------------------
31-Aug-2009 12:40:59
1 row selected.
XE@SQL> select sysdate + interval '1' month from dual;
select sysdate + interval '1' month from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
XE@SQL> select date '2009-01-31' + interval '1' month from dual;
select date '2009-01-31' + interval '1' month from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
XE@SQL> select date '2009-01-28' + interval '1' month from dual;
DATE'2009-01-28'+INT
--------------------
28-Feb-2009 00:00:00
1 row selected.
XE@SQL> select date '2009-01-29' + interval '1' month from dual;
select date '2009-01-29' + interval '1' month from dual
*
ERROR at line 1:
ORA-01839: date not valid for month specified
XE@SQL>
[Update] But I am not sure why procedure is not failing for you or for me?
XE@SQL> ed
Wrote file afiedt.buf
1 CREATE OR REPLACE PROCEDURE SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE) IS
2 DT DATE;
3 BEGIN
4 IF D >= SYSDATE + INTERVAL '1' MONTH THEN
5 RETURN;
6 END IF;
7* end;
8 /
Procedure created.
XE@SQL> ed
Wrote file afiedt.buf
1 begin
2 SP_ADD2T0101 ('AABB11', 'IMAABB11', TO_DATE('2009-08-30 23:47:21', 'yyyy
-MM-dd HH24:mi:ss'));
3* end;
XE@SQL> /
PL/SQL procedure successfully completed.
[Updated on: Mon, 31 August 2009 02:19] Report message to a moderator |
|
|
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420533 is a reply to message #420531] |
Mon, 31 August 2009 02:48 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
| Quote: | It is not failing for us because it is a valid expression in this case:
|
But in the code the Interval is added to sysdate and not to the parameter that was passed.
SYSDATE + INTERVAL '1' MONTH
I therefore thought it would fail if sysdate is 31-Aug-2009. May be I have completely missed something and do not know what it is?
[Updated on: Mon, 31 August 2009 02:49] Report message to a moderator |
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420534 is a reply to message #420533] |
Mon, 31 August 2009 03:11 |
bonker
Messages: 402
Registered: July 2005
|
Senior Member |
|
|
I was able to reproduce the error, if there is something other than "Return" or "Null" is mentioned between the if clause.
XE@SQL> CREATE OR REPLACE PROCEDURE SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE)
IS
2 DT DATE;
3 BEGIN
4 IF D >= SYSDATE + INTERVAL '1' MONTH THEN
5 --dbms_output.put_line(d);
6 RETURN;
7 END IF;
8 end;
9 /
Procedure created.
XE@SQL>
XE@SQL> begin
2 SP_ADD2T0101 ('AABB11', 'IMAABB11',
3 TO_DATE('2009-08-30 23:47:21', 'yyyy-MM-dd hh24:mi:ss'));
4 end;
5 /
PL/SQL procedure successfully completed.
XE@SQL> CREATE OR REPLACE PROCEDURE SP_ADD2T0101(MS VARCHAR, I VARCHAR, D DATE)
IS
2
3 DT DATE;
4 BEGIN
5 IF D >= SYSDATE + INTERVAL '1' MONTH THEN
6 dbms_output.put_line(d);
7 RETURN;
8 END IF;
9 end;
10 /
Procedure created.
XE@SQL>
XE@SQL> ed
Wrote file afiedt.buf
1 begin
2 SP_ADD2T0101 ('AABB11', 'IMAABB11',
3 TO_DATE('2009-08-30 23:47:21', 'yyyy-MM-dd hh24:mi:ss'));
4* end;
XE@SQL> /
begin
*
ERROR at line 1:
ORA-01839: date not valid for month specified
ORA-06512: at "SCOTT.SP_ADD2T0101", line 5
ORA-06512: at line 2
XE@SQL>
Now I do not know if this expected behavior of Oracle PLSQL.
|
|
|
|
|
|
 Re: Stored procedure date type compare ORA-01839 [message #420607 is a reply to message #420496] |
Mon, 31 August 2009 20:02 |
bbmonster
Messages: 47
Registered: June 2007
Location: UB
|
Member |
|
|
Thank you experts,
Currently Oracle doesn't add one month when date is 31th of August. Because September has only 30 days. I.e:
select to_date('2009/08/31','yyyy/MM/dd') + interval '1' month
from dual
ORA-01839: date not valid for month specified
I solved it by simply adding 30 days.
...
IF D >= SYSDATE + INTERVAL '30' DAY THEN
RETURN;
END IF;
...
Have a nice day
[Updated on: Mon, 31 August 2009 20:06] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Stored procedure date type compare ORA-01839 [message #420624 is a reply to message #420539] |
Tue, 01 September 2009 00:55 |
|
Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
I found this is a (unfortunate) PL/SQL optimizer bug:
SQB> CREATE OR REPLACE PROCEDURE SP_ADD2T0101 (MS VARCHAR, I VARCHAR, D DATE)
2 IS
3 DT DATE;
4 BEGIN
5 IF D >= TO_DATE('31/08/2009','DD/MM/YYYY') + INTERVAL '1' MONTH THEN
6 RETURN;
7 END IF;
8 END;
9 /
Procedure created.
SQB> exec SP_ADD2T0101 ('AABB11', 'IMAABB11', sysdate);
PL/SQL procedure successfully completed.
Now change the optimizer level to 1 (default is 2):
SQB> CREATE OR REPLACE PROCEDURE SP_ADD2T0101 (MS VARCHAR, I VARCHAR, D DATE)
2 IS
3 DT DATE;
4 BEGIN
5 IF D >= TO_DATE('31/08/2009','DD/MM/YYYY') + INTERVAL '1' MONTH THEN
6 RETURN;
7 END IF;
8 END;
9 /
Procedure created.
SQB> exec SP_ADD2T0101 ('AABB11', 'IMAABB11', sysdate);
BEGIN SP_ADD2T0101 ('AABB11', 'IMAABB11', sysdate); END;
*
ERROR at line 1:
ORA-01839: date not valid for month specified
ORA-06512: at "MICHEL.SP_ADD2T0101", line 5
ORA-06512: at line 1
You have the error.
In "Database PL/SQL Language Reference", chapter 12 "Tuning PL/SQL Applications for Performance", section "How PL/SQL Optimizes Your Programs", it is stated:
| Quote: | In even rarer cases, you might see a change in exception action, either an exception that is not raised at all, or one that is raised earlier than expected. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.
|
Regards
Michel
[Updated on: Tue, 01 September 2009 00:56] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
