Unique Constraint Error while using Sequnce
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: Unique Constraint Error while using Sequnce

Hybrid View

  1. #1
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Hello,

    We are using version 8.1.6 on NT4. I have creatd sequence with cache 100 increment by 1 nocycle parameters. While inserting records through procedure it's giving me error saying 'ORA-001 Unique Constraint voilated'. I have checked following things...

    SQL> select max(to_number(NO2)) from Table;

    MAX(TO_NUMBER(No2))
    ----------------------
    46727

    SQL> select last_NUMBER from dba_sequences where sequence_name='NO2';

    LAST_NUMBER
    -----------
    46729

    SQL> select No2.currval from dual;

    CURRVAL
    ----------
    46728


    CREATE OR REPLACE PROCEDURE PROINSREC IS
    INSSTMT Varchar2(200);
    Cnt Number :=0;
    BEGIN
    While Cnt <20000 Loop
    INSSTMT := 'Insert into Table (No2,Name) values (No2.nextval,''Ein1'')';
    Execute Immediate INSSTMT;
    Commit;
    Cnt := Cnt + 1;
    End Loop;
    END PROINSREC;

    Please Suggest remedy for this problem. Is this a problem of caching???

    Thanks & Regards
    Shailesh

  2. #2
    Join Date
    Apr 2001
    Posts
    16

    Thumbs up

    I think there is a problem with your cache memory.
    Try increasing value for SEQUENCE_CACHE_ENTRIES parameter in your init.ora file.
    Also Oracle might skip sequence numbers if you choose to cache a set of sequence numbers .
    Let me know if it works...

  3. #3
    Join Date
    Apr 2001
    Posts
    14
    Hi,

    I suppose the procedure is not in Problem, ... What kind of application are you using? Is it Java. May need to check the Java code, becuase Oracle sequence can never be duplicated. If you are using multile databases, may be the application is accessing the other Database.

    Hope ithelps.
    Thanks

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    A couple comments:

    - Why did you need to do a to_number on the column? Shouldn't it already be a number? It is populated via a sequence after all.

    - The cache is irrelevant. This only helps avoid contention when multiple sessions are trying to retrieve sequence IDs at the same time.

    - Naming the column the same as the sequence is confusing at best. It may also be confusing Oracle. Same with a table named table. Of course, you may have done this for purposes of an example. If so, nevermind .

    - Do you have a unique Index on any other column in the table, such as name, per chance?

    - Finally, a minor point about how the statement is interpreted. Since you are using dynamic SQL with a hard-coded value, each statement will be different when passed to Oracle. This means that each will have to be parsed and each will become a separate entry in the SGA. It would be better to use binds with the dynamic SQL. Taking this further, however, dynamic SQL is not even necessary here. This would be faster *and* use binds...

    CREATE OR REPLACE PROCEDURE PROINSREC IS
    ---Cnt Number :=0;
    BEGIN
    ---WHILE Cnt < 20000 LOOP
    ------INSERT INTO
    ---------Table
    ------------(
    ---------------No2,
    ---------------Name
    ------------)
    ------VALUES
    ------------(
    ---------------No2.nextval,
    ---------------'Ein1'
    ------------)
    ------;
    ------COMMIT;
    ------Cnt := Cnt + 1;
    ---END LOOP;
    END PROINSREC;

    Of course, I would again assume that you only did this for purposes of an example, but I just want to make sure you understand the issue in case your actual code does the same thing. Taking that thought even further: Commiting after each row is not necessary and entails quite a bit of overhead. Finally, if you were truly doing this in your app, I would suggest investigating the new BULK processing options to speed such an operation even further.

    HTH,

    - Chris

  5. #5
    Join Date
    Jul 2000
    Location
    Pune, India
    Posts
    80
    Thanks Chris and all for giving me inputs. Actually it's amazing but I have avoided this error by recreating unique indexes for this test application.

    Thanks & 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