Oracle's multiple join view is only recognized when using quotes around - why?
I ran into strange behavior when executing a SQL query in an Oracle view. The view contains several unions. When I type normal sql:
select * from vView - I receive the error that view is not found
select * from "vView" - The query is executed.
I wonder why?
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
a source to share
The view was created with a mixed name. If you output the following (don't put quotes around object names)
create view karl.vView as (select * from dba_tables);
The RDBMS will create a view and you will see a line in dba_views (or user_views if you don't see dba_views) named VVIEW in uppercase. Then select * from karl.vview or ... from KARL.VVIEW will work
If, however, you specify that object names preserve this case, and you must match it explicitly with quotes. (This will also allow spaces in names and other bad scenarios. It is worth knowing how to avoid and resolve when this happens.
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 the "TesT" Drop table;
a source to share