Updated November, 23 2023

Autonomous transactions#

Autonomous transactions emulated due to execution SQL via PostgreSQL dblink. We have to use dblink_fdw to create the remote server and user mapping.

In addition, we create a wrapper for an autonomous function(procedure) with the call of helper function dbt.autonous_exec with function parameters.

 1--Oracle
 2CREATE OR REPLACE PROCEDURE prc_log_errors (v_error_code      IN VARCHAR2,
 3                                            v_error_msg       IN VARCHAR2,
 4                                            v_plsql_program   IN VARCHAR2)
 5AS
 6   PRAGMA AUTONOMOUS_TRANSACTION;
 7BEGIN
 8   INSERT INTO error_log (ERROR_CODE,
 9                          ERROR_MSG,
10                          DATE_OCCURRED,
11                          PLSQL_PROGRAM_REF)
12       VALUES (v_error_code,
13               v_error_msg,
14               SYSDATE,
15               v_plsql_program);
16
17   COMMIT;
18END;
19/

Not yet implemented:

  • Outer transaction temp objects aren’t visible in the autonomous transaction.