Configuring and using Oracle Database Gateway for ODBC

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Published:
Configuring and using Oracle Database Gateway for ODBC

Introduction


First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms.  Once a Gateway is established you can use it simply by creating a database link just as you would between two Oracle databases.  From there you can then query and modify remote tables, invoke procedures and functions and (depending on your permissions) even execute DDL to create, drop or alter remote objects.

In the 11g release of the Oracle database, ODBC Heterogenous Services were replaced with a Database Gateway.  While other Gateway's (such as those for SQL Server, Informix, DB2, etc.) are separately licensed, the ODBC gateway is included with the license of the database itself.  In other words, you do not need to pay additional fees to use the ODBC gateway (see Oracle Support article ID 232482.1 for more information.)  Of course, with new releases the licensing may change so check with your Oracle representative to be sure.

So, no addtional licensing costs and communicate with any platform is the good news.  Surely there's are downsides...yes, there are some.  The remote communication is through ODBC and not native drivers.  Generic connectivity tends to be slower than native connections.  More importantly, you do have limitations on syntax and feature support.  

From the Gateway for ODBC User Guide:

BLOB and CLOB data cannot be read by pass-through queries
Updates or deletes that include unsupported functions within a WHERE clause are not allowed
Does not support stored procedures
Cannot participate in distributed transactions; they support single-site transactions only
Does not support multithreaded agents
Does not support updating LONG columns with bind variables
Does not support rowids

There are also limitations on SQL and PL/SQL syntax when used with generic connnectivity.  For full details on these see the User Guide linked below.  Last, since the communication is through an ODBC driver, you are, of course, limited by the features and implentations of whatever ODBC driver you have installed.  

Now that we've covered what the Gateway is and some of the caveats.  Let's get into the configuration and use of it.

Installation


There is nothing to install for the Gateway itself.  You will however need an ODBC driver.  The instructions for that will vary by platform and the provider of the driver.  For my examples below I will be using the MS Access ODBC driver on Windows.  The database I'm using is Oracle 11g XE; but I've used the exact same instructions with Enterprise Edition as well.

Configuration


Configuration is fairly simple, consisting of only 5 steps. Define an ODBC DSN, add an entry to your listener, creating a heterogeneous service initialization file, creating a database link and finally: add an entry to your tnsnames.

1. Define an ODBC SYSTEM DSN

The specifics of this will, as with installation, depend on the platform and driver.  In the example below I'm defining a SYSTEM DSN for an old MS Access Northwind database.

ODBC SYSTEM DSN for Northwind DB

2. Add a LISTENER entry

Add a SID_DESC entry for the Gateway to the listener.ora file in the $ORACLE_HOME/network/admin directory of the database server.

SID_LIST_LISTENER =
                        (SID_LIST =
                          (SID_DESC =
                            (SID_NAME = PLSExtProc)
                            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                            (PROGRAM = extproc)
                          )
                          (SID_DESC =
                            (SID_NAME = CLRExtProc)
                            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                            (PROGRAM = extproc)
                          )
                          
                      ####  This is the Gateway piece
                          (SID_DESC =
                            (PROGRAM = dg4odbc)
                            (SID_NAME = eetest)
                            (ORACLE_HOME = C:\oraclexe\app\oracle\product\11.2.0\server)
                          )
                      ####  End of Gateway    
                      
                        )
                      
                      LISTENER =
                        (DESCRIPTION_LIST =
                          (DESCRIPTION =
                            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
                            (ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
                          )
                        )
                      
                      DEFAULT_SERVICE_LISTENER = (XE)

Open in new window


Note, for version 10g and lower using the old Heterogeneous Services agents, the instructions are exactly the same except you would change the listener entry (PROGRAM = dg4odbc) to be (PROGRAM = hsodbc)

 

3. Create a Heterogeneous Services initialization file

Each Gateway needs its own init<SID>.ora file in the $ORACLE_HOME/hs/admin directory of your database server.  In this example I've named my SID "eetest", so the file is "initeetest.ora"
Only one line is actually needed: HS_FDS_CONNECT_INFO.  This line defines which ODBC DSN to use in this Gateway.  You can add additional TRACE and SUPPORT lines too for if you wanted to capture debugging information. In this example I've included the lines but disabled both options as they are unnecessary for normal use.

HS_FDS_CONNECT_INFO = eetest
                      HS_FDS_TRACE_LEVEL = off
                      HS_FDS_SUPPORT_STATISTICS=FALSE 

Open in new window


Other parameters for ODBC include the following:

HS_DB_DOMAIN
HS_DB_INTERNAL_NAME
HS_DB_NAME
HS_DESCRIBE_CACHE_HWM
HS_FDS_CONNECT_INFO
HS_FDS_DEFAULT_OWNER
HS_FDS_FETCH_ROWS
HS_FDS_REMOTE_DB_CHARSET
HS_FDS_REPORT_REAL_AS_DOUBLE
HS_FDS_SQLLEN_INTERPRETATION
HS_FDS_TRACE_LEVEL
HS_LANGUAGE
HS_LONG_PIECE_TRANSFER_SIZE
HS_OPEN_CURSORS
HS_RPC_FETCH_REBLOCKING
HS_RPC_FETCH_SIZE
HS_TIME_ZONE
HS_TRANSACTION_MODEL
IFILE

Descriptions of these can be found in the documentation links below.

4. Create a Database Link

Create a link, the username and password are case sensitive.  In my example of the Northwind database, there is no authentication required by Access so any username/password combination will work.

CREATE DATABASE LINK eetest_link CONNECT TO "user" IDENTIFIED BY "password" USING 'eetest';

Open in new window



5. Add a TNS definition

Add an entry for the Gateway to the tnsnames.ora file in the $ORACLE_HOME/network/admin directory.

eetest=
                        (DESCRIPTION=
                          (ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))
                          (CONNECT_DATA= (SID=eetest))
                          (HS=OK)
                        )   

Open in new window



And that's it!  Let's test it.

Using the Database Link


Now that the gateway is configured and the link is created, you would use it just as you would a dblink between two Oracle databases.  The Northwind database includes case-sensitive column names so I must wrap them in double quotes (") when querying them.

SQL> SELECT "CustomerID", "CompanyName", "ContactName"
                        2    FROM customers@eetest_link
                        3   WHERE ROWNUM < 10;
                      
                      CustomerID                     CompanyName
                                                                                        ContactName
                      ------------------------------ ---------------------------------- ---------------------------------
                      ALFKI                          Alfreds Futterkiste                Maria Anders
                      ANATR                          Ana Trujillo Emparedados y helados Ana Trujillo
                      ANTON                          Antonio Moreno Taquería            Antonio Moreno
                      AROUT                          Around the Horn                    Thomas Hardy
                      BERGS                          Berglunds snabbköp                 Christina Berglund
                      BLAUS                          Blauer See Delikatessen            Hanna Moos
                      BLONP                          Blondel père et fils               Frédérique Citeaux
                      BOLID                          Bólido Comidas preparadas          Martín Sommer
                      BONAP                          Bon app'                           Laurence Lebihan
                      
                      9 rows selected.
                      
                      SQL>

Open in new window


That worked ok, but as mentioned above there are compatibility limitations with ODBC generic connectivity.  For example, if you try to query an Access Memo column it will be converted to an Oracle LONG which has its own limitations.  Furthermore, the conversion is not reliable.  In testing I received multiple different, undesirable results including truncated results and fatal errors:
[Error] Execution (4: 1): ORA-00600: internal error code, arguments: [HO define: Long fetch], [], [], [], [], [], [], [], [], [], [], []

Open in new window


All is not lost though.  The Gateway functionality includes a virtual package accessible through the database link.  This package allows for pass-through queries to run natively on the remote system and you can parse out individual results.  The package is called DBMS_HS_PASSTHROUGH and would be used just as if it were a real package on a remote Oracle system. In the example below, I'm reading the Northwind Employees table which includes the Notes column of type Memo.  Since I can't reliably query the Memo in its entirely I instead use a pass-through query and let Access split the column into smaller 250 character Text/VARCHAR fields.  Using this method I can simply extract pieces of the Memo and then concatenate them together to create a larger string on the Oracle side. In this example I only needed 1000 characters to read the entire contents; but I could concatenate into a CLOB if necessary to accomodate large Memo fields.

Using the DBMS_HS_PASSTHROUGH package is similar to using dynamic sql in Oracle through the DBMS_SQL package.  You declare a cursor variable, attach sql text to the cursor, fetch from it and then parse out individual fields from the result set.

DECLARE
                          v_cursor    BINARY_INTEGER;
                          v_id        NUMBER;
                          v_firstname VARCHAR2(20);
                          v_lastname  VARCHAR2(20);
                          v_temp      VARCHAR2(250);  -- the Access Text functions support up to 255 characters if needed
                          v_notes     VARCHAR2(1000);
                      BEGIN
                          v_cursor := DBMS_HS_PASSTHROUGH.open_cursor@eetest_link;
                          DBMS_HS_PASSTHROUGH.parse@eetest_link(
                              v_cursor,
                              'select employeeid, firstname, lastname , left(notes,250) , mid(notes,251,250),
                               mid(notes,501,250),mid(notes,751,250) from employees order by employeeid'
                          );
                      
                          WHILE DBMS_HS_PASSTHROUGH.fetch_row@eetest_link(v_cursor) > 0
                          LOOP
                              DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 1, v_id);
                              DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 2, v_firstname);
                              DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, 3, v_lastname);
                              v_notes := NULL;
                      
                              FOR i IN 4 .. 7
                              LOOP
                                  DBMS_HS_PASSTHROUGH.get_value@eetest_link(v_cursor, i, v_temp);
                                  v_notes := v_notes || v_temp;
                              END LOOP;
                      
                              DBMS_OUTPUT.put_line(
                                  v_id || ' ' || RPAD(v_firstname, 10) || RPAD(v_lastname, 10) || ' : ' || v_notes
                              );
                          END LOOP;
                      
                          DBMS_HS_PASSTHROUGH.close_cursor@eetest_link(v_cursor);
                      END;
                      /
                      
                      
                      1 Nancy     Davolio    : Education includes a BA in psychology from Colorado State University in 1970.  She also completed "The Art of the Cold Call."  Nancy is a member of Toastmasters International.
                      2 Andrew    Fuller     : Andrew received his BTS commercial in 1974 and a Ph.D. in international marketing from the University of Dallas in 1981.  He is fluent in French and Italian and reads German.  He joined the company as a sales representative, was promoted to sales manager in January 1992 and to vice president of sales in March 1993.  Andrew is a member of the Sales Management Roundtable, the Seattle Chamber of Commerce, and the Pacific Rim Importers Association.
                      3 Janet     Leverling  : Janet has a BS degree in chemistry from Boston College (1984).  She has also completed a certificate program in food retailing management.  Janet was hired as a sales associate in 1991 and promoted to sales representative in February 1992.
                      4 Margaret  Peacock    : Margaret holds a BA in English literature from Concordia College (1958) and an MA from the American Institute of Culinary Arts (1966).  She was assigned to the London office temporarily from July through November 1992.
                      5 Steven    Buchanan   : Steven Buchanan graduated from St. Andrews University, Scotland, with a BSC degree in 1976.  Upon joining the company as a sales representative in 1992, he spent 6 months in an orientation program at the Seattle office and then returned to his permanent post in London.  He was promoted to sales manager in March 1993.  Mr. Buchanan has completed the courses "Successful Telemarketing" and "International Sales Management."  He is fluent in French.
                      6 Michael   Suyama     : Michael is a graduate of Sussex University (MA, economics, 1983) and the University of California at Los Angeles (MBA, marketing, 1986).  He has also taken the courses "Multi-Cultural Selling" and "Time Management for the Sales Professional."  He is fluent in Japanese and can read and write French, Portuguese, and Spanish.
                      7 Robert    King       : Robert King served in the Peace Corps and traveled extensively before completing his degree in English at the University of Michigan in 1992, the year he joined the company.  After completing a course entitled "Selling in Europe," he was transferred to the London office in March 1993.
                      8 Laura     Callahan   : Laura received a BA in psychology from the University of Washington.  She has also completed a course in business French.  She reads and writes French.
                      9 Anne      Dodsworth  : Anne has a BA degree in English from St. Lawrence College.  She is fluent in French and German.

Open in new window


For more information about the DBMS_HS_PASSTHROUGH package see the PL/SQL Packages and Types Reference link below


Other Resources



You will need to have an Oracle Support account to access the following Document ID's from https://support.oracle.com/

Master Note for Oracle Gateway Products  - ID 1083703.1
There is a public Oracle Blog entry that (as of this writing) is a couple years old; but still mostly up-to-date: https://blogs.oracle.com/db/entry/oracle_gateway_master_note
Gateway and Generic Connectivity Licensing Considerations - ID 232482.1
How to Resolve Common Errors Encountered while using Transparent Gateways or Generic Connectivity - ID 234517.1


Thanks for reading and I hope you find it helpful.
Questions and comments welcome as always.
2
27,448 Views
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT

Comments (4)

CERTIFIED EXPERT

Commented:
sdstuber, got one doubt... I understood that we can query a non-oracle system using database gateway from our oracle database..

as oracle docs say that procedures are not supported, in fact pl/sql is not supported.. but does all the SQL functions are supported..?

for example can i use pipe lined functions/ranks/partition statements in the select query in oracle to get it retreived from the gateway..

will this query work?

where in level (pseudo column of oracle), sysdate being oracle function, and sqlserverfunction in oracle statement...

select level,sysdate,col1,SQLServerfunction() from sqlservertable@gatewaydblink
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
No, for several reasons, not all of which are gateway related.

First, you can't use LEVEL without a CONNECT BY clause.

ORA-01788: CONNECT BY clause required in this query block

Second, you can't use CONNECT BY through the gateway, you would get

ORA-02070: database GATEWAYDBLINK does not support a connect by clause in this context

Third, remote objects invoked across a dblink must include the @link  syntax on them, otherwise the function would be resolved as if it were a local object

Fourth, no you can't invoke remote functions through sql via a gateway link, even if you do include the @link syntax.

You might or might not be able to invoke the functions with a pass-through query.  That would depend mostly on the remote db and the odbc drivers you're using and not so much anything in Oracle itself.
CERTIFIED EXPERT

Commented:
Hmm.. sounds interesting..!! so basically most of things depends on the target database..

this is a nice article.. Thanks for the article sdstuber..!!!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Author

Commented:
Anything done with the pass-through virtual package will be dependent on the target and the odbc drivers.  When using that, Oracle is simply the recipient of whatever those return.

If you need features that aren't available through ODBC Generic connectivity then you may need to invest in the separately licensed native gateways.

The ODBC Gateway is great for its versatility and that there is no additional cost for it; but it is the lowest rung of functionality.

I'm glad you enjoyed the article, thank you for reading.

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.