The information you provide isn't really enough to help you.
Can you please share some more information e.g. screenshot of the mapping?
Understanding C programming helps in understanding the problem.
The point is that a string is handed over to a NUMBER data type. That means that the Oracle client will invoke a certain standard C function to translate the string into a numeric value. And this C function translates as much of the string into a number as possible. Meaning that if the string doesn't start with digits, the resulting number will be zero.
This is normal behaviour and cannot be changed.
What you can do depends on what exactly you need to achieve. For example, if an ERROR() function would be ok for your mapping, you can insert an Expression in which you use something like this:
IIF( Is_Number( in_string), To_Decimal( in_string), ERROR( in_string || ' is not numeric.'))
If, however, you need this record to be rejected and listed in the .bad file for the relational target, then there's only one workaround (not a clean solution) which I see:
Define the respective attribute as NOT NULL in the database. Then ensure that - if this string is not a number - a NULL value is written to this target. This will lead to the record being rejected and being written to the .bad file due to the NULL value.
If I misunderstood what you need, please clarify with an example.
Thanks for your reply. And i think your understanding is correct. But i cant make the attributes as not null, because there is a possibility of null value to those fields.
Just see below an illustration with one field
Source(csv file) Target(Oracle)
abc Number Datatype
Source ----> SQ ------>Exp Trans --------> Target
Initially until expression, the fields are string datatype.
Then to fix this, the below options are tried
1. Changed the data type in Expression to double, integer, decimal and nothing worked out
2. Created output port and tried with conversion functions.
The problem is that under these circumstances there is NO way to get these records into the .bad file without a little "help" from the DBAs.
What exactly does the overall process look like? If you could tell us why exactly the .bad file is needed and what it's used for, we might be able to find a viable solution.
I capture all the bad records in informatica error tables and those records will be fetched to csv file using mapping.
Then I have a different proposal.
Instead of extracting from the error log tables, simply use a Router transformation to send the "good" records to the real target(s) and the "bad" records to an "error table". Then you don't need the second mapping.