I am trying to implement the process with DBMS_JOB.SUBMIT
with an INSERT statement but I am not able to successfully transfer the data from one table to another.
DECLARE
l_job NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => l_job,
what => 'BEGIN insert into customers_tbl1 select * from customers_tbl;
END;'--,
-- next_date => SYSDATE--, -- Schedule for 1 hour from now
--interval => 'SYSDATE' -- Reschedule every day
);
COMMIT; -- Important to commit the transaction to schedule the job
END;
Table Data:
CREATE TABLE customers_tbl
( customer_id number(10),
customer_name varchar2(50),
city varchar2(50)
);
insert into customers_tbl(customer_id,customer_name,city) values(1,'Albert','Munich');
insert into customers_tbl(customer_id,customer_name,city) values(2,'Alex','Stuttgart');
insert into customers_tbl(customer_id,customer_name,city) values(3,'Sasuke','Tokyo');
CREATE TABLE customers_tbl1
( customer_id number(10),
customer_name varchar2(50),
city varchar2(50)
);
When using DBMS_SCHEDULER
:
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'MT_TEST1',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN insert into customers_tbl1 (CUSTOMER_ID, CUSTOMER_NAME, CITY) select CUSTOMER_ID, CUSTOMER_NAME, CITY from customers_tbl; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=SECONDLY;INTERVAL=10',
enabled => TRUE,
comments => NULL
);
END;
/