I'd think it's probably not a best practice to use EXCEPTION
to do logic/handling, such as duplicate keys. Here's an idea: write a stored proc. Also create a sequence that has your alternate values that will be used in cases of duplicate keys. You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data). Then do your inserts via this stored proc whenever you want the dupe-sub functionality.
CREATE TABLE components (
id_component INTEGER PRIMARY KEY
, code VARCHAR2(255)
, description VARCHAR2(255)
, model VARCHAR2(255)
, resp VARCHAR2(255)
);
CREATE SEQUENCE components_alt_id_seq
START WITH 1000000;
CREATE OR REPLACE PROCEDURE component_ins
( p_id_component IN components.id_component%TYPE
, p_code IN components.code%TYPE
, p_description IN components.description%TYPE
, p_model IN components.model%TYPE
, p_resp IN components.resp%TYPE )
AS
v_is_duplicate INTEGER;
BEGIN
SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
IF v_duplicate = 0 THEN
INSERT INTO components (id_component, code, description, model, resp)
VALUES (p_id_component, p_code, p_description, p_model, p_resp);
ELSE
INSERT INTO components (id_component, code, description, model, resp)
VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
END IF;
END;
/
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.
SELECT id_component FROM components;
ID_COMPONENT
------------
39822
39823
39824
40015
40016
1000005
1000006
7 rows selected.
Best Answer
This has nothing to do with the insert.