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.
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;
/
