如何在Oracle中为用户+表名组合使用加引号的标识符? [英] How do I use quoted identifier for user + table name combination in Oracle?

问题描述

在我的Oracle DB设置中，所有表都是在专用用户帐户SYS0MYUSER下创建的.在我的系统上执行以下查询时，我得到了SQL Error: ORA-00903: invalid table name

In my Oracle DB setup all the tables are created under dedicated user account SYS0MYUSER. When executing following query on my system I got SQL Error: ORA-00903: invalid table name

SELECT COUNT(*) FROM SYS0MYUSER.USER;

我试图这样转义保留关键字:

I tried to escape the reserved keyword like this:

SELECT COUNT(*) FROM "SYS0MYUSER.USER";

但是然后我又遇到另一个错误SQL Error: ORA-00942: table or view does not exist

But then I got another error SQL Error: ORA-00942: table or view does not exist

转义用户名 + 保留关键字组合的正确方法是什么?

What is the correct way to escape user name + reserved keyword combination ?

更新: 关于表别名，我也必须使用双引号吗?

UPDATE: What's about table alias do I have to use double quotes too ?

推荐答案

如果使用 从文档中

数据库对象命名规则

Database Object Naming Rules

每个数据库对象都有一个名称.在SQL语句中，您代表 带引号或不带引号的对象的名称 标识符.

Every database object has a name. In a SQL statement, you represent the name of an object with a quoted identifier or a nonquoted identifier.

• 带引号的标识符以双引号()开头和结尾.如果使用带引号的标识符命名架构对象，则您 每当您引用该对象时，都必须使用双引号.

• A quoted identifier begins and ends with double quotation marks ("). If you name a schema object using a quoted identifier, then you must use the double quotation marks whenever you refer to that object.

未加引号的标识符不包含任何标点符号.

A nonquoted identifier is not surrounded by any punctuation.

例如，

SQL> CREATE TABLE "USER"(A NUMBER);

Table created.

SQL>
SQL> SELECT COUNT(*) FROM LALIT.USER;
SELECT COUNT(*) FROM LALIT.USER
                           *
ERROR at line 1:
ORA-00903: invalid table name


SQL>
SQL> SELECT COUNT(*) FROM LALIT."USER";

  COUNT(*)
----------
         0

SQL>

因此，您需要将该表引用为带引号的标识符:

So, you need to refer the table as a quoted identifier:

SELECT COUNT(*) FROM SYS0MYUSER."USER";

更新 OP修改了有关表别名的问题.

Update OP updated his question regarding table alias.

关于表别名，我也必须使用双引号吗?

What's about table alias do I have to use double quotes too ?

表别名与引用的标识符无关.

Table alias has nothing to do with the quoted identifier.

例如，

SQL> SELECT t.* FROM LALIT."USER" t;

no rows selected

SQL>

这篇关于如何在Oracle中为用户+表名组合使用加引号的标识符?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！