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

Khaja Hussain

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~

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

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?

Khaja Hussain

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

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

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.