Unanswered: Best way to import MS SQL Server table into Oracle
I have a large table (35,000 rows) in SQL Server on WinNT, which I need to import into Oracle 9i on Solaris. Here is the table creation spec for the table i SQL Server:
CREATE TABLE [tblMessage] (
[msgID] [int] NOT NULL ,
[msgSent] [smalldatetime] NOT NULL ,
[msgFromType] [int] NOT NULL ,
[msgFromID] [int] NOT NULL ,
[msgSubject] [varchar] (255) NOT NULL ,
[msgMessage] [text] NOT NULL ,
[msgOriginal] [int] NULL ,
[attID] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
...and in Oracle:
CREATE TABLE tblMessage (
msgID int NULL ,
msgSent date NULL ,
msgFromType int NULL ,
msgFromID int NULL ,
msgSubject varchar2(255) NULL ,
msgMessage clob NULL ,
msgOriginal int NULL ,
attID int NULL
What is the best way to migrate this table? I have tried exporting the table from SQL Server over ODBC, but only to get one of two errors: either an 03113 error (end-of-file on communcation channel) or an illegal parameter call on an OCI function call. I'm reasonably certain that Oracle is installed properly on the server; I've adjusted the memory properly in /etc/system, and the network connection is stead (I've migrated 45 other tables over the same network with no problems). I've tried replacing the CLOB field in Oracle with an NCLOB field (got a data mismatch error) and with varchar2(4000) (got a "value too large" error). I've replaced all of the non-ascii characters in the SQL Server table with ASCII characters. I've tried exporting to a csv file and loading the file with SQL*Loader, but it just hangs forever and won't complete the load.
The Oracle Migration Workbench won't work; the previous DBA failed to assign proper ownership to the SQL Server tables, and the OMWB won't handle them.
You know What I did. There are 3rd party tools out there to do this for you. Check out Google and search for migration tools.
I needed to test moving from Oracle to Microsoft SQL..long story. Anyway I used Access. I imported the tables and data into Access, then exported them into Microsoft SQL. That is a dirty way to get the job done, but it works.
Microsoft has a GUI tool for bulk loading data between data sources and targets. The Source can be numerous types of objects, spreadsheets, flat files, Access, Sql-Server, Oracle, and the Target can be numerous types of databases. Its called DTS. Data Tranformation Services I believe it stands for. You should be able to find it on the Sql_Server CD or with MS development CD.
I have used it to successfully load data to and from numerous databases including Sql-Server and Oracle.