CODEWITHSUNDEEP
javaoracle-databaseprepared-statement
Jerry
Jerry

Reputation: 1

Use prepare statement for Oracle Db, get unique constraint violated

We try to use java preparedstatement to do insert action, we got ORA-00001: unique constraint (POSTIONS_TABLE_PK) violated error.

The app target at Oracle DB. the table name info:

postion_table columns:

 position_id number pk,
 amount number,
 account_id number

We need postion_id to be sequential number. start from 1. so will be 1, 2 ,3 .... n. For example, when we delete record with position_id 10, then the next new insert need to use 10 as position_id.

The insert query for prepared statement looks like:

BEGIN
DECLARE
position_id number;
BEGIN
  SELECT NVL(MAX(t.position_id) + 1, 1)
    INTO position_id
    FROM  positions_table t
   WHERE account_id = 1;
INSERT into positions_table(position_id,amount, account_id)
 values(position_id, ?, ?);
EXCEPTION
  WHEN OTHERS THEN 
    raise_application_error(-20999, 'Error : ' || sqlerrm);
    ROLLBACK;
END;
END;

We got unique constraint (TPOSTIONS_TABLE_PK) violated error when we try to insert multiple records in a batch.

Any suggestion on how to build the prepared SQL.

Upvotes: 0

Views: 261

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

Using

SELECT NVL(MAX(t.position_id) + 1, 1)

will never work in any system where you have more than a single user. Because two users will both run the command, they will both find POSITION_ID to be (say) 17, and they will both then try use 18, and one will fail.

Use a sequence (which might give you gaps) but then when you wish to get a list of rows with a gap-free number, do it at query time, ie

select row_number() over ( partition by ... order by position_id )

If you really really really need a gap free sequence generated at INSERT time, then a reasonably scalable method is:

  1. create table with all the numbers (eg, 1 through 1,000,000) and index that column.

  2. each person wanting a sequence does

    open cursor for select ... from that_table for update skip locked;

and fetches a row from it.

Over time, every number will be used. But its a lot of work and complexity for something that is often not really a business requirement.

Upvotes: 1

Related Questions