CODEWITHSUNDEEP
sql-serveroracle-database
xiagao1982
xiagao1982

Reputation: 1097

How to execute remote query by Oracle Database Link

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.

Upvotes: 1

Views: 3927

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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;

Upvotes: 1

Related Questions