Oracle table column names starting with a number
Hello,
I just finished creating MANY tables that have column names starting with a number. I didn’t realize it was an issue until I started trying to select data from my tables using the column names in the where clause. Inserts work. Selects without where work.
Is there any way to keep the column names or will I have to change them all to be able to limit a select?
SQL> select * from tbl5bis where 5bis_id = 1;
select * from tbl5bis where 5bis_id = 1
*
ERROR at line 1:
ORA-00920: invalid relational operator
The way I named the columns makes the most sense for the data they are holding . . . I would like to keep the column names if possible but obviously I have to be able to use the where clause.
Thanks,
Jaime
Software/Hardware used:
Software/Hardware used:
ASKED:
November 2, 2007 12:56 AM
UPDATED:
November 7, 2007 5:36 PM
Answer Wiki:
Try using a quoted identifier.
select * from tbl5bis where "5bis_id " = 1;
To see all answers submitted to the Answer Wiki: View Answer History.
Thank you, this worked, but now I am having an issue using the column names in the select statement. It doesn’t like the quotes around the column names and I need to do an update to the row.
update tbl5bis set “5bis_01″ = ‘info’;
I have also tried doing
update tbl5bis set tbl5bis.5bis_01 = ‘info’;
It didn’t like that either . . .
Any suggestions would be greatly appreciated.
Jaime