Oracle Procedure for testing of truncating and inserting data into temp table

January 22, 2017 Billy Aung Myint No comments

The basic code using Oracle using HR schema ,

select * from user_scheduler_jobs
create table test_main as select * from employees
insert into test_main (select * from employees);
select * from test_main
create table test_temp as select * from test_main
select * from test_temp

— truncate temp table
truncate table test_temp

— copy main table into temp table
insert into test_temp (select * from test_main)

— truncate main table
truncate table test_main

 


The Procedure

create or replace procedure test_proc
AS
BEGIN
/* truncate temp table */
EXECUTE IMMEDIATE ‘TRUNCATE TABLE test_temp’;

/* copy main table into temp table */
insert into test_temp (select * from test_main);

/* truncate main table */
EXECUTE IMMEDIATE ‘TRUNCATE TABLE test_main’;
commit;
END test_job;

/* Source : http://stackoverflow.com/questions/21995951/truncate-and-insert-within-procedure-dont-work-together */


The Job – will run every 7 days starting from 22/01/2017

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name           =>  ‘test_job’,
job_type           =>  ‘STORED_PROCEDURE’,
job_action         =>  ‘hr.test_proc’,
start_date         => to_timestamp(’22/01/2017 22:00:00′, ‘dd/mm/yyyy hh24:mi:ss’),
repeat_interval    => ‘FREQ=DAILY;INTERVAL=7;BYHOUR=22‘,
auto_drop          =>   FALSE,
enabled            => true,
comments           =>  ‘My new job’);
END;
/

/* Source  : https://docs.oracle.com/cd/B28359_01/server.111/b28310/scheduse004.htm#ADMIN12415

Source 2 : https://myotragusbalearicus.wordpress.com/2012/08/20/oracle-dbms_scheduler-every-30-minutes-in-working-hours/ */