Oracle puzzle: Selecting from a non-existing table

Question

I have a table named "libisatz" in my database, and there is no table, nor view with name "libiSatz" (with capital S instead of s) and I don't find any kind of object in my schema having name "libiSatz". But, surprisingly selecting from "libiSatz" results in the same result as if I had written "libisatz". If I change any other letter in this name from lower case to upper case (e.g. I write "Libisatz", then I get an error. How can this be?.

ADDENDUM

I've checked the ideas of @Jon Heller with the following result:

1. Both select * from DBA_OBJECTS where OBJECT_NAME like 'libi_atz'; and select * from DBA_OBJECTS where lower(OBJECT_NAME) = 'libisatz'; returns one single row (it is the "libisatz" table)

2. select * from DBA_OBJECTS where OBJECT_NAME = 'libisatz'; returns one row and select * from DBA_OBJECTS where OBJECT_NAME = 'libiSatz'; returns no row.

3. Both select * from DBA_SQL_TRANSLATIONS; and select * from DBMS_ADVANCED_REWRITE; results in ORA-00942: table or view does not exist

4. select * from DBA_REWRITE_EQUIVALENCES; results in no rows selected

5. select DUMP(OBJECT_NAME) from DBA_OBJECTS where lower(OBJECT_NAME) = 'libisatz'; returns Typ=1 Len=8: 108,105,98,105,115,97,116,122

So it seems that the puzzle is not yet solved.

ADDENDUM 2.

1. When I try to create a view named "libiSatz" then I get

   ORA-00955: name is already used by an existing object

   in spite of the results above.

2. After I renamed "libiSatz" to "oraclepuzzle",

   select count(*) from "libisatz"; and "select count(*) from "oraclepuzzle"; works, but select count(*) from "libiSatz"; doesn't.

   select * from dba_objects where object_name in ( 'oraclepuzzle', 'libisatz', 'libiSatz'); returns one row with object_name "oraclepuzzle".

Answer 1

Do you have SQL translations, rewrite equivalances, or UTF8 characters?

The SQL translation framework was built to allow applications designed for other database types to seamlessly query Oracle. But instead of translating syntaxes, the feature could also be used to silently change your queries. For example, here is an example of querying a table that does not exist in order to workaround table size limitations. Check the view DBA_SQL_TRANSLATIONS.

Rewrite equivalences can be created by the package DBMS_ADVANCED_REWRITE, and can be used to silently change the results of wrong queries or for some rare performance problems. (In my simple tests I wasn't able to get this query to work for invalid queries, but I bet there is a way to make it work.) Check the view DBA_REWRITE_EQUIVALENCES.

UTF8 characters may be silently swapped out for similar ASCII characters. This probably depends on your database and client character set settings. The below example is pretty obviously not an ASCII "S", but you may have a more subtle problem. Check the source of your values, retype them manually, and use the DUMP function to evaluate the binary of the characters.

SQL> create table "libisatz"(a number);

Table created.

SQL> -- An uppercase "S" does not work
SQL> select * from "libiSatz";
select * from "libiSatz"
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> -- But a "LATIN SMALL LETTER S WITH CARON" works.
SQL> select * from "libišatz";

no rows selected

Lastly, are you 100% sure that the object doesn't exist on your database? The above examples are possible, but extremely rare. Triple-check DBA_OBJECTS.OBJECT_NAME for the mystery object.

Answer 2

My deafult Oracle object names are case insensitive.

So, when you issue teh command:

CREATE TABLE the_table ..

CREATE TABLE The_Table ..

CREATE TABLE THE_TABLE ..

The underlying object name in all cases is THE_TABLE, but you can access it using any mix of upper/lower case:

SELECT * FROM THE_TABLE

SELECT * FROM tHe_TaBlE

SELECT * FROM the_table

Will all work.

When a table is created with the name wrapped in double-quotes, the name becomes case-sensitive.

So, if you issue:

CREATE TABLE "libiSatz" ..

Then the table name in all SQL statements must match the exact name in the double quotes, i.e.

SELECT * FROM "libiSatz" is OK (Correction following comments)

SELECT * FROM libisatz is NOT OK

SELECT * FROM LibiSatz is NOT OK

When you search for this in USER_OBJECTS or USER_TABLES you will have to match the creation name:

SELECT * FROM USER_OBJECTS WHERE OBJECT_NAME = 'libiSatz';