Object does not exist....

[rshivagami]

Hi all,

I created a table from Excel using SQL Server DTS on my Oracle 8.1.6 database. When I do a
SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER = ' aaaa',
I am able to see that particular table, but when I try to access it in my query, I get a
'object does not exist' error.
Does anyone know what is going wrong.

Thanks,
Shiva.

[elintrend]

select * from owner.tablename
if you are not log in as owner

[rsuri]

is your query run by the owner of the table ? otherwise prefix the ownername...

select * from aaaa.ThatTable;

If this is the case, then you may think about creating public synonym for such objects.

- Rajeev

[rshivagami]

I am logged in as the owner.
I created a public synonym and it works fine, but I still like to know what may have caused the issue.

[marist89]

Was the table created with a mixed-case name?

[rshivagami]

No, it is all in lower cases.

[yenni143]

hi,
the table must have been registered in lower case while creating and that is the problem you are facing while you select.
from sql server if you create a table with a lower case, u will face this kind of problem. For testing,Try a table in uppercase, then issue a select.

[marist89]

Yeah, that's your problem.

Check this out:
SQL> select table_name from user_tables;

TABLE_NAME
----------
XYZ
XyZ
xyz

I have three tables, all with different case.

SQL> desc xyz;
Name Null? Type
----------------- -------- ------------
X CHAR(2)
Y CHAR(2)
Z CHAR(2)

without enclosing your table name in double quotes, it converts your query to use the upper case table.

SQL> desc "xyz"
Name Null? Type
----------------- -------- ------------
X CHAR(1)
Y CHAR(1)
Z CHAR(1)

You explicitly get the "xyz" table.

SQL> desc "XyZ"
Name Null? Type
----------------- -------- ------------
X CHAR(3)
Y CHAR(3)
Z CHAR(3)

You explicitly get the "XyZ" table.

You have got to watch out for that on MS Office products...