Last active
April 7, 2020 13:20
-
-
Save aykononov/0abe83f95d0e262ccc6b90ff273e1ddf to your computer and use it in GitHub Desktop.
Функция для формирования отложенного задания (job).
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Функция для формирования отложенного задания (job). | |
Входные данные: ID задания (уникальный идентификатор задания); дата/время запуска задания; pl/sql код, который нужно запустить. | |
Функция должна проверять, есть ли уже запланированное задание с указанным ID. | |
Если есть, то необходимо его обновить, иначе создать. | |
*/ | |
CREATE OR REPLACE FUNCTION FNC_MAKE_JOB ( | |
pJobNameID all_scheduler_jobs.job_name%TYPE, | |
pJobStartDate DATE, | |
pJobExecuteCode CLOB | |
) | |
IS | |
RETURN VARCHAR2(32767); | |
vJobObjectExists NUMBER; | |
ERR_Null EXCEPTION; | |
BEGIN | |
IF pJobNameID IS NOT NULL THEN | |
SELECT COUNT(1) INTO vJobObjectExists FROM all_scheduler_jobs asj WHERE asj.job_name = pJobNameID; | |
IF vJobObjectExists = 0 THEN | |
BEGIN | |
DBMS_SCHEDULER.CREATE_JOB ( | |
job_name => pJobNameID, | |
job_type => 'PLSQL_BLOCK', | |
job_action => pJobExecuteCode, | |
start_date => pJobStartDate | |
repeat_interval => NULL, | |
auto_drop => TRUE, | |
ENABLED => TRUE | |
); | |
END; | |
RETURN pJobNameID || ': New job created successfully.' | |
ELSE | |
BEGIN | |
DBMS_SCHEDULER.set_attribute ( | |
NAME => pJobNameID, | |
ATTRIBUTE => 'start_date', | |
VALUE => pJobStartDate | |
); | |
DBMS_SCHEDULER.set_attribute(NAME => pJobNameID, | |
ATTRIBUTE=> 'job_action', | |
VALUE => pJobExecuteCode); | |
); | |
END; | |
RETURN pJobNameID || ': Job successfully changed.' ); | |
END IF; | |
ELSE | |
RAISE ERR_Null; | |
END IF; | |
EXCEPTION | |
WHEN ERR_Null THEN | |
dbms_output.put_line('pJobNameID: Input parameter not defined.'); | |
WHEN OTHERS THEN | |
RETURN '[FNC_MAKE_JOB]' || CHR(10) || SQLCODE || CHR(10) || SQLERRM || CHR(10) || dbms_utility.format_error_backtrace; | |
END FNC_MAKE_JOB; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment