1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Autonomous transactions in oracle pragma

Discussion in 'Documents Section Discussions' started by TechQueryPond, Mar 21, 2016.

  1. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA


    “Autonomous Transactions in Oracle”

    1) In Oracle session, all of the changes made to data are part of a single
    transaction.

    2) An autonomous transaction is an independent transaction started within another
    transaction (the main transaction).

    3) Autonomous transactions allow you to temporarily suspend the main
    transaction, perform additional SQL operations, commit or rollback those
    operations separately, then resume the main transaction.

    4) To define an autonomous transaction, we can use a PRAGMA statement

    PRAGMA AUTONOMOUS_TRANSACTION;



    CREATE TABLE log_data
    (empno NUMBER(6),
    userid VARCHAR2(30),
    create_date DATE);

    CREATE OR REPLACE TRIGGER add_log
    BEFORE INSERT ORUPDATE ON employee FOREACHROW
    DECLARE
    PRAGMA AUTONOMOUS_TRANSACTION;---Thisstatement made thisautonomous thistrigger
    BEGIN
    INSERT INTO log_data
    VALUES:)new.id,USER,SYSDATE);
    COMMIT;
    END;
    /

    Insert into EMPLOYEE
    (ID,NAME,CITY,SALARY,DEPT_NO)
    Values
    (20,'MY Tech Query','Mumbai',20000,1)

    ROLLBACK;

    SELECT *FROM EMPLOYEE

    SELECT *FROM LOG_DATA
     
    Last edited: Mar 28, 2016
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You should really verify that the code you post works as expected, and works when it is copied and pasted; your posted code fails to run as written:

    SQL> CREATE ORREPLACE TRIGGER add_log
    2 BEFORE INSERT ORUPDATE ON employee FOREACHROW
    3 DECLARE
    4 PRAGMA AUTONOMOUS_TRANSACTION;---Thisstatement made thisautonomous thistrigger
    5 BEGIN
    6 INSERT INTO log_data
    7 VALUES:)new.id,USER,SYSDATE);
    CREATE ORREPLACE TRIGGER add_log
    *
    ERROR at line 1:
    ORA-00604: error occurred at recursive SQL level 1
    ORA-06502: PL/SQL: numeric or value error
    ORA-06512: at line 6
    ORA-00901: invalid CREATE command


    SQL> COMMIT;

    Commit complete.

    SQL> END;
    SP2-0042: unknown command "END" - rest of line ignored.
    SQL> /

    Commit complete.

    SQL>
    SQL> Insert into EMPLOYEE
    2 (ID,NAME,CITY,SALARY,DEPT_NO)
    3 Values
    4 (20,'MY Tech Query','Mumbai',20000,1)
    5
    SQL> ROLLBACK;

    Rollback complete.

    SQL>
    SQL> SELECT *FROM EMPLOYEE
    2
    SQL> SELECT *FROM LOG_DATA
    2

    Statements are not terminated, they aren't complete, they aren't typed properly which frustrates those who want to run your examples and can't, leaving them to believe it's their fault nothing works.

    Fix your code; posting sloppy examples that fail to work will make people ignore your posts because you can't be bothered to ensure they work.
     
  3. TechQueryPond

    TechQueryPond Active Member

    Messages:
    9
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    INDIA
    Hi

    Thanks for your feedback. There is space missing in the code.

    CREATE ORREPLACE : original code

    CREATE OR REPLACE : corrected code.

    now it will run properly i have done necessary changes.
     
    Last edited: Mar 30, 2016