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!