Twin problems with Oracle jdbc and sqllr

Question

I am facing two issues which I believe may be related.

I am trying to execute a JDBC select against oracle (10g) database and result set shows the date column as a timestamp

    ResultSetMetaData rsMetaData = null;  
    rsMetaData = resultSet.getMetaData();  
    if(rsMetaData.getColumnType(1) == java.sql.Types.TIMESTAMP){
        System.out.println("Timestamp");
    }

The datatype for this column in oracle is DATE so I would exptect columnType returned by resultSetMeta to be DATE but it is always TIMESTAMP.

After processing this data I am trying to load the same to Oracle database using sqlldr Because of the wrong data type recevied in the input the output control file uses TIMESTAMP. (This control file is created by my program based on the resultSetMetaData.) Here is exactly how it appears in the control file.

TIMESTAMP "yyyy-MM-dd HH24:mi:ss.ff"  

The problem starts when I try to load the following data using this control file.

1987-06-17 00:00:00.0

The sqlldr successfully loads this data into Oracle without any warnings, but when I check the target table I get the date as '16-Jun-1987' instead of the expected/required '17-Jun-1987'. This may be because of the way the time "00:00:00.0" is interpreted by oracle.

Both of the problems 1 and 2 above are serious issues for me and I could not find an explanation.

Answer 1

The SQL standard DATE type does not have a time component; Oracle's implementation does, with second precision, so even if you only store a date you get the time for free.

If you insert a specific date, e.g. to_date('06/05/2012', 'DD/MM/YYYY') or literal date '2012-05-06', the time will be midnight. If you insert a value with a time, such as SYSDATE, it will be preserved. Either way, if you query through SQL*Plus or similar withouy specifying a date format mask that includes the time it may look like it is just a date, but the time will be there and just not displayed.

In JDBC the Oracle DATE has to be treated as an SQL TIMESTAMP or the time portion would be lost. If you don't want the time in Jave you can cast it to a date type, but I don't think it will help your second problem.

The SQL*Loader problem looks like it might be a timezone issue. It appears that the date you're using is OK, or at least consistent (and even if you lost the explicit time, Oracle would treat it as 00:00 anyway; see the data types).

I can only guess that wherever you're running sqlldr from is in a diferent timezone to where you're querying, possibly from an environment variable. If one knows it is summertime and the other doesn't, for example, your query might be showing 23:00 on the 16th - you need to query with an explicit date format mask to check - because of the hour difference between GMT/BST, EST/EDT etc. Without knowing more about your environment(s) it's hard to tell what's happening. At a wild guess though, you may be calling SQL*Loader from Java, and I can imagine that causing confusion.