ORA-00942: table or view does not exist
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: ORA-00942: table or view does not exist

  1. #1
    Join Date
    Aug 2004
    Location
    Toronto, Canada
    Posts
    7

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    - 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

  4. #4
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Of course, Jeff is always faster ... :-/
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ales
    Of course, Jeff is always faster ... :-/
    Never question the speed of a Red Fox.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2004
    Posts
    1
    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

  7. #7
    Join Date
    Aug 2004
    Location
    Toronto, Canada
    Posts
    7
    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
  •  


Click Here to Expand Forum to Full Width