Error : Type mismatch: cannot convert from int to BigDecimal

[_AnonymousUser]

Hi,
I am working with an Oracle database and number column are translated to BigDecimal in java. But I always have a type mismatch.
Is there a workaround ? Besides, the number column is the PK and is loaded with an oracle sequence.
Thanks,
Jean Marie

[Anonymous]

Well I don't have a single column of the type Int or Integer in the Oracle database that I have to work with. Using Number instead is part of the design and coding conventions . It somehow makes sense as INT, INTEGER and even SMALLINT are sub-types or synonyms of NUMBER ( which one could name a "master type" )
see: http://www.psoug.org/reference/datatypes.html
But for the talend point of view this is still a rather worrying issue :
1. You use a tOracleInput on a repository schema that translated your int or number to bigdecimal.
2. you do whatever stuff you have to do on this data.
3. you try to write it back to Oracle , what does it do ? it tries to put a decimal type where there is not ?
hence those type mismatch ... maybe I'm wrong somewhere.
I got to say I'm only beginning to learn to use talend with Oracle.
It's a little bit off-topic but I have to say those date patterns that you cannot avoid to set drive me crazy.
Anyway Talend is still quite handy when working with files.

[Anonymous]

Hi,
J agree that

INT, INTEGER and even SMALLINT are sub-types or synonyms of NUMBER, ...

but there is an incompatibility when migrating the jobs ...
My jobs are migrated from 2.3 to 2.4 and 3.0.0.
So, J've some expressions in tMap like:

!aa10.AA10_CEPLB.equals("N")?L4:aa10.AA10_CEREMP.equals("P")?L3: aa10.AA10_CEREMP.equals("D")?L1:L2

It worked well < 2.4 because the Talend's out type for my column was Long (INTEGER -> NUMBER -> LONG)
With BigDecimal it generates:

Type mismatch. cannot convert from long to Bigdecimal.
L4 cannot be resolved ...

J'll have to modify all existing jobs
Regards
msomso

[Anonymous]

Hi,
Even when J correct the tMap expressions, somme errors subsist:
- my schemas "IN" are defined with 2.3 and contain the datatype Long/long for database INTEGER columns.
- my new schemas "OUT" are reinitialised with 3.0 (new or modified table) and contain the datatype BigDecimal for database INTEGER columns
J've to do an explicite conversion for each BigDecimal out column: from long (in data) to BigDecimal (new out type).
Before 2.4 it wasn't necessary as J've had IN=long and OUT=long types.
Each job has to be modified when the schema is retrived (table modified).
Regards
msomso

[Anonymous]

... the joys of upgrading.
I don't know what was possible in 2.3 but if you used metadata repository instead of "built-in" for your schemas and queries, you should only change your metadata or maybe refresh / re-retrieve it after you modified oracle xml mapping file ( it worked for me).
change (in the java part)

to this :

Any further solution I must admit I don't know.

off-topic: Why do you use " J " like in " John " instead of " I " for the first person singular ? nothing serious but kind of weird to read.

[Anonymous]

Hi,
Global retriving - good idea, but I use also the built in schemas (lookup tables, when few columns are needed).
Built In = manual upgrade of type
I don't know what solution is preferable.
Thank you for your interest (and for your off-topic ).
Regards
msomso

[Anonymous]

Thanks for the tip DavidD above.
However I have a similar request which is can the default datatypes be overridden for non-database metadata? eg: Delimited Files.
I have some pipe-delimited files where the import of Metadata defaults to CHAR and DOUBLE when I'd like to default it to STRING and INTEGER. It can be quite time consuming going through every file to change the data types and some can get missed easily. The problem is that all the destination databases are Oracle which require a conversion to change CHAR to VARCHAR and DOUBLE to INTEGER as per the DB Schema, but String and Integer are handled without any issues.
Thanks.
Tim S

[Anonymous]

Can anyone post any latest update on this Issue ?

[Anonymous]

Try with below expression for the Big Decimal coloumn:

new BigDecimal(row1.colum_name)

hope this will resolve your error.