oracle number to sql server

Topics: Data Access Application Block
Oct 6, 2009 at 2:49 PM

Hi all,
Firstly sory my poor English.
I m transferring data From Oracle to Sql Server with EntLib 3.1. 

I m fixed oracle number as Trunc(Field1/Field2, 10) for overflow and transfer data into sql server to numeric(38,10)or float.
But some times, some number abnormal rounded  to high value on sql server.

But I get this error only at calculated values like field1/field2.
There is no problem with the fields like Number(18,2) and their multiplyings.

What I use are as providers;
.Net Framework Data Provider From Sql Server (System.Data.SqlClient)
.Net Framework Data Provider From Oracle(System.Data.OracleClient)

help please!
Thank you very much in advance!

Oct 7, 2009 at 8:54 AM

Hi,

You said that abnormal rounding happens just some times,. Can you cite an example of your inputs and outputs for both successfully transfered and some rounded off. Also you might want to check the setting of the field of the sql server if it has the same precsion as the oracle.

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 8, 2009 at 10:11 AM
Edited Oct 8, 2009 at 10:12 AM

Hi Valiant,

My problem details  below :

Source (Oracle) (Entlib Provider : Systam.Data.OracleClient)
--------------
Select 
 ,Trunc(Case When ? then quantity else quantity/func1 end, 10) AS FIELD1
 ,Trunc(price * func2, 10) as FIELD2
From ORATABLE

column types
* quantity : number(21,8)
* price : number(21,2)
- query result: max(field1) = 236000,00000252

Target (SqlServer)(Entlib Provider : Systam.Data.SqlClient)
-------------------
Field1 as numeric(38,10) or float
Field2 as numeric(38,10) or float


C# code with Entlib
--------------------
select :
db1.ExecuteDataSet(cmd)
decimal field1 = Convert.ToDecimal(row["Field1"]);

insert:
db2.AddInParameter(cmd, "Field1", DbType.Decimal, field1);
db2.ExecuteNonQuery;


the source of problem FIELD1, no problem on the FIELD2.
some incorrect/correct values on FIELD1:

Has Error - OraValue  - SqlSrv Value 
------  ----------  -----
+ 232  -->  29000  
-  96,6999989699 ---> 96,6999989699 
-  298  ---> 298  
-  1528,7  ---> 1528,7  
+ 704  ---> 88000  
-  1908,6  ---> 1908,6  
-  203,6  ---> 203,6  
+ 176  ---> 22000  
+ 379,9999989 ---> 47700  
+ 61,0000006499 ---> 28500  
-  340,4  ---> 340,4
-  766  ---> 766
-  17,9999999999 ---> 17,9999999999
-  130,0000018962 ---> 130,0000018962
+ 110,9999997 ---> 9000  

thank you very much

Oct 8, 2009 at 10:39 AM

I wasn't able to repro your error, or I might be missing something.  I'm not clear what are the values you presented, how were you able to arrive at those values?  You said you got the wrong values when performing calculations like Field1/Field2.  What's the relevance then of Max(Field1)?  Are you also not getting the correct values when using Max? 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 8, 2009 at 3:54 PM

Hi Sarah,

Thank you first of all,
I only showed you Max(Field1) as it is the max value in the result to be informative.
So I mean there are no big values in the transfer process. And I diminish the scale to 10 digits with Trunc command.
 
It's a very weird problem and I'm bored trying to solve it & I constantly work on it :(
Also, I forgot to point out in the first email, that when I'd run the transfer application without any changes, it'd been able to transfer 50.000 records correctly at once for a few times. Very strange!
 
I found some short articles related at the link below, they might give you some clues. Check it out please.
http://microsoftdw.blogspot.com/2005/11/final-storyhow-to-get-data-out-of.html


Thanks again.

 

Oct 9, 2009 at 2:12 AM

Let's do this step-by-step:  Verify if all I've written below are correct:

1. You read data from the Oracle database. Did you simply read data or performed some operation like Max?

2. You used a data reader to get the values.  Did you verify that you got correct values?

3. You used sql command to insert the values in the sql database.  Were the values inserted the same as the ones you read?

I'm asking all of these because I'm confused as at what point are you getting the incorrect values.  Is it while executing an oracle statement or probably the datareader's not returning the correct values?  If the above steps are incorrect, please post the correct ones.

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 12, 2009 at 7:03 AM

Hi again,
1. yes, I only do a simple query from oracle. I gave the example above.
2. I didn't use data reader. I compiled a dataset using ExecuteDataSet method.
3. Yes.
 
It took me quite a while to figure out this random error. As I pointed out, in few transfers, the same codes run correctly and transfer the values. During my last test, I came across these:
 
If we observe the process in 3 steps, the problem is at the second step. 
 
1. Always correct values returned from Oracle query. (Using Toad for Oracle). So no problem.
2.  row["Field1"];
     row["Field1"].ToString();
     Convert.ToDecimal(row["Field1"]);  
    
     The values result wrong in all those 3 methods. ( As in the example above 232 -> 29000 )
     But the first step has no error. I come across false values at Dataset.
    
3. The values are transferred to Sql Server likewise. No problem.
 
This is all I can tell you about the problem. No further details. 
Thanks a lot again.

Oct 12, 2009 at 8:26 AM
Edited Oct 12, 2009 at 8:27 AM

I'm a little confused. Correct me if my understanding is wrong, you've said that you verfied that the values returned from the oracle is correct using the using Toad for Oracle. But after doing a query(using .NET), you're dataset now contains wrong values? So is there a gap between the results of the Toad and in the DataSet?

Valiant Dudan
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com

Oct 12, 2009 at 3:01 PM
Edited Oct 12, 2009 at 3:03 PM

Yes, you are right.

Toad for Oracle returning correct values.
When i select record with EntLib (ExecuteDataset method) i have the  incorrect values.

transfer code block:
---------------------

Source (Oracle) (Entlib Provider : Systam.Data.OracleClient)
--------------
Select 
 ,Trunc(Case When ? then quantity else quantity/func1 end, 10) AS FIELD1
 ,Trunc(price * func2, 10) as FIELD2
From ORATABLE

column types
* quantity : number(21,8)
* price : number(21,2)

Target (SqlServer)(Entlib Provider : Systam.Data.SqlClient)
-------------------
Field1 as numeric(38,10) or float
Field2 as numeric(38,10) or float


C# code with Entlib
--------------------
select :
db1.ExecuteDataSet(cmd)
decimal field1 = Convert.ToDecimal(row["Field1"]);

insert:
db2.AddInParameter(cmd, "Field1", DbType.Decimal, field1);
db2.ExecuteNonQuery;

that's all.
thanks
Oct 13, 2009 at 2:12 AM

Yes, we did read that but I can't find the relevance as you are not using a datareader.  Have you tried running the exact query on sql management studio?  If you haven't, try it out first and see if you are getting the correct values.  This way, we could sort out if the problem is indeed with the populating of the dataset. 

 

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.
entlib.support@avanade.com