java.sql.SQLDataException: ORA-01843 while deploying in Weblogic

Question

I am trying to perform a select operation on my oracle db. I have kept my queries in a property file in my application which is as follows:

SELECT EMPLOYEE FROM EMPLOYEETABLE WHERE
JOINING_DATETIME >= ?

The JOINING_DATETIME is a timestamp column in oracle db and its value looks like 04-03-20 07:12:27.150000000 PM

I am supplying the value for JOINING_DATETIME as a String from my application like 04-03-20 05:41:52 PM and it works very well and do the necessary as expected while running in Tomcat servers.

But the problem is when the same has been deployed into Weblogic application I'm getting the error:

java.sql.SQLDataException: ORA-01843: not a valid month

Could someone please help me out with this?

Lennart · Accepted Answer · 2020-06-30 09:34:08Z

3

Supply the value for JOINING_DATETIME as a java.sql.Timestamp instead of a String.

answered Jun 30, 2020 at 9:34

Lennart

913 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Popeye · Accepted Answer · 2020-06-30 07:41:28Z

1

You need to convert the string to timestamp using the proper format in your query as follows:

SELECT EMPLOYEE
  FROM EMPLOYEETABLE
 WHERE JOINING_DATETIME >= TO_TIMESTAMP(?,'DD-MM-RR HH:MI:SS.FF AM');

Update:

Regarding your doubt of AM/PM:

SQL> select TO_TIMESTAMP('04-03-20 07:12:27.150000000 PM','DD-MM-RR HH:MI:SS.FF AM') from dual;
TO_TIMESTAMP('04-03-2007:12:27.150000000PM','DD-MM-RRHH:MI:SS.FFAM')
---------------------------------------------------------------------------
04-MAR-20 07.12.27.150000000 PM

SQL> select TO_TIMESTAMP('04-03-20 07:12:27.150000000 AM','DD-MM-RR HH:MI:SS.FF AM') from dual;

TO_TIMESTAMP('04-03-2007:12:27.150000000AM','DD-MM-RRHH:MI:SS.FFAM')
---------------------------------------------------------------------------
04-MAR-20 07.12.27.150000000 AM

SQL>

edited Jun 30, 2020 at 7:41

answered Jun 30, 2020 at 7:35

Popeye

36k4 gold badges12 silver badges31 bronze badges

5 Comments

Subinraj.S Over a year ago

Is there any format available for mentioning AM/PM. Because it comes dynamically. Also I executed as you mentioned JOINING_DATETIME >= to_timestamp('04-03-20 05:41:52 AM','DD-MM-RR HH:MI:SS FFFFFFFFF AM') But I am getting ORA-01810: format code appears twice 01810. 00000 - "format code appears twice"

Popeye Over a year ago

See the update section!! There is no need to worry. If you pass PM in your string it will take PM. AM is just to indicate that it can be AM/PM in the actual string. I have updated the answer to include only FF instead of 9 F

Subinraj.S Over a year ago

Thanks Tejash for your update. I checked it by executing in db and it works. But I need to test my application once for this. I have a doubt like why the later query worked fine in tomcat but not in weblogic ?

Popeye Over a year ago

It depends on the NLS setting of the sessions initiated by the different tools/clients. You must read about the NLS and you should not use string. It is not recommended at all. You must convert the string to timestamp and use it in the query so that it is executed from anywhere (independent of NLS setting of the session)

Subinraj.S Over a year ago

Thanks again Tejash. I have one more question like, if I do to_timestamp in all my queries instead of changing my code for not passing as a string. Will this result as a costly operation in means of performance ?

Collectives™ on Stack Overflow

java.sql.SQLDataException: ORA-01843 while deploying in Weblogic

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related