Possible to do INSERT with some columns from SELECT query and some columns with specific values

This can be done in a single statement, but you have to wrap the sequence call in a function.

By calling a function in the select (as opposed to the sequence directly), you overcome the problem of getting ORA-02287 errors. However, the function will be called once for each row in A, which is not what you want.

This can be overcome by defining the function as deterministic. This allows Oracle to optimize the function so it is only executed once for each distinct parameter value passed in. To make this work in this case, you'll need to pass in ceil(x/2):

create or replace function f(p integer) return number deterministic  as
  l_retval pls_integer;
begin
  select my_seq.nextval into l_retval from dual;
  return l_retval;
end;
/

insert all 
    when mod(x,2) = 0 then
        into b (val) values (seq)
        into c (val) values (seq)
    else 
        into c (val) values (seq)
select x, f(ceil(x/2)) seq from a;


select * from b;

VAL
---
  1 
  2 

select * from c;

VAL
---
  1 
  2 
  1 
  2 

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.