Monday, June 25, 2012

Table names should be upper case with Oracle

I am working with Oracle XE (Oracle Database 10g Express Edition Release and the wonderful DB tool DbVisualizer. I am doing some data analysis and am using DbVisualizer to import data from Excel into a new table so that I can run some SQL over it. DbVisualizer even creates the table for you (dropping any existing table first if need be). So very quickly I have a table called PoBoxCompare with all my data in it. Next I do a quick sanity test to make sure the data is there. Using DbVisualizer's auto-complete I have my test SQL:

select * from ROB.PoBoxCompare;

Uh-oh, it doesn't exist!

17:34:49  [SELECT - 0 row(s), 0.000 secs]  [Error Code: 942, SQL State: 42000]  ORA-00942: table or view does not exist
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec  [0 successful, 0 warnings, 1 errors]

After some head scratching and experimentation, I find that table names (in Oracle at least) are case sensitive. If you have a table name with mixed case, you need quotes around the name.

select * from ROB."PoBoxCompare";

Of course, the better solution is just to make sure your table names are all upper-case, which is what I am doing now. :)

Note: the exact same rule applied to column names too!