Oracle Procedure to commit per DML statement (PL/SQL)

Commit single transaction Procedure

This procedure can be used in APIs or SQL scripts to commit a DML statement, without committing the other transaction the same script.

--This procedure is used to call an specific DML statment
-- and you need to commit the DML statment only

CREATE OR REPLACE PROCEDURE AUTON_DML (p_dmlstat VARCHAR2)
AS
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN                                       -- Main transaction suspends here.
   EXECUTE IMMEDIATE p_dmlstat;         -- Autonomous transaction begins here.

   COMMIT;                                -- Autonomous transaction ends here.
END;                                         -- Main transaction resumes here.


--------------------- How to USE -------------------------------------
----------------------------------------------------------------------
---EXECUTE AUTON_DML(q'[UPDATE staging_pm_schedule_table SET NOTE = 'TEST_Variable']');
--OR
---EXECUTE AUTON_DML('UPDATE staging_pm_schedule_table SET NOTE = '''TEST_Variable'''');

--*----------========----------========----------========------------*--
--*--========---- Created by: Mohamed El Ghandour :D ----========----*--
--*----------========----------========----------========------------*--