We have a project ID column that has the 2 number year followed by an incrementing number. When we add a new row we need to get the last value and increment it and then concatinate it back together with the year and finally insert it into the database. The problem is that when 2 users do it at exactly the same time we get a duplicate. Is there a way to ensure that the number we have is not grabbed by another insert or lock the table for this insert. We use ADO.NET to run the query using an OdbcCommand and ExecuteNonQuery so there cannot be semicolons in the code or it has to be run as a sepereate query
INSERT INTO GECPLAN (GECPLAN_IS_NUMBER,
TINWSYS_IS_NUMBER, TINWSYS_ST_CODE, ST_PROJECT_ID
) VALUES (PLAN_IS_SEQ.NEXTVAL,
1, 1, (SELECT Distinct to_char(SysDate, 'YY') || '-' ||
SUBSTR(('0000' || (SELECT NVL(MAX(SUBSTR(st_project_
id, 4, 9))+1, 1) AS ProjNum FROM gecplan
where DECODE(LENGTH(TRIM(TRANSLA
TE(st_proj
ect_id, '+-0123456789', ' '))),null,1,0) = 1
AND st_project_id LIKE to_char(SysDate, 'YY') || '-%')),
LENGTH((SELECT NVL(MAX(SUBSTR(st_project_
id, 4, 9))+1, 1) AS ProjNum FROM gecplan
where DECODE(LENGTH(TRIM(TRANSLA
TE(st_proj
ect_id, '+-0123456789', ' '))),null,1,0) = 1
AND st_project_id LIKE to_char(SysDate, 'YY') || '-%')), 5)
FROM GECPLAN ))
Start Free Trial
