Chapter 20
Pages 460, 466-67
Tracking Jobs

CREATE TABLE run_log
  ( run_id            NUMBER        NOT NULL /* from run request   /*
  , task_name         VARCHAR2(16)  NOT NULL /* might want path    /*
  , command_line      VARCHAR2(255) NOT NULL /* gives the arg list /*
  , invoked_by        VARCHAR2(30)  NOT NULL /* requesting user    /*
  , started_at        DATE          NOT NULL
  , ended_at          DATE
  , completion_code   VARCHAR2(72) 
  , rows_processed    NUMBER                 /* from driving table /*
  , cpu_used          NUMBER                 /* if available       /*
  , memory_used       NUMBER                 /*      "             /*
  , io_used           NUMBER                 /*      "             /*
  , CONSTRAINT run_log_pk PRIMARY KEY (run_id)
  );

CREATE TABLE run_step
  ( run_id            NUMBER        NOT NULL /* from run request /*
  , stage             VARCHAR2(16)  NOT NULL
  , rows_processed    NUMBER        NOT NULL
  , reached_at        DATE          NOT NULL
  , cpu_used          NUMBER                 /* if available /*
  , memory_used       NUMBER                 /*      "       /*
  , io_used           NUMBER                 /*      "       /*
  , CONSTRAINT run_step_pk 
              PRIMARY KEY (run_id, stage, rows_processed)
  , CONSTRAINT run_step_pk 
              FOREIGN KEY (run_id) REFERENCES run_log
               );


DECLARE
 CURSOR c1 IS
   SELECT deptno
        , dname
     FROM dept
     ORDER BY dname; -- makes the log look more logical
BEGIN
  INSERT INTO run_log (id,   start_at)
              VALUES  ('.X'. SYSDATE);
  FOR this_dept IN c1
  LOOP
    INSERT INTO new_emp
    SELECT * FROM emp WHERE deptno = this_dept.deptno;
    INSERT INTO run_steps (id,   step,            ended_at)
                VALUES    ('.X'. this_dept.dname, SYSDATE);
    COMMIT;
  END LOOP;
  UPDATE run_log SET ended_at = SYSDATE
   WHERE id = 'XX'.
  COMMIT;
END;
/


DECLARE
 CURSOR c1 IS
   SELECT deptno
        , dname
        , active
     FROM dept
     ORDER BY dname; -- MAKES THE LOG LOOK MORE LOGICAL
BEGIN
  INSERT INTO run_log (id,   start_at)
              VALUES  ('.X'. SYSDATE);
  FOR this_dept IN c1
  LOOP
    IF this_dept.active = 'Y'
    THEN BEGIN
      INSERT INTO new_emp
      SELECT * FROM emp WHERE deptno = this_dept.deptno;
      INSERT INTO run_steps (id,   step,            ended_at)
                  VALUES    ('.X'. THIS_DEPT.DNAME, SYSDATE);
      COMMIT;
    END;
    END IF;
  END LOOP;
  UPDATE run_log SET ended_at = sysdate
   WHERE id = 'XX'.
  COMMIT;
END;
/