ORA-00942: table or view does not exit.

I did a select * from jobs;
and it returned ORA-00942.

But when I did select owner, table_name from all_tables where owner
='SIMT', and it returned jobs, authors....

What's going on? I am under the dba group.

Any advise? Thanks in advance.

Hi ttsim,

Who are you connected as? Try select * from .jobs; where owner = owner of table jobs;

Regds,

I did a select * from simt.jobs and it still returned the ORA. I exported this table from sql server 2000 to oracle.

try:

select * from SIMT.jobs;

According to your query against "ALL_TABLES" you were looking for
the owner of SIMT ....

Gregg

Hi,

What is the result of

select owner,table_name from dba_tables where table_name = '' where table_name is the table you want to select from.

Regds,

SQL> show user
USER is "SIMT"
SQL> select owner, table_name from all_tables where owner = 'SIMT';

OWNER TABLE_NAME
________________________________________________
SIMT jobs

SQL> select * from jobs;
select * from jobs
*
ERROR at line 1:
ORA-00942: table or view does not exist

select * from "jobs"

When you import table from sqlserver to oracle, table name is case sensitive.


xyz

it works when I:-

select * from "jobs";
Why's that? Don't understand?
Next time when I export more tables from SQL Server 2000 into Oracle, how can I avoid this from happening each time when I do a select ... from "...", instead of select ....from ....;
Please advise.
Thanks in advance.

In oracle table name is not case sensitive, in SQL server, default, table name is case sensitive. So after import SQL server table, you table anme is case sensitive. You have to get the table name from user_tables, then you have to use " " for your table name, this way, Oracle know your table name is case sensitive. In your case, your table name is "jobs". But in oracle, if you use

select * from jobs.

In oracle it means

select * from "JOBS".

But your table name is "jobs".

xyz

I don't know if there is any better solution but you can create synonyms if you want to avoid using quotes.

Code:

---

create synonym jobs for "jobs";

---

Rename table