Take the 2-minute tour ×

Trying to update an oracle 10g table using asp.net and the oracleclient connector

Here is the sql syntax:

UPDATE tableX set PURPOSE = 'T' where REQUEST_ID = '2543'

This throws an error:

ORA-00904: "PURPOSE": invalid identifier

If I try to update a different column

UPDATE tableX set DELIVERY_COMMENTS = 'T' where REQUEST_ID = '2543'

everything works fine.

The column PURPOSE does exist and I am able to INSERT information into the PURPOSE column.

Anyone have any ideas why this is not working?

share|improve this question
Can you post the results of running desc tableX in SQL*Plus or SELECT column_name FROM user_tab_cols WHERE table_name = 'TABLEX'? Is there any chance someone has created a case-sensitive column name for the PURPOSE column? –  Justin Cave Feb 24 '11 at 17:04
The results of select column_name etc..... is a listing of the column names and PURPOSE is listed. The funny thing is, is if run the update sql cmd using TOAD it works fine. –  Curt Feb 24 '11 at 17:15

2 Answers 2

I don't think PURPOSE is a resreved word but if it is it must be surrounded by double quotes" set "PURPOSE" = 'T'.

The column name may be lower case or mixed case. Run select * from tableX where rownum < 2 and see what SqlPlus says the column name is. The definitive way to determine the case of the column name is

   select c.owner, c.column_id, c.column_name
     from all_tab_cols c
     where c.table_name = 'TABLEX'
     order by  c.owner, c.column_id

Notice that 'TABLEX' is all uppercase. If PURPOSE is not all uppercase it must be surrounded by double quotes so that Oracle treats it case sensitive.

share|improve this answer
The results are that the column name is UPPERCASE. –  Curt Feb 24 '11 at 17:24
Did you try my first suggestion, surrounding it with double quotes? –  redcayuga Feb 24 '11 at 20:10
Yes, but it still did not work –  Curt Feb 28 '11 at 13:38

Ok...here is the answer


I was connecting to the database as user2, which put me in the user2 schema. user2 schema has a view using user1.target_table named user2.target_table. The problem was that the view was setup like

"Select field1, field2 from user1.target_table"

A new field was added to user1.target_table but the view in user2 schema was not updated to reflect the change.

I discovered the error by fully qualifying the UPDATE statement to

UPDATE user1.target_table set PURPOSE = 'T' where field1 = '1'

the update statement worked like a champ.

Lesson learned.....don't create a view in one schema with a name that is used in another schema.

Hope this clarifies my PEBKAC (problem exists between keyboard and chair)

share|improve this answer
To reinforce the point, if you do a CREATE VIEW ... AS SELECT * FROM ..., it implicitly turns that * into the list of columns at the time of view creation. So the view will NOT automatically include any new columns added to the table. In that case, use a synonym. –  Gary Myers Mar 25 '11 at 0:13

Your Answer


