Before You Begin

This 15-minute tutorial demonstrates how to declare a remote stored procedure and execute a bulk fetch of the data from the stored procedure using the DBMS_HS_PASSTHROUGH package.

Background

This tutorial demonstrates that you can send SQL statements directly to a remote foreign data source using the DBMS_HS_PASSTHROUGH PL/SQL package. This is useful when you want to perform an operation on the remote foreign data source and there is no equivalent Oracle SQL to do so.

Also, this tutorial uses a configured Oracle Database Gateway for SQL Server (dg4msql) and the Oracle Net listener that has an entry for it.

What Do You Need?

• You need a database link.
• In the gateway configuration file, someone with DBA privileges needs to configure the gateway to handle result sets by setting the HS_FDS_RESULTSET_SUPPORT initialization parameter TRUE. For example:

   HS_FDS_RESULTSET_SUPPORT = TRUE

  The gateway configuration file is located at:

   $ORACLE_HOME/target/admin/initgateway_sid.ora

  For this tutorial:
  
  
  • The value for target is dg4msql.
  • The value for initgateway_sid.ora is initdg4msql.ora.
  
  Remember that you need DBA privileges to do this.
• You need access to the following examples used in this tutorial:
• Example: Create a Table in the Remote Foreign Data Source
• Example: Populate the Table
• Example: Create a Stored Procedure
• Example: Create a REF CURSOR and Execute a Bulk Fetch

Create a Database Link to the Gateway

Create a database link to your gateway.

CONNECT / AS SYSDBA;
Connected.
SET ECHO ON;
SET SERVEROUTPUT ON;
CREATE DATABASE LINK linkname
CONNECT TO remote_username IDENTIFIED BY remote_password
USING 'tnsentry_to_gateway';

Database link created.

For this tutorial:

• The value for linkname is fdslink.
• The value for remote_username and remote_password are supplied by you.
• The value for tnsentry_to_gateway is dg4msql.

Create a Table in the Remote Foreign Data Source

In order to create objects in the remote non-Oracle database, the user specified in the database link must have CREATE privileges granted.

Example: Create a Table in the Remote Foreign Data Source

Populate the Table

Populate the table with sample data.

Example: Populate the Table

Create a Stored Procedure

Create a stored procedure that reads data from the table and returns a result set.

Example: Create a Stored Procedure

Create a REF CURSOR and Execute a Bulk Fetch

To execute a bulk fetch, you must first create a REF CURSOR. Once you have created the REF CURSOR, you can execute a bulk fetch.

Example: Create a REF CURSOR and Execute a Bulk Fetch

Want to Learn More?

• Oracle Database Heterogeneous Connectivity User's Guide
• Oracle Database PL/SQL Packages and Types Reference