Home » Developer & Programmer » Forms » connect to different database
connect to different database [message #241117] Mon, 28 May 2007 06:54 Go to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Hi,

I am working on oracle 8i database.
Now i want to create a trigger such that when i insert a record in scott/tiger@ora10g ,the trigger should fire and connect to other database (team/team@testora)then insert into that database table and then come back to scott/tiger database.

Is this possible?

Please guide me.....

Thanks,
Srinivas
Re: connect to different database [message #241127 is a reply to message #241117] Mon, 28 May 2007 07:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Create a database link which would establish connection between two databases.

Then create a database trigger which will insert a record into the remote database (i.e. into a table over previously created database link). Here's an example:
C:\>sqlplus mike/lion@ora8

SQL> create database link dbl_ora10
  2  connect to scott
  3  identified by tiger
  4  using 'ora10';

Database link created.

SQL> -- check whether DB link works properly
SQL> select 'x' from dual@dbl_ora10;

'
-
x

SQL> -- testing environment: one table per user
SQL> create table my_first_tab (col number);

Table created.

SQL> -- a "remote" table
SQL> connect scott/tiger@ora10
Connected.
SQL> create table my_second_tab (col number);

Table created.

SQL> -- returning back to create a trigger
SQL> connect mike/lion@ora8
Connected.
SQL> create or replace trigger trg_my_1_tab
  2  after insert on my_first_tab
  3  for each row
  4  begin
  5    insert into my_second_tab@dbl_ora10 (col) values (:new.col);
  6  end;
  7  /

Trigger created.

SQL> -- OK, let's insert a record into a "home" table.
SQL> -- Trigger should insert it into the remote table.
SQL> insert into my_first_tab values (1234);

1 row created.

SQL> -- home
SQL> select * from my_first_tab;

       COL
----------
      1234

SQL> -- remote
SQL> select * From my_second_tab@dbl_ora10;

       COL
----------
      1234

SQL>
Re: connect to different database [message #241132 is a reply to message #241117] Mon, 28 May 2007 07:49 Go to previous messageGo to next message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Hi,

Thanks for your reply....But....

after i created the database link successfully.
when i tried to connect i got the following error

SQL> conn scott/tiger@ora10g
Connected.
SQL> select 1 from dual@DBL_ORA10;
select 1 from dual@DBL_ORA10
*
ERROR at line 1:
ORA-02085: database link DBL_ORA10 connects to TEST.COM


SQL> select * from user_db_links;

DB_LINK USERNAME PASSWORD HOST CREATED
DBL_ORA10 test test test.com 28-MAY-07

Thanks,
Srinivas

[Updated on: Mon, 28 May 2007 07:50]

Report message to a moderator

Re: connect to different database [message #241134 is a reply to message #241117] Mon, 28 May 2007 07:55 Go to previous message
srinivas.k2005
Messages: 342
Registered: August 2006
Senior Member
Hi,

Thanks i got it , in gave the database link name same as DB name,so now it works fine thanks a lot.

Thanks,
Srinivas
Previous Topic: How to include variable on block's query
Next Topic: Resize FORMS Environment window size?
Goto Forum:
  


Current Time: Sat Dec 10 22:38:42 CST 2016

Total time taken to generate the page: 0.09361 seconds