Table does exist, but not according to SQL+ ! 1

[cojiro]

I'm in the process of writing a large query linking many tables and one table keeps tripping me up. SQL plus tells me this table doesn't exist, however when i use the Oracle Enterprise Manager Console, the table is there (I can see its structure and data).

When running this simple line:
desc consuserallocationxref;
I get this as a result:
ORA-04043: object consuserallocationxref does not exist

I'm using Oracle client 9.2 with an Oracle 8 database. If anyone knows what might be the problem, please help (I know I've spelt the name correctly). Thanks!

 

[JediDan]

In your SQL*Plus session, are you logging in as the table's owner (i.e. the same schema)? Otherwise, do you have SELECT rights on the table?

 

[jee]

I'll just echo the previous response. 99% of the time when I see the "object does not exist" error, it is because of a privilege problem - the user connected at the time does not have SELECT rights on the table.

 

[sem]

Another possible reason is using mixed case in table name (especially if you migrated from Access).

Regards, Dima

 

[cojiro]

Thanks for the suggestions everybody.
#1. It is definitely not a mixed case problem.
#2. Where as it might be a user rights issue, wouldn't I have select rights on the table if when I'm in the Oracle Enterprise Manager Console I can see all the data from any table. I use the same user ID.

This database was set up for our company by an outside resource that isn't very cooperative and seems like it was done fairly poorly so this has been extremely agitating. Could it have anything to do with the table name length? Or that it is an associative table? I'm not experienced with oracle so I'm kind of in the dark here.

 

[alexhu]

can you describe it if you use <owner>.<tablename> ?

Alex

 

[cojiro]

Yes that did work!
But now how could I make that work when i have multiple tables like this?

select capuser.lastname, capuser.firstname, allocation.allocationname, catagory.catagoryname,
ccacct.acctnum, cctran.trandate, vendor.vendorname, cctran.billamount
from capuser,
allocation,
category,
ccacct,
vendor,
consuserallocationxref,
cctran
where allocation.allocationname='A130' and
allocation.allocationid=consuserallocationxref.allocationid and
consuserallocationxref.capuserid=capuser.capuserid and
capuser.capuserid=ccacct.capuserid and
ccacct.ccacctid=cctran.ccacctid and
cctran.vendorid=vendor.vendorid and
cctran.categoryid=category.categoryid
order by cctran.billamount;

 

[cojiro]

Nevermind, I got it. Thanks for your help.