Bug with Creation of Objects in SQL *Plus Worksheet


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Bug with Creation of Objects in SQL *Plus Worksheet

Hybrid View

  1. #1
    Michael Cole Guest

    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



  2. #2
    John Thomas Guest

    Re: Bug with Creation of Objects in SQL *Plus Worksheet


    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
    >
    >



  3. #3
    john feng Guest

    Re: Bug with Creation of Objects in SQL *Plus Worksheet


    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
    >>
    >>

    >



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center