How to execute remote query by Oracle Database Link

Question

I use Oracle Database Link to query data from SQL Server. The query is like:

select *
from tableA@DL_SqlServer a
join tableB@DL_SqlServer b
on a.ID = b.ID

tableA and tableB is large and the result is relatively small. This query executes quickly in SQL Server since indexes are built both on the two tables. But it is very slow on Oracle Database Link to SQL Server. I guess the join operation is performed on Oracle side not on SQL Server side, thus the indexes are not used. Since I just need the joined result, I prefer to perform the query entirely on SQL Server and get the small result only. I konw that using SQL Server's linked server and OPENQUERY function can achieve this goal. I wonder how to do this on Oracle Database Link. Thanks! Btw, I have no privilege to create views on SQL Sevrer.

Create a view on your sql server for it.

Jesse
– Jesse

2013-02-16 01:35:15 +00:00
Commented Feb 16, 2013 at 1:35 — Jesse
– Jesse, Commented Feb 16, 2013 at 1:35
Sorry, I have no privilege to create views on SQL Sevrer.

xiagao1982
– xiagao1982

2013-02-16 02:39:41 +00:00
Commented Feb 16, 2013 at 2:39 — xiagao1982
– xiagao1982, Commented Feb 16, 2013 at 2:39

Justin Cave · Accepted Answer · 2013-02-16 03:21:56Z

1

You most likely need to use the DBMS_HS_PASSTHROUGH package. Something like

DECLARE
  l_cursor PLS_INTEGER;
BEGIN
  l_cursor := dbms_hs_passthrough.open_cursor@dblink_to_sql_server;
  dbms_hs_passthrough.parse@dblink_to_sql_server( l_cursor, <<select statement>> );
  while dbms_hs_passthrough.fetch_row@link_to_sql_server(l_cursor) > 0 
  loop
    dbms_hs_passthrough.get_value@dblink_to_sqlserver( l_cursor, 1, <<local variable for first column>> );
    dbms_hs_passthrough.get_value@dblink_to_sqlserver( l_cursor, 2, <<local variable for second column>> );
    ...
  end loop;
  dbms_hs_passthrough.close_cursor@dblink_to_sqlserver( l_cursor );
END;

answered Feb 16, 2013 at 3:21

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute remote query by Oracle Database Link

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related