File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes ORA-01861: literal does not match format string Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "ORA-01861: literal does not match format string" Watch "ORA-01861: literal does not match format string" New topic

ORA-01861: literal does not match format string

Marta Oliva

Joined: Sep 02, 2004
Posts: 1
Hi all,

i am crazy trying to find a solution to my problem.
I have read the other thread also related to this error but didn not found my solution.
I have a test and a production application.
They both use the same code but suddently, some time ago, the test application started showing problems with the error literal does not match format string.

I have set both test and production nls_date_format on the database exactly the same but still, on production everything works fine but on test the problem happens.

I knew that some time ago there was a crash in the database test machine that can help causing this problem.

I have done a trace to find some of the queries where this problem is happening.

One of them is this:
SQL> INSERT INTO schema1.table1( idt , iduser, x , center , ra ,ra2 , an1 , an2 , loc , vel, time , pro, xa , ya, error )
(5433,'877989',0.0,0.0,0.0,0.0,0.0,0.0,'no localfa',0,'2004.09.01

Is i run this in prod, the same error appears but the code is exactly the same in both environments so, this querie HAS TO WORK.

The only thing i'm seeing is that date - '2004.09.01 20:00:17' comming as a string but is the same in prod.

Since the nls is the same in prod and test this had to work.

The nls is set to dd-mon-yy which is not the same as the string i'm inserting.Also, this date is comming from the system.

The tst system has 2 machines - one for the database and one for the application;
Production has more machines but also one independent for database.

Can anyone help me please?

Thanks in advance,
Julian Kennedy
Ranch Hand

Joined: Aug 02, 2004
Posts: 823
Hi Marta,

Welcome to JavaRanch!

It's a bit of an odd place to be posting an Oracle error IMHO, but I'll try and help anyway. A Google search for the error message yielded this page (amongst others).

The general message seems to be to use to_date explicitly. Give it a go!

As to why you get different results in your different environments, perhaps they're different versions? If not then perhaps you need to run some kind of consistency check to make sure that all's well after the recent crash.

Anyway, by not using to_date explicitly you're not using recommended practice. If the solution works I wouldn't waste your time worrying about why the strange behaviour occurred.

Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33098

Did you verify that the schema is exactly the same in both environments?

Consider using a prepared statement in the future. It can help avoid this sort of problem.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Peter den Haan
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
Originally posted by Marta Oliva:
i am crazy trying to find a solution to my problem.
In my experience, getting all the formats right in Oracle can be a nightmare. I'm not a DBA so I cannot provide you with the gory details. But from a software developer's point of view, I've got two questions for you:
  • Are you using PreparedStatement and parameters as opposed to using Statement and generating all the SQL text yourself? PreparedStatement improves the effectiveness of the Oracle SQL cache, sidesteps all formatting issues, prevents problems with special characters in Strings, and protects you from SQL injection attacks.
  • If you cannot avoid generating your own SQL, have you considered using SQL date escape syntax, such as {ts '2004-09-03 09:49:31'}? This, too, will sidestep the vagaries of database format settings.
  • HTH

    - Peter
    I agree. Here's the link:
    subject: ORA-01861: literal does not match format string
    It's not a secret anymore!