Home » SQL & PL/SQL » SQL & PL/SQL » Stored procedure date type compare ORA-01839 (Oracle 11g, RHEL 5)
Stored procedure date type compare ORA-01839 [message #420496] Sun, 30 August 2009 20:31 Go to next message
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

  RETURN;
 END IF; 


What's wrong with me?

Thanks in advance
Re: Stored procedure date type compare ORA-01839 [message #420497 is a reply to message #420496] Sun, 30 August 2009 20:36 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
>IF D >= SYSDATE + INTERVAL '1' MONTH THEN
try using ADD_MONTHS() function instead
Re: Stored procedure date type compare ORA-01839 [message #420505 is a reply to message #420496] Sun, 30 August 2009 23:20 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
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 Go to previous messageGo to next message
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 #420524 is a reply to message #420522] Mon, 31 August 2009 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bonker wrote on Mon, 31 August 2009 09:05
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...

How could this be an invalid date for OP and not for me?
I hope this is not the expected behaviour.

Regards
Michel

Re: Stored procedure date type compare ORA-01839 [message #420527 is a reply to message #420524] Mon, 31 August 2009 02:13 Go to previous messageGo to next message
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 #420531 is a reply to message #420527] Mon, 31 August 2009 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But I am not sure why procedure is not failing for you or for me?

It is not failing for us because it is a valid expression in this case:
SQL> select TO_DATE('2009-08-30 23:47:21', 'yyyy-MM-dd HH24:mi:ss') + INTERVAL '1' MONTH 
  2  from dual
  3  /
TO_DATE('2009-08-30
-------------------
30/09/2009 23:47:21

1 row selected.

Regards
Michel
Re: Stored procedure date type compare ORA-01839 [message #420533 is a reply to message #420531] Mon, 31 August 2009 02:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #420539 is a reply to message #420533] Mon, 31 August 2009 04:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59100
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
bonker wrote on Mon, 31 August 2009 09:48
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?

Doh! Seem I am dumb this morning...

I will investigate on it as soon as I have a moment.

Regards
Michel

icon7.gif  Re: Stored procedure date type compare ORA-01839 [message #420607 is a reply to message #420496] Mon, 31 August 2009 20:02 Go to previous messageGo to next message
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 #420608 is a reply to message #420496] Mon, 31 August 2009 20:12 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
  1* select add_months(sysdate,1) code_done_right from dual
SQL> /

CODE_DONE_RIGHT
-------------------
2009-09-30 18:11:59
Re: Stored procedure date type compare ORA-01839 [message #420610 is a reply to message #420607] Mon, 31 August 2009 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 22783
Registered: January 2009
Senior Member
bbmonster wrote on Mon, 31 August 2009 18:02


I solved it by simply adding 30 days.




If run on the 1st of some months, you will obtain WRONG! results.
icon14.gif  Re: Stored procedure date type compare ORA-01839 [message #420611 is a reply to message #420496] Mon, 31 August 2009 21:02 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Dear BlackSwan

Thank you very much.

I just know that add_months is a system function.
Re: Stored procedure date type compare ORA-01839 [message #420624 is a reply to message #420539] Tue, 01 September 2009 00:55 Go to previous message
Michel Cadot
Messages: 59100
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

Previous Topic: NUMBER EQUALS
Next Topic: problem with DBMS_DATAPUMP
Goto Forum:
  


Current Time: Wed Sep 17 05:18:50 CDT 2014

Total time taken to generate the page: 0.18034 seconds