Oracle view with multiple join is only recognize when use a quotes around - why ?

Question

I have encountered a strange behavior while executing an sql query on the Oracle view. The view contains multiple join. When I type a regular sql: select * from vView - I receive the error that view is not find select * from "vView" - The query is executed. I am wondering why ?

[url=http://www.freeimagehosting.net/image.php?138bcd008e.jpg][img]http://www.freeimagehosting.net/uploads/th.138bcd008e.jpg[/img][/url]
[url=http://www.freeimagehosting.net/image.php?e0738a36dd.jpg][img]http://www.freeimagehosting.net/uploads/th.e0738a36dd.jpg[/img][/url]

Below is my sql:

 CREATE OR REPLACE FORCE VIEW "TMSCODE"."vCountEventsData" ("EV_ID_NUMBER", "SI_ID", "EV_YEAR", "EV_INS_DATE", "EV_REM_DATE", "EV_AADT_TOT", "EV_AADT_DIR1", "EV_AADT_DIR2", "EV_REPORT", "DIRECTION", "CNAME", "STATION_DESC") AS

SELECT "TMSCODE"."STC_EVENTS".EV_ID_NUMBER, "TMSCODE"."STC_EVENTS".SI_ID, "TMSCODE"."STC_EVENTS".EV_YEAR, "TMSCODE"."STC_EVENTS".EV_INS_DATE, "TMSCODE"."STC_EVENTS".EV_REM_DATE, "TMSCODE"."STC_EVENTS".EV_AADT_TOT, "TMSCODE"."STC_EVENTS".EV_AADT_DIR1, "TMSCODE"."STC_EVENTS".EV_AADT_DIR2, "TMSCODE"."STC_EVENTS".EV_REPORT, "TMSCODE"."D_DIRECTION".DIRECTION, "TMSCODE"."D_CONSULTANT".CNAME, "TMSCODE"."D_STATION_TYPE".STATION_DESC FROM "TMSCODE"."STC_EVENTS" INNER JOIN "TMSCODE"."D_DIRECTION" ON ("TMSCODE"."STC_EVENTS".EV_DIR = "TMSCODE"."D_DIRECTION".ID) INNER JOIN "TMSCODE"."D_CONSULTANT" ON ("TMSCODE"."STC_EVENTS".EV_CONS = "TMSCODE"."D_CONSULTANT".ID) INNER JOIN "TMSCODE"."D_STATION_TYPE" ON ("TMSCODE"."STC_EVENTS".EV_STATION_TYPE = "TMSCODE"."D_STATION_TYPE".ID) WITH READ ONLY

Answer 1

The view was created with a mixed case name. If you issue the following (note no quotes around object names)

create view karl.vView 
as 
(select * from dba_tables);

The RDBMS will create the view and you will then find a line in dba_views (or user_views if you can't see dba_views) with the name VVIEW in upper case. Then select * from karl.vview or ... from KARL.VVIEW will work

If however you quote the objects names retains the case and you have to explicitly match it again with quotes. (This will also allow spaces in names and other bad scenarios. Worth knowing about to avoid and to be able to resolve when it does happen.

SYS@icedev> create table "TesT" (a int);

Table created.

SYS@icedev> insert into TesT values (1);
insert into TesT values (1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@icedev> insert into test values (1);
insert into test values (1)
            *
ERROR at line 1:
ORA-00942: table or view does not exist


SYS@icedev> insert into "TesT" values (1);

1 row created.

Of course to drop this table I had to use Drop table "TesT";

Answer 2

In Oracle, objects have an uppercase name unless quoted. You used quotes to create an object with a mixed case name, so you must now ALWAYS refer to the object using a quoted identifier to say "Don't change this to uppercase".