Hi all,

I've only ever used SQL Server and DB2 except for my university days,
and I'm about to do a couple of days work on an Oracle site to do some
data migration.

Is there something similar to a SQL Server 'linked server' in Oracle?
Basically I need a way to run selects on external data sources from
Oracle scripts, treating the external sources (which may be Access,
SQL Server, DB2, Informix etc) as Oracle databases/tables.

Can you give me a point in the right direction?

Cheers,
Josh.

yes there is.... I think the best place to start reading is at
http://otn.oracle.com/products/gatew...eways_fov.html


Regards,
Johan Louwers.

whitegoose (AT) inorbit (DOT) com (Josh White) wrote in message news:<aafea0a8.0405032358.4516ab5b (AT) posting (DOT) google.com>...
Functions written in a 3GL language can be called for PL/SQL. This
call allow you to transparently access a different database.

An Oracle PL/SQL or SQL application can call an external procedure and
the listener (piece of the Oracle Database that "listens" for external
calls) launches a network session-specific process called extproc.
Through the listner service, PL/SQL passes the following information
to extproc:
1. Shared library name
2. External procedure name
3. Parameters (if necessary)
The "extproc" program then loads the shared library and invokes the
external procedure.

Josh,

StarQuest has a product called StarQuest Data Replicator which
replicates data between IBM DB2, Oracle and Microsoft SQL Server
databases. It's pretty fast and easy to use.

See http://www.starquest.com/Productfolder/infoSQDR.html

Bob

whitegoose (AT) inorbit (DOT) com (Josh White) wrote in message news:<aafea0a8.0405032358.4516ab5b (AT) posting (DOT) google.com>...

I think that the oracle way is more stable than the starquest
replicator ....


On 5 May 2004 10:35:19 -0700, db2team (AT) hotmail (DOT) com (DB2) wrote:

Very Simple. Configure an ODBC connection for the external data source
and then configure Oracle's Heterogenous Services. I have had to do that to
import old data from Paradox and Mickeysoft Access into Oracle.

Although, you don't need to configure Access to do that. If you configure
Oracle
as an ODBC data source, Access can export a whole table to Oracle.

Oracle did not like the fact that Paradox 4.x allowed certain
characters("(", ")", ".", "?")
in the field name, so I had to use an old copy of Paradox to change some of
the field
names and get a list of the field names.

Also, Oracle did not allow me to use the "desc"ribe function, nor did it
allow me to do
a "select * from <whatever>". I had to list each field individually.

We did not plan on using Paradox, but wanted the data, so I just imported
everything
into Oracle.

For each table...

create table <new_oracle_table> as
(select field1,
field2,
...
from <Paradox_Table>@<Paradox_ODBC_Datasource>
)
/

alter table <new_oracle_table>
add constraint (primary key <keyname_pk>(key_field[s])
tablespace <oracle_tablespace> or <default for
schema>);

Some of my tables had, in Paradox terminology, "Secondary Indexes", so I had
to "create index ..." on some of the tables.

If you want to leave the data in the original database, you will not have to
any of this.
You'll just need to configure the ODBC connection, Hetergenous Services,
then your
Oracle "front end" to make "select" calls to your data sources.