query working in sqldeveloper and not from java jdbc

[M. Phaneendra Vijay]

Hi,

I have this query

INSERT INTO MAIN_TABLE(START_DATE1) SELECT RESULT_TIME AS START_DATE1 FROM TEMP_TABLE

START_DATE1 is timestamp(9) and RESULT_TIME is timestamp(6) and I am using Oracle 10g.
The above query works if i execute it from sql developer or from sqlplus prompt.

The same thing if executed from java program using jdbc (ojdbc14.jar) is throwing the following exception:
java.sql.SQLException: ORA-01861: literal does not match format string

at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:113)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:754)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:963)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1192)
at oracle.jdbc.driver.OracleStatement.executeUpdateInternal(OracleStatement.java:1662)
at oracle.jdbc.driver.OracleStatement.executeUpdate(OracleStatement.java:1631)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:228)
at com.pyro.huawei.bss.pm.dao.BSSDAOImpl.insertIntoMainTable(BSSDAOImpl.java:641)
at com.pyro.huawei.bss.pm.service.BSSServiceImpl.insertIntoMainTable(BSSServiceImpl.java:77)
at com.pyro.huawei.bss.pm.load.BSSOracleLoader.loadFileToDatabase(BSSOracleLoader.java:288)
at com.pyro.huawei.bss.pm.classes.HuaweiBSSUpdater.processFiles(HuaweiBSSUpdater.java:214)
at com.pyro.huawei.bss.pm.classes.HuaweiBSSUpdater.processBSSUpdater(HuaweiBSSUpdater.java:310)
at com.pyro.huawei.bss.pm.classes.BSSUpdaterScheduler.run(HuaweiBSSUpdater.java:490)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471)
at java.util.concurrent.FutureTask$Sync.innerRunAndReset(FutureTask.java:351)
at java.util.concurrent.FutureTask.runAndReset(FutureTask.java:178)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.access$201(ScheduledThreadPoolExecutor.java:165)
at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:267)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at java.lang.Thread.run(Thread.java:636)


Thanks in advance.
Phaneendra Vijay.

[Martin Vashko]

The ORA-01861 error indicates a conversion from textual format is taking place. Post the java code so that we can see what's happening there.

Also, I've never used ojdbc14.jar and I don't want to claim this is connected to your issue, but unless you're on Java 1.4, I'd suggest moving to newer version of the JDBC client.

[M. Phaneendra Vijay]

Hi,

Thanks for your reply.
I am using JDK1.6 and I have downloaded the latest jdbc driver file for Oracle 10g from the Oracle site, which suggested me to use ojdbc14.jar (previously I was using the classes12.jar).

After searching on the internet, I found that it could be an issue with the NLS_TIMESTAMP_FORMAT. Now how can I set that from the java code? My java code is 24 * 7 / 365 running program. The program runs every hour.

[Martin Vashko]

M. Phaneendra Vijay wrote:I am using JDK1.6 and I have downloaded the latest jdbc driver file for Oracle 10g from the Oracle site, which suggested me to use ojdbc14.jar (previously I was using the classes12.jar).

I'm using ojdbc5.jar with Oracle 10g with no problems. Oracle clients are generally compatible with any version of the database. However, this is probably unrelated to your issue and I wouldn't want to hint you to switch without proper testing on your side.

After searching on the internet, I found that it could be an issue with the NLS_TIMESTAMP_FORMAT. Now how can I set that from the java code? My java code is 24 * 7 / 365 running program. The program runs every hour.

Yes, the ORA-01861 is connected with the NLS_TIMESTAMP_FORMAT setting, because it indicates error in the textual conversion of the date or timestamp. However, the statement you've posted should not lead to a textual conversion of any sort. So unless you post the code for us to see, we can't help you resolving the issue.

[M. Phaneendra Vijay]

The query and the columns are generated dynamically. Because of this, I posted a general query.
This is the code I use.

	public boolean insertIntoMainTable(String tableId, String bssTableColumns, String version, String oss_id) throws BSSDAOException {
		String method_name = class_name + "::insertIntoMainTable(String tableId, String bssTableColumns, String version, String oss_id)::";
		BSSLogs.logInfo(method_name + ENTRY);

		Connection conn = null;
		Statement stmt = null;
		boolean isRecordInsertSuccessful = false;
		try {
			conn = getConnection();
			stmt = conn.createStatement();
			HashMap<String, BSSTableIdsVO> bssTableIdsHashMap = getBSSTableIds(conn);
			BSSTableIdsVO bssTableVO = bssTableIdsHashMap.get(tableId);
			
			StringBuffer sql = new StringBuffer("");
			
			sql.append("INSERT INTO M_").append(bssTableVO.getTableName()).append(" (")
			.append(bssTableVO.getiTab()).append(",").append(bssTableColumns).append(", OSS_ID")
			.append(") SELECT ").append(bssTableVO.getiQuery()).append(",").append(bssTableColumns)
			.append(", '").append(oss_id).append("' FROM ").append(version).append("_")
			.append(bssTableVO.getTableName());
			
			//System.out.println("Query: "+sql);
			BSSLogs.logInfo("Query: "+sql);
			BSSLogs.logInfo(bssTableVO.toString());
			
			int count = stmt.executeUpdate(sql.toString());
			if(count >= 0) {
				isRecordInsertSuccessful = true;
			}
			conn.commit();
		}
		catch(BSSDAOException bssde) {
			try {
				conn.rollback();
				throw bssde;
			}
			catch(SQLException se) {
				throw new BSSDAOException("Cannot rollback the committed the data.");
			}
		}
		catch(SQLException se) {
			BSSLogs.logError("SQLException", se);
			throw new BSSDAOException(se);
		}
		catch(Exception e) {
			BSSLogs.logError("Exception", e);
			throw new BSSDAOException(e);
		}
		finally {
			closeStatement(stmt);
			closeConnection(conn);
		}
		BSSLogs.logInfo(method_name + EXIT);
		return isRecordInsertSuccessful;
	}

[M. Phaneendra Vijay]

I executed the following query from java and also in SQLPLUS

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_TIMESTAMP_FORMAT'


From java, I got the result: DD-MM-RR HH12:MI:SSXFF AM

In SQLPLUS, I got the result YYYY-MM-DD HH24:MI

In SQLDeveloper, I got the result: DD-MM-RR HH12:MI:SSXFF AM
So in SQLDeveloper Tools -> Preferences -> Database -> NLS, I changed the TIMESTAMP FORMAT to YYYY-MM-DD HH24:MI:SS, and the query worked.

So what change should I make for it to work from Java?

Thanks.

[Martin Vashko]

You can change the timestamp format using ALTER SESSION SET NLS_TIMESTAMP_FORMAT='<whatever you want>' command. However, your problem is that an implicit conversion is taking place somewhere. Consider this small demonstration I've created (it was run on 11g, but I'm sure it would do the same on 10g):
SQL> create table t1(note varchar2(50), x timestamp(9)); Table created. SQL> create table t2(y timestamp(6)); Table created. SQL> create table t3(z varchar2(100)); Table created. SQL> alter session set NLS_TIMESTAMP_FORMAT='YYYYMMDD'; Session altered. SQL> insert into t2(y) values(systimestamp); 1 row created. SQL> -- The standard way SQL> insert into t1(note, x) select 'normal', y from t2; 1 row created. SQL> -- Now with implicit conversion SQL> insert into t3(z) select y from t2; 1 row created. SQL> insert into t1(note, x) select 'text', z from t3; 1 row created. SQL> select note, to_char(x, 'DD.MM.YYYY HH24:MI:SS') from t1; NOTE TO_CHAR(X,'DD.MM.YY -------------------------------------------------- ------------------- normal 18.11.2011 14:58:08 text 18.11.2011 00:00:00
You can clearly see that the NLS_TIMESTAMP_FORMAT does not affect the insert where no conversion takes place (marked as 'normal'). Only the second insert, where an implicit conversion to varchar2 and back is enforced, the NLS_TIMESTAMP_FORMAT does affect the outcome (by only retaining the date part).

My bet is that one of the columns you think is TIMESTAMP is actually a VARCHAR2. I'd suggest to double-check this. I can't think of any other mechanism that would cause the implicit conversions to happen, but I cannot rule that possibility out completely.

By the way, setting the timestamp format might seem like a solution to the problem, but once you do this, your application will become fragile. If in future someone else faces another similar issue and decides to solve it by changing the timestamp format yet again, your current issue will resurface. Track down and root out every implicit conversion in your code, they are evil.

[M. Phaneendra Vijay]

Hi,

I found the problem with my query. The problem was not this timestamp to timestamp column. It was another column which does this
TO_CHAR(RESULT_TIME,'YYYY-MM-DD')
where RESULT_TIME is a timestamp and it cannot be converted to the YYYY-MM-DD format, which is throwing the error "literal does not match format string"

I changed that to CAST(RESULT_TIME AS DATE) and now it is working fine.

In SQL Developer I changed the NLS parameters for DATE format, which is why it was working in the sqldeveloper.

Sorry for troubling and thanks for your reply.

[Martin Vashko]

M. Phaneendra Vijay wrote:Hi,

I found the problem with my query. The problem was not this timestamp to timestamp column. It was another column which does this
TO_CHAR(RESULT_TIME,'YYYY-MM-DD')
where RESULT_TIME is a timestamp and it cannot be converted to the YYYY-MM-DD format, which is throwing the error "literal does not match format string"

I changed that to CAST(RESULT_TIME AS DATE) and now it is working fine.

In SQL Developer I changed the NLS parameters for DATE format, which is why it was working in the sqldeveloper.

Sorry for troubling and thanks for your reply.

Good to hear you've find the problematic spot.

However, your interpretation of it is not exactly right. The TO_CHAR(RESULT_TIME,'YYYY-MM-DD') cannot fail. The TO_CHAR function could fail if you put an illegitimate format string to it, but YYYY-MM-DD is perfectly valid. What was really happening there is that somewhere down the execution path, this expression, which is actually a VARCHAR2, was being assigned to a DATE or TIMESTAMP column (this is where the 'evil' implicit conversion takes place), while the corresponding NLS FORMAT parameter has been set to an incompatible format string.

I'd be still wary about the CAST(RESULT_TIME AS DATE). Your previous posts seem to indicate that you're in effect trying to copy a TIMESTAMP column from one table to another. Casting it to DATE in this process will certainly lose the milliseconds of the original timestamp, as DATE cannot hold milliseconds, and if it actually was a TIMESTAMP WITH TIMEZONE, the timezone information would get lost too. Even if you assign the timestamp to a date, a cast is not needed (though make sure you understand the ramifications, especially any time-zone related conversions).

In short, you should not need the cast. What happens if you drop the cast and just leave the RESULT_TIME column as it is?