hi,
we have datatype number in oracle database, but when we try to import the data in datastage it is converting decimal datatype. is it possible to convert as int instead of decmail with out manua intervention.because we are using runtime column propagation for around 100 Tables in same job.
Number datatype converting to decimal datatype while import
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
-
- Participant
- Posts: 81
- Joined: Thu Nov 30, 2006 7:46 am
- Location: india
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
By default you should be getting the fractional part of the values truncated if the Oracle field is define as number(p,s) and the DS metadata for same field is define as integer ( it truncates the fractional part of the decimal value), but I guess that due to run time column propagation you are not defining any
If you like you could try to get the behavior that predate IIS9.1 to see if that help. Before IIS9.1 the Oracle connector if you read Oracle number define with (p,s) to a DS decimal without scale or to integer then the decimal values will be round to integer values (SQLT_CHR), which looks like is what you are looking for, correct?
i.e 123.000 --> 123
123.450 --> 123
123.540 --> 124
Add below user environment variable to your project set to empty, add it to your DS job and set to TRUE
CC_ORA_BIND_DECIMAL_AS_CHAR
Let us know how it goes
If you like you could try to get the behavior that predate IIS9.1 to see if that help. Before IIS9.1 the Oracle connector if you read Oracle number define with (p,s) to a DS decimal without scale or to integer then the decimal values will be round to integer values (SQLT_CHR), which looks like is what you are looking for, correct?
i.e 123.000 --> 123
123.450 --> 123
123.540 --> 124
Add below user environment variable to your project set to empty, add it to your DS job and set to TRUE
CC_ORA_BIND_DECIMAL_AS_CHAR
Let us know how it goes
Julio Rodriguez
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
Not sure if its good to continue on this post, but this being the only post that talks about the problem of interpreting number as decimal in rcp, i thought of continuing on the same.
Coming back to the subject, we have the exact same requirement of not to send the integer as decimals, whereas the oracle connecter is interpreting the number as decimal format which is correct because the number format defined in oracle table has a seperator(NUMBER(7,2) or NUMBER(5,0), But this column never gets a decimal. Ideally we have to change the column data type, but it would be a nightmare to change it in all the tables and corresponding tests etc...
Hence thought of handling them at datastage, and the environment variable "CC_ORA_BIND_DECIMAL_AS_CHAR" proposed here was interesting, but i do not find them in 11.5 https://www.ibm.com/support/knowledgece ... arora.html
Has anyone overcame this issue? Any pointers would be of great help.
Thanks
Coming back to the subject, we have the exact same requirement of not to send the integer as decimals, whereas the oracle connecter is interpreting the number as decimal format which is correct because the number format defined in oracle table has a seperator(NUMBER(7,2) or NUMBER(5,0), But this column never gets a decimal. Ideally we have to change the column data type, but it would be a nightmare to change it in all the tables and corresponding tests etc...
Hence thought of handling them at datastage, and the environment variable "CC_ORA_BIND_DECIMAL_AS_CHAR" proposed here was interesting, but i do not find them in 11.5 https://www.ibm.com/support/knowledgece ... arora.html
Has anyone overcame this issue? Any pointers would be of great help.
Thanks
sen