 |
|
|
| > |
QUESTION POSED ON: 28 November 2005
I'm receiving the SQL execution error "ORA-00904: invalid column name." I have checked all the names and they are correct.
I checked the SQL string and it is working in TOAD, which means it
retrieved the data. I am lost!
|
|
|
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
One thing that trips people up is the case sensitivity of Oracle table
and column names. For most situations, you can specify table and column
names without paying attention to the case sensitivity. But if the
table was created with double quotes around the table or column name,
then the table or column name must be specified with case sensitivity
in mind. For example, assume I create the table as follows:
CREATE TABLE t1 (
"Id" NUMBER,
"Val" VARCHAR2(10));
Since I put the column names in double quotes, I must match case when
referring to these columns. The following query will fail with the
ORA-904 error you mentioned above:
SELECT Id FROM t1;
The problem here is that Oracle first converts the column name to upper
case wich does not match the name I specified in the CREATE TABLE
statement. To get around this problem, I must put the column name in
double quotes:
SELECT "Id" FROM t1;
Let's look at another example. I create a table as follows:
CREATE TABLE t2 (
id NUMBER);
Now if I query this table as follows, I will receive the ORA-904 error:
SELECT "id" FROM t2;
In this example, the table was created without double quotes around the
column name. When I put double quotes in my query, I am instructing
Oracle to look for a column with that exact spelling. But Oracle
converted the column name to upper case when the table was created, so
the column is not found.
|
|
|
|
|
|
|
|
Search and Browse the Expert Answer Center
Search and browse more than 25,000 question and
answer pairs from more than 250 TechTarget industry experts.
|
|
|
|
|
|
|
|
|
|
|
|
