• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Oracle JDBC Thin Client behaviour concerning date data types and it's format.

 
Rick Sy
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys,
I'm just curious abouth this sql statement here
SQL Statement:

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.

 
Fatih Keles
Ranch Hand
Posts: 182
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Richrard,

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


  • Regards,
    Fatih.
     
    Rick Sy
    Greenhorn
    Posts: 3
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Faith,

    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...
     
    Fatih Keles
    Ranch Hand
    Posts: 182
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    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.
     
    Rick Sy
    Greenhorn
    Posts: 3
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Faith,

    Thanks for your answers!! More power to you! XD
     
    With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic