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

March 9, 2006 at 10:43 pm 1 comment

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.

Entry filed under: Data Transformation Services, ORA-00942, Oracle, SQL Server.

Gordon Parks Letter to Lansing City Council

1 Comment Add your own

  • 1. thomas  |  April 27, 2012 at 3:13 pm

    You saved my day! Thank you!

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Trackback this post  |  Subscribe to the comments via RSS Feed


Flickr Photos

3D Printed Products

Tweets


%d bloggers like this: