Rem Rem $Header: sdk_jobs_pkgbody.sql 28-jun-2007.04:09:46 kmanicka Exp $ Rem Rem sdk_jobs_pkgbody.sql Rem Rem Copyright (c) 2002, 2007, Oracle. All rights reserved. Rem Rem NAME Rem sdk_jobs_pkgbody.sql - Rem Rem DESCRIPTION rem This is the PL/SQL SDK interface to the job system Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem kmanicka 06/28/07 - Backport kmanicka_bug-6047579 from main Rem kmanicka 06/13/07 - bug 6047579 : fix suspend_timeout Rem kmanicka 05/24/07 - Implement 10.2.0.4 Trusted RemoteOP Rem rdabbott 05/22/07 - bug 5714960b: desupport user suspend with timeout Rem lefeng 04/10/07 - Fix bug 5967327 Rem lefeng 05/01/07 - Backport lefeng_bug-5967327 from main Rem lefeng 08/02/06 - Backport lefeng_bug-5074716 from main Rem lefeng 07/17/06 - bug 5074716: job owner must be registered Rem skini 09/21/05 - Check for execution id during deleted tgt check Rem dcawley 08/30/05 - Add job grants Rem skini 08/28/05 - Do not release dbms locks until commit/rollback Rem dsahrawa 08/01/05 - bug 4482310, clone multitask job type on Rem submission from library Rem pkantawa 07/14/05 - Change Restart exception Rem skini 07/08/05 - Remove code to release lock Rem dsahrawa 07/05/05 - bug 4148622, command block rewrite Rem pkantawa 06/07/05 - Exclude CAs from query Rem skini 06/06/05 - grabtrans 'skini_bug-4377344' Rem skini 05/20/05 - Cutover suspend_job to call engine proc Rem rdabbott 04/26/05 - Fix 4316792: stop job run Rem skini 04/15/05 - Add suspended/creds to suspended bucket Rem skini 04/14/05 - Suspended/creds status Rem skini 04/01/05 - Continue large parameter fix Rem skini 03/31/05 - Fix submitJobFromLib to handle large parameters Rem pkantawa 03/28/05 - Use reassigned exec status Rem kmanicka 03/07/05 - Impl IMMEDIATE Schedule Rem kmanicka 03/10/05 - Impl JOB_STATUS_EXPIRED Rem pkantawa 03/08/05 - disallow restart exec if job is delete pending Rem shianand 03/01/05 - Rem kmanicka 02/10/05 - throw exception while suspending execution if Rem execution is already suspended Rem shianand 02/17/05 - Rem shianand 01/24/05 - Audit Functionality Added Rem skini 11/21/04 - Cutover to common schema insert procedure Rem dsahrawa 11/05/04 - store start_grace_period in mgmt_job_schedule Rem dsahrawa 10/08/04 - Rem skini 10/08/04 - Versioning changes Rem skini 09/07/04 - Fix catchall Rem skini 08/23/04 - Fix bug in submitJobFromLibrary Rem kmanicka 08/17/04 - bugfix - 3489013 same job name Rem skini 08/12/04 - Cutover all printlns to logs Rem pshishir 07/15/04 - Moved restart_job_execution to Job_engine. Rem pshishir 07/15/04 - Add MGMT_JOB_ENGINE.set_job_params in submit_job(). Rem kmanicka 08/06/04 - fixing retry with prameters Rem skini 08/02/04 - Retry callbacks only on new execution Rem kmanicka 07/20/04 - Added support for retry Callback and new restart APIs Rem skini 07/19/04 - Add notification support Rem dsahrawa 07/14/04 - move waiting from running to scheduled bucket Rem dsahrawa 07/09/04 - Rem dsahrawa 06/22/04 - implement skipped job executions Rem dcawley 07/07/04 - Increase username size. Rem skini 05/19/04 - Encrypt job parameters Rem jsadras 03/18/04 - Rem jsadras 03/17/04 - Added user lock to submit jobs Rem jsadras 03/16/04 - Rem rdabbott 02/23/04 - dont expose status2 Rem rdabbott 02/03/04 - suspend, stop pending is actually a 'running' Rem rdabbott 01/31/04 - use indexed function Rem rdabbott 01/30/04 - Fix 3256113: create 'active' bucket Rem rdabbott 01/09/04 - fix 3328590: save timezone region Rem dcawley 01/26/04 - Do not pass user when granting full job Rem dcawley 01/22/04 - Change grantingof full job to owner Rem dsahrawa 11/19/03 - bug 3265319 Rem dsahrawa 11/14/03 - bug 3202830 Rem dsahrawa 11/12/03 - bugs 3176751, 3241925 Rem rdabbott 10/28/03 - bug 3209093: fix buckets Rem dsahrawa 10/24/03 - bug 3183135 Rem skini 10/02/03 - Add delete_job_run Rem skini 10/01/03 - Return the execution ids Rem skini 10/01/03 - Rem skini 09/30/03 - Submit job to queue Rem skini 09/30/03 - Add status bucket column Rem dcawley 09/17/03 - Change granting of full job to owner Rem skini 09/17/03 - Set created_at_submit Rem skini 09/10/03 - Implement suspend job Rem skini 09/09/03 - Change in arguments to schedule_record Rem skini 09/08/03 - WOrkaround for vpd issue Rem skini 09/04/03 - Fix submit_job_from_library Rem skini 09/02/03 - Edit changes Rem skini 08/31/03 - Implement suspendable, restartable, editable Rem skini 08/26/03 - Rem skini 08/25/03 - Continue with groups impl Rem skini 08/24/03 - Single-target job support Rem skini 08/23/03 - Changes to make job name/owner unique Rem skini 08/18/03 - Add override credentials support Rem skini 07/16/03 - Job library support Rem skini 06/20/03 - Add edit_job Rem skini 06/11/03 - Large parameter support Rem skini 05/13/03 - Security checks Rem skini 12/06/02 - Fix timezone_offset Rem skini 10/11/02 - Change Call to update_job_parameters Rem rdabbott 10/03/02 - fix comment Rem rdabbott 10/02/02 - review: use correct target index Rem rdabbott 09/30/02 - restart job by execid Rem skini 09/16/02 - suspended_serialized=>suspended_lock Rem skini 08/28/02 - Implement suspend timeout Rem skini 08/27/02 - Implement event-based suspends Rem rdabbott 08/05/02 - restart stopped jobs too Rem skini 07/09/02 - Fix error in delete_job call Rem skini 06/14/02 - continue implementing blackouts Rem skini 06/12/02 - Rem skini 06/07/02 - Changes to support TIMEZONE_SPECIFIED Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts Rem rpinnama 05/15/02 - Created rem skini 04/18/02 - SMP_MGMT_JOB=>MGMT_JOB rem rpatti 02/25/02 - use function to get current user rem skini 02/27/02 - Implement option to nuke system jobs rem skini 01/29/02 - Change signature of schedule_job rem skini 01/31/02 - history table rem skini 01/29/02 - Change signature of schedule_job rem skini 01/24/02 - Schedules, contd rem skini 01/18/02 - Implement security info, purging, schedules rem rpinnama 01/17/02 - Add pingCfmEMDDown command rem skini 01/16/02 - Encryption, system jobs rem skini 12/28/01 - Fix restart_job to return value rem skini 12/26/01 - Catch incorrectly specified parameters rem rpinnama 12/12/01 - Add support for switch step set. rem shuberma 12/07/01 - Adding a new command for downloading a patch zip file.. rem mgoodric 11/29/01 - Add PatchEMD job type rem skini 11/21/01 - Fix restart param copy rem skini 11/20/01 - Resubmit rem skini 11/18/01 - Add repSQL command rem skini 11/14/01 - Add parameter to registerCommand rem mgoodric 11/09/01 - Create a new PatchOneOff job type rem vkhizder 11/06/01 - adding snapshot collection job command rem skini 11/04/01 - Implement restart rem skini 10/31/01 - Register fileTransfer command rem tjaiswal 10/25/01 - Fix raise error rem skini 10/22/01 - Introduce suspendJob rem skini 10/15/01 - Ensure all targets are genuine rem skini 10/04/01 - Register email command rem shuberma 09/28/01 - Adding ecm patch notify command type.. rem skini 09/24/01 - Add echoParams command rem skini 09/14/01 - Handle error cases Rem CREATE OR REPLACE PACKAGE BODY MGMT_JOBS AS /****** PRIVATE METHODS ******/ /****** PUBLIC METHODS ******/ FUNCTION get_job_schedule_record(p_frequency_code NUMBER, p_start_time DATE, p_end_time DATE, p_execution_hours NUMBER, p_execution_minutes NUMBER, p_interval NUMBER, p_months MGMT_JOB_INT_ARRAY, p_days MGMT_JOB_INT_ARRAY, p_timezone_info NUMBER, p_timezone_target_index NUMBER, p_timezone_offset NUMBER, p_timezone_region VARCHAR2, p_start_grace_period NUMBER DEFAULT -1) RETURN MGMT_JOB_SCHEDULE_RECORD IS BEGIN return MGMT_JOB_ENGINE.get_job_schedule_record(p_frequency_code, p_start_time, p_end_time, p_execution_hours, p_execution_minutes, p_interval, p_months, p_days, p_timezone_info, p_timezone_target_index, p_timezone_offset, p_timezone_region, p_start_grace_period); END; FUNCTION get_immediate_schedule_record RETURN MGMT_JOB_SCHEDULE_RECORD IS BEGIN return MGMT_JOB_ENGINE.get_immediate_schedule_record; END; PROCEDURE create_library_job(p_job_name IN VARCHAR2, p_description IN VARCHAR2, p_job_type IN VARCHAR2, p_job_targets IN MGMT_JOB_TARGET_LIST, p_job_params IN MGMT_JOB_PARAM_LIST, p_schedule IN OUT MGMT_JOB_SCHEDULE_RECORD, p_job_id_out OUT RAW, p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_target_type VARCHAR2 DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS l_target_lists MGMT_JOB_TARGET_LIST_ARRAY := MGMT_JOB_TARGET_LIST_ARRAY(); l_execution_ids MGMT_JOB_GUID_ARRAY; BEGIN l_target_lists.extend(1); l_target_lists(1) := p_job_targets; submit_job(p_job_name, p_description, 1, p_job_type, l_target_lists, p_job_params, p_schedule, 0, p_job_id_out, l_execution_ids, null, 0, p_job_creds, p_job_target_type, null, p_job_notify_states); END; PROCEDURE submit_job(p_job_name IN VARCHAR2, p_description IN VARCHAR2, p_job_type IN VARCHAR2, p_job_targets IN MGMT_JOB_TARGET_LIST, p_job_params IN MGMT_JOB_PARAM_LIST, p_schedule IN OUT MGMT_JOB_SCHEDULE_RECORD, p_job_id_out OUT RAW, p_execution_id_out OUT RAW, p_owner IN VARCHAR2 DEFAULT NULL, p_system_job IN INTEGER DEFAULT 0, p_job_creds IN MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_target_type VARCHAR2 DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS l_target_lists MGMT_JOB_TARGET_LIST_ARRAY := MGMT_JOB_TARGET_LIST_ARRAY(); l_execution_ids MGMT_JOB_GUID_ARRAY; BEGIN l_target_lists.extend(1); l_target_lists(1) := p_job_targets; submit_job(p_job_name, p_description, 0, p_job_type, l_target_lists, p_job_params, p_schedule, 0, p_job_id_out, l_execution_ids, p_owner, p_system_job, p_job_creds, p_job_target_type, null, p_job_notify_states); p_execution_id_out := l_execution_ids(1); END; PROCEDURE submit_job_to_queue(p_job_name IN VARCHAR2, p_queue_names IN SMP_EMD_STRING_ARRAY, p_description IN VARCHAR2, p_job_type IN VARCHAR2, p_job_targets IN MGMT_JOB_TARGET_LIST, p_job_params IN MGMT_JOB_PARAM_LIST, p_owner IN VARCHAR2 DEFAULT NULL, p_system_job IN INTEGER DEFAULT 0, p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_target_type VARCHAR2 DEFAULT NULL, p_job_id_out OUT RAW, p_execution_ids_out OUT MGMT_JOB_GUID_ARRAY) IS l_target_lists MGMT_JOB_TARGET_LIST_ARRAY := MGMT_JOB_TARGET_LIST_ARRAY(); l_schedule MGMT_JOB_SCHEDULE_RECORD := null; BEGIN l_target_lists.extend(1); l_target_lists(1) := p_job_targets; submit_job(p_job_name, p_description, 0, p_job_type, l_target_lists, p_job_params, l_schedule, 0, p_job_id_out, p_execution_ids_out, p_owner, p_system_job, p_job_creds, p_job_target_type, p_queue_names); END; PROCEDURE submit_job(p_job_name IN VARCHAR2, p_description IN VARCHAR2, p_is_library IN NUMBER, p_job_type IN VARCHAR2, p_job_targets IN MGMT_JOB_TARGET_LIST_ARRAY, p_job_params IN MGMT_JOB_PARAM_LIST, p_schedule IN OUT MGMT_JOB_SCHEDULE_RECORD, p_execution_timeout NUMBER, p_job_id_out OUT RAW, p_execution_ids_out OUT MGMT_JOB_GUID_ARRAY, p_owner VARCHAR2 DEFAULT NULL, p_system_job IN INTEGER DEFAULT 0, p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_target_type VARCHAR2 DEFAULT NULL, p_queue_names SMP_EMD_STRING_ARRAY DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS cnt INTEGER := 0; l_creator VARCHAR2(256); l_current_user VARCHAR2(256); l_target_list MGMT_JOB_TARGET_LIST; l_schedule_id RAW(16); l_count INTEGER; l_em_user VARCHAR2(256); l_single_target_job MGMT_JOB_TYPE_INFO.single_target%TYPE; l_lock_handle varchar2(256) ; l_job_name_lock_handle VARCHAR2(256) ; l_job_name_lock_value VARCHAR2(256) ; l_job_name_lock_status NUMBER; l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE; l_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE; --Audit l_audit_level NUMBER; BEGIN l_em_user := MGMT_USER.get_current_em_user; -- The current user should be in MGMT_CREATED_USERS SELECT count(*) INTO cnt FROM MGMT_CREATED_USERS WHERE user_name = l_em_user; -- Count should be at least 1, -- if the user presents in the table. IF cnt < 1 THEN raise_application_error(MGMT_GLOBAL.UNREGISTERED_OWNER_ERR, MGMT_GLOBAL.UNREGISTERED_OWNER_ERR_M); END IF; -- HACK for git, we should actually get it from the context IF p_owner IS NULL THEN l_creator := l_em_user; ELSE l_creator := p_owner; END IF; -- If the designated owner of the job is not the "current" EM user -- as identified by the sys context, then the current EM user must -- have super-user privilege. IF l_em_user != l_creator THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'The owner cannot be set to anyone other than the creator'); END IF; l_lock_handle := mgmt_user.get_read_lock(MGMT_GLOBAL.JOB_CREATE_FAILED_ERR) ; IF p_is_library=0 THEN p_execution_ids_out := MGMT_JOB_GUID_ARRAY(); p_execution_ids_out.extend(p_job_targets.count); END IF; -- Get the job type id and the most recent version of the job type -- Also validates the job type MGMT_JOB_ENGINE.get_max_versions(p_job_type, l_major_version, l_job_type_id); BEGIN SELECT single_target into l_single_target_job FROM MGMT_JOB_TYPE_INFO WHERE job_type_id=l_job_type_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_TYPE_ERR, 'The specified job type does not exist'); END; -- Single target jobs can have only one target list IF l_single_target_job=1 AND p_job_targets.COUNT > 1 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Single-target jobs can have only one target list'); END IF; l_job_name_lock_value := l_creator || ':' || p_is_library; l_job_name_lock_handle := MGMT_LOCK_UTIL.get_exclusive_lock(MGMT_LOCK_UTIL.JOB_NAME_LOCK, l_job_name_lock_value, 300, MGMT_GLOBAL.JOB_CREATE_FAILED_ERR, 'error in submitting job ' || p_job_name) ; -- Validate that there is no other job with the same name SELECT COUNT(1) INTO cnt FROM MGMT_JOB WHERE job_name=upper(p_job_name) AND job_owner=upper(l_creator) AND is_library=p_is_library AND is_corrective_action=0 AND nested=0; IF cnt > 0 THEN raise_application_error(mgmt_global.JOB_EXISTS_ERR, 'A job with the specified name already exists'); END IF; -- Validate the schedule IF p_is_library=0 THEN IF p_queue_names IS NULL AND p_schedule IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Schedule cannot be null for non-library jobs'); END IF; IF p_queue_names IS NOT NULL AND p_queue_names.COUNT > 0 AND p_schedule IS NOT NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Schedule must be null when submitting to a queue'); END IF; IF p_schedule IS NOT NULL THEN MGMT_JOB_ENGINE.validate_schedule(p_schedule); END IF; END IF; l_count := p_job_targets.count; -- Insert the schema entries for the CA MGMT_JOB_ENGINE.insert_job(p_job_name, l_creator, p_is_library, p_job_target_type, p_description, p_job_type, p_job_params, p_schedule, p_job_creds, p_job_notify_states, p_execution_timeout, l_count, p_system_job, 0, null, null, null, p_job_id_out); -- Insert the target lists. This will also schedule executions -- as necessary p_execution_ids_out := MGMT_JOB_ENGINE.upsert_job_target_lists(p_job_id_out, p_is_library, p_job_targets, p_schedule, p_job_target_type, false, p_queue_names); --Audit submit_job mgmt_audit_admin.audit_level(l_audit_level); IF (l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_ALL OR l_audit_level = mgmt_audit_admin.AUDIT_LEVEL_SELECTED) THEN mgmt_audit_log.audit_log(mgmt_audit_log.SUBMIT_JOB, upper(p_job_name), upper(p_job_type), upper(l_creator)); END IF; END; -- Delete job methods PROCEDURE delete_job_execution(p_execution_id RAW) IS BEGIN MGMT_JOB_ENGINE.delete_job_execution(p_execution_id); END; PROCEDURE delete_job_run(p_job_name VARCHAR2, p_job_owner VARCHAR2, p_scheduled_time DATE) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner); MGMT_JOB_ENGINE.delete_job_run(l_job_id, p_scheduled_time); END; PROCEDURE delete_job(p_job_name IN VARCHAR2, p_job_owner IN VARCHAR2, p_commit NUMBER DEFAULT 0, p_is_library NUMBER DEFAULT 0) IS BEGIN MGMT_JOB_ENGINE.delete_job(p_job_name=>p_job_name, p_job_owner=>p_job_owner, p_commit=>p_commit, p_is_library=>p_is_library); END; PROCEDURE delete_job(p_job_name IN VARCHAR2, p_commit NUMBER DEFAULT 0, p_is_library NUMBER DEFAULT 0) IS BEGIN delete_job(p_job_name, MGMT_USER.get_current_em_user, p_commit, p_is_library); END; PROCEDURE delete_job(p_job_id IN RAW, p_commit NUMBER DEFAULT 0) IS BEGIN MGMT_JOB_ENGINE.delete_job(p_job_id=>p_job_id, p_commit=>p_commit); END; PROCEDURE delete_job_executions(p_execution_ids IN MGMT_JOB_GUID_ARRAY, p_commit NUMBER DEFAULT 0) IS BEGIN MGMT_JOB_ENGINE.delete_job_executions(p_execution_ids, p_commit); END; PROCEDURE delete_all_executions(p_job_name VARCHAR2, p_job_owner VARCHAR2 DEFAULT NULL, p_commit NUMBER DEFAULT 0, p_is_library NUMBER DEFAULT 0) IS BEGIN MGMT_JOB_ENGINE.delete_all_executions(p_job_name, p_job_owner, p_commit, p_is_library); END; -- Stop job methods PROCEDURE stop_execution(p_execution_id RAW) IS BEGIN MGMT_JOB_ENGINE.stop_execution(p_execution_id); END; -- Stop the specified set of executions. PROCEDURE stop_executions(p_execution_ids MGMT_JOB_GUID_ARRAY) IS BEGIN FOR i IN 1..p_execution_ids.COUNT LOOP stop_execution(p_execution_ids(i)); END LOOP; END; -- Stop all active executions in the specified job PROCEDURE stop_all_executions(p_job_name VARCHAR2, p_job_owner VARCHAR2 DEFAULT NULL) IS BEGIN MGMT_JOB_ENGINE.stop_all_executions(p_job_name, p_job_owner); END; PROCEDURE stop_job_run(p_job_name VARCHAR2, p_job_owner VARCHAR2, p_scheduled_time DATE) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner); MGMT_JOB_ENGINE.stop_job_run(l_job_id, p_scheduled_time); END; -- Edit the specified job, using the specified description, -- parameters, targets and schedule PROCEDURE edit_job(p_job_name VARCHAR2, p_owner VARCHAR2, p_is_library NUMBER, p_description VARCHAR2, p_params MGMT_JOB_PARAM_LIST, p_targets MGMT_JOB_TARGET_LIST, p_schedule MGMT_JOB_SCHEDULE_RECORD, p_overridden_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_owner, p_is_library); MGMT_JOB_ENGINE.edit_job(l_job_id, p_description, p_params, p_targets, p_schedule, p_overridden_creds, p_job_notify_states); END; -- Edit the current job; multiple target lists PROCEDURE edit_job(p_job_name VARCHAR2, p_owner VARCHAR2, p_is_library NUMBER, p_description VARCHAR2, p_params MGMT_JOB_PARAM_LIST, p_targets MGMT_JOB_TARGET_LIST_ARRAY, p_schedule MGMT_JOB_SCHEDULE_RECORD, p_overridden_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_owner, p_is_library); MGMT_JOB_ENGINE.edit_job(l_job_id, p_description, p_params, p_targets, p_schedule, p_overridden_creds, p_job_notify_states); END; PROCEDURE register_command(p_command_name VARCHAR2, p_class_name VARCHAR2, p_command_type NUMBER, p_is_trustable NUMBER DEFAULT NON_TRUSTABLE_COMMAND) IS BEGIN MGMT_JOB_ENGINE.check_priv('SUPER_USER'); DELETE FROM MGMT_JOB_COMMAND WHERE command_name=p_command_name; INSERT INTO MGMT_JOB_COMMAND(command_name, command_class, command_type, is_trustable) VALUES(p_command_name, p_class_name, p_command_type,p_is_trustable); END; -- Register a command block procedure i.e. a procedure -- that can be called from command block xml. PROCEDURE register_command_block_proc(p_proc_name VARCHAR2, p_param_types MGMT_SHORT_STRING_ARRAY) IS l_err_msg VARCHAR2(1024); l_param_types MGMT_SHORT_STRING_ARRAY := MGMT_SHORT_STRING_ARRAY(); BEGIN IF (EM_CHECK.is_valid_signature(p_proc_name,p_param_types,l_err_msg, TRUE)) THEN IF ((p_param_types IS NOT NULL) AND (p_param_types.COUNT > 0)) THEN l_param_types.extend(p_param_types.COUNT); FOR idx IN 1..p_param_types.COUNT LOOP l_param_types(idx) := UPPER(p_param_types(idx)); END LOOP; END IF; INSERT INTO MGMT_JOB_COMMAND_BLOCK_PROCS(proc_name, param_types) VALUES (UPPER(p_proc_name), l_param_types); ELSE raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, 'Procedure ' || p_proc_name || ' is either not defined or the signature did not match. The error was ' || l_err_msg); END IF; END; -- Deregister a command block procedure PROCEDURE deregister_command_block_proc(p_proc_name VARCHAR2) IS BEGIN DELETE FROM MGMT_JOB_COMMAND_BLOCK_PROCS WHERE proc_name = UPPER(p_proc_name); END; PROCEDURE add_job_parameters(p_job_id RAW, p_execution_id RAW, p_param_list MGMT_JOB_PARAM_LIST) IS BEGIN -- Note: no one, other than the owner, can edit the parameters MGMT_JOB_ENGINE.check_modify_job(p_job_id, false); MGMT_JOB_ENGINE.update_job_parameters(p_job_id, p_execution_id, p_param_list, false, false, true); END; PROCEDURE insert_step_error_message(p_step_id NUMBER, p_error_message VARCHAR2) IS BEGIN MGMT_JOB_ENGINE.check_modify_step(p_step_id); MGMT_JOB_ENGINE.write_step_error_message(p_step_id, p_error_message); END; PROCEDURE register_purge_policy(p_policy_name VARCHAR2, p_timeframe NUMBER, p_purge_criteria MGMT_JOB_PURGE_CRITERION_LIST) IS BEGIN MGMT_JOB_ENGINE.register_purge_policy(p_policy_name, p_timeframe, p_purge_criteria); END; -- Drop a purge policy that has the specified name PROCEDURE drop_purge_policy(p_policy_name VARCHAR2) IS BEGIN MGMT_JOB_ENGINE.drop_purge_policy(p_policy_name); END; PROCEDURE suspend_job_execution(p_execution_id IN RAW) IS l_job_status NUMBER; BEGIN MGMT_JOB_ENGINE.check_modify_execution(p_execution_id); SELECT status INTO l_job_status FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id=p_execution_id; -- Cannot suspend a job that has already finished execution.... IF l_job_status = SUSPENDED_LOCK_STATUS OR l_job_status = SUSPENDED_EVENT_STATUS THEN raise_application_error(MGMT_GLOBAL.SUSPEND_FINISHED_JOB_ERR, 'Cannot suspend a job that is suspended on a lock or event'); END IF; -- Do nothing if the job is already suspended IF l_job_status = SUSPENDED_STATUS THEN raise_application_error(MGMT_GLOBAL.EXEC_SUSPENDED_ERR, 'Cannot suspend execution: execution currently suspended'); END IF; -- Change the status of all scheduled steps to SUSPENDED. Note that -- we leave currently executing steps as they are; we do want to -- update their status when the steps finish MGMT_JOB_ENGINE.suspend_job_execution(p_execution_id); EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.SUSPEND_FINISHED_JOB_ERR, 'The execution was not found or has already finished'); END; PROCEDURE suspend_job_execution(p_step_id IN NUMBER, p_suspend_step IN NUMBER) IS l_execution_id RAW(16); BEGIN MGMT_JOB_ENGINE.check_modify_step(p_step_id); MGMT_JOB_ENGINE.suspend_job_execution(p_step_id, p_suspend_step); END; -- Suspend the specified job, and all its executions PROCEDURE suspend_job(p_job_name IN VARCHAR2, p_job_owner IN VARCHAR2) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner, 0); MGMT_JOB_ENGINE.check_modify_job(l_job_id); MGMT_JOB_ENGINE.suspend_job(l_job_id); END; -- Resume the specified job PROCEDURE resume_job(p_job_name IN VARCHAR2, p_job_owner IN VARCHAR2) IS l_job_id MGMT_JOB.job_id%TYPE; BEGIN l_job_id := MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner, 0); MGMT_JOB_ENGINE.check_modify_job(l_job_id); MGMT_JOB_ENGINE.resume_job(l_job_id); END; PROCEDURE resume_job_execution(p_execution_id IN RAW) IS BEGIN MGMT_JOB_ENGINE.check_modify_execution(p_execution_id); mgmt_job_engine.resume_job_execution(p_execution_id); END; -- Resubmit a job FUNCTION resubmit_job(p_job_id RAW) RETURN MGMT_JOB_GUID_ARRAY IS l_execution_ids MGMT_JOB_GUID_ARRAY := MGMT_JOB_GUID_ARRAY(); l_count INTEGER; l_nested INTEGER; l_max_target_list_index INTEGER; l_schedule MGMT_JOB_SCHEDULE_RECORD; BEGIN MGMT_JOB_ENGINE.check_modify_job(p_job_id, false); -- Ensure that the job is not nested SELECT nested, max_target_list_index INTO l_nested, l_max_target_list_index FROM MGMT_JOB WHERE job_id=p_job_id; IF l_nested=1 THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAM_ERR, 'Cannot resubmit a nested job'); END IF; -- Ensure that the job is not currently running SELECT COUNT(execution_id) INTO l_count FROM MGMT_JOB_EXEC_SUMMARY WHERE job_id=p_job_id; IF l_count > 0 THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'Cannot resubmit a job with active executions'); END IF; -- get immediate frequence code l_schedule := get_immediate_schedule_record; l_execution_ids.extend(l_max_target_list_index); FOR i in 1..l_max_target_list_index LOOP -- Schedule the execution l_execution_ids(i) := MGMT_JOB_ENGINE.schedule_execution(p_job_id, null, null, null, l_schedule, i, null); END LOOP; RETURN l_execution_ids; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'The specified job does not exist'); END; FUNCTION resubmit_job(p_job_name VARCHAR2, p_job_owner VARCHAR2 DEFAULT NULL) RETURN MGMT_JOB_GUID_ARRAY IS BEGIN RETURN resubmit_job(p_job_id=>MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner)); END; FUNCTION restart_job(p_job_id IN RAW, p_target_list_index INTEGER, p_params MGMT_JOB_PARAM_LIST DEFAULT NULL) RETURN RAW IS l_num_restarted_execs INTEGER := 0; l_source_exec_id RAW(16); l_execution_status INTEGER; l_new_exec_id RAW(16); l_queue_id RAW(16); l_queue_name MGMT_JOB_QUEUES.queue_name%TYPE; BEGIN IF p_params IS NULL THEN -- Params not being modified, superusers are allowed MGMT_JOB_ENGINE.check_modify_job(p_job_id, true); ELSE -- Params are being modified (this is an edit); only the owner or users with FULL are allowed MGMT_JOB_ENGINE.check_modify_job(p_job_id, false); END IF; IF NOT MGMT_JOB_ENGINE.is_restartable(p_job_id) THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_ERR, 'Cannot restart job: job or job type marked non-restartable'); END IF; -- Attempt to find the source execution id SELECT execution_id, queue_id, status INTO l_source_exec_id, l_queue_id, l_execution_status FROM (SELECT execution_id, queue_id, status FROM MGMT_JOB_EXEC_SUMMARY WHERE target_list_index=p_target_list_index AND job_id=p_job_id ORDER BY start_time DESC) WHERE rownum=1; IF l_execution_status != ABORTED_STATUS AND l_execution_status != FAILED_STATUS THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'The specified job has no failed or stopped executions'); END IF; -- check if this execution has already been restarted before SELECT COUNT(1) INTO l_num_restarted_execs FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id <> source_execution_id AND source_execution_id=l_source_exec_id; IF l_num_restarted_execs > 0 THEN raise_application_error(MGMT_GLOBAL.RESTART_RESTART_FAILED_JOB_ERR, 'The specified job execution has already been restarted'); END IF; IF l_queue_id IS NOT NULL THEN BEGIN SELECT queue_name INTO l_queue_name FROM MGMT_JOB_QUEUES WHERE queue_id=l_queue_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_queue_name := null; END; END IF; l_new_exec_id := MGMT_JOB_ENGINE.schedule_execution(p_job_id, l_queue_name, null, l_source_exec_id, null, p_target_list_index, null, p_restart_params => p_params); -- Call all job retry callbacks MGMT_JOB_ENGINE.process_retry_callbacks(p_job_id,l_new_exec_id); UPDATE MGMT_JOB SET job_status=MGMT_JOB_ENGINE.JOB_STATUS_ACTIVE WHERE job_id=p_job_id; --update RETURN l_new_exec_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'The specified job does not exist or has no failed or stopped executions'); END; FUNCTION restart_job(p_job_id IN RAW, p_params MGMT_JOB_PARAM_LIST DEFAULT NULL) RETURN RAW IS BEGIN RETURN restart_job(p_job_id, 1, p_params); END; FUNCTION restart_job(p_job_name IN VARCHAR2, p_job_owner IN VARCHAR2, p_target_list_index IN INTEGER DEFAULT 1, p_params MGMT_JOB_PARAM_LIST DEFAULT NULL) RETURN RAW IS BEGIN RETURN restart_job(MGMT_JOB_ENGINE.get_job_id(p_job_name, p_job_owner), p_target_list_index,p_params); END; FUNCTION restart_job_execution(p_exec_id IN RAW, p_params MGMT_JOB_PARAM_LIST DEFAULT NULL) RETURN RAW IS l_num_restarted_execs INTEGER := 0; l_num_deleted_targets INTEGER; l_execution_status INTEGER; l_job_id RAW(16); l_target_list_index INTEGER; l_new_exec_id RAW(16); l_queue_id RAW(16); l_queue_name MGMT_JOB_QUEUES.queue_name%TYPE; BEGIN -- get execution info SELECT status, job_id, queue_id, target_list_index INTO l_execution_status, l_job_id, l_queue_id, l_target_list_index FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id=p_exec_id AND status != MGMT_JOB_ENGINE.DELETE_PENDING_STATUS; IF p_params IS NULL THEN -- Params not being modified, superusers are allowed MGMT_JOB_ENGINE.check_modify_job(l_job_id, true); ELSE -- Params are being modified (this is an edit); only the owner or users with FULL are allowed MGMT_JOB_ENGINE.check_modify_job(l_job_id, false); END IF; SELECT COUNT(*) INTO l_num_deleted_targets FROM MGMT_JOB_EXEC_SUMMARY e, MGMT_JOB_EXT_TARGETS ex WHERE e.execution_id = ex.execution_id AND e.execution_id=p_exec_id AND ex.target_guid = MGMT_JOB_ENGINE.NO_GUID; IF l_num_deleted_targets > 0 THEN raise_application_error(MGMT_GLOBAL.RESTART_DELETED_TARGETS_ERR, 'Cannot restart execution: one or more targets have been deleted'); END IF; IF NOT MGMT_JOB_ENGINE.is_restartable(l_job_id) THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_ERR, 'Cannot restart job: job or job type marked non-restartable'); END IF; IF l_execution_status != ABORTED_STATUS AND l_execution_status != FAILED_STATUS THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'The specified job execution is not failed or stopped'); END IF; -- check if this execution has already been restarted before SELECT COUNT(1) INTO l_num_restarted_execs FROM MGMT_JOB_EXEC_SUMMARY WHERE execution_id <> source_execution_id AND source_execution_id=p_exec_id; IF l_num_restarted_execs > 0 THEN raise_application_error(MGMT_GLOBAL.RESTART_RESTART_FAILED_JOB_ERR, 'The specified job execution has already been restarted'); END IF; IF l_queue_id IS NOT NULL THEN BEGIN SELECT queue_name INTO l_queue_name FROM MGMT_JOB_QUEUES WHERE queue_id=l_queue_id; EXCEPTION WHEN NO_DATA_FOUND THEN l_queue_name := null; END; END IF; l_new_exec_id := MGMT_JOB_ENGINE.schedule_execution(l_job_id, l_queue_name, null, p_exec_id, null, l_target_list_index, null, null, p_restart_params => p_params); -- Call all job retry callbacks MGMT_JOB_ENGINE.process_retry_callbacks(l_job_id,l_new_exec_id); UPDATE MGMT_JOB SET job_status=MGMT_JOB_ENGINE.JOB_STATUS_ACTIVE WHERE job_id=l_job_id; RETURN l_new_exec_id; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.RESTART_INVALID_JOB_ERR, 'The specified job execution does not exist or is not failed or stopped'); END; -- Register a new event PROCEDURE add_event(p_event_name VARCHAR2, p_event_callback VARCHAR2) IS BEGIN MGMT_JOB_ENGINE.add_event(p_event_name, p_event_callback); END; -- Raise an already registered event with the specified params PROCEDURE raise_event(p_event_name VARCHAR2, p_event_params SMP_EMD_NVPAIR_ARRAY) IS BEGIN MGMT_JOB_ENGINE.raise_event(p_event_name, p_event_params); END; -- Suspend the job execution (excluding the current step) on -- the specified event, having the specified parameters PROCEDURE suspend_job_execution_on_event(p_execution_id RAW, p_event_name VARCHAR2, p_event_params SMP_EMD_NVPAIR_ARRAY, p_suspend_timeout NUMBER DEFAULT 0) IS BEGIN MGMT_JOB_ENGINE.suspend_job_execution_on_event(p_execution_id, p_event_name, p_event_params, p_suspend_timeout); END; -- "Associate" an event with the specified execution, without -- actually suspending it. This generally indicates an intention -- to eventually suspend the execution based on that event. PROCEDURE associate_event_with_execution(p_execution_id RAW, p_event_name VARCHAR2, p_event_params SMP_EMD_NVPAIR_ARRAY) IS BEGIN MGMT_JOB_ENGINE.associate_event_with_execution(p_execution_id, p_event_name, p_event_params); END; -- -- Return the large paremeter having the specified name -- corresponding to the specified job and execution. -- This will create and return an empty clob if the parameter -- has not yet been initialized, so the caller must commit after -- this call -- FUNCTION get_large_param(p_job_id RAW, p_execution_id RAW, p_parameter_name VARCHAR2) RETURN CLOB IS BEGIN RETURN MGMT_JOB_ENGINE.get_large_param(p_job_id, p_execution_id, p_parameter_name); END; -- Submit a job from the library. Assumes one target list PROCEDURE submit_job_from_library(p_lib_job_name IN VARCHAR2, p_lib_job_owner IN VARCHAR2, p_new_job_name IN VARCHAR2, p_job_description IN VARCHAR2, p_job_targets IN MGMT_JOB_TARGET_LIST, p_job_params IN MGMT_JOB_PARAM_LIST, p_replace_targets NUMBER, p_replace_params NUMBER, p_schedule IN MGMT_JOB_SCHEDULE_RECORD, p_job_id_out OUT RAW, p_execution_id_out OUT RAW, p_system_job NUMBER DEFAULT 0, p_job_creds MGMT_JOB_CRED_ARRAY DEFAULT NULL, p_job_notify_states SMP_EMD_INTEGER_ARRAY DEFAULT NULL) IS l_lib_job_id MGMT_JOB.job_id%TYPE; l_job_name MGMT_JOB.job_name%TYPE; l_job_description MGMT_JOB.job_description%TYPE; l_job_type MGMT_JOB.job_type%TYPE; l_job_targets MGMT_JOB_TARGET_LIST; l_job_target_lists MGMT_JOB_TARGET_LIST_ARRAY; l_job_params MGMT_JOB_PARAM_LIST; l_schedule MGMT_JOB_SCHEDULE_RECORD; l_execution_ids MGMT_JOB_GUID_ARRAY; l_large_values MGMT_JOB_GUID_ARRAY; l_job_target_type MGMT_JOB.target_type%TYPE; l_schedule_id MGMT_JOB.schedule_id%TYPE; l_target_list_index MGMT_JOB_EXEC_SUMMARY.target_list_index%TYPE; l_job_creds MGMT_JOB_CRED_ARRAY; l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user; l_notify_states SMP_EMD_INTEGER_ARRAY := null; l_job_major_version MGMT_JOB_TYPE_INFO.major_version%TYPE; l_job_type_id MGMT_JOB_TYPE_INFO.job_type_id%TYPE; l_job_type_category MGMT_JOB_TYPE_INFO.job_type_category%TYPE; BEGIN BEGIN SELECT job_id, job_type, job_description, schedule_id, target_type INTO l_lib_job_id, l_job_type, l_job_description, l_schedule_id, l_job_target_type FROM MGMT_JOB WHERE job_name=p_lib_job_name AND job_owner=p_lib_job_owner AND is_library=1; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'The library job ' || p_lib_job_name || ' Cannot be found'); END; IF p_new_job_name IS NOT NULL THEN l_job_name := p_new_job_name; ELSE l_job_name := p_lib_job_name; END IF; IF p_job_description IS NOT NULL THEN l_job_description := p_job_description; END IF; IF p_replace_params=1 THEN l_job_params := p_job_params; ELSE -- Select the parameters from the library job SELECT MGMT_JOB_PARAM_RECORD(parameter_name, parameter_type, scalar_value, vector_value), large_value BULK COLLECT INTO l_job_params, l_large_values FROM MGMT_JOB_PARAMETER WHERE job_id=l_lib_job_id AND execution_id=MGMT_JOB_ENGINE.NO_EXECUTION AND encrypted=0; END IF; IF p_replace_targets=1 THEN l_job_targets := p_job_targets; ELSE IF MGMT_JOB_ENGINE.is_single_target_job(l_lib_job_id) THEN l_target_list_index := 0; ELSE l_target_list_index := 1; END IF; -- Select the targets from the library job SELECT MGMT_JOB_TARGET_RECORD(target_name, target_type) BULK COLLECT INTO l_job_targets FROM MGMT_JOB_TARGET jt, MGMT_TARGETS t WHERE t.target_guid=jt.target_guid AND job_id=l_lib_job_id AND execution_id=MGMT_JOB_ENGINE.NO_EXECUTION AND target_list_index=l_target_list_index ORDER BY target_index; END IF; IF p_schedule IS NOT NULL THEN l_schedule := p_schedule; ELSE IF l_schedule_id IS NULL THEN raise_application_error(MGMT_GLOBAL.INVALID_PARAM_ERR, 'No schedule provided for job'); END IF; -- Pick up the schedule from the job SELECT MGMT_JOB_SCHEDULE_RECORD(frequency_code, start_time, end_time, start_grace_period, execution_hours, execution_minutes, interval, months, days, timezone_info, timezone_target_index, timezone_offset, timezone_region) INTO l_schedule FROM MGMT_JOB_SCHEDULE WHERE schedule_id=l_schedule_id; END IF; IF p_job_creds IS NULL THEN -- Get the overridden credentials for the job, -- IF the user is also the owner IF l_current_user=p_lib_job_owner THEN l_job_creds := MGMT_CREDENTIAL.get_job_credentials(l_lib_job_id); ELSE l_job_creds := null; END IF; ELSE l_job_creds := p_job_creds; END IF; IF p_job_notify_states IS NULL THEN SELECT notify_state BULK COLLECT INTO l_notify_states FROM MGMT_JOB_NOTIFY_STATES WHERE job_id=l_lib_job_id; ELSE l_notify_states := p_job_notify_states; END IF; MGMT_JOB_ENGINE.get_max_versions(l_job_type, l_job_major_version, l_job_type_id); SELECT job_type_category INTO l_job_type_category FROM MGMT_JOB_TYPE_INFO WHERE job_type_id = l_job_type_id; IF l_job_type_category = MGMT_JOB_ENGINE.JOBTYPE_CATEGORY_HIDDEN THEN l_job_type := MGMT_JOB_ENGINE.clone_multitask_job_type(l_lib_job_id); END IF; submit_job(l_job_name, l_job_description, l_job_type, l_job_targets, l_job_params, l_schedule, p_job_id_out, p_execution_id_out, null, p_system_job, l_job_creds, l_job_target_type, l_notify_states); -- Now write out the large parameters, if any. We will need to -- make copies of the large parameters since we are submitting -- a new job MGMT_JOB_ENGINE.copy_large_params(p_job_id_out, l_lib_job_id); END; -- -- Given a job status, return the appropriate status bucket value -- --private worker FUNCTION get_status_bucket_impl(p_status IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN if p_status is null then raise_application_error(-20001,'INVALID PARAMETER'); end if; case p_status -- when SCHEDULED_STATUS then RETURN STATUS_BUCKET_SCHEDULED; when QUEUED_STATUS then RETURN STATUS_BUCKET_SCHEDULED; when WAITING_STATUS then RETURN STATUS_BUCKET_SCHEDULED; -- when EXECUTING_STATUS then RETURN STATUS_BUCKET_RUNNING; when SUSPEND_PENDING_STATUS then RETURN STATUS_BUCKET_RUNNING; when STOP_PENDING_STATUS then RETURN STATUS_BUCKET_RUNNING; -- when SUSPENDED_STATUS then RETURN STATUS_BUCKET_SUSPENDED; when AGENTDOWN_STATUS then RETURN STATUS_BUCKET_SUSPENDED; when SUSPENDED_BLACKOUT_STATUS then RETURN STATUS_BUCKET_SUSPENDED; when SUSPENDED_LOCK_STATUS then RETURN STATUS_BUCKET_SUSPENDED; when SUSPENDED_EVENT_STATUS then RETURN STATUS_BUCKET_SUSPENDED; when SUSPENDED_CREDS_STATUS then RETURN STATUS_BUCKET_SUSPENDED; -- when COMPLETED_STATUS then RETURN STATUS_BUCKET_OK; -- when ABORTED_STATUS then RETURN STATUS_BUCKET_PROBLEM; when FAILED_STATUS then RETURN STATUS_BUCKET_PROBLEM; when STOPPED_STATUS then RETURN STATUS_BUCKET_PROBLEM; when INACTIVE_STATUS then RETURN STATUS_BUCKET_PROBLEM; when FAILED_RETRIED_STATUS then RETURN STATUS_BUCKET_PROBLEM; when SKIPPED_STATUS then RETURN STATUS_BUCKET_PROBLEM; when DELETE_PENDING_STATUS then RETURN STATUS_BUCKET_PROBLEM; when REASSIGNED_STATUS then RETURN STATUS_BUCKET_PROBLEM; -- else raise_application_error(-20001,'INVALID PARAMETER'); end case; END get_status_bucket_impl; FUNCTION get_status_bucket(p_status IN NUMBER) RETURN NUMBER DETERMINISTIC IS BEGIN return get_status_bucket_impl(p_status); END get_status_bucket; -- PURPOSE -- -- To return a list of users and roles assigned privileges to a particular job. -- -- PARAMETERS -- job_id_in - the job_id -- owner_out - the name of the job owner -- grantees_out - a cursor - user/role name, type (ROLE,ADMIN,SUPER) and privilege -- PROCEDURE GET_JOB_GRANTS (job_id_in IN RAW, owner_out OUT VARCHAR2, grantees_out OUT GRANT_CURSOR) IS BEGIN IF job_id_in = MGMT_JOB_ENGINE.NO_GUID THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'Null job ID'); END IF; BEGIN SELECT job_owner INTO owner_out FROM mgmt_job WHERE job_id = job_id_in; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, job_id_in); END; OPEN grantees_out FOR SELECT a.grantee, 'FULL_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'FULL_JOB' AND a.guid = job_id_in UNION SELECT a.grantee, 'VIEW_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'VIEW_JOB' AND a.guid = job_id_in AND a.grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE priv_name = 'FULL_JOB' AND guid = job_id_in) UNION ALL SELECT grantee, 'VIEW_JOB', 'SUPER' FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in); END GET_JOB_GRANTS; -- PURPOSE -- -- To return a list of users and roles assigned privileges to a particular job for a set of users/roles. -- -- PARAMETERS -- job_id_in - the job_id -- names_in - an array of names for the cursor -- owner_out - the name of the job owner if in the set, else '' -- grantees_out - a cursor - user/role name, type (ROLE,ADMIN,SUPER) and privilege -- PROCEDURE GET_JOB_GRANTS_FOR_SET (job_id_in IN RAW, names_in IN SMP_EMD_STRING_ARRAY, owner_out OUT VARCHAR2, grantees_out OUT GRANT_CURSOR) IS l_name VARCHAR2(256); BEGIN IF job_id_in = MGMT_JOB_ENGINE.NO_GUID THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, 'Null job ID'); END IF; BEGIN SELECT job_owner INTO owner_out FROM mgmt_job WHERE job_id = job_id_in; EXCEPTION WHEN OTHERS THEN raise_application_error(MGMT_GLOBAL.INVALID_JOB_ERR, job_id_in); END; IF names_in IS NULL OR names_in.COUNT < 2 THEN IF names_in IS NULL THEN l_name := '%'; ELSE l_name := names_in(1); END IF; OPEN grantees_out FOR SELECT a.grantee, 'FULL_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'FULL_JOB' AND a.guid = job_id_in AND a.grantee LIKE l_name UNION SELECT a.grantee, 'VIEW_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'VIEW_JOB' AND a.guid = job_id_in AND a.grantee LIKE l_name AND a.grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE priv_name = 'FULL_JOB' AND guid = job_id_in) UNION SELECT grantee, 'VIEW_JOB', 'SUPER' FROM mgmt_priv_grants WHERE grantee LIKE l_name AND priv_name='SUPER_USER' AND grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in) UNION SELECT user_name, 'NONE', 'ADMIN' FROM mgmt_created_users WHERE user_name LIKE l_name AND user_name NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in OR priv_name='SUPER_USER') UNION SELECT role_name, 'NONE', 'ROLE' FROM mgmt_roles WHERE role_name LIKE l_name AND role_name NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in); ELSE OPEN grantees_out FOR SELECT a.grantee, 'FULL_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'FULL_JOB' AND a.guid = job_id_in AND a.grantee IN (SELECT * FROM TABLE(CAST(names_in AS SMP_EMD_STRING_ARRAY))) UNION SELECT a.grantee, 'VIEW_JOB', DECODE(a.grantee_is_role, 1, 'ROLE', 0, (DECODE((SELECT count(grantee) FROM mgmt_priv_grants WHERE priv_name='SUPER_USER' AND grantee = a.grantee),0,'ADMIN','SUPER'))) type FROM mgmt_priv_grants a WHERE a.priv_name = 'VIEW_JOB' AND a.guid = job_id_in AND a.grantee IN (SELECT * FROM TABLE(CAST(names_in AS SMP_EMD_STRING_ARRAY))) AND a.grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE priv_name = 'FULL_JOB' AND guid = job_id_in) UNION SELECT grantee, 'VIEW_JOB', 'SUPER' FROM mgmt_priv_grants WHERE grantee IN (SELECT * FROM TABLE(CAST(names_in AS SMP_EMD_STRING_ARRAY))) AND priv_name='SUPER_USER' AND grantee NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in) UNION SELECT user_name, 'NONE', 'ADMIN' FROM mgmt_created_users WHERE user_name IN (SELECT * FROM TABLE(CAST(names_in AS SMP_EMD_STRING_ARRAY))) AND user_name NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in OR priv_name='SUPER_USER') UNION SELECT role_name, 'NONE', 'ROLE' FROM mgmt_roles WHERE role_name IN (SELECT * FROM TABLE(CAST(names_in AS SMP_EMD_STRING_ARRAY))) AND role_name NOT IN (SELECT grantee FROM mgmt_priv_grants WHERE guid = job_id_in); END IF; END GET_JOB_GRANTS_FOR_SET; END MGMT_JOBS; / show errors;