How to access the table of another server [database] 6

[ifxtechie]

Hi,
I am writing a trigger to insert a record into a table of server2 [different machine] whenever there is an insert in one of the local tables. The only problem I am facing is 'How to access a TABLE OF DIFFERENT SERVER'.

Local Machine Credentials:
Database : db1
ip:ip1
user:user1
passwordassword
Remote machine Credentials:
Database: db2
ip:ip2
userid:user2
passwordassword

I have set the tnsnames.ora and I am able to access the databases in individual sessions.But in one session I want to access both databases. Can anybody pls tell me how to do this.
Thanks
Partha

 

[dsanchez2]

Create a Database Link on the local database to the remote database. Look for CREATE DATABASE LINK in the Oracle's documentation for more information.

 

[sem]

Oracle Server is not defined by IP only, you also need listener port and SID or Service Name. Though the answer is the same: create database link

Regards, Dima

http://seminihin.narod.ru

 

[SantaMufasa]

Partha,

I know that an example can be worth 1000 words, so here is an example of creating and using a database link to another, remote database. ("DONTDROP" is a user on a remote database, "FOS" [host service string in tnsnames.ora]; password same as username; contains table named "DUMMY". Synonym, "IFXTECHIE_DEMO", optionally facilitates and masks use of database link, "IFXTECHIE".):

SQL> create database link ifxtechie
  2   connect to dontdrop identified by dontdrop using 'fos';

Database link created.

SQL> create synonym ifxtechie_demo for dummy@ifxtechie;

Synonym created.

SQL> insert into ifxtechie_demo values ('This is a test');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ifxtechie_demo;

X
--------------------------------------------------
This is a test

1 row selected.

SQL> select * from dummy@ifxtechie;

X
--------------------------------------------------
This is a test

1 row selected.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 08:14 (17Dec03) GMT, 01:14 (17Dec03) Mountain Time)

 

[Turkbear]

Hi,
Just a note about a common 'gotcha' or 2 with Database links..

The tnsnames.ora on the database server is the one that needs the info about the linked database instance, not the one on your client. ( I won't reveal how many times that one has bitten me in the a__)

Also, the link name is controlled, to some extent, by the GLOBAL_NAMES parameter in the remote databases init file.
If it is set to TRUE, then the link name must be the same as the database name. Otherwise you can call the link anything you want.

 

[ifxtechie]

Thank you all very much for your help. It worked.
Now the problem I am facing is a trigger. I have created a trigger on the local db table to insert the record to remote server table. This trigger is just to keep both the tables in sync. and it is not working. In the local table it is inserting the records. I am committing both the local-insert and trigger-remote-insert.
Can anybody tell me why the trigger is not working.
Thanks
partha

 

[SantaMufasa]

IFX,

Please post the trigger code and the results messages (if any) from your trigger execution.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:06 (17Dec03) GMT, 12:06 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi SantaMufasa,
Thanks for the response. Here is the code for trigger:
CREATE OR REPLACE TRIGGER mir_sto_insert
AFTER INSERT ON mir_stores
FOR EACH ROW
BEGIN
insert into frys_stores values
( :NEW.id,
:NEW.name,
:NEW.address1,
:NEW.address2,
:NEW.city,
:NEW.state,
:NEW.zip );
commit;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('WARNING! The row could not be inserted .');
DBMS_OUTPUT.PUT_LINE('The two databases in FRYS and SUN_LPTP are not SYNC!');
END;
/

The trigger is created without any error.
It is not posting any error messages also.
Here is my insert statement.
SQL> ed
Wrote file afiedt.buf

1 insert into mir_stores values(
2 13, 'Trigger Test','Trigger address', 'Trigger_address2','RoyalOak','MI',
3* '48073')
SQL> /

1 row created.

Thanks
Partha

 

[SantaMufasa]

Partha,

Is "frys_stores" a synonym for a db-linked table on another machine? Can you please post the results of this query:

SELECT COUNT(*) from frys_stores;

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:41 (17Dec03) GMT, 12:41 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi SantaMufasa,
Here is the result. Yes it is a synonym on mir_stores.
SQL> select * from frys_stores;

ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
1 BestBuy 1234
Cedar Mills Road Beaverton OR 97006

2 Gart Sports 2234
Cedar Mills Road Beaverton OR 97006

3 CIAM 3234
Cedar Mills Road Beaverton OR 97006


ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
4 ROSS 4234
Cedar Mills Road Beaverton OR 97006


SQL> commit
2 ;

Commit complete.
SQL> select count(*) from mir_stores;

COUNT(*)
----------
8
Thanks
Partha

 

[SantaMufasa]

Whoa, Partha !!!

You said, "it is a synonym on mir_stores". From the context, all I can presume is that "It" is "frys_stores". If "frys_stores" is a synonym for "mir_stores" and your trigger fires "AFTER INSERT ON mir_stores", then this is a mess. Do you mean that "frys_stores" is a synonym for "mir_stores" on the database linked server?

If "frys_stores" and "mir_stores" are the same, then how do you explain that "select * from frys_stores;" returns 4 rows and "SQL> select count(*) from mir_stores;" returns 8?

I believe something is disappearing in translation here. Please set me straight.

Mufasa
(aka Dave of Sandy, Utah, USA @ 21:48 (17Dec03) GMT, 14:48 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi SantaMufasa,
This information may help for you to suggest me about the trigger.

SQL> delete from mir_stores;

8 rows deleted.

SQL> delete from frys_stores;

4 rows deleted.

SQL> insert into mir_stores values(
2 13, 'Trigger Test','Trigger address', 'Trigger_address2','Beaverton','OR',
3 '97006');

1 row created.

SQL> select * from mir_stores;

ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
13 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006


SQL> select * from frys_stores;

no rows selected

SQL>

Pls see that no rows are added to frys_stores which means the trigger is not working.
Thanks
Partha

 

[SantaMufasa]

Partha,

If I were in your position, the next thing that I would try is to confirm that dbms_output.put_line is working properly. To do this, I'd 1) COMMENT out your exception section so that we'd see the actual error message if one is occurring and 2) Add in a dbms_output.put_line ('Successful INSERT to "Frys_stores".'); immediately following your INSERT statement.

Let us know what you find.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:16 (17Dec03) GMT, 16:16 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi SantaMufasa,
This information may help for you to suggest me about the trigger. It looks like a mess. As you said, frys_stores is a synonym on mir_stores of the remote db. Basically the the local and remote machine tables should be in SYNC. Hence I tried with the trigger. Only thing is whenever there is an insert in the local machine, there should be a corresponding insert in the remote db-table.
Hope this will not keep you in confusion and you may think why trigger is not working.
Thanks
Partha

 

[SantaMufasa]

Partha,

When I said it looks like a mess, that was when your earlier message made it look like "frys_stores" and "mir_stores" were the same table on the same machine. There should not be a problem using this method to keep the two tables synchronized if they really are DIFFERENT tables.

To troubleshoot your problem, please try the "dbms_output.put_line" experiment I mentioned in my previous post, then post the results.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:29 (17Dec03) GMT, 16:29 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi Dave,
Thanks very much for your help on this.
As you said, it was not catching the exception. Now it started giving the problem. [This makes me to lose faith on Exceptions!]. Now the error is
SQL> ed
Wrote file afiedt.buf

1 insert into mir_stores values(
2 143, 'Trigger Test','Trigger address', 'Trigger_address2','Beaverton','OR',

3* '97006')
SQL> /
insert into mir_stores values(
*
ERROR at line 1:
ORA-04098: trigger 'SYSTEM.MIR_STO_INSERT' is invalid and failed re-validation
ORA-02063: preceding line from LPTP_LINK
ORA-06512: at "SYSTEM.MIR_STO_INSERT", line 2
ORA-04088: error during execution of trigger 'SYSTEM.MIR_STO_INSERT'

SQL> select * from frys_stores;

no rows selected

SQL> select * from mir_stores;

ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
13 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006


SQL>
The trigger is :
CREATE OR REPLACE TRIGGER mir_sto_insert
AFTER INSERT ON mir_stores
FOR EACH ROW
BEGIN
insert into frys_stores values
( :NEW.id,
:NEW.name,
:NEW.address1,
:NEW.address2,
:NEW.city,
:NEW.state,
:NEW.zip );
DBMS_OUTPUT.PUT_LINE('Success add the row into Compaqlptp db!!!! .');
DBMS_OUTPUT.PUT_LINE('The two databases in FRYS and CompaqLPTP are SYNC!');
commit;
/*
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE('WARNING! The row could not be inserted .');
DBMS_OUTPUT.PUT_LINE('The two databases in FRYS and CompaqLPTP are not SYNC!');
*/
END;
/

This shows the row is inserted into mir_stores. But trigger action has failed. Can you pls tell me why this is failing.
thanks
Partha

[Just a note: In informix I have worked this kind and is successful!]

 

[SantaMufasa]

Partha,

First, don't lose faith in EXCEPTIONS. That is not your problem. The problem is that the trigger is becoming INVALID before it even gets a chance to execute (ORA-04098: trigger 'SYSTEM.MIR_STO_INSERT' is invalid and failed re-validation).

One concern I have is that you are building application modules in the schema named "SYSTEM". It may not be an easy job to change things now, but in the future, be sure to build in some Oracle user that is not SYS and not SYSTEM.

As far as your trigger is concerned, after you re-create your trigger, and again before you do your INSERT, execute this SELECT:

select object_name, object_type, status from user_objects
where status <> 'VALID';

also, just to be sure, run this SELECT:

select object_name, object_type, status from user_objects
where object_name = 'MIR_STO_INSERT';

If the status for MIR_STO_INSERT trigger is not VALID, then don't attempt to test the trigger until your are certain (immediately prior to its execution) that the status for the trigger is VALID. (Objects become INVALID for a variety of reasons. The typical reasons are 1) the original compile did not work properly or 2) a table or other object that the object (i.e., trigger) refers to has been changed since the last successful compile.

Let us know what you find.

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:51 (18Dec03) GMT, 17:51 (17Dec03) Mountain Time)

 

[ifxtechie]

Hi Dave,
I made some changes like - removed the commit as it said that commit is not allowed in Trigger. I recomplied and of course same result. I did what you suggested also. It says that is a valid trigger. Below is the output.
SQL> select object_name, object_type, status from user_objects
2 where status <> 'VALID';

no rows selected

SQL> select object_name, object_type, status from user_objects
2 where object_name = 'MIR_STO_INSERT';

OBJECT_NAME
-----------------------------------------------------------------------

OBJECT_TYPE STATUS
------------------ -------
MIR_STO_INSERT
TRIGGER VALID


SQL>
Now whenever I try to insert, it is not inserting to main table also.It is focussing both insertions [ine\sert into local and remote table] as a transaction. When the remote insert fails, it fails for local table also.
Both the local table, remote table and the synonym are valid. Because I selected from these tables and it worked without any errors. The following is the output of thoses selects.
SQL> select * from mir_stores@lptp_link;

no rows selected

SQL> select * from frys_stores;

no rows selected

SQL> select * from mir_stores;

ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
13 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006

144 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006


SQL>

Is there anything that you may suggest me to look into.
Thanks very much for your valuable suggestions.
Partha

 

[SantaMufasa]

Partha,

I've successfully simulated your environment, below. My trigger IS successful in writing to the remote table:

SQL> set echo on
SQL> set feedback on
SQL> set serveroutput on
SQL> CREATE OR REPLACE TRIGGER mir_sto_insert
  2  AFTER INSERT ON mir_stores
  3  FOR EACH ROW
  4  BEGIN
  5    insert into frys_stores@ifxtechie values
  6      ( :NEW.id,
  7        :NEW.name);
  8        DBMS_OUTPUT.PUT_LINE('Success add the row into Compaqlptp db!!!! .');
  9        DBMS_OUTPUT.PUT_LINE('The two databases in FRYS and CompaqLPTP are SYNC!');
 10   /*
 11   EXCEPTION
 12     WHEN OTHERS
 13     THEN
 14        DBMS_OUTPUT.PUT_LINE('WARNING! The row could not be inserted .');
 15        DBMS_OUTPUT.PUT_LINE('The two databases in FRYS and CompaqLPTP are not SYNC!');
 16  */
 17  END;
 18  /

Trigger created.

SQL> insert into mir_stores values (1,'Beaverton');
Success add the row into Compaqlptp db!!!! .
The two databases in FRYS and CompaqLPTP are SYNC!

1 row created.

SQL> insert into mir_stores values (2,'Portland');
Success add the row into Compaqlptp db!!!! .
The two databases in FRYS and CompaqLPTP are SYNC!

1 row created.

SQL> select * from mir_stores;

        ID NAME
---------- --------------------------------------------------
         1 Beaverton
         2 Portland

2 rows selected.

SQL> select * from frys_stores@ifxtechie;

        ID NAME
---------- --------------------------------------------------
         2 Portland
         1 Beaverton

2 rows selected.

SQL>

What can you see that is different between my environment and yours?

Mufasa
(aka Dave of Sandy, Utah, USA @ 01:49 (18Dec03) GMT, 18:49 (17Dec03) Mountain Time)

 

[ifxtechie]

Great! Atleast it worked there. Now I have some confidence. Definitely there should be some problem in my environment.
Here also I tried but again failed. I put all code in file and executed, but no good results. The file looks like this

set echo on;
drop database link lptp_link ;
create database link lptp_link connect to system identified by partha using 'lptp';
CREATE OR REPLACE TRIGGER mir_sto_insert
AFTER INSERT ON mir_stores
FOR EACH ROW
BEGIN
insert into
mir_stores@lptp_link values (
:NEW.id,
:NEW.name,
:NEW.address1,
:NEW.address2,
:NEW.city,
:NEW.state,
:NEW.zip ) ;
DBMS_OUTPUT.PUT_LINE('Success add the row into Compaqlptp db!!!!');
DBMS_OUTPUT.PUT_LINE('The two DBs in FRYS and LPTP are in SYNC');
END;
/
select object_name, object_type, status from user_objects
where status <> 'VALID';

select object_name, object_type, status from user_objects
where object_name = 'MIR_STO_INSERT';

desc mir_stores@lptp_link;
desc mir_stores;
select * from mir_stores@lptp_link;
select * from mir_stores;
insert into mir_stores values ( 1,'BestBuy','1234','Cedar Mills Road','Beaverton','OR','97006');


The results are as below: [it fails in the insert!]
SQL> ed new_trig

SQL> @new_trig
SQL> drop database link lptp_link ;

Database link dropped.

SQL> create database link lptp_link connect to system identified by partha usin
g 'lptp';

Database link created.

SQL> CREATE OR REPLACE TRIGGER mir_sto_insert
2 AFTER INSERT ON mir_stores
3 FOR EACH ROW
4 BEGIN
5 insert into
6 mir_stores@lptp_link values (
7 :NEW.id,
8 :NEW.name,
9 :NEW.address1,
10 :NEW.address2,
11 :NEW.city,
12 :NEW.state,
13 :NEW.zip ) ;
14 DBMS_OUTPUT.PUT_LINE('Success add the row into Compaqlptp db!!!!');
15 DBMS_OUTPUT.PUT_LINE('The two DBs in FRYS and LPTP are in SYNC');
16 END;
17 /

Trigger created.

SQL> select object_name, object_type, status from user_objects
2 where status <> 'VALID';

no rows selected

SQL>
SQL> select object_name, object_type, status from user_objects
2 where object_name = 'MIR_STO_INSERT';

OBJECT_NAME
--------------------------------------------------------------------------------

OBJECT_TYPE STATUS
------------------ -------
MIR_STO_INSERT
TRIGGER VALID


SQL>
SQL> desc mir_stores@lptp_link;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
NAME NVARCHAR2(30)
ADDRESS1 NVARCHAR2(30)
ADDRESS2 NVARCHAR2(30)
CITY NVARCHAR2(30)
STATE NVARCHAR2(2)
ZIP NVARCHAR2(10)

SQL> desc mir_stores;
Name Null? Type
----------------------------------------- -------- ----------------------------

ID NOT NULL NUMBER(38)
NAME NVARCHAR2(30)
ADDRESS1 NVARCHAR2(30)
ADDRESS2 NVARCHAR2(30)
CITY NVARCHAR2(30)
STATE NVARCHAR2(2)
ZIP NVARCHAR2(10)

SQL> select * from mir_stores@lptp_link;

no rows selected

SQL> select * from mir_stores;

ID NAME ADDRESS1
---------- ------------------------------ ------------------------------
ADDRESS2 CITY ST ZIP
------------------------------ ------------------------------ -- ----------
13 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006

144 Trigger Test Trigger address
Trigger_address2 Beaverton OR 97006


SQL> insert into mir_stores values ( 1,'BestBuy','1234','Cedar Mills Road','Beav
erton','OR','97006');
insert into mir_stores values ( 1,'BestBuy','1234','Cedar Mills Road','Beaverton
','OR','97006')
*
ERROR at line 1:
ORA-04098: trigger 'SYSTEM.MIR_STO_INSERT' is invalid and failed re-validation
ORA-02063: preceding line from LPTP_LINK
ORA-06512: at &quot;SYSTEM.MIR_STO_INSERT&quot;, line 2
ORA-04088: error during execution of trigger 'SYSTEM.MIR_STO_INSERT'


SQL>
SQL>


What env differences could be there.
Configurations of my machines [later pgms will be moved to unix and linux]
M/c 1 : PII - 256 MB, Oracle 9i.
M/c 2 : PIV - 784 MB, Oracle 9i.

I don't want to give-up since your effort & suggestions hasve given lot of confidence. Anyways, if you think that any other difference may be causing this, pls let me know.
Thanks very much.
Partha