How to use quoted id for username + table combination in Oracle?
In my Oracle DB setup, all tables are created under a dedicated user account SYS0MYUSER
. On following request on my system, I gotSQL 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 avoid username + reserved keyword combination?
UPDATE: How about the table alias, do I need to use double quotes as well?
source to share
If you created a table using a quoted identifier , you should always use a double quote for the label where you are referencing the object.
From the documentation,
Database Object Naming Rules
Every database object has a name. In an SQL expression, you represent the name of an object with a specified ID or an unquoted ID.
A citation identifier begins and ends with double quotes ("). If you name a schema object using the quoted identifier, then you must use double quotes when you refer to that object.
The unquoted 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 pass the table as the specified id :
SELECT COUNT(*) FROM SYS0MYUSER."USER";
Update . OP updated his question about table alias.
How about a table alias, do I need to use double quotes as well?
The table alias has nothing to do with the specified identifier.
For example,
SQL> SELECT t.* FROM LALIT."USER" t;
no rows selected
SQL>
source to share