ORA-00001 while Insert with Sub Select........
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

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

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    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;
    /

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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!!!!!
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Thanks for your reply. Anyway now I have use sequences to insert record and discarded earlier method.

    Regards,

    Shailesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width