CODEWITHSUNDEEP
sqloracle-databaseviewora-00942
Greener
Greener

Reputation: 477

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

I have encountered a strange behavior while executing an sql query on the Oracle view. The view contains multiple joins. When I type a regular sql:

select * from vView - I receive the error that view is not found
select * from "vView" - The query is executed.

I am wondering 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

Upvotes: 0

Views: 1243

Answers (2)

Karl
Karl

Reputation: 3372

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

Upvotes: 8

Gary Myers
Gary Myers

Reputation: 35401

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

Upvotes: 4

Related Questions