Unable to update column in Oracle 10g table having the name "PURPOSE"

Question

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?

Answer 1

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.

Answer 2

Ok...here is the answer

OPERATOR ERROR

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)