Bug with Creation of Objects in SQL *Plus Worksheet

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

Hi Michael Cole,

You can both create and access objects with mix-case names in Oracle:

eg:
create table "HuMan"(
Id Number(9),
Name Varchar2(35));

This table should be queried as :

select * from "HuMan";

"Michael Cole" <michael.cole@hansen.com> wrote:
>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
>
>

What is the version number of your DB and which OS is it running?

I don't have the issue you encountered.

Oracle DB is not case-sensitive in terms of its object.

-john

"John Thomas" <johnpadam@hotmail.com> wrote:
>
>Hi Michael Cole,
>
>You can both create and access objects with mix-case names in Oracle:
>
>eg:
> create table "HuMan"(
> Id Number(9),
> Name Varchar2(35));
>
>This table should be queried as :
>
> select * from "HuMan";
>
>"Michael Cole" <michael.cole@hansen.com> wrote:
>>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
>>
>>
>