You can translate the question and the replies:

Although I can query the table for count from oracle db to Denodo. But while fetching data through it results in the following error.

sys@oracle> select count(1) from "Finance"."mis_monthly_data"@"VDBSTAGE" ; COUNT(1) ---------- 44087129 1 row selected. select * from FDSL_UAT.MIS_MONTHLY_DATA where rownum<10 * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ERROR: Error executing query. Total time 177.325 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ORA-12801: error signaled in par java.sql.SQLException: Error executing query. Total time 177.325 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ORA-12801: error signaled in par; No query has been executed with that handle { ,NativeErr = 1} ORA-02063: preceding 12 lines from VDBSTAGE sys@oracle> select * from "Finance"."mis_monthly_data"@"VDBSTAGE" ; select * from "Finance"."mis_monthly_data"@"VDBSTAGE" * ERROR at line 1: ORA-28500: connection from ORACLE to a non-Oracle system returned this message: ERROR: Error executing query. Total time 167.455 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ORA-12801: error signaled in par java.sql.SQLException: Error executing query. Total time 167.455 seconds. QUERY [VIRTUAL] [ERROR] QUERY [JDBC WRAPPER] [ERROR] QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ORA-12801: error signaled in par; No query has been executed with that handle { ,NativeErr = 1} ORA-02063: preceding 12 lines from VDBSTAGE and hence the view that is defined on this table results in the same error. Tried executing the query to retrive first 10 rows only but still same error.
user
20-05-2020 06:52:58 -0400

3 Answers

Hi, I have been able to run a select statement to fetch data from Oracle based on row numbers by using the FETCH or LIMIT clause, for example to select first 10 rows: ```SELECT * FROM TABLE LIMIT 10``` ```SELECT * FROM TABLE FETCH NEXT 10 ROWS ONLY``` More information can be found in the Virtual DataPort VQL Guide section on [OFFSET, FETCH, and LIMIT](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/queries_select_statement/offset_fetch_and_limit/offset_fetch_and_limit). Hope this helps!
Denodo Team
26-05-2020 15:58:09 -0400
We have created a view on Oracle server , which use DBlink point to Denodo source Oracle view name: FDSL_UAT.MIS_MONTHLY_DATA Tried to query as: select * from FDSL_UAT.MIS_MONTHLY_DATA where rownum<10 But it report error : QUERY [JDBC ROUTE] [ERROR] Received exception with message 'ORA-12801: error signaled in par; No query has been executed with that handle { ,NativeErr = 1} ORA-02063: preceding 12 lines from VDBSTAGE(this is DB link name ) Besides, I tried with below query for same table and It works and gives the count . Failing to understand this behaviour. select "gbs_level_5_book",sum("qtd_amount_usd") from FDSL_UAT.MIS_MONTHLY_DATA where "parent_tree_book" ='GBSALT_BK_WS_NET' group by "gbs_level_5_book"
user
27-05-2020 21:18:55 -0400
Hi, I have found when using Denodo as a source using a linked table in Oracle, it works best to use a function supported by both Denodo and Oracle, like FETCH and OFFSET. Denodo does not support the Oracle function rownum() in a WHERE clause. Hope this helps!
Denodo Team
03-06-2020 17:18:05 -0400
You must sign in to add an answer. If you do not have an account, you can register here