How to populate repetitive sequence number based on 1 batch load

Question

I have the below table-

Id   First_Name  Last_Name  Run_Id
1    Alice       Spears      1 
2    Bob         Vance       1

I need to populate the Run_id column, where the next batch of data should have value as 2 for all the rows populated in the load. Can we use sequence here, I'm not sure how to achieve this.

The next batch load should look like below-

Id   First_Name  Last_Name  Run_Id
1    Alice       Spears      1 
2    Bob         Vance       1
3    Kate        Josh        2
4    Harry       Gough       2
5    Ben         Bohmer      2

Thanks in advance.

Answer 1

Create a sequence and a function. Then call the function and save it into a variable and reference the variable while doing your DML.

CREATE SEQUENCE batch_seq
  START WITH 1
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

       CREATE OR REPLACE FUNCTION get_batch_seq RETURN number AS l_return number;
         BEGIN 
             SELECT batch_seq.nextval into l_return from dual;
            return l_return;
        END;
/



 SELECT get_batch_seq INTO l_batch_seq FROM dual;