Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

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

Khaja HussainOct 24 2009 — edited Oct 25 2009
Hi,

iam getting the ORA-04092: cannot COMMIT in a trigger while creating seq inside a trigger, kindly anyone explain why iam getting the error though i don't any commit statement in it.


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';

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;

Comments

Peter Gjelstrup
Hi,

DDL has two implicit commits, one before and one after the statement.

But honestly, why don't you just create the sequences you need. If this is because you want sooo many sequences, then perhaps you could use the data dictionary to create a script.
You shouldn't be writing a trigger for this.

Btw, you do have an explicit commit, in your exception handler.

Regards
Peter
728534
I am just giving you a way to achieve what you are doing.
I do not know if what you are doing is correct or not. Iw ill pressume you need the trigger.. here is what you need to do

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);

PRAGMA AUTONOMOUS_TRANSACTION; -- ADD THIS AND IT WILL WORK FINE
BEGIN
/*rest of you code*/
end;
Rob van Wijk
Buga wrote:
I am just giving you a way to achieve what you are doing.
...
PRAGMA AUTONOMOUS_TRANSACTION; -- ADD THIS AND IT WILL WORK FINE
Ouch!

Please do not use this. This will split up your transactions. And when one of them fails, the other ones might succeed and you'll have inconsistent data.

Regards,
Rob.
728534
Thanks ROB.
But i beleive he is commting only in WHEN OTHERS (Another Bug if you may say)
So i dont think it will split up the transaction (Commiting only in exception block)

Cheers!!!
Bhushan
Rob van Wijk
What business goal are you trying to achieve here?
The idea that I can deduce from your code doesn't sound good. If, for some reason, you only want 1 sequence, then this isn't the way to do it. Just use one_seq.nextval inside your insert statement. To remove possible contention, one-sequence-per-table is a much safer bet.

Regards,
Rob.
Rob van Wijk
Brushan,

A transaction involves more than a single insert statement and one execution of this database trigger. What if an after-row-insert trigger fails? The code in the before-row-insert trigger has committed ... What if the insert succeeds and the user issues a rollback? The code in the before-row-insert-trigger has committed ...

There is also an implicit commit in his "execute immediate 'create sequence'" statement, by the way.

Regards,
Rob.
728534
Dear Rob,
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.

If what you are saying is correct than One can never have an audit trigger.

Regards,
Bhushan
Khaja Hussain
Hi Everyone,

My intention is to create a sequence at runtime(using dynamic SQL), i don't know the name of the sequence in advance. It will not create the sequence if it exists, once the sequence is created it will be rest after end of every month.

Removing the commit statement from when others block didn't solve the problem.
Inclusion of PRAGMA AUTONOMOUS_TRANSACTION has thrown ORA-06519: active autonomous transaction detected and rolled back.

Thanks

Regards
Khaja
Khaja Hussain
Hi Everyone,

My intention is to create a sequence at runtime(using dynamic SQL), i don't know the name of the sequence in advance. It will not create the sequence if it exists, once the sequence is created it will be rest after end of every month.

Removing the commit statement from when others block didn't solve the problem.
Inclusion of PRAGMA AUTONOMOUS_TRANSACTION has thrown ORA-06519: active autonomous transaction detected and rolled back.

Thanks

Regards
Khaja
Dom Brooks
My intention is to create a sequence at runtime(using dynamic SQL)
As people are trying to tell you, this is a pretty damn good sign that you're taking a wrong approach for some reason.
Why do you think you need to dynamically create sequences?
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.
1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Nov 22 2009
Added on Oct 24 2009
16 comments
4,841 views