CREATE OR REPLACE PROCEDURE load_proc (P_ID IN NUMBER)
IS
   CURSOR C
   IS
      SELECT   GROUP_ID, tablename, target_table
        FROM   temp_table
       WHERE   GROUP_ID = p_id;
BEGIN
   FOR I IN C
   LOOP
      EXECUTE IMMEDIATE   'INSERT INTO '
                       || I.target_table
                       || '(SELECT * FROM '
                       || I.tablename
                       || ' WHERE '
                       || 'last_dt<=SYSDATE-500'
                       || ')';
     EXECUTE IMMEDIATE   'DELETE FROM '
                       || I.tablename
                       || ' WHERE '
                       || 'last_dt<=SYSDATE-500';
      COMMIT;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.PUT_LINE (
         'An error was encountered - ' || SQLCODE || ' -ERROR- ' || SQLERRM
      );
END load_proc;
/

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X 
   ,what      => 'ARP.load_proc(1);'
   ,next_date => to_date('20/01/2013 00:00:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/

SNO RECORDS_INSERTED               RECORDS_DELETED              JOBID   JOB_START_TIME         JOB_END_TIME          JOB_LAST_RAN_ON       JOB_STATUS   
1  2000 records inserted into     2000 records deleted from      1     20/01/2013 00:00:00    20/01/2013 02:30:00  20/01/2013 02:30:00   completed successfully 
   table WEDB_EMPLOYEE            table WEDB_EMPLOYEE
2							         1     21/01/2013 00:00:00     21/01/2013 01:00:00		         Failed: error message
3          

CREATE TABLE log_load_proc
(sno NUMBER,
records_inserted VARCHAR2(4000),
records_deleted VARCHAR2(4000),
jobid NUMBER,
job_start_time TIMESTAMP,
job_end_time TIMESTAMP,
job_last_ran_on TIMESTAMP,
job_status VARCHAR2(4000));