Invalid Number error when loading data from view

User_E496R

I am facing an invalid number error while loading data from an ORACLE view to an Oracle Table. But, the query generated by ODI (when simulation is run) works perfectly when executed in the target database.

I tried changing all the source and target datatypes to String and VARCHAR2 but the error persists!

Christyxo

There are too many unknowns in your question to give the definitive solution. Where is the error occurring; in the SELECT statement or in the INSERT statement?

Are you positive that the source view is correct? Could there be a datatype conversion error within the view itself?

Can you be sure that your ODI joins are correct. Are you joining a varchar to a number?

Are you filtering your data using the correct datatype?

A transformation in ODI could be wrong ( are you converting a field to a number that could have an alpha character? )

If you are validating your SELECT query in SQL Developer, you need to remember that the results will often only be the first 50 rows which return successfully where the error could be on the next 50, or even the final row of the results. If you expand the number of results, do you get the desired output or does the error now appear?

User_E496R

There are too many unknowns in your question to give the definitive solution. Where is the error occurring; in the SELECT statement or in the INSERT statement? - ODI is fetching the data into a C$ table from the file and then loading the target table from that table. The C$ table is being populated properly. Hence, I am assuming that the problem is occurring in the Final Insert (into Source) statement

Are you positive that the source view is correct? Could there be a datatype conversion error within the view itself? The source view should be correct. As I have mentioned, the ORACLE statement generated by ODI is working in SQL DEVELOPER to insert the rows into the target table.

Can you be sure that your ODI joins are correct. Are you joining a varchar to a number? There are no joins involved. Straight forward 1-1 mapping.

Are you filtering your data using the correct datatype? There are no filters involved. Straight forward 1-1 mapping.

A transformation in ODI could be wrong ( are you converting a field to a number that could have an alpha character? ) - I am doing that but again, the SELECT fetches ONLY the numeric rows, and that is the reason SQLDeveloper works good with the insert here.

If you are validating your SELECT query in SQL Developer, you need to remember that the results will often only be the first 50 rows which return successfully where the error could be on the next 50, or even the final row of the results. If you expand the number of results, do you get the desired output or does the error now appear? - I tried that too. Fetched all rows at a single go, also inserted the whole output into a table. It is only in ODI where the mapping fails.

Also, I see that the mapping gets executed if I use the LOCAL AGENT instead of the Oracle ODI Agent defined.

Christyxo

Thanks for the information. It sounds like your mapping is correct, but the final comment you mentioned in suggestive of a forced language in your agent environment.

This might be system wide or user specific. Changing this would be outside of my knowledge but take a look at the following and see if there is anything in there that might suggest the cause in your environment.

Take a look here: https://www.tecmint.com/set-system-locales-in-linux/

Run the local localectl status and locale -a command and post the outputs here. Also, look into your bash_profile and look for any lines that read LC_ALL=... or LANG=... and post those. If I'm even vaguely close, having those may enable someone else to guide you to a solution.

User_E496R

$ localectl status

System Locale: LANG=en_US.UTF-8

VC Keymap: us

X11 Layout: us

locale -a.txt

I don't have access to the bash profile, hence can't look for the lines there