-- Scheduled jobs (screen "المهام المجدولة", SU only). Run once per database in the PRIMARY schema (NAFITHTCS). Safe to re-run; not run by the app. -- Until this script has run the scheduler stays idle (one warning in the API log) and the screen shows a message. -- -- GRAINS_JOBS one row per job: stopped / started, schedule, last run. Rows are created by the screen, nothing to seed. -- GRAINS_JOB_RUNS history of every run (kept 90 days by the application). -- -- Jobs (ports of the legacy jobs\*.cfm files): activate-company-destinations, close-old-permits, fill-company-shares. -- Schedule: SCHEDULETYPE = 'INTERVAL' (INTERVALMINUTES) or 'DAILY' (DAILYTIMES = 'HH:mm,HH:mm', database server time). DECLARE PROCEDURE ddl(p_sql VARCHAR2) IS BEGIN EXECUTE IMMEDIATE p_sql; EXCEPTION WHEN OTHERS THEN -- already there: name in use (955), column list already indexed (1408) IF SQLCODE NOT IN (-955, -1408) THEN RAISE; END IF; END; BEGIN ddl('CREATE TABLE GRAINS_JOBS ( JOBKEY VARCHAR2(50) NOT NULL, ENABLED NUMBER(1) DEFAULT 0 NOT NULL, SCHEDULETYPE VARCHAR2(10), INTERVALMINUTES NUMBER(8), DAILYTIMES VARCHAR2(200), LASTSTARTDATE DATE, LASTENDDATE DATE, LASTSTATUS VARCHAR2(20), LASTMESSAGE VARCHAR2(500), LASTCOUNT NUMBER(10), UPDATEDATE DATE, UPDATEDBY VARCHAR2(50), CONSTRAINT PK_GRAINS_JOBS PRIMARY KEY (JOBKEY) )'); ddl('CREATE TABLE GRAINS_JOB_RUNS ( ID NUMBER(12) NOT NULL, JOBKEY VARCHAR2(50) NOT NULL, STARTDATE DATE NOT NULL, ENDDATE DATE, STATUS VARCHAR2(20), ROWSAFFECTED NUMBER(10), MESSAGE VARCHAR2(500), TRIGGEREDBY VARCHAR2(50), CONSTRAINT PK_GRAINS_JOB_RUNS PRIMARY KEY (ID) )'); ddl('CREATE SEQUENCE SEQ_GRAINS_JOB_RUNS START WITH 1 INCREMENT BY 1 NOCACHE'); ddl('CREATE INDEX IX_GRAINS_JOB_RUNS_KEY ON GRAINS_JOB_RUNS (JOBKEY, ID)'); END; / -- Stop a job from SQL if the screen is not reachable: UPDATE GRAINS_JOBS SET ENABLED = 0 WHERE JOBKEY = 'close-old-permits'; COMMIT;