Question

FME changes a number (0.8) (SQLExecutor) into a string (windows-1252) and results in a error.

  • 22 December 2021
  • 3 replies
  • 1 view

This is the error message:

The database generated an error while attempting to convert the string `0.8' to an OCINumber, so alternative number parsing was performed. (serverType=`ORACLE8I', serverName=`****', userName=`*******', password=`***', dbname=`')

 

It doesn't matterhow you cast the number in the sql query.


3 replies

Badge +2

@j.brugge​ I couldn't reproduce your issue. I've attached an example workspace that perhaps you can try and manipulate to replicate the problem. Or attached a simplified version of your data and SQL. Also let us know what version of FME you're working with. This example is FME 2021.2

@j.brugge​ I couldn't reproduce your issue. I've attached an example workspace that perhaps you can try and manipulate to replicate the problem. Or attached a simplified version of your data and SQL. Also let us know what version of FME you're working with. This example is FME 2021.2

@Mark Stoakes​. Since i'm new here I also made a 'case'. I'm not familiar with the basic community rules. So maybe i'll wait for the answer on my case and I'll post it here?

 

I'm using FME(R) 2021.1.1.0 (20210730 - Build 21619 - WIN64).

------------------------

--SQL to fill database

------------------------

select

  CAST ('0.8' as varchar(3)) as "CASTASVARCHAR",

  cast (0.8 as number) as "CASTASNUMBER",

  0.8 as Zonder

from dual

 

 

 

------------------------

--SQL to create table test

------------------------

 

Create table test

(

 DWH_ID number GENERATED by default ON NULL AS IDENTITY, 

 CASTASvarchar VARCHAR2(3),

 CASTASNUMBER number(2,1),

 Zonder number(2,1),

 DWH_STARTDATUM   DATE,

 DWH_MUTATIEDATUM   DATE default sysdate not null,

 CONSTRAINT test_PK PRIMARY KEY (DWH_ID)

);

 

GRANT SELECT, INSERT, UPDATE, DELETE ON test TO DIENSTVERLENING_ETL;

GRANT SELECT ON test TO DIENSTVERLENING_REPORT;

 

COMMENT ON TABLE test IS 'TEST';

 

commit;

Userlevel 4

It might be a locale issue. Perhaps try using a StringReplacer to change "0.8" to "0,8" before the SQLExecutor and see if that helps.

Reply