OCI Stage - Issue with reading decimal values

vincentjeevan · Post by **vincentjeevan** » Mon Dec 15, 2008 3:15 pm

Hi, I am reading data from an Oracle table using OCI stage but the decimal data seems to be appending additional digits at the end. For example 1234 is appended as 12344. I have this issue only in Prod environment but not in the development environment. Please advise. Any inputs on this will he highly appreciated.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 15, 2008 3:40 pm

What is different between production and development? ("Nothing" is not the correct answer.)

SURA · Post by **SURA** » Mon Dec 15, 2008 3:48 pm

Hi

Try to read it as varchar.

Ram

vincentjeevan wrote:Hi, I am reading data from an Oracle table using OCI stage but the decimal data seems to be appending additional digits at the end. For example 1234 is appended as 12344. I have this issue only in Prod environment but not in the development environment. Please advise. Any inputs on this will he highly appreciated.

chulett · Post by **chulett** » Mon Dec 15, 2008 3:51 pm

Shouldn't be necessary.

If I had to guess, I'd venture one that you have a different Oracle client installed in Production.

vincentjeevan · Post by **vincentjeevan** » Mon Dec 15, 2008 4:28 pm

Yes. I just noticed that the Oracle client versions are different between prod and dev datastage server. (Prod-10.1.0.5.0 and dev-10.2.0.1.0). But I believe DS should work fine in both versions. Is there a known issue with either of these oracle versions?

chulett wrote:Shouldn't be necessary.

If I had to guess, I'd venture one that you have a different Oracle client installed in Production. ...

chulett · Post by **chulett** » Mon Dec 15, 2008 4:34 pm

This isn't a DataStage issue but rather an Oracle one. What version is your Oracle database - 10.1 or 10.2? Sure seems like you should get the 10.2 client onto production since that's the one working for you.

vincentjeevan · Post by **vincentjeevan** » Mon Dec 15, 2008 4:37 pm

My database version is 8.1.7.4.0. It is same in both prod and dev.

chulett wrote:This isn't a DataStage issue but rather an Oracle one. What version is your Oracle database - 10.1 or 10.2? Sure seems like you should get the 10.2 client onto production since that's the one w ...

chulett · Post by **chulett** » Mon Dec 15, 2008 4:48 pm

Oh my, even worse news. There is no guarantee of compatibility from Oracle between new clients and old databases. However, old clients can generally be used without issue against newer databases.

If that 8.1 database is your only Oracle source/target, then you really need to get a matching 8.1 client installed on the DataStage server for it to use. If you have a mix of database versions, then the solution becomes more problematical depending on said mixture. We use a 9i client to access both 9i and 10g databases without issue, but I would wager neither of those 10.x clients will be completely issue free accessing that 8 instance. You may think everything is working, but I'd be concerned that more strange things like those extra digits are waiting for you in the wings.

vincentjeevan · Post by **vincentjeevan** » Mon Dec 15, 2008 5:14 pm

Thanks. I think keeping client & oracle versions in sync is the solution for this. But changing the version not possible here as the oracle client is used by few other applications as well.

As a temporary fix I used TO_CHAR() function to convert all Decimal values to VARCHAR in OCI stage and then converting it back to decimal in Transformer stage.

Also adding a SORT BY clause on the decimal field seems to be working. But I am not sure if it will work when there are more than one decimal field in the query (I only have one decimal field in my query).

If there is any other fix to this problem please let me know.

chulett wrote:Oh my, even worse news. There is no guarantee of compatibility from Oracle between new clients and old databases. However, old clients can generally be used without issue against newer databases.

If that 8.1 database is your only Oracle source/target, then you really need to get a matching 8.1 client installed on the DataStage server for it to use. If you have a mix of database versions, then the solution becomes more problematical depending on said mixture. We use a 9i client to access both 9i and 10g databases without issue, but I would wager neither of those 10.x clients will be completely issue free accessing that 8 instance. You may think everything is working, but I'd be concerned that more strange things like those extra digits are waiting for you in the wings.

chulett · Post by **chulett** » Mon Dec 15, 2008 5:19 pm

Didn't say you needed to change the Oracle client, simply have your DBA add another one, an 8.1 client in this case. Then point DataStage to that client by setting $ORACLE_HOME to it in the dsenv file.

No other applications would be affected.

vincentjeevan · Post by **vincentjeevan** » Tue Dec 16, 2008 8:20 am

Thanks. i'll request the DBA to install 8.1 client.

chulett wrote:Didn't say you needed to change the Oracle client, simply have your DBA add another one, an 8.1 client in this case. Then point DataStage to that client by setting $ORACLE_HOME to it in the dsenv file.

No other applications would be affected.

say2prabhu1 · Post by **say2prabhu1** » Tue Jan 27, 2009 4:04 pm

Use cast function while reading the oracle decimal values..

For example , Table T1 has column A has decimal datatype (12,3) means

Try to read the above A column value as VARCHAR usinf cast function

Select (cast ( a as Varchar(15)) A
from T1(Table)

This wll help you out ..

DSXchange

OCI Stage - Issue with reading decimal values

OCI Stage - Issue with reading decimal values

Re: OCI Stage - Issue with reading decimal values