156

In TSQL I could use something like Select [table] from tablename to select a column named "table".

How do I do this for reserved words in oracle?

Edit: I've tried square braces, double quotes, single quotes, and backquotes, they don't work...

As a further clarification, I have a column which someone named comment. As this is a reserved word oracle is chucking a wobbly trying to select with it, its failing when parsing the query. I've tried Select "comment" from tablename but it didn't work. I'll check case and come back.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
Spence
  • 28,526
  • 15
  • 68
  • 103
  • possible duplicate of [Oracle: What exactly do quotation marks around the table name do?](http://stackoverflow.com/questions/563090/oracle-what-exactly-do-quotation-marks-around-the-table-name-do) – user123444555621 May 03 '14 at 14:39

5 Answers5

223

From a quick search, Oracle appears to use double quotes (", eg "table") and apparently requires the correct case—whereas, for anyone interested, MySQL defaults to using backticks (`) except when set to use double quotes for compatibility.

Luke Girvin
  • 13,221
  • 9
  • 64
  • 84
eyelidlessness
  • 62,413
  • 11
  • 90
  • 94
  • 2
    I marked you down because I tried to escape the word using double quotes and it didn't work. – Spence Jul 22 '09 at 00:18
  • 17
    By default, Oracle will upcase any identifiers. So if you need either lower case characters or special characters, or the identifier is an Oracle reserved word, it needs to be enclosed in double quotes. Since double quotes preserves case, the identifier also needs to be the correct case. – Metro Jan 11 '13 at 01:05
  • I created a table with a field "alias" using SQL Developer v4 hitting a Oracle 11g Release 2 Express database. My select worked when I wrote "ALIAS" instead of alias. – Broken_Window Mar 11 '16 at 20:47
  • Not working for triggers, try to create a trigger for a table with a name what violates ORA-30507 (e.g. DATABASE). – luukvhoudt May 29 '18 at 00:58
  • 1
    Try it in all uppercase "TABLE". – RSHAP Aug 14 '18 at 15:42
39

Oracle normally requires double-quotes to delimit the name of identifiers in SQL statements, e.g.

SELECT "MyColumn" AS "MyColAlias"
FROM "MyTable" "Alias"
WHERE "ThisCol" = 'That Value';

However, it graciously allows omitting the double-quotes, in which case it quietly converts the identifier to uppercase:

SELECT MyColumn AS MyColAlias
FROM MyTable Alias
WHERE ThisCol = 'That Value';

gets internally converted to something like:

SELECT "ALIAS" . "MYCOLUMN" AS "MYCOLALIAS"
FROM "THEUSER" . "MYTABLE" "ALIAS"
WHERE "ALIAS" . "THISCOL" = 'That Value';
Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
10

double quotes worked in oracle when I had the keyword as one of the column name.

eg:

select t."size" from table t
kabirbaidhya
  • 3,264
  • 3
  • 34
  • 59
Suresh G
  • 119
  • 1
  • 3
  • Most likely Oracle DB will have the column name capitalized, and, when quoted, the column identifier is case sensitive! – Steve Jones Nov 23 '20 at 22:36
5

Oracle does use double-quotes, but you most likely need to place the object name in upper case, e.g. "TABLE". By default, if you create an object without double quotes, e.g.

CREATE TABLE table AS ...

Oracle would create the object as upper case. However, the referencing is not case sensitive unless you use double-quotes!

Andrew not the Saint
  • 2,496
  • 2
  • 17
  • 22
-9

you have to rename the column to an other name because TABLE is reserved by Oracle.

You can see all reserved words of Oracle in the oracle view V$RESERVED_WORDS.

Pete
  • 57,112
  • 28
  • 117
  • 166
oualid
  • 27
  • 1
  • 5
  • I get `ORA-00942` when I try a `select * from V$RESERVED_WORDS`. – ceving Oct 16 '14 at 09:51
  • Author has already said that they are not able to rename or drop table column. You just need to use double quotes "table" as said in other posts. – vytaute Mar 05 '22 at 20:28