oracle19c

DBMS_JOB.SUBMIT with INSERT statement


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)
    );

Solution

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