Oracle PRAGMA AUTONOMOUS_TRANSACTION เขียน PL ให้เป็นอิสระจาก Main Transaction

บทความเกี่ยวกับ : Oracle PRAGMA AUTONOMOUS_TRANSACTION เขียน PL ให้เป็นอิสระจาก Main Transaction


วันนี้มีปัญหาเรื่อง Store procedure ที่เขียนไว้ commit ไม่ได้ทั้งที่นานทีปีหนไม่เคยเป็น
อยู่ดีๆ เจอ Error ตัวนี้ : ORA-02089: COMMIT is not allowed in a subordinate session

เท่าที่ลองหาข้อมูลดูน่าจะเกิดจาก App ที่ Call (ตอนนี้เป็น Spring +Hibernate) มี Transaction Main ครอบอยู่ ประมาณนี้

 Begin

   - Call PL ที่มี Being commit อยู่ข้างใน

    -Do some thing

    -Do some thing

    -Do some thing

Commit

ทำให้เกิด Error เพราะ PL พยายามจะไป Commit ซ้อนใน Main Transaction อีกที
เคสนี้บังเิอิญว่างานที่ผมทำก็ไม่ได้ต้องการให้ Logic ใน PL ไปรวมอยู่ใน Transaction นั้นๆ อยู่แล้ว
ทางแก้แบบง่ายๆ ที่สุดก็คือ เอา บรรทัดที่ Call PL ออกมาไว้นอก Transaction Block ซะตามนี้

 - Call PL ที่มี Being commit อยู่ข้างใน

 Begin  

    -Do some thing

    -Do some thing

    -Do some thing

Commit

ฟังดูแล้วเหมือนจะไม่ยากแต่ทางฟากน้องที่เขียน Java บอกมาว่าไม่รู้จะไปแก้ที่จุดใหน T_T
แล้วไปใล่แก้ code java นั้นยากมาก ผมเลยไปค้นหาต่อจนค้นพบว่าแก้ที่ PL ก็ได้ หุหุ
ในเมื่อมันแยกตอน Call PL ไม่ได้ ก็ให้ PL แยกตัวออกมาเองซะเลยด้วยการเติมบรรทัดนี้เข้าไป ก่อน Begin  PRAGMA AUTONOMOUS_TRANSACTION เพียงเท่านี้เราก็สามารถจัดการ Transaction ใน PL ได้เป็นอิสระโดยไม่ต้องขึ้นตรงกับ Transaction หลักอีกต่อไป

ตัวอย่างการใช้งาน PRAGMA AUTONOMOUS_TRANSACTION

PROCEDURE TEST_TRANSACTION  (p_msg) is
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
  INSERT INTO msg_tbl  VALUES  (p_msg);
  COMMIT;
END TEST_TRANSACTION;