无法更新 Oracle 10g 表中名为“PURPOSE”的列
尝试使用 asp.net 和 oracleclient 连接器更新 oracle 10g 表
这是 sql 语法：
UPDATE tableX set PURPOSE = 'T' where REQUEST_ID = '2543'
这会引发错误：
ORA-00904: "PURPOSE": invalid identifier
如果我尝试更新不同的列，
UPDATE tableX set DELIVERY_COMMENTS = 'T' where REQUEST_ID = '2543'
则一切正常。
目的列确实存在，我可以将信息插入到目的列中。
任何人都知道为什么这不起作用？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不认为 PURPOSE 是一个保留词，但如果是，则必须用双引号引起来”
set "PURPOSE" = 'T'。列名可以是小写或混合大小写。运行
select * from tableX where rownum < 2并查看 SqlPlus 所说的列名是什么。确定列名大小写的最终方法是请注意，“TABLEX”全部大写。如果 PURPOSE 不全是大写，则必须用双引号引起来，以便 Oracle 区分大小写。
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 < 2and see what SqlPlus says the column name is. The definitive way to determine the case of the column name isselect c.owner, c.column_id, c.column_name from all_tab_cols c where c.table_name = 'TABLEX' order by c.owner, c.column_idNotice 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.
好的...这是答案
OPERATOR ERROR
我以 user2 身份连接到数据库，这将我置于 user2 模式中。 user2 模式有一个使用 user1.target_table 的视图，名为 user2.target_table。问题在于视图的设置类似于
“从 user1.target_table 选择 field1, field2”
向 user1.target_table 添加了新字段，但 user2 架构中的视图未更新以反映更改。
我通过将 UPDATE 语句完全限定为
UPDATE user1.target_table set PURPOSE = 'T' where field1 = '1'
来发现该错误，更新语句运行得非常好。
吸取的教训......不要在一个模式中创建具有在另一模式中使用的名称的视图。
希望这能澄清我的 PEBKAC（键盘和椅子之间存在问题）
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)