3

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

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 ?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
tommyk
  • 3,187
  • 7
  • 39
  • 61

2 Answers2

3

If you have created the table using quoted identifier, then you must always use double-quotation marks wherever you refer the object.

From documentation,

Database Object Naming Rules

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.

For example,

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

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.

For example,

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

no rows selected

SQL>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1
SELECT COUNT(*) FROM "SYS0MYUSER"."USER";
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • How will you know `SYS0MYUSER` is the schema name? – Rahul May 28 '15 at 08:20
  • No need of quoted identifier for the schema name. – Lalit Kumar B May 28 '15 at 08:35
  • It's never wrong to quote identifiers, even if they aren't reserved words. I'd say it's actually good practice to always double quote all identifiers, mainly because of portability and forward compatibility. (But I'm a bit lazy, so I don't do it always...) – jarlh May 28 '15 at 08:44
  • *it's actually good practice to always double quote all identifiers* I don't agree with that. It would create more confusion for developers. Let's say a developer build a habit of using quoted identifiers, and then he uses a lower-case object name in his quoted identifier, he will get error `ORA-00942: table or view does not exist`. With quoted-identifier the problem is that it makes it **case sensitive**. – Lalit Kumar B May 28 '15 at 08:56
  • How does Oracle chose between TableA and TABlea, if you do select * from TABLEA? – jarlh May 28 '15 at 09:02
  • @jarlh All the three are same. There will never be a problem if you use nonquoted identifiers while creating objects. Problems start when you create objects using quoted identifiers. – Lalit Kumar B May 28 '15 at 09:13
  • Can you create both "TABlea", "TableA" and tablea as three separate tables? – jarlh May 28 '15 at 09:22