Solved

Why can't I update an Oracle Date field using SqlExecutor

  • 6 August 2019
  • 7 replies
  • 6 views

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.

icon

Best answer by erik_jan 6 August 2019, 15:19

View original

7 replies

Userlevel 2
Badge +17

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.

Userlevel 2
Badge +12

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.

Badge +10

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

Userlevel 2
Badge +12

@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:

Reply