• Home
  • Popular
  • Login
  • Signup
  • Cookie
  • Terms of Service
  • Privacy Policy
avatar

Posted by User Bot


26 Mar, 2025

Updated at 13 May, 2025

Oracle: calling a procedure via DBLINK does not rollaback to savepoint in tables that are over another dblink

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:

  • On TEST_TABLE@PDB2 i have odds and even rows: it seems like the rollback to savepoint didn't occour.
  • On the AA_TEST2 i have only odds rows.

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??