为什么Oracle 12c查询需要在表周围用双引号引起来 [英] Why does Oracle 12c query require double quotes around table

问题描述

我要查询的数据库是Oracle 12c.有关数据库版本的详细信息如下:

The database I'm querying is Oracle 12c. Detailed info about database version is as follows:

Oracle Database 12c企业版12.1.0.2.0版-64位生产

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

PL/SQL版本12.1.0.2.0-生产

PL/SQL Release 12.1.0.2.0 - Production

我试图消除在SQL查询中的每个视图或表周围使用双引号的必要.

I'm trying to eliminate the need to have double quotes around every view or table in my SQL query.

以下工作(来自Oracle Sql Developer GUI)

Following works (from Oracle Sql Developer GUI)

select m."Metadata"
from "EvMetadata" m

以下给出了错误(来自Oracle Sql Developer GUI)

Following gives error (from Oracle Sql Developer GUI)

select m.Metadata
from EvMetadata m

错误是

ORA-00942:表或视图不存在 00942. 00000-表或视图不存在" *原因:
*操作:第2行的错误:第6列

ORA-00942: table or view does not exist 00942. 00000 - "table or view does not exist" *Cause:
*Action: Error at Line: 2 Column: 6

我生成了DDL，看起来像这样

I generated DDL, which looks like this

CREATE TABLE "EVP"."EvMetadata" 

("EvMetadataId" NUMBER(10,0) GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE , 

"InsertDate" TIMESTAMP (6), 

"SessionId" NVARCHAR2(17), 

"FileCheckSum" NVARCHAR2(32), 
"Metadata" NCLOB, 
"Device" NVARCHAR2(20), 
"User" NVARCHAR2(20)
) SEGMENT CREATION IMMEDIATE 

因此，根据下面的@toddlermenot注释，很有可能是这样创建表的-用双引号引起来.我使用ORM Entity Framework Code First为我生成了架构，因此ORM似乎默认情况下使用双引号.

So based on @toddlermenot's comment below, it is very possible that this is how the table was created - with double quotes. I used ORM Entity Framework Code First to generate the schema for me so it seems like the ORM puts the double quotes by default.

推荐答案

也许您用双引号创建了表? 使用双引号将保留大小写，并且由于在示例中表名同时具有大写和小写字母，因此，只有在使用双引号时，Oracle才能找到它.

Maybe you created the table with double quotes? Using double quotes would preserve the case and since the table name has both upper and lower case letters in your example, Oracle is able to find it only when you use the double quotes.

默认情况下，不管表中是否存在任何大小写，Oracle都可能不使用双引号，而只使用一种大小写(大写?).

Without the double quotes, Oracle probably uses a single case (upper?) irrespective of any case you might have in the table, by default.

例如: 如果您使用

create table "TaBlE_NaMe" (blah..)

然后，您必须在SELECT中使用双引号.

then you must use the double quotes in your SELECT.

如果您使用创建表

create table TaBlE_NaMe (blah..)

不带引号的SELECT应该正确工作. (如果您将表格名称的所有字母都大写，也可以使用引号)

The SELECT without quote should work correctly. (It would work with the quote also if you had all the letters of the table's name in upper case)

这篇关于为什么Oracle 12c查询需要在表周围用双引号引起来的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！