I'm reading an Oracle Date field in my reader, and then using that date to update a Date field in another Oracle table using SQLExecutor, but I'm getting "`ORA-00932: inconsistent datatypes: expected DATE got NUMBER'." How is that possible?
Any help and explanation will be much appreciated.
Best answer by erik_jan
The internal FME date format is a number. To be able to write this to Oracle using the SQL statement in the SQLExecutor, you will need to format the data to the Oracle format (DD-MON-YYYY by default) first, using the DateTimeConverter transformer.
Hope this helps.
Hi @fritspoulsen12, check if the date format used in the SQL statement is appropriate to SQL syntax for Oracle DB. I suspect that you built the SQL statement using a date value formatted in the FME standard date format %Y%m%d (e.g. '20190806').
@takashi. Hi. I'm not using any formatting, because the input date type is the same as the output date type. When doing an update using a writer, if works without any formatting. My SQL statement in the executor looks simply like this:
UPDATE sch_enkeltstik.ansoegning SET DATOOPRETTET = @Value(OPRETTET) where ID = @Value(NY_ID)
If I'm supposed to specify any formatting to copy one date to another of same type, please advice me on how to specify it in the SQLExecutor.
+26The internal FME date format is a number. To be able to write this to Oracle using the SQL statement in the SQLExecutor, you will need to format the data to the Oracle format (DD-MON-YYYY by default) first, using the DateTimeConverter transformer.
Hope this helps.
+54@takashi. Hi. I'm not using any formatting, because the input date type is the same as the output date type. When doing an update using a writer, if works without any formatting. My SQL statement in the executor looks simply like this:
UPDATE sch_enkeltstik.ansoegning SET DATOOPRETTET = @Value(OPRETTET) where ID = @Value(NY_ID)
If I'm supposed to specify any formatting to copy one date to another of same type, please advice me on how to specify it in the SQLExecutor.
I don't have oracle to hand but think you need something like, assuming it's a date not a date time
UPDATE sch_enkeltstik.ansoegning SET DATOOPRETTET = (TO_DATE(@Value(OPRETTET)
, 'yyyymmdd')) where ID = @Value(NY_ID)
@erik_jan. Thx Erik. However I can't make it work.
The date field looks like this in an SQL developer (dd-mm-yy-tt-mm-ss): 
In FME Inspector it looks like this (yyyymmddttmmss):
20120807000000
Can you give an example on, how the DateTimeConverter should look?
Thx to all of you. Problem solved. This works in SQLExecutor:
UPDATE dbtable SET datefield = (TO_DATE(@Value(inputdate), 'YYYYMMDDHH24MISS'))
+26@erik_jan. Thx Erik. However I can't make it work.
The date field looks like this in an SQL developer (dd-mm-yy-tt-mm-ss): In FME Inspector it looks like this (yyyymmddttmmss):
20120807000000
Can you give an example on, how the DateTimeConverter should look?
I believe this is what it should look like:
