In one of my former jobs I had been in charge of a rather small project tracking tool for the engineering department. As buying equipment and ordering services was one of the duties of these engineers these tasks had also been modelled into the tool. Of course the orders had to be transported into the central financial application. Therefore a meeting with the consultants for that ERP system (no, not ours… they had got the wrong one there… ;-)) was set up.
After I explained what data our tool can deliver and offered to create some database views for that purpose, I asked for what information the ERP system would need to be able process the data correctly. The consultant smiled and told me “Don’t bother – just give us direct access to the database and we will suck the data out!”
It’s unnecessary to tell that this interface was never brought fully productive…
Anyhow: connecting systems is one of the major problems bigger IT-landscapes face. SAP Netweaver it a tool specifically designed for this task. With it it’s possible to define system interfaces that support each of the attached system to “grow” and develop further and still being able to use the interface. (XI would be the technology in charge).
But there is the downside of complexity. Sometimes you just want to get some data from one database of a system into the one of a different system without having to define a messaging infrastructure first.
With the db-links of Oracle this is quite easy. Unfortunately these links only seem to work with Oracle databases… but only on first sight!
Let’s assume you’ve got a MaxDB and a Oracle Database and want some data from that MaxDB in the Oracle Database. That can be done by either exporting the data from MaxDB into flat files and then import it into Oracle (for which you would have to have some 3rd party tool) or you access the data in the MaxDB directly from Oracle.
Let’s see how this is done.
Oracle comes with a functionality called “Generic Connectivity” (GC). GC enables Oracle to access data via DB-Links that can be reached through OLE DB or a ODBC connection.
In this example I will set up an ODBC connection to my MaxDB database and perform the necessary steps to access the data out of oracle. I assume that both databases are already correctly installed and that there is the ODBC driver for MaxDB installed on the Oracle Server.
1. Create a ODBC connection
START -> Programs -> Administrative Tools -> Data Sources (ODBC)
or
START -> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)
Change to “System DSN” and click “Add…”
In the “Create New Data Source” dialog choose the “MaxDB” ODBC driver and click on “Finish”.
In the upcoming dialog I enter the following:
Data Source Name: SDB
\
Server: <hostname of MaxDB Server>
\
Database: SDB
\
Then click on “Settings …” and select the ORACLE sqlmode. Close the dialog windows by clicking “OK”.
Now a new ODBC data source has been created.
2. Modify the Oracle Net setup
Create a file named init<SID>.ora in the %ORACLE_HOME%/hs/admin-Folder. In my case the file is named initSDB.ora If you look into that folder you’ll find also templates for this file, but the one I use just contains these two lines:
HS_FDS_CONNECT_INFO = SDB
\
HS_FDS_TRACE_LEVEL = OFF
\
Basically this will tell Oracle later on, which ODBC data source should be used. So make sure that the <SID> is correct here.
The next thing is to add an entry into the tnsnames.ora file like this one:
SDB.WORLD =
\
(DESCRIPTION=
\
(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1512))
\
(CONNECT_DATA=(SID=SDB))
\
(HS=OK)
\
)
\
The (HS=OK) part is the key point here! Make sure to check if the Listener port is correct for your system.
As a last step of configuring the listener configuration has to be made aware that it should listen to the new entry. So add this to the SID_LIST_LISTENER list in the listener.ora-file:
(SID_DESC=
\
(SID_NAME=SDB)
\
(ORACLE_HOME=c:\\oracle\\tdb\\102)
\
(PROGRAM=hsodbc)
\
)
\
With that the Oracle listener will startup a kind of adapter program when a connection to this SID is made. This adapter program then will translate the SQL*NET requests into ODBC calls.
Now restart the oracle listener:
lsnrctl reload
\
3. Creating a DB-Link inside the Oracle database
The finishing step to access data in the MaxDB database out of Oracle is to create a db-link that points to the new tnsnames.ora-entry:
sqlplus /
\
create database link sdb.world connect to lars identified by lars using ‘SDB.WORLD’;
\
Of course you will have to put in your username and password in here, but I guess you get how this should work.
4. The first query:
Now we’re already ready to get data out from the MaxDB:
select * from user_catalog@sdb.world;
\
TABLE_NAME TABLE_TYPE
\
--------------- -----------
\
TEST TABLE
\
If you now get a error message like this:
ERROR at line 1:
\
ORA-02085: database link SDB.WORLD connects to HO.WORLD
\
Then this is just because the oracle parameter GLOBAL_NAME is currently set to TRUE. After a
alter system set global_names=FALSE scope=both;
\
this error will be gone.
So we’ve an easy way to get data from a MaxDB into a Oracle database. Actually this will work with any database that you can reach over ODBC.
For further reading have a look at: Oracle® Database Heterogeneous Connectivity Administrator's Guide 10g Release 2 (10.2) 7 Generic Connectivity (http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14232/gencon.htm)
A Warning
I just presented an easy rather quick and dirty way two make up an interface between to systems, by just accessing the data in the databases of the systems. Please don’t consider this to be the proper way to implement an interface! This is nothing but pure data shovelling and should not even be thought as the right layer on which the system interface is designed. What happens when the data definition in on of the databases change? Do you want to change the interface, too? Every time? Surely not! How do you keep track of problems in the interface? Write a logging facility yourself?
Therefore: Use what you’ve paid for! Use Netweaver! It’s already there and it’s designed just for this kind of tasks.
Hope you have some fun with this technical option anyhow.
Best regards,
Lars
