3

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

Deviland
  • 3,324
  • 7
  • 32
  • 53
  • What problem is this causing? – Gordon Linoff Aug 13 '12 at 13:36
  • Msg 7356, Level 16, State 1, Line 1 The OLE DB provider "MSDAORA" for linked server "LINKEDSERVER" supplied inconsistent metadata for a column. The column "ROW_ID" (compile-time ordinal 1) of object ""VIEWNAME"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time. – Deviland Aug 13 '12 at 13:38

2 Answers2

6

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)
schurik
  • 7,798
  • 2
  • 23
  • 29
  • I did this thanks but the same error appears and complaining about ROW_ID 's DataType I have lookd at the returned datatype using SQLDeveloper and this shows that the datatype has not changed and is still set to NUMBER :s – Deviland Aug 13 '12 at 14:01
  • @Deviland could you post you dml statment. What datatype shows in SQLDeveloper if you create the view from my answer? – schurik Aug 13 '12 at 14:11
  • it would appear that even if you choose to cast rownum it will still return a NUMBER datatype. – Deviland Aug 14 '12 at 10:25
0

Not really a good solution but by removing the rownum and working around the problem from a different angle I have removed the error.

Deviland
  • 3,324
  • 7
  • 32
  • 53