ORA-00942 on Data Moved with SQL Server Data Transformation Services

Here’s another little fun run-in with Oracle.  I used the Data Transformation Services in SQL Server Enterprise Manager (2000) using the Microsoft ODBC for Oracle as the driver for my transfer.  Everything ran successfully, however, when I logged into the database using the SQL*Plus tool and did a simple query:

SELECT * FROM MyTable;

I got the following error message:

ORA-00942: table or view does not exist

Now– when I queried the built-in All_Tables table (which by the way, that is a query I’ve found helpful on more than one occassion):

SELECT Table_Name, Tablespace_Name, Owner FROM All_Tables;

The results show me that my table is in the right tablespace and has the right ownership.  I confirmed the login I was using to SQL*Plus had view permissions to the table.  Everything seemed in order– what could be wrong?

Well, apparently there is something unique about the naming when SQL Server DTS writes tables to Oracle– quotes are around the table name (but not reflected in the All_Tables query).  Sure enough, if you run this query:

SELECT * FROM “MyTable”;

results are returned as expected.

The final solution is quite easy– just run a RENAME TABLE command from your SQL> prompt in SQL*Plus.

RENAME TABLE “MyTable” to MyTable;

And you are good to go.  Since I’m going to be migrating the same SQL database over on multiple occassions for testing purposes, I have a text file handy with all my rename calls.  I copy them in bulk to SQL*Plus and I’m off.