Re: Loading External ODBC Data Source to Oracle Table via PL/SQL

From: Sergey Adamenko <adamenko_at_i.com.ua>
Date: Fri, 23 Aug 2002 22:47:52 +0300
Message-ID: <ak7g40$22d8$1@news.dg.net.ua>

"PLSQLANDODBC" <plsqlandodbc_at_aol.com> ÓÏÏÂÝÉÌ/ÓÏÏÂÝÉÌÁ × ÎÏ×ÏÓÔÑÈ ÓÌÅÄÕÀÝÅÅ: news:20020823010701.28064.00003303_at_mb-fi.aol.com...
> I'd like to be able to take data from an external ODBC data source and load
> this into an Oracle table. Ideally I'd like to do this in a PL/SQL procedure.
> Alternative solutions are also welcome but I'm really looking for the PL/SQL
> option.
>
> Can someone point me to some info on this on how to get started? Even better
> would be some sample code if that exists. I've been searching the web and so
> far have not had any luck. Are there any sites in particular that are good
for
> finding code exampless?
>
> Thanks!

You can use Heterogeneous Services to access an ODBC source directly from SQL or PL/SQL.
With the service configured you can issue: CREATE TABLE DATA_FROM_ODBC
SELECT * FROM CUSTOMER_at_non_oracle_system;

Here is an extract from Oracle8i Distributed Database Systems Release 2 (8.1.6), Part III
Heterogeneous Services Concepts and Administration:

<<----------------------------------------------

Heterogeneous Services (HS) is an integrated component within the Oracle8i database server, and provides the generic technology for accessing non-Oracle systems from the Oracle database server. Heterogeneous Services enables you to use:
1) Oracle SQL statements to transparently access data stored in non-Oracle systems as if the data resided within an Oracle database server. 2) Oracle procedure calls to transparently access non-Oracle systems, services, or application programming interfaces (APIs)from your Oracle distributed environment.

To access a non-Oracle system, you need to use a complementary Heterogeneous Services agent. You can connect to a non-Oracle system through an Oracle Transparent Gateway, which is an agent that is tailored specifically for the system that you are accessing. If you connect to the non-Oracle system using generic connectivity through the ODBC or OLE DB interfaces, however, then the agent is an executable that it automatically installed with the Oracle database server.

---------------------------------------------->>

Sergey Adamenko Received on Fri Aug 23 2002 - 14:47:52 CDT