• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ORA-01861: literal does not match format string

 
Marta Oliva
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 )
VALUES
(5433,'877989',0.0,0.0,0.0,0.0,0.0,0.0,'no localfa',0,'2004.09.01
20:00:17',1,0.0,0.0,-1)
/

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
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Jules
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34974
379
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Marta,
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.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic