Re: Access to heterogeneous data sources - XML

From: Glen A Stromquist <glen_stromquist_at_no_spam_yahoo.com>
Date: Wed, 10 Mar 2004 00:25:55 GMT
Message-ID: <ngt3c.181119$Hy3.11754@edtnps89>

Andrea M. Segovia wrote:

> Mark Bole <makbo_at_pacbell.net> wrote in message news:<9X03c.7348$gd2.4732_at_newssvr27.news.prodigy.com>...
>

>>Andrea M. Segovia wrote:
>>
>>>Hello, everyone:
>>>
>>>We have a Oracle 3 tier application (using Oracle iAs to a back-end
>>>Oracle database on an Tru64 UNIX server) which also incorporates
>>>custom written analysis and mapping tools for fisheries-related data.
>>>
>>>There is interest in broadening the scope of this application to also
>>>gather data from other data sources. The problem is that the other
>>>data sources are not Oracle databases (some are MS-SQL Server, others
>>>are MS-Access databases). Moving data to an Oracle format is not a
>>>possibility, as some of these data sources are out of our
>>>organization's control. To complicate matters even further, some of
>>>the data sources are not local (ie. in remote locations in either
>>>internal or external networks from one end of the country to another).
>>>
>>>Is XML a possibility for this? What XML functionality is available
>>>through Oracle, MS-SQL server? How would I do this using XML?
>>>
>>>Regards,
>>>Andrea
>>
>>Oracle 9i includes XML DB, as well as a number of utilities for parsing 
>>and writing XML files.
>>
>>With a free, one-time resgistration to OTN (Oracle Technology Network) 
>>go to the following link and look for books start with "XML" for 
>>documentation on the former.  Also look inside the "Supplied PL/SQL 
>>Packages" book for procedures starting with "DBMS_XML" for documentation 
>>on the latter.
>>
>>http://download-west.oracle.com/docs/cd/B10501_01/nav/docindex.htm?remark=homepage
>>
>>If you can get the data exported from the other sources in XML, you 
>>should be able to import it one way or another into your Oracle backend, 
>>using Oracle-supplied tools.  My guess is this will involve a joint 
>>design and programming effort between your team and the owners of the 
>>external data.
>>
>>--Mark Bole

>
>
>
> Does this solution support real-time access through the Oracle
> database to other databases with XML support? Since you speak of
> "exporting" the data, I am thinking you mean the traditional export
> and import of data into the database.
>
> Unfortunately, what I am looking for is real-time access through the
> Oracle database to the external data sources, that for all intents and
> purposes looks like the data resides locally.
>
> I have looked at a couple of ODBC-type solutions, as well, but these
> solutions often require software agents/servers on either side of the
> connection which are vendor specific. I was trying to come up with a
> "cleaner" solution that we could aim towards.....I believe that these
> types of problems will keep cropping up in the future.
>
> Thanks for the input,
> Andrea

I have set up heterogeneous services on an 8.1.7.4 db residing on a windows server thru ODBC to an MSaccess db elsewhere on our network. The tables can be queried from within oracle like they are on a regular oracle dblink. It's a bit quirky sometimes, especially with the dblink you need to set up, but I pointed mine directly to the server rather than use the tns alias.

hth Received on Tue Mar 09 2004 - 18:25:55 CST