SQL expressions as column name with Oracle 12c database

Question

we have migrated our Oracle Database to 12c. I have a given table with column names DATE and HOUR (not possible to change). Before I used this statement with c#:

command.CommandText = @"SELECT ""DATE"", ""HOUR"" FROM Table";

This statement is not running with 12c:

ORA-00911: invalid character

What do I have to change? Thanks

@Aleksej `DATE` is a keyword in Oracle and cannot be used as an identifier unless it is surrounded in double quotation marks. — MT0, Dec 13 '16 at 14:36
@MT0 My bad, I did not see the (obvious) reason for the quotes... — Aleksej, Dec 13 '16 at 14:37
It seems nothing was changed in this area in 12c. Maybe a driver issue? — David דודו Markovitz, Dec 13 '16 at 14:37
@DuduMarkovitz yes seemed to be a driver issue, thanks for that hint — Kᴀτᴢ, Dec 14 '16 at 08:38
@katz, Happy to hear. I have added an answer for documentation purposes, could you please fill in the details? Thanks :-) — David דודו Markovitz, Dec 14 '16 at 10:37

score 1 · Answer 1 · edited May 23 '17 at 12:08

1

You can use double quotes (without the \ escape character) to specify a keyword as an identifier for an object in the database:

SELECT "DATE", "HOUR" FROM Table;

Note: If you are going to use double quotes around an identifier (which you have to do if you are using a keyword as a column name) then you are enforcing the use of a case-sensitive name for that column and you must always use the same case letters in the name every time you refer to it - see my answer here for more details.

edited May 23 '17 at 12:08

Community

1
1

answered Dec 13 '16 at 14:33

MT0

143,790
11
59
117

Thanks for the answer. In the Oracle SQL Developer this is working, but I need this for a query from c#. When I use only `"DATE"` it gives me an error because the string expects a `;` after the second `"` – Kᴀτᴢ Dec 13 '16 at 14:50
@katz You appear to be doing it correctly in the sample code given in your question given the answers here: [How to use a string with quotation marks inside it?](http://stackoverflow.com/questions/5540701/how-to-use-a-string-with-quotation-marks-inside-it) – MT0 Dec 13 '16 at 14:54

score 0 · Answer 2 · answered Dec 14 '16 at 10:36

0

Driver issue

@katz - please fill in the details.
Thanks

answered Dec 14 '16 at 10:36

David דודו Markovitz

42,900
6
64
88

SQL expressions as column name with Oracle 12c database

2 Answers2