Search code examples
oraclerowid

Adding ROWID to an Oracle View

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).

Solution

  • 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.