Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a middleware that replicates data from an Oracle (11g) database to a SQL Server (2012) using ODBC connection to the destination. One of the tables has a NUMBER data type on Oracle and given there is no exact equivalent on SQL Server, we have used DECIMAL (18,0) on the SQL Server side in hope of catching errors if the data actually has fractions. However we've noticed that replication works without any error while truncating the fractions from the numbers. We traced the queries coming from the middleware and there are bunch of sp_prepexec statements where parameters are defined as the target type (e.g. DECIMAL(18,0)) and values are already converted before reaching SQL server and hence no error despite having NUMERIC_ROUNDABORT ON on the SQL Server. Is this how ODBC supposed to work? Are there ways to force errors on these type of data type conversions?

share|improve this question
    
You say that you have NUMERIC_ROUNDABORT on in SQL Server. How are you setting that for the replication process? –  Ben Thul Feb 20 at 4:03
    
Sounds like a middleware problem. What are you using? SQL Server and Oracle can connect directly via several different means –  Phil Feb 20 at 4:04
    
We've tried the NUMERIC_ROUNDABORT at the database level and also as a global user option of the server. –  ODBC Feb 20 at 6:21
    
We're using Oracle GoldenGate for replication. –  ODBC Feb 20 at 6:43
add comment

2 Answers 2

If the parameter to the ODBC prepared statement is defined as decimal(18,0), that's how it's transmitted to the server. The conversion is performed by the ODBC driver. AFAIK the driver is not required by the standard to emit a diagnostic when dropping the fractional part on conversion to an integral type. Presumably the user knows what he's doing. ;-)

I think you're mistaken, though. From what I make of the NUMBER datatype, it's identical to SQL Server's decimal: you specify scale and precision up to a maximum of 38 digits. How are they incompatible?

If you really can't pass the data as a decimal type, I think I'd pass it as a varchar(30) and use a constraint on the SQL Server table to make sure it conforms.

share|improve this answer
    
That's what I was afraid of, i.e. ODBC driver not required to report error in this particular scenario. In fact NUMBER is not exactly portable between Oracle and SQL Server. Unlike SQL, Oracle does not force you to identify precision and scale for NUMBER type, in which case it takes data as is, i.e. any precision or scale up to 38 digits. –  ODBC Feb 20 at 6:32
add comment

Given that SQLserver's decimal datatype potentially has the same range as oracle's number, albeit with a fixed scale, your decision to limit yourself to (18,0) and trying to catch errors seems strange.

Shouldn't something like (38,20) suit your needs better, since you seem to be assuming a max of 18 natural digits anyway?

The optimal solution would seem to be analyzing the oracle data to decide the best decimal value to use in SQLserverm but I assume that option is not available?

share|improve this answer
    
Using decimal (18,0) was an arbitrary choice as after some quick review of the model we thought most of those fields contain int/bigint values. However that's not the point, the point is ODBC happily truncating data and not letting you know, so whatever we chose there's a risk of data loss. We do have the ability to profile the Oracle database and that's what we're doing now, but the data changes rapidly and some of those fields may hold calculation results which could vary widely. –  ODBC Feb 22 at 5:03
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.