-
ORA-00942: table or view does not exist
Hi everyone,
I exported some tables from a MSSQL Server database using the DTS package and a Microsoft ODBC Driver for Oracle.
The tables structure and data was moved without any complaints to the Oracle 8i database (on Windows 2000 Server), but when I tried to query them I got the "ORA-00942: table or view does not exist" error message, despite the fact that the tables exist.
I can see the tables and their content in DBA Studio: they are OK. The tables and views are in the data dictionary also.
When I try to describe them I got the error: ORA-04043: object does not exist.
I researched on the Internet and noticed that this behavior is not new, but I didn't find a solution there.
Does anybody know why this problem happens and how to get around it?
Thanks in advance,
Paul
-
You most likely have one of two problems.
1. You are trying to access tables in another users schema without specifying ownership:
desc somebodyelse.mybigtable
2. Your lovely conversion routine creates tables with mixed case on Oracle. In this case, you need to access your tables with double quotes:
desc "MyBigTable"
3. If neither of these are the case, then show me the output from:
Code:
select table_name from user_tables
order by table_name
Jeff Hunter
-
- check privileges
- check if the table names are in uppercase in dictionary.
Consider:
Code:
scott@oracle> create table abc (a number);
Table created.
scott@oracle> create table "abc" (a char);
Table created.
scott@oracle> desc abc
Name Null? Type
----------------------------------------------------------------- -------- -------
A NUMBER
scott@oracle> desc "abc"
Name Null? Type
----------------------------------------------------------------- -------- -------
A CHAR(1)
scott@oracle> select table_name from tabs where upper(table_name)='ABC';
TABLE_NAME
------------------------------
ABC
abc
Ales The whole difference between a little boy and an adult man is the price of toys
-
Of course, Jeff is always faster ... :-/
Ales The whole difference between a little boy and an adult man is the price of toys
-
Originally posted by ales
Of course, Jeff is always faster ... :-/
Never question the speed of a Red Fox.
Jeff Hunter
-
Paul,
I am a total Oracle novice.
However I was experiencing a similar problem to yourself.
Perhaps the responses submitted already have answered your question but I will just add a short comment.
What I think was happenning to you was:
The DTS Package you were using was creating a table in the following format "PAUL"."TABLENAME".
Therefore a table was being created but you could only access it if you said something like 'select * from "TABLENAME"' or 'describe "TABLENAME"' . Select * from TABLENAME would return your error .
From my experience of using the DTS Wizard , if you change the name of the Oracle destination table to simply TABLENAME , with no double quotes you can then access it.
Now there may be a need to call it PAUL.TABLENAME or something but then I wouldn't know about that as I am an Oracle novice.
DD
-
Thank you guys,
You are right. It's about case sensitivity. The tables were exported with their names in lower cases and they were not found unless using quotation marks.
I exported again, I changed the table names to upper case (replacing "Paul"."tablename" with "Paul"."TABLENAME"), and everything was fine afterwards.
Thanks,
Paul
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|