i have a 19c installtion and i'm trying to understand why somedata i still written when i use a savepoint. One database is a non-CDB (DB1) and the other is a PDB (PDB2). Each databases have dblinks to each others.
I have this procedure in DB1 that i made to reproduce the error. The aim is to have a loop to analyze data. If an error is found, ignore the error and continue with the next row.
Here i will try to insert a row in a table that live in PDB2 database (TEST_TABLE) and a row in a table (AA_TEST2) that lives in the DB that host the procedure (DB1)
create PROCEDURE AA_SP_TEST2B
BEGIN
DECLARE
var_ID INTEGER;
BEGIN
var_ID := 0;
DELETE FROM AA_TEST2 t;
DELETE FROM REMOTE_TABLE;
WHILE var_ID < 20
LOOP
BEGIN
SAVEPOINT START_LOOP;
INSERT INTO AA_TEST2 ( ID, DES, DATA ) VALUES ( var_ID, 'LOC', SYSDATE);
INSERT INTO MY_user.REMOTE_TABLE@PDB2( ID, VARCHAR1 ) VALUES( var_ID ,'REM');
IF var_ID > 4 AND var_ID/2 = TRUNC(var_ID/2) THEN
INSERT INTO AA_TEST2 ( DATA ) VALUES ('PIPPO');
END IF;
var_ID := var_ID + 1;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK TO START_LOOP;
END;
END LOOP;
END;
COMMIT;
END;
/
I'm calling this procedure FROM pdb2, that is also the database where TEST_TABLE lives:
BEGIN
DB1_USER.AA_SP_TEST2B@DB1;
END;
The procedure is simple: when i reach a var_ID that is ODD and exception should occour, so will find in the table only rows with odds numbers becouse of the SAVEPOINT and ROLLBACK declarations.
What is see instead is that:
I don't understand what i'm missing here.
This procedure is called from SQL Developer on PDB2 user My_user.
The only thing i noticed is that if i add the PRAGMA AUTONOMOUS_TRANSACTION to this procedure it seems to work right: the even rows are not inserted in the REMOTE_TEST table and the local AA_TEST2 Table.
Any hint??