Forum Stats

  • 3,855,262 Users
  • 2,264,493 Discussions
  • 7,905,953 Comments

Discussions

ORA-04092: cannot COMMIT in a trigger while creating seq inside a trigger

2»

Answers

  • Khaja Hussain
    Khaja Hussain Member Posts: 9 Blue Ribbon
    Hi,

    Application requirement is end user will define, enable and disable the clients in the master table through oracle forms. whenever a client is defined a new sequence with the name of client should be created. we want this to done automatically.

    Thanks

    Regards
    Khaja
  • ~AK~
    ~AK~ Member Posts: 20
    Try this


    create or replace procedure SEQ
    is
    pragma AUTONOMOUS_TRANSACTION;
    BEGIN
    EXECUTE IMMEDIATE 'create sequence ' || V_PROD ||
    ' minvalue 1 maxvalue 999999 start with 1';
    END;


    CREATE OR REPLACE TRIGGER TRG_GEN_SEQUENCES
    BEFORE INSERT on MASTER_TABLE
    FOR EACH ROW
    DECLARE
    V_PROD VARCHAR2(5);
    N_ID NUMBER := 0;
    CT NUMBER := 0;
    ERR_MSG VARCHAR2(2000);
    BEGIN



    -- Retrieve the ID e of the last inserted row which is 100 by default
    -- set the default client_id value with nextvalue of sequence prod_IDS
    IF :NEW.ID = 100 THEN
    V_PROD := :NEW.PROD;
    SELECT PROD_IDS.NEXTVAL INTO N_ID FROM DUAL;
    :NEW.ID := N_ID;
    END IF;

    BEGIN


    SELECT COUNT(*)
    INTO CT
    FROM USER_SEQUENCES US
    WHERE UPPER(US.SEQUENCE_NAME) = UPPER(V_PROD);


    IF CT = 0 THEN
    -- create the sequence with name of V_PROD if doesn't exist


    INSERT INTO CDR_SQL_ERR
    (DB_OBJ, ERR_MSG, PROC_DATE)
    VALUES
    ('TRG_GEN_SEQUENCES',
    V_PROD || ' sequence will be created ', SYSDATE);


    --EXECUTE IMMEDIATE 'create sequence ' || V_PROD ||
    ---' minvalue 1 maxvalue 999999 start with 1';
    begin
    SEQ;
    end;

    ELSE


    INSERT INTO CDR_SQL_ERR
    (DB_OBJ, ERR_MSG, PROC_DATE)
    VALUES
    ('TRG_GEN_SEQUENCES',
    V_PROD || ' sequence alreday exist',
    SYSDATE);


    END IF;


    EXCEPTION
    WHEN OTHERS THEN
    ERR_MSG := TO_CHAR(SQLERRM) || ' ';
    INSERT INTO SQL_ERR
    (DB_OBJ, ERR_MSG, PROC_DATE)
    VALUES
    ('TRG_GEN_SEQUENCES', ERR_MSG || SEQ_DDL, SYSDATE);
    END;

    EXCEPTION
    WHEN OTHERS THEN
    NULL;
    ERR_MSG := TO_CHAR(SQLERRM) || ' ';
    INSERT INTO SQL_ERR
    (DB_OBJ, ERR_MSG, PROC_DATE)
    VALUES
    ('TRG_GEN_SEQUENCES', ERR_MSG || SEQ_DDL, SYSDATE);
    COMMIT;
    END;
  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    i don't know the name of the sequence in advance.
    So what code will be using the new sequence? Is that going to be dynamic as well?
    William Robertson
  • Khaja Hussain
    Khaja Hussain Member Posts: 9 Blue Ribbon
    Hi Everyone,

    You people are right creating the sequence at runtime doesn't make sense as i have realized the code
    will also be dynamic in nature.

    Thanks for everyone who involved in the discussion, iam closing this issue here itself.

    Regards
    Khaja
  • Khaja Hussain
    Khaja Hussain Member Posts: 9 Blue Ribbon
    Hi Everyone,

    You people are right creating the sequence at runtime doesn't make sense as i have realized the code
    will also be dynamic in nature.

    Thanks for everyone who involved in the discussion, iam closing this issue here itself.

    Regards
    Khaja
  • Rob van Wijk
    Rob van Wijk Member Posts: 5,856 Bronze Trophy
    Buga wrote:
    I beleive autonomous transaction will only commit the uncommited data in that trigger and not the one which are outside the BLOCK of CODE i.e any UNCOMMITED DATA FROM the application or any other TRIGGER.
    That's right. And that's exactly the big problem which violates the atomicity of a transaction (the A in ACID).
    If what you are saying is correct than One can never have an audit trigger.
    I can't follow your logic here.

    Auditing is probably one of the very few legitimate reason for autonomous transactions. And even then you have to realize that a row in the audit table doesn't necessarily mean that the corresponding action has been committed.

    Regards,
    Rob.
This discussion has been closed.