ORA-00001 while Insert with Sub Select........

[shailesh]

Hi,

We are using Oracle 8.1.6 on Win 2000. I am inserting records in TblTest table with Insert INTO ...SELECT .. FROM query and generating TestId with Trigger. I can't use Sequences. I have also taken care to avoid mutating error. When I insert single record into Tbltest table. It works properly,but when try to insert Insert INTO ...SELECT .. FROM, Get error ORA-00001: unique constraint (SYSADM.XAK1I1DDTABLE) violated. Is there any other way to find out MAX(testid) from Test table before inserting new record into it?

How can I avoid this? please help.

Thanks & Regards,

Shailesh
---------------------------------------
CREATE OR REPLACE PACKAGE PkgTest As
TYPE REC IS RECORD(
nTestId TblTest.TestId%TYPE);
TYPE Tab_Rec IS TABLE OF REC INDEX BY BINARY_INTEGER;
Arr_Rec Tab_Rec;
Null_Rec Tab_Rec;
nCounter number:=0;
END PkgTest;
/
/***********************************************************************/

CREATE OR REPLACe PROCEDURE ProIns (nTestId IN NUMBER) AS
nID TblTest.TestId%TYPE;
BEGIN
SELECT NVL(MAX(TestId) + 1,1) INTO nID FROM TblTest;
UPDATE TblTest SET TestId = nID WHERE TestId = nTestId;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
END;
/
/***********************************************************************/

CREATE OR REPLACE TRIGGER TRGROW_Ins1
BEFORE INSERT
ON TblTest
FOR EACH ROW
Begin
:NEW.TestId:=0;
PkgTest.nCounter:= PkgTest.nCounter + 1;
PkgTest.arr_rec(PkgTest.nCounter).nTestId:=:NEW.TestId;

End;
/
/***********************************************************************/

CREATE OR REPLACE TRIGGER TRGSTMT_Ins1
AFTER INSERT ON TblTest

BEGIN
For I in 1..PkgTest.nCounter LOOP
ProIns(PkgTest.arr_Rec(I).nTestId);
END LOOP;
PkgTest.nCounter := 0;
PkgTest.arr_Rec:=PkgTest.Null_Rec;

End;
/

[jmodic]

Wow, what a pice of code you have written - a package, a procedure, a trigger and yet another trigger! All that for the same thing you could get with a single statement if you were using sequence!!!! You said you can't use sequences - any particular reason for that?

Now why you get ORA-00001? Because all the records you are inserting have their key kolumn TestID set to 0 - you explicitely set it to 0 in your before insert trigger. For any one of them, no exceptions.

Another thing with this aproach (even after you solve that problem with TestId=0): You will still get ORA-00001 if by any chance there is more than one session that will concurently insert into your table. There is nothing to stop Oracle to return the same MAX(TestID) from your table TblTest to more than one session if they insert rows simultaneously!!!!!

[shailesh]

Thanks for your reply. Anyway now I have use sequences to insert record and discarded earlier method.

Regards,

Shailesh