Re: checking a database link in PL/SQL

Andreas Ballenthin <ballenth_at_gmx.de> wrote in message news:<3C0F9E5E.B5ED0679_at_gmx.de>...
> Hi,
>
> try to select dummy from dual_at_db_link.
>
> If select is ok, then insert into remote table,
> if exception occurs, insert into local table.
>
> cu
> Andreas

Hi,

Thanks for that, I tried implementing with this:

CREATE OR REPLACE TRIGGER TEST_TRIGGER AFTER INSERT OR UPDATE ON TEST DECLARE

	cnt INTEGER := 0;
	i INTEGER := 0;
	CURSOR FILLED IS
		SELECT VALUE FROM TEST;
BEGIN
	FOR FLX IN FILLED LOOP
		SELECT COUNT(VALUE) INTO i FROM TEST_at_CP WHERE ROWNUM<2;
		IF i <> 0 THEN
			INSERT INTO TEST_at_CP(VALUE) VALUES(FLX.VALUE);
			SELECT COUNT(*) INTO cnt FROM TEST_at_CP WHERE 
			ID = FLX.VALUE;
			IF cnt != 0 THEN
				DELETE FROM TEST WHERE 
					VALUE = FLX.VALUE;
			END IF;
		i := 0;
		END IF;
	END LOOP;

..which works fine when the connection is ok, but I still get:

SQL> INSERT INTO TEST VALUES('556');
INSERT INTO TEST VALUES('556')
*
ERROR at line 1:

ORA-02055: distributed update operation failed; rollback required
ORA-02068: following severe error from CP
ORA-12571: TNS:packet writer failure

SQL> SELECT * FROM TEST;
SELECT * FROM TEST
*
ERROR at line 1:
ORA-02067: transaction or savepoint rollback required

..when the link is down.

Any ideas?
thanks

Tim Received on Wed Dec 19 2001 - 03:46:04 CST