Search code examples
sqloracle-databaserownum

Oracle Rownum cast

I am trying to create a complex view that will be accessed via a linked server in a MS-SQL server. The problem I am having is that the query uses ROWNUM to generate a ROWID.

The generated ROWID has a datatype of NUMBER but this is apparently causing me a problem and if I can get this into a NUMBER(insert size here) It will all be fine. but I am unsure if this is even possible.

CREATE OR REPLACE FORCE VIEW EXAMPLE ("ROW_ID")
AS
SELECT ROWNUM ROW_ID,
FROM
(SUB-QUERY)

I am unable to give the full query and column names (work constraints) but here are the returned column types from the query in SQL Developer

column types

I think the problem is centred around the returned datatype of ROWNUM being Number and not NUMBER(20) or similar length and that this cross-over is the problem I am seeing with regards to the linked server error but if anyone knows different let me know ;)

Solution

  • you can use the cast function: cast( rownum AS NUMBER(10)) as row_id 

    create or replace view tvv as 
   select cast( rownum AS NUMBER(10)) as row_id 
   from all_objects 
   where rownum < 10;

> desc tvv
 Name              Null?    Typ
 ----------------- -------- ------------
 ROW_ID                     NUMBER(10)