英文:
Adding ROWID to an Oracle View
问题
When trying to add ROWID to an ORACLE view, it asks for a column alias. If your view has no joins, groups, or distinct clauses, you can define the view without ROWID and select it separately. However, if the view becomes more complex with joins or aggregations, you might need to understand how ROWID works in that context.
英文:
When I try to add ROWID to an ORACLE view, it tells me to create an alias for some reason.
CREATE VIEW VW_TEST_ARGUS_CLOB_2 AS
SELECT ROWID, CASE_ID_VERSION FROM T_DQ_QUEUE
Generates this:
ORA-00998: must name this expression with a column alias
00998. 00000 - "must name this expression with a column alias"
*Cause:
*Action:
So I assume ROWID is implicitly returned anyway?
If my view has no joins, no groups and no distinct clauses, is there a difference to me adding ROWID to the view, or just defining a view as this:
CREATE VIEW VW_TEST_ARGUS_CLOB_2 AS
SELECT CASE_ID_VERSION FROM T_DQ_QUEUE
And then selecting like this:
SELECT ROWID, CASE_ID_VERSION FROM T_DQ_QUEUE
i.e. don't add ROWID to the view, but call it from outside the view?
I don't know enough about ROWID and how it works in VIEWS, and the difference between adding it to the view, or just selecting it from outside the view. i.e. is ORACLE clever enough to return the rowid of the underlying table when the view is only operating on one table.
I am not sure how ROWID would work outside the view (in relation to tables), if the view was more complex and had distinct or group by statements (or joins).
答案1
得分: 1
ROWID是一个保留关键字。当你在视图的SELECT列表中包含它时，它将成为一个列名（在dba_tab_columns中可见），而像任何其他用户定义的对象名一样，列名不能是保留关键字。
所以，给它起个别名... ROWID AS row_id
至于从视图隐式地提取ROWID，只有在Oracle能够确定ROWID是什么时才会起作用，这只有在视图输出与原始行的1:1对应时才能做到，因为原始行才具有ROWID，所以聚合操作不允许使用ROWID。它还必须知道是哪个表，因此如果在父查询块中连接了两个表，它就不知道从哪个表获取ROWID。
如果你在一个表上直接创建一个视图，你可以隐式地选择ROWID：
create table tab1 (col1 integer);
create or replace view view1 as select * from tab1;
SELECT ROWID FROM view1
完全没问题。如果你在直接选择时遇到问题，那么可能你实际上是从另一个视图（T_DQ_QUEUE）中选择。请检查一下它是否是一个真正的表：
SELECT object_type FROM all_objects WHERE object_name = 'T_DQ_QUEUE'
如果它是一个视图，你需要获取视图代码，看看为什么不能从中选择ROWID。
英文:
ROWID is a reserved keyword. When you include in a view SELECT list, it's going to become a column name (visible in dba_tab_columns) and a column name, like any other user-defined object name, cannot be a reserved keyword.
So, alias it... ROWID AS row_id
As for pulling ROWIDs implicitly from views, that will work if Oracle is able to determine what the ROWID is, which it can only do if the view output is 1:1 with the original rows (which alone possess a ROWID), so that's why aggregations disallow ROWIDs. It must also know which table, so if you're joining two tables in the parent query block, it doesn't know which one to get it from.
If you do a straight view on a table, you can select ROWID implicitly:
create table tab1 (col1 integer);
create or replace view view1 as select * from tab1;
SELECT ROWID FROM view1
Works just fine. If you are having trouble with a straight select like this, then you might be actually selecting from another view (T_DQ_QUEUE). Check to see if that's a real table:
SELECT object_type FROM all_objects WHERE object_name = 'T_DQ_QUEUE'
If it's a view, you'll need to get the view code and see why you can't select a ROWID from it.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论