I have created a DB link between Oracle 10.2.0.5 to SQL server 2005. Is there any way to limit the number of records from sql server table accessed from oracle using DB link. I tried using rownum but error came that it is not supported in context. The sql server top 5 did not worked as well.
The gateway is Oracle 11.2.0 gateway for windows 32 bit.
The DB version is Oracle 10g 10.2.0.5 64 bit.
In the gateway listener.ora file; the listener entry contain string 'PROGRAM = dg4msql'.
The query is
select * from my_table@sdeslink where rownum < 5;
SQL Error: ORA-02070: database SDESLINK does not support ROWNUM in this context
02070. 00000 - "database %s%s does not support %s in this context"
*Cause: The remote database does not support the named capability in
the context in which it is used.
*Action: Simplify the SQL statement.
When you get the error -
Msg 207, Level 16, State 1, Line 1
Invalid column name 'ROWNUM'.
can you give the following information -
- what SQL are you running ?
- what is the full name and version of the gateway you are using ? Is it Dg4ODBC or DG4MSQL ?
- what is the version of the RDBMS being used ?
I am unable to reproduce this problem using 11.2 gateways and 10.2 RDBMS.