How do I escape a reserved word in Oracle?

OracleReserved Words

Oracle Problem Overview


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.

Oracle Solutions


Solution 1 - Oracle

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.

Solution 2 - Oracle

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

Solution 3 - Oracle

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

eg:

select t."size" from table t 

Solution 4 - Oracle

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!

Solution 5 - Oracle

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.

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSpenceView Question on Stackoverflow
Solution 1 - OracleeyelidlessnessView Answer on Stackoverflow
Solution 2 - OracleJeffrey KempView Answer on Stackoverflow
Solution 3 - OracleSuresh GView Answer on Stackoverflow
Solution 4 - OracleAndrew not the SaintView Answer on Stackoverflow
Solution 5 - OracleoualidView Answer on Stackoverflow