PROCEDURE IN ONE ORACLE SERVER INSERT INTO ANOTHER SERVER'S TABLE?

[Almarton]

Hi All, wondering if is possible for a procedure, function, trigger or other that running from an Oracle database Server would make inserts into another Oracle Server running in another machine. If so could you give me an example of the code. Many thanks.

 

[alexhu]

Checkout Database Links in the manuals (sorry I can't give code - I don't have any here)

Alex

 

[SantaMufasa]

Alex is correct. The feature that you are describing is a "database link". A database link allows you to access objects that some user on some (local or remote) database owns. I'll illustrate the creation of, and access via, a dblink:

Section 1 -- creating a database link:

create database link yada connect to dontdrop identified by dontdrop using 'fos';

Database link created.

The term 'fos' is an entry in my local (Salt Lake City, Utah) tnsnames.ora connectivity file that points to a database in San Francisco, California).

Section 2 -- using a database link reference:

select * from dummy@yada;

X
------------------
This is dummy data

1 row selected.

Now, to insert or update to the remote database, the code is as you would now expect:

insert into dummy@yada values ('Text for Almarton');

...and I confirm the results next, using the same, previous SELECT command:

select * from dummy@yada;

This is dummy data
Text for Almarton

2 rows selected.

I issued the SELECTs and INSERT command from a user connection from a database in Salt Lake City, Utah, USA, while the "yada" database link connected to a database in San Francisco, California, USA.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:17 (27Sep04) UTC (aka "GMT" and "Zulu"), 14:17 (27Sep04) Mountain Time)