This content has been marked as final. Show 3 replies
try using this code to remove some characters at the end of the input values.
regexp_replace(:1, ''[[:cntrl:]]'', null))
A couple of things you may run into:
Conversion restrictions: when converting from numeric to character datatypes (and vice-versa) the implicit conversion is not always available. In your data load procedure, I recommend explicitly casting ALL non-text fields from character into their appropriate datatypes.
Definitional restrictions: you may be trying to insert a numeric value that is too big for the specified precision+scale of your number datatype. You may have to round, etc. in order to fix this.
Date restrictions: you can also get an error complaining about a numerical violation when dealing with dates. Make sure you are explicitly converting the dates according to the specified date format model.
Or how about just putting everything in a table with varchar columns only. Then try one of these
i) run a insert into orig_table select col1, cols2..collast from varchar_table
ii) select to_number(col1) from varchar_table; test all the number columns with this and you will soon find which column has issues.
I had the same error and I found that the last column was coming with a carriage return so I replaced all non printable characters on the last column.