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.
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.
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.
@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'))
@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:
