can't insert field from flat file into Oracle numeric column

, ,
1

I have a flat file I’m trying to insert into an Oracle table. I have the JDBC adapter working fine and everything looks to be working fine until I get to the adapter call in my flow (simple insert operation). The field I’m having issues with is an ID and the field column is defined as Number(20) on the Oracle side. In my FF schema it has the field type set as java.math.BigDecimal. When it was that, I got the error the string couldn’t be cast into that. After reading the forum I thought there woul dbe SetAsString for this ID but that wasn’t an option for some reason. It’s an option for the date fields, but for this ID the only options were big decimal, object, and string. There is no setAsString. I changed it to string just in case but when I ran the flow I got an error (NumberFormat Exception for field: PERSON_NUMBER. Error: null)…this is a required field on the database.

Can anyone tell me how to get around this and/or why SetAsString isn’t there? Is there something I’m missing on inserting into a number column? I’ve also attached screenshots to show what I have set up.

Thanks for any help or guidance.

Damon

jdbcAdapter.PNG
jdbcAdapter.PNG920×510 37.9 KB

database.PNG

2

Hi Damon,

what about setting the field in the JDBC Adapter to java.util.String and let the JDBC Adapter do the transformation?
You should receive an Exception when the ID does contain not only numeric characters.

The Date fields should be set to java.util.Date instead of java.sql.Date.
You might need to transform the date strings into date objects, but there are some Built-In Services for that in WmPublic package.

Regards,
Holger

3

Holger,

Thanks for the reply. I did try setting the field to a string and that’s when I got the null field error (as though it wasn’t’ doing the insert at all). I changed the date fields as you suggested but this PersonID field only has java.math.bigDecimal, java.lang.string, and java.lang.object as the format options. It seems each format has its own error when I run the flow with it set to those.

Thanks again for any help you can suggest.

Thanks,
Damon

4

Hi,

please provide the error messages for the different data formats so we can check in detail where the issue arises.

Looks like there might be a mapping issue before it comes to the JDBC Adapter.

Regards,
Holger

5

Ok…it’s finally working! I changed the input field type to java.lang.string and then modified the Loop indices to be correct and everything was inserted correctly. Thanks to everyone for all the guidance and help.

Damon