I have two databases: Oracle 10G and SQL Server 2000.
Inside the Oracle database, I have several MS Word documents stored as BLOBs. What I need to do is extract the files from Oracle and place them into SQL Server. I am curious as to how I do this?
I have heard something about DTS but not sure if that is something to look into.
In sql server 2000, your choices for blob storages is either binary/varbinary or image. binary/varbinary can be used if the blob is <=8000bytes and image should be use when blob is larger than 8000 bytes.
Personally I would use perl DBI to extract the files from oracle and insert them into sql server since I'm comfortable with perl.
You can also use oracle's hsodbc to make a link between oracle and the sql server and attempt to insert data from one to the other.
If you are more comfortable in sql server then DTS is a option. I'm not sql server person so I would favor a different approach.