• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

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
0
rutgermons
Asked:
rutgermons
  • 2
1 Solution
 
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
 
johnsoneSenior 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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now