Data Management

Passing SQL to Heterogeneous Services with DBMS_HS_PASSTHROUGH

There are some functions that cannot be mapped from Oracle SQL to the remote database SQL. In those cases, you should consider passing SQL directly to the remote host. Learn how to use the DBMS_HS_PASSTHROUGH "package" to pass SQL through to the remote database.

Oracle's Heterogeneous Services layer is a way for an Oracle database server to access data on external, non-Oracle databases transparently using Oracle.

For example, you can configure Heterogeneous Services and SQL*Net to access a Microsoft Access database through ODBC or OLE DB. Afterwards, you can query the table as if it were in a remote Oracle database:

create database link  using '';
select name,latitude,longitude from capital@;

The Oracle Heterogeneous Services layer does a fairly good job at mapping common SQL grouping functions and joins to an external database provider. If the provider can perform a MAX function, then it's usually better to let the provider do it and return one row than to make the provider return every single row in the table and make Oracle perform a MAX function, in memory, on the result.

select max("latitude") from capital@;

If you turn on tracing, you will be able to see that the SQL text gets transformed into:

SELECT MAX(A1."latitude") from "CAPITAL" A1

A similar mapping can be performed with MIN, AVG, LIKE, and most relational operations. However, there are some functions that cannot be mapped from Oracle SQL to the remote database SQL. In those cases, you might want to consider passing SQL directly to the remote host. This can also be useful for performing DDL operations against the remote database.

To pass SQL through to the remote database, you must use the DBMS_HS_PASSTHROUGH "package." DBMS_HS_PASSTHROUGH isn't a package you can see in the database or install through SQL—it's embedded inside the RDBMS and can be executed only against Heterogeneous Services connections.

The DBMS_HS_PASSTHROUGH "package" is very similar to the DBMS_SQL package—you must open a cursor and parse a SQL statement. If it's a query, you can fetch data from the query. Listing A is a sample that retrieves a bounding rectangle for my "capital" table. Notice that the syntax for each dbms_hs_passthrough call includes @capital2. This indicates that you're creating and fetching from a cursor created by the Heterogeneous Services layer on the remote database.

View Listing B to see how you can use perform DDL functions using dbms_hs_passthrough. You can also verify that the table was actually created by describing it from SQL*Plus:

describe foo@capital2

TechRepublic's Oracle newsletter covers automating Oracle utilities, generating database alerts, solving directed graph problems, and more. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox