Sunday, May 30, 2004
Wednesday, May 26, 2004
Some code shows how to manage transaction in multiple database system
we create a table test_log in remote database, and a procedure test_log_pkg.insert_log to insert log into this table
create or replace package body test_log_pkg is
procedure insert_log(pvs_log in varchar2) is
begin
insert into test_log values (pvs_log);
end;
end test_log_pkg;
we create a procedure test_dblink_pkg.insert_remote_log in local database which invoke the insert_log in remote database by database link. we added PRAGMA AUTONOMOUS_TRANSACTION in declaration section, and commit the insert at the end.
create or replace package body test_dblink_pkg is
procedure insert_remote_log(pvs_log IN varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert_log@remote_db(pvs_log);
commit;
END;
end test_link_pkg;
then we create a table test_log in local database, and we create a test procedure which insert log into test_log in local database, and
invoke insert_remote_log to insert log into test_log in remote database, at the end, we rollback all of operations.
procedure test(pvs_log IN varchar2) is
BEGIN
insert into test_log values(pvs_log);
insert_remote_log(pvs_log);
rollback;
END;
after we run test, test_log in remote database has a new record, but test_log in local database has nothing.
It shows that the transaction for local database is rolled back, but the transaction for remote database is commited
we create a table test_log in remote database, and a procedure test_log_pkg.insert_log to insert log into this table
create or replace package body test_log_pkg is
procedure insert_log(pvs_log in varchar2) is
begin
insert into test_log values (pvs_log);
end;
end test_log_pkg;
we create a procedure test_dblink_pkg.insert_remote_log in local database which invoke the insert_log in remote database by database link. we added PRAGMA AUTONOMOUS_TRANSACTION in declaration section, and commit the insert at the end.
create or replace package body test_dblink_pkg is
procedure insert_remote_log(pvs_log IN varchar2) is
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert_log@remote_db(pvs_log);
commit;
END;
end test_link_pkg;
then we create a table test_log in local database, and we create a test procedure which insert log into test_log in local database, and
invoke insert_remote_log to insert log into test_log in remote database, at the end, we rollback all of operations.
procedure test(pvs_log IN varchar2) is
BEGIN
insert into test_log values(pvs_log);
insert_remote_log(pvs_log);
rollback;
END;
after we run test, test_log in remote database has a new record, but test_log in local database has nothing.
It shows that the transaction for local database is rolled back, but the transaction for remote database is commited
Tuesday, May 25, 2004
Monday, May 24, 2004
Thursday, May 20, 2004
Tuesday, May 18, 2004
Monday, May 17, 2004
Sunday, May 16, 2004
Friday, May 14, 2004
Thursday, May 13, 2004
Wednesday, May 12, 2004
Tuesday, May 11, 2004
Sunday, May 09, 2004
Saturday, May 08, 2004
Friday, May 07, 2004
Thursday, May 06, 2004
Wednesday, May 05, 2004
Tuesday, May 04, 2004
Monday, May 03, 2004
Sunday, May 02, 2004
Subscribe to:
Posts (Atom)