Hi,
I am creating a database link to access the remote DB and I am using that remote table in Stored Procedure.Below are the details:-
CREATE PUBLIC DATABASE LINK contact_link CONNECT TO TEST1 IDENTIFIED BY TEST1 USING 'localhost:1521/oracl';
create or replace
procedure customer_upsert_data as
customer_Id varchar2(254);
begin
for CUSTOMER in (select customer_name, address, contact from TEST.CUSTOMER cust, TEST.CUSTOMER_ADDRESS custaddr
where (ismodified='Y' or isnew='Y') and cust.ID = custaddr.ID)
loop
begin
select contact_id into contactId from TEST1.contact@contact_link where CONTACT_LIST = CUSTOMER.CONTACT_LIST;
EXCEPTION
WHEN NO_DATA_FOUND THEN
contactId := NULL;
end;
if contactId is null then
insert into TEST1.contact@contact_link(CONTACT_ID,CONTACT_NAME, CONTACT_VERSION)
values(1002,'BOB', 100);
else
update TEST1.contact@contact_link set CONTACT_LIST = CUSTOMER.CONTACT_LIST
where contact_id = contactId;
end if;
end loop;
commit;
end customer_upsert_data;
When I run the above SP , I am getting the below error:-
ORA-02067: transaction or savepoint rollback required
ORA-02067: transaction or savepoint rollback required
ORA-00604: error occurred at recursive SQL level 1
ORA-02067: transaction or savepoint rollback required
It seems that this is due to the remote access using database link.
Cause: A failure (typically a trigger or stored procedure with multiple
remote updates) occurred such that the all-or-nothing execution
of a previous Oracle call cannot be guaranteed.
Action: rollback to a previous save point or rollback the transaction
and resubmit.
Please let me know how to solve this issue?
Thanks.