ORACLE, database, 10.2.0.3, autokey update issue

folks

I have the following query which selects values from 1 table based on criteria and dumps it into another


DECLARE
 v_SEED NUMBER;
BEGIN
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
 INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
END;
 this works fine on 1 record,though if I have two records then I get a unique constraint error,this occurs

as the below update isnt run

UPDATE AUTOKEY SET SEED =
(SELECT MAX(TICKETID) FROM
TICKET
) WHERE AUTOKEYNAME = 'SRID'

how could I integrate this between each row if insert so i elimiate duplicates?

r
rutgermonsAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Can other sessions be accessing the AUTOKEY table?  If so, then you need to ensure you have exclusive access to the record.

One method would be:

DECLARE
 v_SEED NUMBER;
BEGIN
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID' for update;
 INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
  update autokey set seed = v_seed where autokeyname = 'SRID';
END;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
obviously, the  seed field is primary key, so for the second insert, you need a new seed value...



DECLARE
 v_SEED NUMBER;
BEGIN
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
 INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
 INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
END;

or:

DECLARE
 v_SEED NUMBER;
BEGIN
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
 INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED+1,ASSETNUM FROM WORKORDER WHERE COST '100';
END;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
sorry, read only half of the question, my bad:


DECLARE
 v_SEED NUMBER;
BEGIN
 SELECT SEED+1 INTO v_SEED FROM AUTOKEY WHERE AUTOKEYNAME ='SRID';
 INSERT INTO B(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 INSERT INTO D(TYPE,ID,DESCRIPTION) SELECT 'STR', v_SEED,ASSETNUM FROM WORKORDER WHERE COST '100';
 UPDATE AUTOKEY SET SEED = (SELECT MAX(TICKETID) FROM TICKET )
  WHERE AUTOKEYNAME = 'SRID';
END;

0
 
rutgermonsAuthor Commented:
still have the same issue  when having more than 1 output record

any further advise?

brgds

r
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.