Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a procedure that is called from concurrent transactions:

//Some actions here

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);

INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
INSERT INTO table1
                (table1_id, table1_val1, table1_val2, table1_val3)
              VALUES
                (gettablenewid('TABLE1'), val1, val2, val3);
//some other actions

Function gettablenewid code (id_table stores PKs for each table):

create or replace
    function        GetTableNewId(tablename in varchar2)
      return number is
      PRAGMA AUTONOMOUS_TRANSACTION;
      Result number;
    cursor c1 is SELECT ig.id_value+1 id_new
                              FROM id_table ig
                              WHERE ig.table_identifier = tablename
                              FOR UPDATE of  ig.id_value;
    begin
    for c1_rec in c1 loop  
      UPDATE id_table ig
         SET ig.id_value = c1_rec.id_new
       WHERE current of c1 ;
       Result:=c1_rec.id_new;
    end loop;
    commit;
      return(Result);
    end GetTableNewId;

Occasionally insert statement fails with ORA-00001 for table1_id, I can't undestand why it happens.

share|improve this question
2  
Use sequences!! –  FreshPhilOfSO Mar 13 '13 at 6:42
1  
Could it be that somewhere there is an insert without the GetTableNewId function or with the wrong tablename ? i.e. you're updating table1 with gettablenewid('TABLE2') –  A.B.Cade Mar 13 '13 at 9:00
    
glad you found a solution: I'm going to close this as it isn't likely to be of help to someone else :) –  Jack Douglas Mar 14 '13 at 7:50
add comment

closed as too localized by Jack Douglas Mar 14 '13 at 7:50

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.

1 Answer

@A.B.Cade you're right! I found another process that modified id_table concurrently for table_identifier = 'TABLE1'. Thanks to all for your help.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.