ORA-01861 literal does not match format string

dataservices job design
1

My source (flat file) contains a date column with the following format:
‘2010-12-04 00:00:00’ -> ‘yyyy-mm-dd hh24:mi:ss’

I’m using a Query and a Table Comparison to import data into a database table of an oracle database.
When I start the Job to IMPORT data, then the import of the date column works fine. But the data column is converted in the database to:
‘04.12.2010 00:00:00’ -> ‘dd.mm.yyyy hh24:mi:ss’

When I now start the Job to UPDATE data, then the import of the date column fails. I receive the following error message:
ORA-01861 literal does not match format string

How can I handle the different date formats during the UPDATE?

iksoll :de: (BOB member since 2010-04-27)

2

Hi,

I suggest you to use YYYY.MM.DD HH24:MI:SS.

I hope it works for you.

Thanks,
Raghu.

BODI_USA (BOB member since 2010-11-14)

3

This SQL is generating by DI on UPDATE:

UPDATE TABLE_DATA SET NAME1 = 'Spain',  NAME2 = 'San_Marino',  
VALID_DATE = '2010-10-04 00:00:00',  IMPORT_DATE = '2011-01-07 16:36:25'   
WHERE NAME1 = 'Spain' AND  NAME2 = 'San_Marino' 
AND  VALID_DATE = '2010-10-04 00:00:00' AND  IMPORT_DATE = '2011-01-07 15:35:17';

As you can see DI doesn’t covert the date format. How can I force the conversion of the date format?

iksoll :de: (BOB member since 2010-04-27)

4

Hi,

Have you defined the format of the field in the File Format editor.

Under File Format -> Default Format -> Date-time = yyyy-mm-dd hh24:mi:ss

sameer81 :new_zealand: (BOB member since 2007-09-15)

5

The problem here is that you are assuming that the default DATE format on the job server is YYYY.MM.DD HH24:MI:SS. I NEVER assume a date format anywhere. I ALWAY explicitly convert date strings into date data types.

Somewhere in your Dataflow (presumably in the Query transform) you should explicitly convert the string to a date data type.

to_date(file_format.date_field, ‘YYYY.MM.DD HH24:MI:SS’)

eganjp :us: (BOB member since 2007-09-12)

6

Inside my file_format I declared the date column as string (varchar(50)).
Inside a Query transform I explicitly convert the string to a date data type using the following function:

to_date(file_format.date_field, 'YYYY.MM.DD HH24:MI:SS')

When I start the job now, then first a Warning appears and then an Error message:

 Warning: Cannot convert string <2010-10-04 00:00:00> to date using format string <YYYY.MM.DD HH24:MI:SS>.
Error: ODBC data source <XXX> error message for operation <SQLExecute>: <[Oracle][ODBC][Ora]ORA-01400: cannot insert null into ("file_format.date_field")

Remember, my source (flat file) contains a date column, that is now declared as string with the following format:

'2010-12-04 00:00:00' -> 'yyyy-mm-dd hh24:mi:ss'

iksoll :de: (BOB member since 2010-04-27)

7

You should use

to_date(file_format.date_field, 'YYYY-MM-DD HH24:MI:SS')

Regards,
Sameer

sameer81 :new_zealand: (BOB member since 2007-09-15)

8

I’m using:

to_date(file_format.date_field, 'YYYY-MM-DD HH24:MI:SS')

When I now start the Job to UPDATE data, then the import of the date column fails. I receive the following error message:

ORA-01861 literal does not match format string

UPDATE TABLE_DATA SET NAME1 = 'Spain',  NAME2 = 'San_Marino', 
VALID_DATE = '2010-10-04 00:00:00',  IMPORT_DATE = '2011-01-07 16:36:25'   
WHERE NAME1 = 'Spain' AND  NAME2 = 'San_Marino'
AND  VALID_DATE = '2010-10-04 00:00:00' AND  IMPORT_DATE = '2011-01-07 15:35:17';

Because of the UPDATE command, I suspect that DI is trying to compare strings with each other. And this fails because the formats don’t match:

'2010-10-04 00:00:00' (DI) <> 'dd.mm.yyyy hh24:mi:ss' (Oracle)

How can I force DI to compare date values?

iksoll :de: (BOB member since 2010-04-27)

9

Hi,

Can’t you update the field to Datetime in the Source File format and also specify the format.

Under File Format -> Default Format -> Date-time = yyyy-mm-dd hh24:mi:ss

Regards,
Sameer

sameer81 :new_zealand: (BOB member since 2007-09-15)

10

I have updated the field to Datetime in the Source File format and I also specified the format.
Under File Format → Default Format → Date-time = yyyy-mm-dd hh24:mi:ss

In my Query transform I now don’t use any function. Just a simple mapping of the fields (Source->Target). The result is the same:

ORA-01861 literal does not match format string

UPDATE TABLE_DATA SET NAME1 = 'Spain',  NAME2 = 'San_Marino',
VALID_DATE = '2010-10-04 00:00:00',  IMPORT_DATE = '2011-01-07 16:36:25'   
WHERE NAME1 = 'Spain' AND  NAME2 = 'San_Marino'
AND  VALID_DATE = '2010-10-04 00:00:00' AND  IMPORT_DATE = '2011-01-07 15:35:17';

:hb: :hb: :hb:

iksoll :de: (BOB member since 2010-04-27)

11

Hi,

You have 2 datetime fields in the source VALID_DATE and IMPORT_DATE. Have you declared datatypes of these fields as ‘Datetime’ in the File. Also check the query transform for datatypes of these fields.

Regards,
Sameer

sameer81 :new_zealand: (BOB member since 2007-09-15)

12

The field IMPORT_DATE is not in the source. I’m using the function sysdate() in my query transform to set the IMPORT_DATE in the target.
The source contains the field VALID_DATE only. Please see attached my Source File format and my query transform.
ORA-01861.png

iksoll :de: (BOB member since 2010-04-27)

13

I’m not sure what’s going on now. Given the following update statement it looks like DI continues to think your column is a string.

UPDATE TABLE_DATA SET NAME1 = 'Spain',  NAME2 = 'San_Marino', 
VALID_DATE = '2010-10-04 00:00:00',  IMPORT_DATE = '2011-01-07 16:36:25'    
WHERE NAME1 = 'Spain' AND  NAME2 = 'San_Marino' 
AND  VALID_DATE = '2010-10-04 00:00:00' AND  IMPORT_DATE = '2011-01-07 15:35:17';

I expected DI to pass the date in as an actual DATE data type to Oracle. Ignoring that issue the next problem is that the date format used on the job server does not match the date format on the Oracle database server. Run this query to figure out what the default date format is on your Oracle database server:

SELECT value FROM v$nls_parameters WHERE parameter ='NLS_DATE_FORMAT';

My local Oracle 10g database running on Windows 2000 shows: MM-DD-YYYY HH24:MI:SS

Get the setting that your job server is using by running this query. I think running it from SQLPlus would be good enough but running it from a SQL() function in DI would eliminate any doubts.

select parameter, value from nls_session_parameters;

You’ll probably need to verify more than one of the parameters. My client machine running Vista with an Oracle 10.2 client shows the following:

You can change the date format on the Oracle database server using the following command:

alter system set nls_date_format = 'YYYY-MM-DD HH24:MI:SS' scope=spfile;

I would be VERY reluctant to change this setting unless your system is the only one using the Oracle database. You could potentially cause problems with a lot of other applications which (stupidly, in my opinion) are relying on the default date format.

The date format can also be set for the session using the following code:

alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';

But this isn’t sticky (it isn’t held between connect/disconnect) and you have no way to do this in DI (DS however can do this).

If you are running the JOB SERVER on Windows then make sure there is an NLS_DATE_FORMAT registry entry for your Oracle home. On my client the registry key is HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient10g_home1 and the registry entry is NLS_DATE_FORMAT. This may not already exist. If not, just add it and make the format match your Oracle database server’s format. I just tested this on my client and after closing and reopening SQLPlus the session parameter changed from DD-MON-RR to MM-DD-YYYY HH24:MI:SS (the value in the registry).

This information is from the Oracle manual on globalization support and relates somewhat to this discussion

eganjp :us: (BOB member since 2007-09-12)

14

The image you posted is completely unreadable. Try posting an image with better clarity and in the JPG format.

eganjp :us: (BOB member since 2007-09-12)

15

My local Oracle database shows:

DD.MM.RR

I have run the select statement from Oracle SQL Developer. My output is:

NLS_LANGUAGE	GERMAN
NLS_TERRITORY	GERMANY
NLS_CURRENCY	€
NLS_ISO_CURRENCY	GERMANY
NLS_NUMERIC_CHARACTERS	,.
NLS_CALENDAR	GREGORIAN
NLS_DATE_FORMAT	DD.MM.RR
NLS_DATE_LANGUAGE	GERMAN
NLS_SORT	GERMAN
NLS_TIME_FORMAT	HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT	DD.MM.RR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT	HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT	DD.MM.RR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY	€
NLS_COMP	BINARY
NLS_LENGTH_SEMANTICS	BYTE
NLS_NCHAR_CONV_EXCP	FALSE

I have added the NLS_DATE_FORMAT registry entry for my Oracle home.

Value: DD.MM.RR

Unfortunately, the result is the same:

ORA-01861 literal does not match format string

UPDATE TABLE_DATA SET NAME1 = 'Spain',  NAME2 = 'San_Marino',
VALID_DATE = '2010-10-04 00:00:00',  IMPORT_DATE = '2011-01-07 16:36:25'   
WHERE NAME1 = 'Spain' AND  NAME2 = 'San_Marino'
AND  VALID_DATE = '2010-10-04 00:00:00' AND  IMPORT_DATE = '2011-01-07 15:35:17';

iksoll :de: (BOB member since 2010-04-27)

16

Based on what you posted (NLS_DATE_FORMAT = DD.MM.RR) I don’t see how the date format of either the client or the server is coming into play. That tells me that something in your Dataflow is explicitly generating a string from a date using a specific format. Can you post an ATL file that contains an export of the Dataflow and all the associated tables?

eganjp :us: (BOB member since 2007-09-12)

17

Is the update statement really the statement is it was generated by DS?

Why is it not using parameters? update col1= :AlVariable1 …

That is the root cause of all. You are using the Oracle driver, not ODBC, yes? Nothing in the triggers tab of the table loader for updates?

Werner Daehn :de: (BOB member since 2004-12-17)

18

I got it. :smiley: :smiley: :smiley:
I changed the Database type in my Datastore settings from ODBC to Oracle.
Now DI donesn’t use ODBC to connect to the database and now all problems disappeared.

iksoll :de: (BOB member since 2010-04-27)