X
Home & Office

Use Heterogeneous Services to access non-Oracle data

If you need to use data from a separate system inside Oracle, you can treat it as a local table using Heterogeneous Services.
Written by Scott Stephens, Contributor

If you need to use data from a separate system inside Oracle, you can treat it as a local table using Heterogeneous Services

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 this example (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.

longitude,latitude,name
115.85,-31.95,Perth
130.83,-12.45,Darwin
138.6,-34.93,Adelaide
144.97,-37.82,Melbourne
147.32,-42.88,Hobart
149.13,-35.3,Canberra
151.2,-33.87,Sydney
153.02,-27.47,Brisbane

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:

create database capital;
use capital;
create table capital(name varchar(30) not null, longitude float, latitude float);
load data local infile 'capital.txt' into table capital
fields terminated by ',' lines terminated by '\r\n' ignore 1 lines
(latitude,longitude,name);
quit

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.) This is how mine looks:

[capital.txt]
ColNameHeader=True
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI
Col1=LONGITUDE Float
Col2=LATITUDE Float
Col3=NAME Char Width 30
[schema.ini]
ColNameHeader=False
Format=CSVDelimited
MaxScanRows=0
CharacterSet=ANSI

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 cscript tester.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.

dim ws,con,rs
set ws = CreateObject("WScript.Shell")
set con = CreateObject("ADODB.Connection")
set rs = CreateObject("ADODB.RecordSet")
con.ConnectionString="capital3"
con.Open
rs.open "select * from capital",con,,3
If Not rs.BOF Then
      rs.MoveFirst
      While Not rs.EOF
          wscript.echo rs.Fields("name").value
          rs.MoveNext
      Wend
End If
rs.Close
con.Close
set rs = Nothing
set con = Nothing
set ws = nothing

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.ora"). To connect to a System DSN, I enter the following two lines:

HS_FDS_CONNECT_INFO = capital
HS_FDS_TRACE_LEVEL = 0

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

HS_FDS_CONNECT_INFO = capital2
HS_FDS_TRACE_LEVEL = 0

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 following:

[oledb]
; Everything after this line is an OLE DB initstring
Provider=MSDASQL.1;Persist Security Info=False;Extended
Properties="DSN=capital3;DESC=MySQL ODBC 3.51 Driver
DSN;DATABASE=capital;SERVER=localhost;UID=;PASSWORD=;PORT=3306;SOCKET=;OPTION= 3;STMT=;"

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


HS_FDS_CONNECT_INFO = "UDLFILE=c:/oracle/ora92/hs/admin/capital.udl"
HS_FDS_TRACE_LEVEL = 0

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, like so:


     (SID_DESC =
         (SID_NAME = capital)
         (ORACLE_HOME = C:\oracle\ora92)
         (program = hsodbc)
        )
         (SID_DESC =
         (SID_NAME = capital2)
         (ORACLE_HOME = C:\oracle\ora92)
         (program = hsodbc)
        )
         (SID_DESC =
         (SID_NAME = capital3)
         (ORACLE_HOME = C:\oracle\ora92)
         (program = hsolesql)
        )

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:

capital =
   (description =
    (address = (protocol=tcp)(host=localhost)(port=1521))
     (connect_data = (sid=capital))
     (hs=ok)
   )

capital2 =
    (description =
     (address = (protocol=tcp)(host=localhost)(port=1521))
     (connect_data = (sid=capital2))
     (hs=ok)
    )

capital3 =
    (description =
    (address = (protocol=tcp)(host=localhost)(port=1521))
    (connect_data = (sid=capital3))
    (hs=ok)
   )

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. The following shows how you can create a table or materialized view with a simple call (column names are case-sensitive):

create table capital as     select "name" as name,"longitude" as longitude,"latitude" as latitude from
  capital@capital2;

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

Editorial standards