This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Oracle JDBC Thin Client behaviour concerning date data types and it's format. Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Oracle JDBC Thin Client behaviour concerning date data types and it Watch "Oracle JDBC Thin Client behaviour concerning date data types and it New topic
Author

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

Richrard Cabangon
Greenhorn

Joined: Jul 13, 2010
Posts: 3
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

Joined: Sep 01, 2005
Posts: 182
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.
    Richrard Cabangon
    Greenhorn

    Joined: Jul 13, 2010
    Posts: 3
    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

    Joined: Sep 01, 2005
    Posts: 182
    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.
    Richrard Cabangon
    Greenhorn

    Joined: Jul 13, 2010
    Posts: 3
    Hi Faith,

    Thanks for your answers!! More power to you! XD
     
    It is sorta covered in the JavaRanch Style Guide.
     
    subject: Oracle JDBC Thin Client behaviour concerning date data types and it's format.
     
    Similar Threads
    Database Abstract Factory
    thin and oci
    Statement.setEscapeProcessing doesn't work!
    JDBC Drivers
    Oracle noob