I'm just curious abouth this sql statement here
Apparently this sql statement here does not work on Oracle client manager, since it always says "literal string does not match" error. But apparently it works on Oracle JDBC Thin Client when you execute this sql statement in the java statement. This kinda made me puzzled. What kind of formats does Oracla JDBC Thin Client work on date datatypes. I really would like to be clarified on this.
It really would be appreciated also if there's an article concerning date datatypes for Oracle JDBC. I was looking for one and I can't seem to find this topic on the net.
I'm sorry for the english, and thank you to those who would reply.
Your sql statement involves implicit data type conversion. Your date string is converted to date before execution with some date format mask stored in NLS_DATE_FORMAT session value. Each database client so session declares it's date format mask. The correct and compatible version should be
The error message is self explanatory. Suppose your session declares a format of DD-MON-YYYY than any date string that is to be converted to date without specifying the format should match NLS_DATE_FORMAT string.
In JDBC it is simple use PreparedStatement and setDate method, it would handle the issue.
So long story short,
use PreparedStatement and setDate in JDBC
use to_date('<your_date_string>','<your_date_format>') in SQL/PLSQL
Joined: Jul 13, 2010
Thank you for your reply. Now I understand why my SQL statement works now on JDBC. I would like to have one more clarification please. My SQL Statement is issued using only Statement instead of PreparedStatement. Does this implicit conversion also works on Statement? Thanks in advance...
Joined: Sep 01, 2005
It does not matter if it is a Statement or PreparedStatement. If a table column is to be compared with some value -when you add a where clause to your query- then both sides of the equal sign must have the same data type if not then one of them is to be converted in order to compare them.
Suppose this senario
The above code sample uses a table with a varchar column. It is fine to insert a number since it can be converted to varchar implicitly. So our select statement returns 2 rows.
But after inserting a varchar to table I get an "Invalid Number" error. Because Oracle tries to convert my_table.col1 to number in order to compare it to 15 in my select statement's where clause. It is similiar for date columns.
This one works for me and returns 1
But after I change the format it gives : ORA-01858: a non-numeric character was found where a numeric was expected
The above statement will always work regardless of session date format, because it supplies the format modifier.