CODEWITHSUNDEEP
javastored-proceduresoracle11g
Parag
Parag

Reputation: 379

ORA-01843: not a valid month

I want to insert record in oracle11g database table. I am calling a stored procedure from java. While executing stored procedure I am getting following error.

ORA-01843: not a valid month.

I am converting my date from String to Date in java as follows

date=new SimpleDateFormat("MM/dd/yyyy").parse(visitDate);

In my stored procedure I have following insert query 

create or replace PROCEDURE CREATE_VISIT(p_visit_date varchar2)
insert into visit(VISIT_ID,visit_date) values
(frc.SEQ_VISIT.nextval,nvl(to_date(p_visit_date, 'mm/dd/yyyy hh:mi am'), 
sysdate));
END CREATE_VISIT;

Upvotes: 0

Views: 1455

Answers (1)

Justin Cave
Justin Cave

Reputation: 231691

You have a String in Java. You convert that to a Date in Java. So far, presumably, so good.

Your procedure, however, accepts a varchar2. So at some point the Date that you constructed is being explicitly or implicitly cast to a varchar2. You don't say where that is happening (in Java or in PL/SQL) or whether it is being done explicitly or implicitly. If the conversion is being done implicitly in PL/SQL, then that would use your session's NLS_DATE_FORMAT which may be different for different sessions in the same database. In a default US-based install of the database and of the client, that would be DD-MON-RR.

Within your procedure, you then call to_date on the string that was passed in using an explicit format mask. If the string that is passed in is not in the format mm/dd/yyyy hh:mi am you'll either get an error or, worse, you'll get an incorrect result (this is the old question of whether the string 01/02/03 represents January 2, 2003 or February 1, 2003 or February 3, 1901).

If we guess that the Date in Java is being implicitly cast to a varchar2 using your session's default NLS_DATE_FORMAT of DD-MON-YYYY, then we can see why your to_date conversion would fail. If you have a Date of May 30, 2014, for example, that would be implicitly cast to the string "30-MAY-14". If you then try to cast that back to a date using to_date and a format mask of mm/dd/yyyy hh:mi am, Oracle would try to convert the 30 from your string to a month and raise an error since there is no 30th month.

Realistically, you should be using the proper data type throughout and you should be avoiding implicit casts like the plague. If you declare your procedure to take a date parameter, then you can pass a Date from your Java code and omit the to_date call in your procedure. The Date will be passed from Java to PL/SQL without needing to be converted to a varchar2 and then converted back to a date and you won't have to worry about making sure that all your format masks align throughout your code base.

create or replace PROCEDURE CREATE_VISIT(p_visit_date date)
AS
BEGIN
  insert into visit(VISIT_ID,visit_date) 
    values (frc.SEQ_VISIT.nextval,nvl(p_visit_date, sysdate);
END CREATE_VISIT;

Upvotes: 2

Related Questions