Oracle execute procedure based on a table count

Question

I have a procedure in PLSQL that picks 5000 records from a table and inserts in another table.

The JMS queue then picks the data from the queue and processes them. When the queue is empty, I run the procedure again to pick another 5000 records.

Is there a way in plsql to automate this? Whenever the count of the queue table is 0, it executes the procedure again.

Answer 1

You can create an Oracle Job that runs every now and then to insert new records if your queue is empty: DBMS_JOB vs DBMS_SCHEDULER

It looks pretty simple to schedule something: https://stackoverflow.com/a/27820057/6019417

Create a procedure

PROCEDURE p1
IS 
  v_count number := 0;
BEGIN
  select count(1) into v_count from my_table;
  if v_count = 0  then
    -- add 5000 rows
  end if;
END;

call it in DBMS_JOBS for each time you like:

call the same in DBMS_JOBS each time you like (in my example it is every 10 minutes):

VARIABLE jobno number;
BEGIN
  DBMS_JOB.SUBMIT(:jobno, 'p1', SYSDATE, 'trunc(sysdate,''MI'')+60/1440');
  COMMIT;
END;
/