Home » SQL & PL/SQL » SQL & PL/SQL » Table doesn't exist? (Oracle Express g10)
Table doesn't exist? [message #312605] Wed, 09 April 2008 07:24 Go to next message
stefanc
Messages: 3
Registered: April 2008
Junior Member
Hi,

When I'm trying to execute the following query, i'm getting this error: ORA-00942: table or view does not exist

SELECT * FROM Artists


Offcourse, I did made this table, and added some data to it. Also I executed a query to check if the table exists by showing all the tables from the database.

Anyone knows what I'm doing wrong here?

Help is much appreciated,

Stefan
Re: Table doesn't exist? [message #312609 is a reply to message #312605] Wed, 09 April 2008 07:31 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
can you post the result for the below query :
select owner,table_name 
from all_tables 
where table_name = 'ARTISTS'


regards,
Re: Table doesn't exist? [message #312610 is a reply to message #312605] Wed, 09 April 2008 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Beware that Oracle is NOT case sensitive, by default, all identifiers are converted to upper case.

Regards
Michel
Re: Table doesn't exist? [message #312611 is a reply to message #312605] Wed, 09 April 2008 07:38 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
stefanc wrote on Wed, 09 April 2008 08:24
Also I executed a query to check if the table exists by showing all the tables from the database.



And what did that show? Is it in another schema? Are you sure you are connecting to the same database where you created the table?

It could that be someone else has dropped this table.
Re: Table doesn't exist? [message #312613 is a reply to message #312610] Wed, 09 April 2008 07:39 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel Cadot wrote on Wed, 09 April 2008 14:37
Beware that Oracle is NOT case sensitive, by default, all identifiers are converted to upper case.

Regards
Michel



I was thinking the same.

So, I guess it's better we have the output of following query:

select owner,table_name 
from all_tables 
where upper(table_name) = 'ARTISTS'

Re: Table doesn't exist? [message #312614 is a reply to message #312605] Wed, 09 April 2008 07:39 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
May be, the table_name was provided within quotes during creation.
Re: Table doesn't exist? [message #312722 is a reply to message #312611] Wed, 09 April 2008 11:54 Go to previous messageGo to next message
stefanc
Messages: 3
Registered: April 2008
Junior Member
joy_division wrote on Wed, 09 April 2008 07:38
stefanc wrote on Wed, 09 April 2008 08:24
Also I executed a query to check if the table exists by showing all the tables from the database.



And what did that show? Is it in another schema? Are you sure you are connecting to the same database where you created the table?

It could that be someone else has dropped this table.



That was this query with the following result:

Quote:
select * from user_objects where object_type = 'TABLE';


OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY

Artists - 13841 13841 TABLE 09-APR-08 09-APR-08 2008-04-09:12:30:35 VALID N N N

With some more irrelevant rows. I also tried the query posted here, which resulted in 'no data found' error.

I'm working in the webbrowser, logged in as 'Deejay' when creating the tables AND running all these queries.

[Updated on: Wed, 09 April 2008 11:55]

Report message to a moderator

Re: Table doesn't exist? [message #312728 is a reply to message #312722] Wed, 09 April 2008 12:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So your table has a mixed case name.
You have to enclose it with " in your query.
But it will be better if you rename it in upper case.

Regards
Michel
Re: Table doesn't exist? [message #312738 is a reply to message #312728] Wed, 09 April 2008 12:35 Go to previous messageGo to next message
stefanc
Messages: 3
Registered: April 2008
Junior Member
Michel Cadot wrote on Wed, 09 April 2008 12:01
So your table has a mixed case name.
You have to enclose it with " in your query.
But it will be better if you rename it in upper case.

Regards
Michel



Okay, nice, it works now Smile Many thanks! Smile But why isn't it possible to name a table with mixed case?
Re: Table doesn't exist? [message #312748 is a reply to message #312738] Wed, 09 April 2008 13:00 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said Oracle works by default with upper case identifier.
To work with mixed you have to enclose name with " like "Artists" but it is a bad practice.

Regards
Michel

Previous Topic: Create UNQ Random Number
Next Topic: Specify [INSERT INTO] to run at a specified time every day
Goto Forum:
  


Current Time: Sun Dec 04 10:58:23 CST 2016

Total time taken to generate the page: 0.12348 seconds