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 ----========----*--
--*----------========----------========----------========------------*--