Numbers in a db

Oracle and SQL handle numbers just fine. The issue is when you move data from one DB to another. One db that is troubling me lately has some fun ideas about identity numbers (columns used for the unique primary key, and it's a number). The Oracle table has them as Number (20,0) instead of INT or BIGINT. For those who don't see my pain right off, the troubling part in that sentence is the (20,0). Number is a data type you would use when storing values that have something after the decimal point, like how I bought a pack of gum today and it was $1.29. 20,0 states that there are 20 available spaces to the left of the decimal and NONE to the right. If you are not storing anything after the decimal use an INT!

SSIS is nice enough to automatically map this from Oracle as Numeric(20,0) into SQL. Changing the data type to BIGINT makes it much less moronic and usable. Oh, and a nugget of fun with this. If you are using the SQL connections in .NET they tend to flip out at a Numeric (20,0) data type.

blog edits

I removed an older post today, it had the code for easily creating CRUD objects from tables. If you want a copy let me know.

Replication

Once again, SQL Server 2008 lands a flying kick to Oracles hydra like heads.. or kicks...whatever. In a move that it both stunning and flavored with awesomesauce, SQL Server has made a way to replicate from Oracle to SQL. SQL can register the Oracle instance, install some objects (tables, triggers, normal replication stuff) and then watches for changes. SQL acts as the distributor of the Oracle instance.

I have ran through the setup multiple times just to get the feel and try out different options. Guess what kids, it just plain WORKS.

For those of you asking, "but your DBAness, why would we do such a thing?" the answer is simple. Because sometimes we have to.