OCI Stage - Issue with reading decimal values
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 12
- Joined: Thu Jan 08, 2004 9:45 am
OCI Stage - Issue with reading decimal values
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Re: OCI Stage - Issue with reading decimal values
Hi
Try to read it as varchar.
Ram
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.
-
- Participant
- Posts: 12
- Joined: Thu Jan 08, 2004 9:45 am
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. ...
-
- Participant
- Posts: 12
- Joined: Thu Jan 08, 2004 9:45 am
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Thu Jan 08, 2004 9:45 am
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.
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.
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.
No other applications would be affected.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 12
- Joined: Thu Jan 08, 2004 9:45 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.
-
- Participant
- Posts: 27
- Joined: Mon Jul 09, 2007 1:06 am