Tips and Tricks: Creating an Oracle Heterogeneous Data Source

Author: Scott Foulk

July 2008

Situation
The company is implementing a SQL server database and an existing Oracle Database needs to be able to communicate with it to exchange data.

Problem
The department that created the SQL server database and loaded all of its data did not take into account that the correlating data is in an Oracle database.

Solution
Even though this may seem like an almost impossible task, all of the data that is housed in the SQL database can be accessed by the Oracle database simply by creating a System DSN and a heterogeneous data source within Oracle. This will allow the Oracle database users to query the SQL server database and pass the information back to Oracle.

Tips
Connect to the server that houses the Oracle database through remote desktop or through the console. Once connected, create the System DSN. Go to Administrative tools, open ODBC data sources, click the System DSN tab and select Add. A wizard should open to walk you through the process. Select the SQL Server driver and click next. On the next screen give your new connection a name. I recommend using the name of the SQL database that you want to connect to. Once the name has been specified, select the drop down and the server name that houses the SQL database. Select next. On the following screen you will setup how you are to connect to the SQL database; this will need to match how the SQL database was configured. The choices are with NT authentication orSQL server authentication. For this example we will use SQL server authentication. When this is selected the username and password fields will become active. Enter the username and password of the SQL database you want to connect with and select next. This next step is the most important part and perhaps missed the most. You must change the default database, which is usuallymaster. Select the box to change the default database, and from the drop down select the SQL database you want to connect to; then select next and finish. At this point you can begin to configure the Oracle heterogonous data source.
First you must edit the listener.ora. This creates the instance that the tnsnames.ora references on the connection through SQL*Plus. It creates a SID_NAME and specifies the Oracle executable to run on connection. The listener.ora is found within $ORACLE_HOME/network/admin. Configuring HSODBC needs the creation of a new SID_DESC within SID_LIST in the SID_LIST_LISTENER. Below is an example of an edited listener.ora:

(SID_DESC=
(SID_NAME=sqldatabasename)
(ORACLE_HOME=C:\ORACLE_HOME)
(PROGRAM=hsodbc)

Next, edit the tnanames.ora, found in $ORACLE_HOME/network/admin. This entry identifies the Oracle server to be attached to and the SID_NAME to be used. A new Oracle definition needs to be created:

#-------------------------------------------------------------------------
# These are the service names Heterogeneous Data Source going to SQL Server
# on servername
#-------------------------------------------------------------------------
sqldatabase.world =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=1521))
(CONNECT_DATA=(SID=hsodbc))
(HS=Yes)
)

There is one more file that needs to be created and is placed in $ORACLE_HOME\hs\admin. It is the init file that holds the HS parameters needed for an ODBC agent and must contain sqldatabasename from the listener.ora in the filename. If sqldatabasename is SQLSERVER then the filename would be initSQLSERVER.ora. Open a text editor and create the init file. An example of this is:

#
# HS init parameters
#
#HS_FDS_CONNECT_INFO =
#HS_FDS_TRACE_LEVEL =

HS_FDS_CONNECT_INFO = SQLDATABASENAME
HS_FDS_TRACE_LEVEL = 0

#
# Environment variables required for the non-Oracle system
#
#set =

Now that these three files have been edited and created, you will need to restart the listener before creating the database link in oracle. Once the listener has restarted, log in to SQL*plus connecting to the database instance that needs to connect to SQL. Once connected enter the following command:

CREATE PUBLIC DATABASE LINK hslink CONNECT TO "dbuser"
IDENTIFIED BY "dbpassword" using 'hsconnectid';

In the above command, hslink is the name of the oracle database link that is created using the hsconnect oracle entry within the tnsnames.ora. Note that when creating the database link, the database user and password are entered. These are not the operating system user and passwords. Also if the 'hsconnectid' is not within single quotes, errors are returned:

ERROR at line 1:
ORA-02010: missing host connect string

Once the database link is created, you can pass a sql statement to it:

Select * from sysobjects@hslink
When passing in SQL to the newly created instance, the syntax is always:
table@linkname

If you need more information, contact Gary Codeluppi at 937-431-1026 x123 or visit the Ross Group Inc web site at www.rossgroupinc.com.

Set as favorite

Bookmark

Email this

Comments (0) Add Comment

Write comment