PL/SQL Revisited

Task- To populate a table B, sourced from table A and schedule it on regular intervals.

It is somewhat  creating ETL like mechanism to load PL/SQL table.

Steps-

  1. Write Procedure
  2. Create PL/SQL Job/schedule

1. Writing Procedure- Write a procedure with below syntax, it will load data in to the new table from existing query.

Create or replace procedure load_data 
As
Begin
Insert into yourtable  (select col1,col2,…. From Table A);
Commit;
Exception when others 
….
….
End;
2. Create  DBMS SChedule
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => ‘testjob’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN test01proc; END;’,
start_date => sysdate,
repeat_interval => ‘FREQ=SECONDLY;INTERVAL=10’, 
end_date => sysdate+4,
enabled => FALSE,
comments => ‘Gather table statistics’);
END;
Note- To disable any test jobs use
execute dbms_scheduler.disable(‘owner.jobname’)

Comments are closed.