Insert a Sequence for every query execution in Oracle

Question

I have an automated job that runs an sql query. Below is the query.

Insert into employee_master (Job_Run_ID, Employee_ID, Employee_Name, Employee_Location) values (runId, eId, eName, eLoc);

Let's say this job runs for the first time and it inserts 10 records. Now, All the inserted records should have Job_Run_Id as 10001.

For the second run, the Job_Run_Id should increment, and 10002 should be the Job_Run_Id for all records inserted into the table.

Is it possible.? Please give your inputs.

I think your data structure is in trouble here. What is the primary key of this table? — mmmmmpie
– mmmmmpie, Commented Mar 13, 2015 at 12:20
The Oracle way prior 12c is to populate the id in a trigger from a sequence. Everything else is likely wrong. YMMV, though. — user272735
– user272735, Commented Mar 13, 2015 at 12:44

gmiley · Accepted Answer · 2015-03-13 12:22:26Z

1

Sure, you can do this several ways. First, you could create a sequence and select the next number from that before running your batch. The next would be to simply:

SELECT Max(Job_Run_ID) + 1 INTO New_Job_ID FROM employee_master;

Then do your job loop running your inserts:

INSERT INTO employee_master (New_Job_ID, Employee_ID, Employee_Name, Employee_Location) 
VALUES (runId, eId, eName, eLoc);

answered Mar 13, 2015 at 12:22

gmiley

6,6041 gold badge15 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ruudvan Over a year ago

Why not use a sequence here? If another job starts while one is running, both would get the same max(job_run_id).

gmiley Over a year ago

Yes, I did mention Sequences. I did not go in-depth because someone else had already mentioned sequences, but apparently deleted their answer.

Jeffrey Kemp · Accepted Answer · 2015-03-14 14:31:57Z

1

Use a sequence to ensure each job run gets a unique value. Use PL/SQL to re-use the same value for all rows inserted in a job run, e.g.:

<<p>>
DECLARE
  job_run_id NUMBER;
BEGIN
  job_run_id := job_run_seq.NEXTVAL;
  INSERT INTO employee_master (job_run_id, ...)
  VALUES (p.job_run_id, ...);
END p;

answered Mar 14, 2015 at 14:31

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Collectives™ on Stack Overflow

Insert a Sequence for every query execution in Oracle

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related