Add column named "Comment"

[omacron]

Hi

I am running into an issue and don't know if there is a solution. My app runs on multiple DBMS packages (Oracle, DB2 and MS SQL). There were features that were originally added to DB2 and now need to ported over to Oracle. In the new features a new table was required and in that table one of the fields is named "Comment". Issue is in creating this table in Oracle I am getting the following error message;

Error report:
SQL Error: ORA-00904: : invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Is there anyway to get column named "Comment" into Oracle? Altering a table name is not only code changes but the end client will have to change their code.

thanks

 

[Dagon]

The only solution is to surround it with double quotes e.g.

create table comtest ("COMMENT" varchar2(20))

For Oracle-related work, contact me through Linked-In.

 

[SantaMufasa]

Dagon is absolutely correct about the double quotes, but surrounding "COMMENT" in double quotes is still problematic since it requires that every subsequent reference, including those in your application must also appear within double quotes:

create table tab1 ("COMMENT" varchar2(20))
/

Table created.

SQL> INSERT INTO TAB1 VALUES ('This is a test.');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tab1;

COMMENT
--------------------
This is a test.

SQL> select COMMENT from tab1;
select COMMENT from tab1
       *
ERROR at line 1:
ORA-00936: missing expression


SQL> select "COMMENT" from tab1;

COMMENT
--------------------
This is a test.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”