To all,

Following some research, I have discovered an interesting issue with the
creation of objects in Oracle. This problem seems to exist in both 8 and 9.

[Assuming you have a Table1 table with fields Key and Description.]

Example: Run the following script in SQL *Plus Worksheet: -

CREATE OR REPLACE VIEW Test1 AS
SELECT Table1.Key, Table1.Description
FROM Table1
WHERE Table1.Key= 41;

SELECT Count(*) FROM Test1;

An error will be received stating: -
SELECT Count(*) FROM Test1
*
Error at Line 1:
ORA-00942: table or view does not exist

If you then look in Schema Manager, you will see that the view "Test1" does
indeed exist, with proper casing of the name.

If you then run the following scripts: -
CREATE OR REPLACE VIEW TEST1 AS
SELECT Table1.Key, Table1.Description
FROM Table1
WHERE Table1.Key= 41;

SELECT Count(*) FROM Test1;
No errors, and you will get back the record count. The difference is that
in the second instance, the "TEST1" name is in all capitals. In other
words, SQL *Plus Worksheet will allow you to create database objects with
names that are not all in capitals, but if you do, you cannot refer to them,
even if the case is exactly the same.

If you then look at the schema, you will find that there will be two
objects, in that Oracle has created both views, TEST1 and Test1, and
considers them as distinct. SQL *Plus Worksheet will create objects using
the same case as you set, however in the queries, it automatically
translates all to uppercase.

Note that if you create the objects in Schema Manager, you are only allowed
to enter uppercase in the objects name field.

This particular issue caused me quite a bit of grief - I was wondering
whether this is a "feature", and if so, whether it is either documented or
undocumented.

--
Regards,

Michael Cole