Use Heterogeneous Services to access non-Oracle data

Oracle's Heterogeneous Services (HS) allows users to access data stored outside Oracle as if the data were in a local Oracle table. It can be intimidating setting up HS in Oracle SQL*Net, so I'll provide some simple examples.

Oracle's Heterogeneous Services (HS) allows users to access data stored outside Oracle as if the data were in a local Oracle table. It can be intimidating setting up HS in Oracle SQL*Net, so I'll provide some simple examples.

It's best to start from the data and work your way towards actual configuration. For the example in Listing A (which is for Oracle 9i on Windows), I'll start with a list of Australian capitals and their geographical coordinates in a file called capital.txt in an empty folder.

From this data, I'll create three data sources using the same data. For the first example, I'll use the Microsoft Text Driver to access the comma-delimited text file directly. For the second data source, I use Microsoft Access, create a new blank database (capital.mdb), and import the file into the Capitals table. For the third data source, I use MySQL and issue the following commands from the command line in the same folder as the capitals.txt file. View Listing B.

Now that I have my data sources ready, I need to set up ODBC to access these sources. I create System DSNs for all three and call them capital, capital2, and capital3. For capital, I select the folder containing capital.txt and define a format for the file. (Defining a format will create a schema.ini file in the folder.) Listing C shows how mine looks.

For the Microsoft Access data source, I select the capital.mdb and name the DSN capital2. For MySQL, I use MySQL ODBC 3.51 Driver (which is available separately from MySQL for Windows), name it capital3, and take out all the defaults. For each one, I verify that I could access each data source using this simple VBS script. (To do so, store it in a file called tester.vbs, and run it with cscripttester.vbs from the command line.) Change the ConnectString to the DSN you created. If you configure it correctly, it should list out the names of each city. View Listing D.

The next step is to configure each HS. In the ORACLE_HOME/hs/admin directory, I created "initcapital.ora" for my first data source (the file must start be named "init<SID>.ora"). To connect to a System DSN, I enter the following two lines:


Then I do the same for the Microsoft Access data source in initcapital2.ora:


For the MySQL data source, I switch to OLE DB for a comparison. To support OLE DB, I use a UDL file to define the data source and reference the UDL file from my init file. To create a UDL file, create a text file, rename it to something like capital.udl, double-click it, and an editor will come up allowing you to configure the data source. I built a connect string and ended up with a UDL file (in my ORACLE_HOME/hs/admin directory) that contains the code in Listing E.

I reference this UDL file in my initcapital3.ora file with these two lines:


You can access HS by the SQL*Net listener, not the client. So you must let SQL*Net know about these services and the programs that proxy Oracle SQL to these ODBC and OLE DB providers. In the listener.ora file in ORACLE_HOME/network/admin, you should see an area for SID_LIST_LISTENER, and within that an SID_LIST. In that list, I add the entries for each service. See Listing F.

You use the hsodbc program for ODBC connections, while hsolesql and hsolefs programs access OLE DB. You'll use the hsolesql program for providers that can process SQL and hsolefs for other providers. The ORACLE_HOME directory directs the SQL*Net listener to the correct installation for that program. Once you make these changes, restart the listener by entering the following code at the command line:

lsnrctl stop
lsnrctl start

Now you need to add entries to point to the HS in the ORACLE_HOME/network/admin/tnsnames.ora file. View Listing G.

I can test these connections from the command line using the TNSPING utility, such as tnsping capital. However, this only checks the connection up to SQL*Net, and not whether the data is available. Now I need to go into the database and create some database links:

SQL> create database link capital using 'capital';
SQL> create database link capital2 using 'capital2';
SQL> create database link capital3 using 'capital3';

Notice that the database link is stored on the server, so the tnsnames.ora file is being used to access these services, not the client. Since a client can never "connect" to an HS service directly, you don't need to configure it on remote machines.

These are all the steps for configuration. To access the first data source, you have to keep in mind that Oracle will take your table name and pass it to the ODBC driver. The Microsoft Text Driver ODBC driver expects a file name, so the table name should be capital.txt. However, if you enter select * from capital.txt@capital, Oracle will think you're trying to access table "txt" owned by user "capital". You can use the "#" character to avoid this, so type the following:

select * from capital#txt@capital;

If this is the first time you used the ODBC Heterogeneous Service, the first time you execute this statement, it will be slow. That is because it is automatically registering itself in the HS_FDS system tables. The next two data sources are more straightforward:

select * from capital@capital2;
select * from capital@capital3;

Notice that many heterogeneous service translation schemes are case-sensitive. For example, in order for me only to select the name column from the MySQL example, I have to enclose the column name in quotes, like this:

select "name" from capital@capital3;

Don't expect blazing speeds using this technique, and translations aren't perfect and may require the use of "pass-through SQL" to work correctly. HS can be useful for importing data from non-Oracle sources using portable, server-side code rather than using external batch jobs. Listing H shows how you can create a table or materialized view with a simple call (column names are case-sensitive).

Scott Stephens worked for Oracle for more than 13 years in technical support, e-commerce, marketing, and software development.