REM drv: Rem Rem $Header: empp/source/oracle/sysman/emdrep/sql/pp/latest/paf/paf_pkgbodys.sql st_empp_lsatyapr_bug-9854096/1 2010/07/15 02:20:02 lsatyapr Exp $ Rem Rem paf_pkgdefs.sql Rem Rem Copyright (c) 2005, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem paf_pkgdefs.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem lsatyapr 07/14/10 - Bug9854096 Support only positive waits Rem rahgupta 10/27/08 - Bug 7479325 Rem rahgupta 09/28/08 - XbranchMerge Rem mparamas_blr_backport_5990507_10.2.0.4 from Rem st_empp_10.2 Rem mparamas 06/06/08 - Fix for Bug:6856546 'job status update properly Rem if the oms is down' Rem rahgupta 05/06/08 - Rem zsamar 11/03/06 - Rem ktlaw 10/23/06 - bug 5611551 Rem ktamtoro 10/05/06 - Backport ktamtoro_bug-5381533 from main Rem rahgupta 10/04/06 - threshhold to be 45 Rem zsamar 08/25/06 - Backport zsamar_jarfile from main Rem rlemos 10/03/06 - Add procedure to register PAF notification rules Rem zsamar 08/24/06 - make daemon a system job Rem rahgupta 08/21/06 - check and then reg Rem rahgupta 08/18/06 - Create notification package Rem zsamar 07/27/06 - Backport zsamar_bug-5399430 from main Rem ktlaw 07/10/06 - Backport ktlaw_bug-5118749 from main Rem zsamar 07/19/06 - bug 5399430 changing EM user to sysman Rem when checking status of prov daemon Rem fantic 04/26/06 - adding procedure update_runtime_data Rem ktlaw 03/31/06 - Rem ktlaw 03/31/06 - Rem rahgupta 03/03/06 - Bug 5068222: Daemon apis should take job_owner Rem ktamtoro 03/02/06 - Add procedure to register group Rem ktlaw 02/02/06 - XbranchMerge ktlaw_bug-4997741 from main Rem ktamtoro 01/09/06 - XbranchMerge Rem ktamtoro_ui_bugs_4925024_4925037_4927434 from Rem main Rem ktamtoro 01/09/06 - XbranchMerge Rem ktamtoro_ui_bugs_4925024_4925037_4927434 from Rem main Rem ktlaw 01/26/06 - Rem ktamtoro 01/05/06 - Add display name to job parameter registration Rem fantic 11/17/05 - add proc/functions start_daemon, stop_daemon, Rem daemon_status Rem ktamtoro 09/09/05 - Add secret paramater to REGISTER_JOBTYPE_PARAM Rem rahgupta 07/26/05 - Implement procedures Rem ktlaw 07/18/05 - ktlaw_paf_update_0712 Rem ktlaw 07/13/05 - Rem ktlaw 06/14/05 - ktlaw_paf_update_0614 Rem ktlaw 06/14/05 - Created Rem CREATE OR REPLACE PACKAGE BODY MGMT_PAF_UTL AS -- API to register a jobtype -- returns a GUID for the jobtype to be used in further -- invocation of register_jobtype_param for registering -- the jobtype parameters -- @param p_jobtype the jobtype name -- @param p_description the jobtype description -- @param p_target_list_dependent if job requires a target list to run or not 'Y' or 'N' -- defaults 'Y' -- @param OUT x_guid the GUID of the jobtype. PROCEDURE REGISTER_JOBTYPE ( p_jobtype IN VARCHAR2, p_description IN VARCHAR2 DEFAULT NULL, p_target_list_dependent IN CHAR DEFAULT 'Y', x_guid OUT VARCHAR2 ) IS l_date DATE ; BEGIN l_date := SYSDATE ; x_guid := SYS_GUID (); INSERT INTO MGMT_PAF_JOBTYPES ( JOBTYPE_GUID, JOBTYPE, DESCRIPTION, LAST_UPDATED, TARGET_LIST_DEPENDENT ) VALUES ( x_guid, p_jobtype, p_description, l_date, p_target_list_dependent ); END REGISTER_JOBTYPE; -- API to register a component subtype to jobtype mapping -- returns a GUID for the jobtype to be used in further -- invocation of register_jobtype_param for registering -- the jobtype parameters -- @param p_jobtype the jobtype name -- @param p_component_subtype the software library component subtype -- @param p_description the jobtype description -- @param OUT x_guid the GUID of the jobtype. PROCEDURE REGISTER_COMP_JOBTYPE_MAPPING ( p_jobtype VARCHAR2, p_component_subtype VARCHAR2, p_description VARCHAR2 DEFAULT NULL, x_guid OUT VARCHAR2 ) IS l_date DATE ; l_map_guid MGMT_PAF_COMP_JOBTYPE_MAPPINGS.MAPPING_GUID%TYPE; BEGIN l_date := SYSDATE ; l_map_guid := SYS_GUID (); -- First register the jobType REGISTER_JOBTYPE ( p_jobtype => p_jobtype, p_description => p_description, x_guid => x_guid ); -- Now create the mapping INSERT INTO MGMT_PAF_COMP_JOBTYPE_MAPPINGS ( MAPPING_GUID, COMPONENT_SUBTYPE, JOBTYPE_GUID, LAST_UPDATED ) VALUES ( l_map_guid, p_component_subtype, x_guid, l_date ); END REGISTER_COMP_JOBTYPE_MAPPING; -- API to register group for job parameters for the jobtype mapping -- @param p_jobtype_guid the GUID of the registered jobtype that the group is for -- @param p_name display name of the group -- @param p_description description of the group -- @param p_group_order display order of the group -- @param OUT x_guid the GUID of the new group. PROCEDURE REGISTER_JOBTYPE_PARAM_GROUP ( p_jobtype_guid VARCHAR2, p_name VARCHAR2, p_description VARCHAR2 DEFAULT NULL, p_group_order INTEGER DEFAULT 0, x_guid OUT VARCHAR2 ) IS l_date DATE ; BEGIN l_date := SYSDATE ; x_guid := SYS_GUID (); INSERT INTO MGMT_PAF_PARAM_GROUPS ( JOBTYPE_GUID, GROUP_GUID, NAME, DESCRIPTION, GROUP_ORDER ) VALUES ( p_jobtype_guid, x_guid, p_name, p_description, p_group_order ); END REGISTER_JOBTYPE_PARAM_GROUP; -- API to register the job parameter for the jobtype mapping -- @param p_guid the GUID of the registered jobtype -- @param p_param_name the name of the parameter, this should be the -- same as the parameter name used in jobtype -- @param p_param_value, optional parameter value to indicate the -- expression for binding this parameter -- @param p_implicit if parameter is implicit or not 'Y' or 'N' -- defaults 'N' -- @param p_secret_status if parameter is secret or not 'ENCRYPTED' or 'NOT_ENCRYPTED' or NULL -- 'ENCRYPTED' means that the parameter is secret and encrypted, -- 'NOTENCRYPTED' means that the parameter is secret but not encrypted yet, -- and NULL means that the parameter is not a secret parameter, -- defaults NULL -- @param p_target_type if this parameter is to specify a target, then this -- defines the type of the target, NULL otherwise -- @param p_target_type_choice if this jobtype parameter needs multiple target -- selection then set this to 'MULTIPLE' else set it to 'SINGLE' -- @param p_name the jobparam display name -- @param p_description the jobparam description -- @param p_order display order of the job parameter. It is recommended that -- this is the global order and not the order in a particular group -- @param p_group_guid GUID of the group that this parameter is in PROCEDURE REGISTER_JOBTYPE_PARAM ( p_guid VARCHAR2, p_param_name VARCHAR2, p_param_value VARCHAR2 DEFAULT NULL, p_implicit CHAR DEFAULT 'N', p_secret_status VARCHAR2 DEFAULT NULL, p_target_type VARCHAR2 DEFAULT NULL, p_target_type_choice VARCHAR2 DEFAULT 'SINGLE', p_name VARCHAR2 DEFAULT NULL, p_description VARCHAR2 DEFAULT NULL, p_order INTEGER DEFAULT 0, p_group_guid VARCHAR2 DEFAULT NULL ) IS l_date DATE ; BEGIN l_date := SYSDATE ; INSERT INTO MGMT_PAF_JOBTYPE_PARAMS ( JOBTYPE_GUID, NAME, DESCRIPTION, PARAM_NAME, PARAM_VALUE, IMPLICIT, SECRET_STATUS, PARAM_ORDER, PARAM_GROUP, TARGET_TYPE, CONTENT0 ) VALUES ( p_guid, p_name, p_description, p_param_name, p_param_value, p_implicit, p_secret_status, p_order, p_group_guid, p_target_type, p_target_type_choice ); END REGISTER_JOBTYPE_PARAM; -- API to register the procedure type -- @param p_name the name of the procedure type, this name will be use in the -- deployment procedure XML as the procedure type attribute -- @param p_description a short description of the type -- @param p_app_url the url of the application associated with the procedure type -- @param p_resource_bundle_class_name the fully qualified Java class name of the -- resource bundle for the assoicated application and deployment procedures -- @param p_reinterviewable Enter 'Y' if your application supports saving and re-interviewing -- users. By specifying this, PAF will re-launch interview wizard of your application -- when users select a saved instance and run it. -- the assoicated application and deployment procedures PROCEDURE REGISTER_APPLICATION ( p_name VARCHAR2, p_description VARCHAR2, p_app_url VARCHAR2, p_resource_bundle_class_name VARCHAR2, p_reinterviewable CHAR DEFAULT NULL ) IS app_guid RAW(16); BEGIN select application_guid into app_guid from mgmt_paf_applications where name = p_name; update mgmt_paf_applications set description = p_description, url = p_app_url, resource_bundle = p_resource_bundle_class_name, interviewable = p_reinterviewable where name = p_name; EXCEPTION WHEN NO_DATA_FOUND THEN insert into MGMT_PAF_APPLICATIONS ( APPLICATION_GUID, NAME, DESCRIPTION, URL, RESOURCE_BUNDLE, DATA_GUID, INTERVIEWABLE ) values ( SYS_GUID(), p_name, p_description, p_app_url, p_resource_bundle_class_name, NULL, p_reinterviewable ); END REGISTER_APPLICATION; -- API to start the provisioning daemon -- @param p_period the period between individual daemon runs (in minutes) PROCEDURE START_DAEMON ( p_period NUMBER DEFAULT 5 ) IS job_schedule MGMT_JOB_SCHEDULE_RECORD; job_id RAW(16); exec_id RAW(16); job_targets MGMT_JOB_TARGET_LIST; job_owner VARCHAR2(32); em_user VARCHAR(32); BEGIN job_owner := MGMT_USER.GET_REPOSITORY_OWNER; IF ( MGMT_PAF_UTL.DAEMON_STATUS = 0) THEN --daemon's periodic schedule, running every p_period minutes job_schedule := MGMT_JOBS.get_job_schedule_record ( MGMT_JOBS.INTERVAL_FREQUENCY_CODE, SYSDATE, null, 0, 0, p_period, null, null, MGMT_JOBS.TIMEZONE_REPOSITORY, 0, 0, null, null ); -- the daemon job doesn't operate on targets, the targat list is empty job_targets := MGMT_JOB_TARGET_LIST(); -- get current em user em_user := MGMT_USER.GET_CURRENT_EM_USER; IF ( em_user != job_owner ) THEN -- PAF Daemon Job should be owned by SYSMAN -- set em user context to job_owner. -- This is required bcoz job system -- throws an error if job_owner != em_user SETEMUSERCONTEXT ( em_client_in => job_owner, op_in => 1 ); END IF; BEGIN -- submit daemon job to the job system MGMT_JOBS.submit_job('PROVISIONING DAEMON','job poller for PAF jobs', 'PAFDaemonJob',job_targets,null,job_schedule, job_id, exec_id, job_owner, MGMT_JOB_ENGINE.SYSTEM_JOB,null,null,null); EXCEPTION WHEN OTHERS THEN -- incase of exception, restore emusercontext -- and rethow the exception caught IF ( em_user != job_owner ) THEN SETEMUSERCONTEXT ( em_client_in => em_user, op_in => 1 ); END IF; RAISE; END SUBMIT_JOB; -- reset em user its originall value IF ( em_user != job_owner ) THEN SETEMUSERCONTEXT ( em_client_in => em_user, op_in => 1 ); END IF; END IF; END START_DAEMON; -- API to stop the provisioning daemon PROCEDURE STOP_DAEMON IS job_owner VARCHAR2(32); em_user VARCHAR(32); BEGIN em_user := MGMT_USER.GET_CURRENT_EM_USER; job_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Changing EM usercontext to sysman SETEMUSERCONTEXT ( em_client_in => job_owner, op_in => 1 ); MGMT_JOBS.stop_all_executions ( p_job_name => 'PROVISIONING DAEMON', p_job_owner => job_owner ); -- MGMT_JOBS.delete_job( p_job_name => 'provisioning daemon', -- p_job_owner => job_owner, -- p_commit => 1, p_is_library => 0 ); delete from mgmt_job where job_name = 'PROVISIONING DAEMON' ; commit ; --Setting EM usercontext back to original value SETEMUSERCONTEXT ( em_client_in => em_user, op_in => 1 ); END STOP_DAEMON; -- API to get the status of the provisioning daemon -- @return integer indicating period of the daemon (in minutes). -- 0 is returned if daemon is not running FUNCTION DAEMON_STATUS RETURN INTEGER IS p_interval INTEGER; p_count INTEGER; job_owner VARCHAR2(32); em_user VARCHAR(32); BEGIN job_owner := MGMT_USER.GET_REPOSITORY_OWNER; em_user := MGMT_USER.GET_CURRENT_EM_USER; -- Changing EM usercontext to sysman SETEMUSERCONTEXT ( em_client_in => job_owner, op_in => 1 ); SELECT count(1) INTO p_count FROM MGMT_JOB j, MGMT_JOB_SCHEDULE sch WHERE j.schedule_id=sch.schedule_id AND j.job_name='PROVISIONING DAEMON' AND j.job_owner = job_owner ; IF p_count = 0 THEN p_interval := 0; ELSE SELECT interval INTO p_interval FROM MGMT_JOB j, MGMT_JOB_SCHEDULE sch WHERE j.schedule_id=sch.schedule_id AND j.job_name='PROVISIONING DAEMON' AND j.job_owner = job_owner ; END IF; --Setting EM usercontext back to original value SETEMUSERCONTEXT ( em_client_in => em_user, op_in => 1 ); RETURN p_interval; END DAEMON_STATUS; PROCEDURE ENCRYPT_STRING ( p_base_guid VARCHAR2 , p_string VARCHAR2 , x_guid OUT VARCHAR ) IS l_guid VARCHAR2(32); BEGIN select SYS_GUID() into l_guid from dual ; insert into mgmt_paf_encrypted_strings ( str_guid , base_guid , encrypted , last_updated ) values ( l_guid , p_base_guid , encrypt(p_string) , SYSDATE ); x_guid := l_guid ; commit; END ENCRYPT_STRING; PROCEDURE DECRYPT_STRING ( p_guid VARCHAR2 , x_value OUT VARCHAR ) IS l_value VARCHAR2(2000); BEGIN select decrypt(encrypted) into l_value from mgmt_paf_encrypted_strings where str_guid = p_guid ; x_value := l_value ; commit; END DECRYPT_STRING; -- API to update runtime data -- @param p_job_exec_id the execution id of the job from which this API -- is being called -- @param p_variable_name name of variable to update in the runtime data -- @param p_variable_value value of variable to update in the runtime data PROCEDURE UPDATE_RUNTIME_DATA ( p_job_exec_id VARCHAR2, p_variable_name VARCHAR2, p_variable_value VARCHAR2 ) IS l_instance_guid VARCHAR2(32); l_oms_guid VARCHAR2(32); l_result VARCHAR2(20);--random size 20 l_return_status VARCHAR2(20);--random size 20 BEGIN select j.instance_guid into l_instance_guid from mgmt_paf_jobs j where j.exec_id = p_job_exec_id; select i.oms_guid into l_oms_guid from mgmt_paf_jobs j join mgmt_paf_instances i on j.instance_guid = i.instance_guid and j.exec_id = p_job_exec_id; MGMT_PAF_AQ.EXECUTE_SERVER_CMD ( 'str:oracle.sysman.pp.paf.dal.RuntimeDataUpdate:'||l_instance_guid||';x;'||p_variable_name||';'||p_variable_value, l_oms_guid, l_result, l_return_status ); commit; END UPDATE_RUNTIME_DATA; END MGMT_PAF_UTL ; / show errors; CREATE OR REPLACE PACKAGE BODY MGMT_PAF_AQ AS PROCEDURE ENQUEUE_REQUEST ( p_node_id IN VARCHAR2, p_xml_data IN VARCHAR2, x_request_id OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 ) IS enqueue_options dbms_aq.enqueue_options_t; message_properties dbms_aq.message_properties_t; message_handle RAW(16); message MGMT_PAF_AQ_MSG; request_id VARCHAR2(32) ; xml_data_null EXCEPTION; BEGIN IF p_xml_data IS NULL then raise xml_data_null; END IF; -- select request id from sequence select SYS_GUID() into request_id from dual ; message_properties.correlation := p_node_id; message_properties.expiration := 600; enqueue_options.visibility := DBMS_AQ.IMMEDIATE; message := MGMT_PAF_AQ_MSG(p_node_id,request_id,SYSDATE,p_xml_data); dbms_aq.enqueue ( queue_name => G_REQUEST_QUEUE, enqueue_options => enqueue_options, message_properties => message_properties, payload => message, msgid => message_handle ); x_request_id := request_id ; x_return_status := G_SUCCESS ; EXCEPTION WHEN xml_data_null THEN x_return_status := G_MSGNULL ; END ENQUEUE_REQUEST; PROCEDURE ENQUEUE_RESPONSE ( p_node_id IN VARCHAR2, p_request_id IN VARCHAR2, p_xml_data IN VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 ) IS enqueue_options dbms_aq.enqueue_options_t; message_properties dbms_aq.message_properties_t; message_handle RAW(16); message MGMT_PAF_AQ_MSG; xml_data_null EXCEPTION; BEGIN IF p_xml_data IS NULL then raise xml_data_null; END IF; message_properties.correlation := p_request_id; message_properties.expiration := 600; enqueue_options.visibility := DBMS_AQ.IMMEDIATE; message := MGMT_PAF_AQ_MSG(p_node_id,p_request_id,SYSDATE,p_xml_data); dbms_aq.enqueue ( queue_name => G_RESPONSE_QUEUE, enqueue_options => enqueue_options, message_properties => message_properties, payload => message, msgid => message_handle ); x_return_status := G_SUCCESS ; EXCEPTION WHEN xml_data_null THEN x_return_status := G_MSGNULL ; END ENQUEUE_RESPONSE; PROCEDURE DEQUEUE_REQUEST ( p_node_id IN VARCHAR2, p_wait IN NUMBER, x_xml_data OUT NOCOPY VARCHAR2, x_request_id OUT NOCOPY VARCHAR2, x_timestamp OUT NOCOPY DATE, x_return_status OUT NOCOPY VARCHAR2 ) IS dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; message_handle RAW(16); message MGMT_PAF_AQ_MSG; no_messages exception; pragma exception_init (no_messages, -25228); BEGIN dequeue_options.correlation := p_node_id; IF NVL(p_wait, 0) < 0 THEN dequeue_options.wait := DBMS_AQ.NO_WAIT; ELSE dequeue_options.wait := p_wait ; END IF; dequeue_options.visibility := DBMS_AQ.IMMEDIATE; dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE ; dbms_aq.dequeue ( queue_name => G_REQUEST_QUEUE, dequeue_options => dequeue_options, message_properties => message_properties, payload => message, msgid => message_handle ); x_xml_data := message.xml_data ; x_request_id := message.request_id ; x_timestamp := message.timestamp ; x_return_status := G_SUCCESS ; EXCEPTION WHEN no_messages THEN x_return_status := G_TIMEOUT ; END DEQUEUE_REQUEST; PROCEDURE DEQUEUE_RESPONSE ( p_request_id IN VARCHAR2, p_wait IN NUMBER, x_xml_data OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 ) IS dequeue_options dbms_aq.dequeue_options_t; message_properties dbms_aq.message_properties_t; message_handle RAW(16); message MGMT_PAF_AQ_MSG; no_messages exception; pragma exception_init (no_messages, -25228); BEGIN dequeue_options.correlation := p_request_id; IF NVL(p_wait, 0) < 0 THEN dequeue_options.wait := DBMS_AQ.NO_WAIT; ELSE dequeue_options.wait := p_wait ; END IF; dequeue_options.visibility := DBMS_AQ.IMMEDIATE; dequeue_options.navigation := DBMS_AQ.FIRST_MESSAGE ; dbms_aq.dequeue ( queue_name => G_RESPONSE_QUEUE, dequeue_options => dequeue_options, message_properties => message_properties, payload => message, msgid => message_handle ); x_xml_data := message.xml_data ; x_return_status := G_SUCCESS ; EXCEPTION WHEN no_messages THEN x_return_status := G_TIMEOUT; END DEQUEUE_RESPONSE; PROCEDURE EXECUTE_SERVER_CMD ( p_cmd IN VARCHAR2, p_server_id IN VARCHAR2, x_result OUT NOCOPY VARCHAR2, x_return_status OUT NOCOPY VARCHAR2 ) IS l_request_id VARCHAR2(32); l_status CHAR(1) ; BEGIN select status into l_status from mgmt_paf_oms_status where oms_guid = p_server_id ; if l_status = 'D' then x_return_status := G_SERVER_DOWN ; return ; end if; -- Initialize API return status to success x_return_status := G_SUCCESS; enqueue_request ( p_server_id, p_cmd, l_request_id, x_return_status ); dequeue_response ( l_request_id, 45, x_result, x_return_status ); EXCEPTION WHEN no_data_found THEN x_return_status := G_INVALID_ID ; END EXECUTE_SERVER_CMD; /* PROCEDURE OMS_FAILOVER_CALLBACK ( p_failover_id IN NUMBER, p_last_updated IN DATE ) IS l_host_url VARCHAR(256) ; l_count NUMBER ; BEGIN select host_url into l_host_url from mgmt_failover_table where failover_id = p_failover_id ; select count(*) into l_count from mgmt_paf_oms_status where host_url = l_host_url ; if l_count > 0 then update mgmt_paf_oms_status set status = 'D' , last_updated = SYSDATE where host_url = l_host_url ; end if; END OMS_FAILOVER_CALLBACK ; */ END MGMT_PAF_AQ; / CREATE OR REPLACE PACKAGE BODY MGMT_PAF_NOTIF AS PROCEDURE NOTIFY_PAF ( p_job_status IN MGMT_NOTIFY_JOB ) IS l_oms_guid VARCHAR(32); l_job_guid VARCHAR(32); l_job_name VARCHAR(128); l_job_status NUMBER; l_paf_inst_guid VARCHAR(32); l_paf_state_guid VARCHAR(32); l_result VARCHAR2(20); l_return_status VARCHAR2(20); l_message VARCHAR2(4000); CURSOR l_paf_oms_status_cur IS SELECT oms_guid FROM mgmt_paf_oms_status WHERE STATUS = 'U'; BEGIN l_job_guid := RAWTOHEX ( p_job_status.job_guid ); l_job_name := p_job_status.job_name; l_job_status := p_job_status.job_status; BEGIN select instance_guid, state_guid into l_paf_inst_guid, l_paf_state_guid from mgmt_paf_states s, mgmt_job j where j.job_id = s.job_id and l_job_guid = s.job_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; BEGIN INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, 'PAF Received notification for job status change.' ); FOR l_oms_guid_rec IN l_paf_oms_status_cur LOOP l_oms_guid := l_oms_guid_rec.oms_guid; BEGIN MGMT_PAF_AQ.EXECUTE_SERVER_CMD ( 'str:oracle.sysman.pp.paf.dal.JobStatusUpdate:'||l_paf_inst_guid||';'||l_job_guid||';'||l_paf_state_guid||';'||l_job_status, l_oms_guid, l_result, l_return_status ); IF l_return_status = MGMT_PAF_AQ.G_SUCCESS THEN INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, 'Successfully submitted job status change event to the Provisioning Engine.' ); -- If we are here, then there was no exception. -- So, exit out of the loop EXIT; ELSE l_message := 'Failed to submit job status change event to the Provisioning Engine.' || ' Return Status: ' || l_return_status; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, l_message ); END IF; EXCEPTION WHEN OTHERS THEN l_message := 'PAF Failed to submit job status change event to the Provisioning Engine.' || ' Error Code:' || SQLCODE || ' Message: ' || SQLERRM || ' Stack Trace: ' || dbms_utility.format_error_stack ; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, l_message ); END NOTIFY_PAF_CHK_FOR_EXCEPTION; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; EXCEPTION WHEN OTHERS THEN l_message := 'PAF Error while processing provision job update JOB ID:' || l_job_guid || ' Error Code:' || SQLCODE || ' Message: ' || SQLERRM || ' Stack Trace: ' || dbms_utility.format_error_stack ; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( NULL, NULL, NULL, NULL, NULL, NULL, SYSDATE, l_message ); END NOTIFY_PAF; FUNCTION NOTIF_RULE_EXISTS RETURN INTEGER IS l_em_repos_owner VARCHAR2(128); l_rule_name VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; RETURN EMD_NOTIFICATION.RULE_EXISTS ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner ); END NOTIF_RULE_EXISTS; FUNCTION TGT_TYPE_REG_WITH_NOTIF ( p_target_type IN VARCHAR2 ) RETURN INTEGER IS l_rule_name VARCHAR2(128); l_rname VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; BEGIN SELECT rule_name INTO l_rname FROM mgmt_notify_job_rule_configs WHERE rule_name = l_rule_name AND TARGET_TYPE = p_target_type; IF l_rname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; END TGT_TYPE_REG_WITH_NOTIF; FUNCTION JOB_TYPE_REG_WITH_NOTIF ( p_job_type IN VARCHAR2 ) RETURN INTEGER IS l_rule_name VARCHAR2(128); l_rname VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; BEGIN SELECT rule_name INTO l_rname FROM mgmt_notify_job_rule_configs WHERE rule_name = l_rule_name AND JOB_TYPE = p_job_type; IF l_rname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; END JOB_TYPE_REG_WITH_NOTIF; FUNCTION TGT_JOB_TYPE_REG_WITH_NOTIF ( p_target_type IN VARCHAR2, p_job_type IN VARCHAR2 ) RETURN INTEGER IS l_rule_name VARCHAR2(128); l_rname VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; BEGIN SELECT rule_name INTO l_rname FROM mgmt_notify_job_rule_configs WHERE rule_name = l_rule_name AND TARGET_TYPE = p_target_type AND JOB_TYPE = p_job_type; IF l_rname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; END TGT_JOB_TYPE_REG_WITH_NOTIF; FUNCTION PLSQL_DEVICE_EXISTS RETURN INTEGER IS l_plsql_device_name VARCHAR2(128); l_dname VARCHAR2(128); BEGIN l_plsql_device_name := PAF_NOTIF_PLSQL_DEVICE_NAME; BEGIN SELECT device_name INTO l_dname FROM mgmt_notify_devices WHERE device_name = l_plsql_device_name; IF l_dname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; END PLSQL_DEVICE_EXISTS; FUNCTION PLSQL_DEVICE_REG_WITH_NOTIF RETURN INTEGER IS l_rule_name VARCHAR2(128); l_plsql_device_name VARCHAR2(128); l_dname VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; l_plsql_device_name := PAF_NOTIF_PLSQL_DEVICE_NAME; BEGIN SELECT device_name INTO l_dname FROM mgmt_notify_notifyees WHERE device_name = l_plsql_device_name AND rule_name = l_rule_name; IF l_dname IS NOT NULL THEN RETURN 1; END IF; RETURN 0; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN 0; END; END PLSQL_DEVICE_REG_WITH_NOTIF; PROCEDURE REG_NOTIF IS BEGIN REG_PLSQL_DEVICE (); REG_NOTIF_RULE (); REG_PLSQL_DEVICE_WITH_NOTIF (); END REG_NOTIF; PROCEDURE REG_NOTIF_RULE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2(128); -- variables for default notification rules l_rule_name VARCHAR2(128); l_rule_owner VARCHAR2(128); l_rule_desc VARCHAR2(256); l_rule_public NUMBER(1); l_target_type VARCHAR2(64); l_rule_targets_array SMP_EMD_STRING_ARRAY; l_target_types_array SMP_EMD_STRING_ARRAY; l_rule_job_names SMP_EMD_STRING_ARRAY; l_rule_job_types SMP_EMD_STRING_ARRAY; l_rule_job_owners SMP_EMD_STRING_ARRAY; l_want_job_scheduled SMP_EMD_INTEGER_ARRAY; l_want_job_started SMP_EMD_INTEGER_ARRAY; l_want_job_suspended SMP_EMD_INTEGER_ARRAY; l_want_job_succeeded SMP_EMD_INTEGER_ARRAY; l_want_job_problem_in SMP_EMD_INTEGER_ARRAY; l_rule_device_names SMP_EMD_STRING_ARRAY; l_rule_device_types SMP_EMD_INTEGER_ARRAY; l_device_not_avail_list SMP_EMD_STRING_ARRAY; l_email_gateway_setup NUMBER; l_want_clears SMP_EMD_INTEGER_ARRAY; l_want_warnings SMP_EMD_INTEGER_ARRAY; l_want_critical_alerts SMP_EMD_INTEGER_ARRAY; l_want_warning_succeed SMP_EMD_INTEGER_ARRAY; l_want_warning_problem SMP_EMD_INTEGER_ARRAY; l_want_critical_succeed SMP_EMD_INTEGER_ARRAY; l_want_critical_problem SMP_EMD_INTEGER_ARRAY; l_rule_policies SMP_EMD_STRING_ARRAY; l_rule_mnames_array SMP_EMD_STRING_ARRAY; l_rule_mcolumns_array SMP_EMD_STRING_ARRAY; l_rule_mindexes_array SMP_EMD_NVPAIR_ARRAY; BEGIN IF NOTIF_RULE_EXISTS <> 0 THEN RETURN ; END IF; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. -- create rule "Provisioning Engine Notifier ruls" for all target types BEGIN -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- initialize the values l_rule_name := PAF_NOTIF_RULE_NAME; l_rule_desc := 'System-generated notification rule for provisioning engine: Notifies the provisioning engine about any change in job status submitted by it.'; l_rule_public := 1; l_rule_owner := l_em_repos_owner; l_target_type := MGMT_GLOBAL.G_HOST_TARGET_TYPE; l_rule_targets_array := SMP_EMD_STRING_ARRAY(); l_rule_targets_array.extend( 1 ); l_rule_targets_array( 1 ) := '%'; l_target_types_array := SMP_EMD_STRING_ARRAY(); l_target_types_array.extend( 1 ); l_target_types_array( 1 ) := '%'; l_rule_job_names := SMP_EMD_STRING_ARRAY(); l_rule_job_names.extend ( 4 ); l_rule_job_names ( 1 ) := '%'; l_rule_job_names ( 2 ) := '%'; l_rule_job_names ( 3 ) := '%'; l_rule_job_names ( 4 ) := '%'; l_rule_job_owners := SMP_EMD_STRING_ARRAY(); l_rule_job_owners.extend ( 4 ); l_rule_job_owners ( 1 ) := '%'; l_rule_job_owners ( 2 ) := '%'; l_rule_job_owners ( 3 ) := '%'; l_rule_job_owners ( 4 ) := '%'; l_rule_job_types := SMP_EMD_STRING_ARRAY(); l_rule_job_types.extend ( 4 ); l_rule_job_types ( 1 ) := 'PAFHostCommand'; l_rule_job_types ( 2 ) := 'DirectiveStepJob'; l_rule_job_types ( 3 ) := 'ComponentStepJob'; l_rule_job_types ( 4 ) := 'PAFFileTransfer'; l_want_job_scheduled := SMP_EMD_INTEGER_ARRAY(); l_want_job_scheduled.extend ( 4 ); l_want_job_scheduled ( 1 ) := 0; l_want_job_scheduled ( 2 ) := 0; l_want_job_scheduled ( 3 ) := 0; l_want_job_scheduled ( 4 ) := 0; l_want_job_started := SMP_EMD_INTEGER_ARRAY(); l_want_job_started.extend ( 4 ); l_want_job_started ( 1 ) := 1; l_want_job_started ( 2 ) := 1; l_want_job_started ( 3 ) := 1; l_want_job_started ( 4 ) := 1; l_want_job_suspended := SMP_EMD_INTEGER_ARRAY(); l_want_job_suspended.extend ( 4 ); l_want_job_suspended ( 1 ) := 1; l_want_job_suspended ( 2 ) := 1; l_want_job_suspended ( 3 ) := 1; l_want_job_suspended ( 4 ) := 1; l_want_job_succeeded := SMP_EMD_INTEGER_ARRAY(); l_want_job_succeeded.extend ( 4 ); l_want_job_succeeded ( 1 ) := 1; l_want_job_succeeded ( 2 ) := 1; l_want_job_succeeded ( 3 ) := 1; l_want_job_succeeded ( 4 ) := 1; l_want_job_problem_in := SMP_EMD_INTEGER_ARRAY(); l_want_job_problem_in.extend ( 4 ); l_want_job_problem_in ( 1 ) := 1; l_want_job_problem_in ( 2 ) := 1; l_want_job_problem_in ( 3 ) := 1; l_want_job_problem_in ( 4 ) := 1; -- associate Provisioning Job Updater to the rul --- l_rule_device_names := SMP_EMD_STRING_ARRAY(); l_rule_device_names.extend ( 1 ); l_rule_device_names ( 1 ) := 'Provisioning Job Updater'; l_rule_device_types := SMP_EMD_INTEGER_ARRAY(); l_rule_device_types.extend ( 1 ); l_rule_device_types ( 1 ) := EMD_NOTIFICATION.PLSQL_DEVICE_TYPE; l_want_clears := SMP_EMD_INTEGER_ARRAY (); l_want_warnings := SMP_EMD_INTEGER_ARRAY (); l_want_critical_alerts := SMP_EMD_INTEGER_ARRAY (); l_want_warning_succeed := SMP_EMD_INTEGER_ARRAY (); l_want_warning_problem := SMP_EMD_INTEGER_ARRAY (); l_want_critical_succeed := SMP_EMD_INTEGER_ARRAY (); l_want_critical_problem := SMP_EMD_INTEGER_ARRAY (); l_rule_policies := SMP_EMD_STRING_ARRAY (); l_rule_mnames_array := SMP_EMD_STRING_ARRAY (); l_rule_mcolumns_array := SMP_EMD_STRING_ARRAY (); l_rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY (); mgmt_preferences.create_notification_rule( rule_name_in => l_rule_name, rule_owner_in => l_rule_owner, rule_desc_in => l_rule_desc, rule_public_in => l_rule_public, rule_target_type_in => l_target_type, rule_targets_in => l_rule_targets_array, target_types_in => NULL, want_tgt_up_in => 0, want_tgt_down_in => 0, want_tgt_unreachable_start_in => 0, want_tgt_unreachable_end_in => 0, want_tgt_metric_err_start_in => 0, want_tgt_metric_err_end_in => 0, want_tgt_blkout_started_in => 0, want_tgt_blkout_ended_in => 0, ignore_rca_in => 0, rule_metric_names_in => l_rule_mnames_array, rule_metric_columns_in => l_rule_mcolumns_array, rule_metric_indexes_in => l_rule_mindexes_array, metric_want_clears_in => l_want_clears, metric_want_critical_alerts_in => l_want_critical_alerts, metric_want_warning_alerts_in => l_want_critical_alerts, want_warning_succeed_in => l_want_warning_succeed, want_warning_problem_in => l_want_warning_problem, want_critical_succeed_in => l_want_critical_succeed, want_critical_problem_in => l_want_critical_problem, policy_names_in => l_rule_policies, want_policy_violation_in => NULL, want_policy_clear_in => NULL, want_policy_job_succeed_in => NULL, want_policy_job_problem_in => NULL, job_names_in => l_rule_job_names, job_owner_in => l_rule_job_owners, job_types_in => l_rule_job_types, want_job_scheduled_in => l_want_job_scheduled, want_job_started_in => l_want_job_started, want_job_suspended_in => l_want_job_suspended, want_job_succeeded_in => l_want_job_succeeded, want_job_problem_in => l_want_job_problem_in, send_email_in => 0, device_names_in => l_rule_device_names, device_types_in => l_rule_device_types, device_not_avail_list_out => l_device_not_avail_list, email_gateway_setup_out => l_email_gateway_setup ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REG_NOTIF_RULE; PROCEDURE REG_PLSQL_DEVICE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2 (128); -- variables for default notification rules l_plsql_device_name VARCHAR2(128); l_plsql_device_desc VARCHAR2(256); l_plsql_proc VARCHAR2(128); BEGIN IF PLSQL_DEVICE_EXISTS <> 0 THEN RETURN; END IF; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- create device "Provisioning Job Updater" l_plsql_device_name := PAF_NOTIF_PLSQL_DEVICE_NAME; l_plsql_device_desc := PAF_NOTIF_PLSQL_DEVICE_DESC; l_plsql_proc := l_em_repos_owner || '.' || PAF_PLSQL_PROC; BEGIN EMD_NOTIFICATION.ADD_PLSQL_DEVICE ( v_device_name => l_plsql_device_name, v_profile_name => l_em_repos_owner, v_plsql_proc => l_plsql_proc, v_description => l_plsql_device_desc ); END; EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REG_PLSQL_DEVICE; PROCEDURE REG_PLSQL_DEVICE_WITH_NOTIF IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2 (128); -- variables for default notification rules l_rule_name VARCHAR2(128); l_plsql_device_name VARCHAR2(128); l_plsql_device_desc VARCHAR2(256); BEGIN IF PLSQL_DEVICE_REG_WITH_NOTIF <> 0 THEN RETURN; END IF; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- create device "Provisioning Job Updater" l_rule_name := PAF_NOTIF_RULE_NAME; l_plsql_device_name := PAF_NOTIF_PLSQL_DEVICE_NAME; l_plsql_device_desc := PAF_NOTIF_PLSQL_DEVICE_DESC; BEGIN EMD_NOTIFICATION.ADD_DEVICE_TO_RULE ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner, v_device_name => l_plsql_device_name, v_profile_name => l_em_repos_owner ); END; EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REG_PLSQL_DEVICE_WITH_NOTIF; PROCEDURE REG_ALL_TGT_TYPES_WITH_NOTIF IS BEGIN REG_ALL_TGT_TYPES_WITH_NOTIF ( p_job_type => '%' ); END REG_ALL_TGT_TYPES_WITH_NOTIF; PROCEDURE REG_ALL_TGT_TYPES_WITH_NOTIF ( p_job_type IN VARCHAR2 ) IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2(128); l_rule_name VARCHAR2(128); l_target_type VARCHAR2 (128); CURSOR l_tgt_type_list_cur IS SELECT DISTINCT (target_type ) FROM mgmt_target_types WHERE target_type <> MGMT_GLOBAL.G_HOST_TARGET_TYPE; BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; BEGIN -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); FOR l_tgt_type_rec IN l_tgt_type_list_cur LOOP l_target_type := l_tgt_type_rec.target_type; REG_TGT_JOB_TYPE_WITH_NOTIF ( p_target_type => l_target_type, p_job_type => p_job_type ); END LOOP; EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REG_ALL_TGT_TYPES_WITH_NOTIF; PROCEDURE REG_TGT_TYPE_WITH_NOTIF ( p_target_type IN VARCHAR2 ) IS BEGIN REG_TGT_JOB_TYPE_WITH_NOTIF ( p_target_type => p_target_type, p_job_type => '%' ); END REG_TGT_TYPE_WITH_NOTIF; PROCEDURE REG_TGT_JOB_TYPE_WITH_NOTIF ( p_target_type IN VARCHAR2, p_job_type IN VARCHAR2 ) IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2(128); l_rule_name VARCHAR2(128); BEGIN IF tgt_job_type_reg_with_notif ( p_target_type => p_target_type, p_job_type => p_job_type ) <> 0 THEN RETURN; END IF; l_rule_name := PAF_NOTIF_RULE_NAME; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; BEGIN -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); EMD_NOTIFICATION.ADD_CONFIGURATION_TO_JOB_RULE ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner, v_target_type => p_target_type, v_target_name => '%', v_group_guid => NULL, v_job_name => '%', v_job_owner => '%', v_job_type => p_job_type, v_want_job_scheduled => 0, v_want_job_running => 1, v_want_job_succeeded => 1, v_want_job_suspended => 1, v_want_job_problems => 1 ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REG_TGT_JOB_TYPE_WITH_NOTIF; PROCEDURE REG_ALL_JOB_TYPES_WITH_NOTIF IS BEGIN REG_ALL_TGT_TYPES_WITH_NOTIF ( p_job_type => '%' ); END REG_ALL_JOB_TYPES_WITH_NOTIF; PROCEDURE REG_ALL_JOB_TYPES_WITH_NOTIF ( p_target_type IN VARCHAR2 ) IS BEGIN REG_TGT_JOB_TYPE_WITH_NOTIF ( p_target_type => p_target_type, p_job_type => '%' ); END REG_ALL_JOB_TYPES_WITH_NOTIF; PROCEDURE REG_JOB_TYPE_WITH_NOTIF ( p_job_type IN VARCHAR2 ) IS BEGIN REG_ALL_TGT_TYPES_WITH_NOTIF ( p_job_type => p_job_type ); END REG_JOB_TYPE_WITH_NOTIF; PROCEDURE UNREG_NOTIF IS BEGIN UNREG_NOTIF_RULE (); UNREG_PLSQL_DEVICE (); END UNREG_NOTIF; PROCEDURE UNREG_NOTIF_RULE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2 (128); l_rule_name VARCHAR2(128); BEGIN l_rule_name := PAF_NOTIF_RULE_NAME; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- create device "Provisioning Job Updater" BEGIN EMD_NOTIFICATION.DELETE_NOTIFICATION_RULE ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner, v_super_user => l_em_repos_owner ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END UNREG_NOTIF_RULE; PROCEDURE UNREG_PLSQL_DEVICE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2 (128); l_plsql_device_name VARCHAR2(128); BEGIN l_plsql_device_name := PAF_NOTIF_PLSQL_DEVICE_NAME; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- create device "Provisioning Job Updater" BEGIN MGMT_NOTIFICATION.DELETE_DEVICE ( p_profile_name => l_em_repos_owner, p_device_name => l_plsql_device_name ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END UNREG_PLSQL_DEVICE; --------------------------------------------------------- -- -- PAF Status Notification section -- --------------------------------------------------------- FUNCTION PAF_STATUS_NOTIF_RULE_EXISTS RETURN INTEGER IS l_em_repos_owner VARCHAR2(128); l_rule_name VARCHAR2(128); BEGIN l_rule_name := PAF_STATUS_NOTIF_RULE_NAME; l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; RETURN EMD_NOTIFICATION.RULE_EXISTS ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner ); END PAF_STATUS_NOTIF_RULE_EXISTS; PROCEDURE REGISTER_PAF_STATUS_NOTIF_RULE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2(128); -- variables for default notification rules l_rule_name VARCHAR2(128); l_rule_owner VARCHAR2(128); l_rule_desc VARCHAR2(256); l_rule_public NUMBER(1); l_target_type VARCHAR2(64); l_rule_targets_array SMP_EMD_STRING_ARRAY; l_target_types_array SMP_EMD_STRING_ARRAY; l_rule_job_names SMP_EMD_STRING_ARRAY; l_rule_job_types SMP_EMD_STRING_ARRAY; l_rule_job_owners SMP_EMD_STRING_ARRAY; l_want_job_scheduled SMP_EMD_INTEGER_ARRAY; l_want_job_started SMP_EMD_INTEGER_ARRAY; l_want_job_suspended SMP_EMD_INTEGER_ARRAY; l_want_job_succeeded SMP_EMD_INTEGER_ARRAY; l_want_job_problem_in SMP_EMD_INTEGER_ARRAY; l_rule_device_names SMP_EMD_STRING_ARRAY; l_rule_device_types SMP_EMD_INTEGER_ARRAY; l_device_not_avail_list SMP_EMD_STRING_ARRAY; l_email_gateway_setup NUMBER; l_want_clears SMP_EMD_INTEGER_ARRAY; l_want_warnings SMP_EMD_INTEGER_ARRAY; l_want_critical_alerts SMP_EMD_INTEGER_ARRAY; l_want_warning_succeed SMP_EMD_INTEGER_ARRAY; l_want_warning_problem SMP_EMD_INTEGER_ARRAY; l_want_critical_succeed SMP_EMD_INTEGER_ARRAY; l_want_critical_problem SMP_EMD_INTEGER_ARRAY; l_rule_policies SMP_EMD_STRING_ARRAY; l_rule_mnames_array SMP_EMD_STRING_ARRAY; l_rule_mcolumns_array SMP_EMD_STRING_ARRAY; l_rule_mindexes_array SMP_EMD_NVPAIR_ARRAY; BEGIN IF PAF_STATUS_NOTIF_RULE_EXISTS <> 0 THEN RETURN ; END IF; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. -- create rule "PAF Status Notification" for host target type BEGIN -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); -- initialize the values l_rule_name := PAF_STATUS_NOTIF_RULE_NAME; l_rule_desc := PAF_STATUS_NOTIF_RULE_DESC; l_rule_public := 1; l_rule_owner := l_em_repos_owner; l_target_type := MGMT_GLOBAL.G_HOST_TARGET_TYPE; l_rule_targets_array := SMP_EMD_STRING_ARRAY(); l_rule_targets_array.extend( 1 ); l_rule_targets_array( 1 ) := '%'; l_target_types_array := SMP_EMD_STRING_ARRAY(); l_target_types_array.extend( 1 ); l_target_types_array( 1 ) := 'host'; l_rule_job_names := SMP_EMD_STRING_ARRAY(); l_rule_job_names.extend ( 1 ); l_rule_job_names ( 1 ) := '%PAFNOTIFICATION%'; l_rule_job_owners := SMP_EMD_STRING_ARRAY(); l_rule_job_owners.extend ( 1 ); l_rule_job_owners ( 1 ) := '%'; l_rule_job_types := SMP_EMD_STRING_ARRAY(); l_rule_job_types.extend ( 1 ); l_rule_job_types ( 1 ) := 'PAFNotification'; l_want_job_scheduled := SMP_EMD_INTEGER_ARRAY(); l_want_job_scheduled.extend ( 1 ); l_want_job_scheduled ( 1 ) := 0; -- send notification when job is started l_want_job_started := SMP_EMD_INTEGER_ARRAY(); l_want_job_started.extend ( 1 ); l_want_job_started ( 1 ) := 1; l_want_job_suspended := SMP_EMD_INTEGER_ARRAY(); l_want_job_suspended.extend ( 1 ); l_want_job_suspended ( 1 ) := 0; l_want_job_succeeded := SMP_EMD_INTEGER_ARRAY(); l_want_job_succeeded.extend ( 1 ); l_want_job_succeeded ( 1 ) := 0; l_want_job_problem_in := SMP_EMD_INTEGER_ARRAY(); l_want_job_problem_in.extend ( 1 ); l_want_job_problem_in ( 1 ) := 0; l_rule_device_names := SMP_EMD_STRING_ARRAY(); l_rule_device_types := SMP_EMD_INTEGER_ARRAY(); l_want_clears := SMP_EMD_INTEGER_ARRAY (); l_want_warnings := SMP_EMD_INTEGER_ARRAY (); l_want_critical_alerts := SMP_EMD_INTEGER_ARRAY (); l_want_warning_succeed := SMP_EMD_INTEGER_ARRAY (); l_want_warning_problem := SMP_EMD_INTEGER_ARRAY (); l_want_critical_succeed := SMP_EMD_INTEGER_ARRAY (); l_want_critical_problem := SMP_EMD_INTEGER_ARRAY (); l_rule_policies := SMP_EMD_STRING_ARRAY (); l_rule_mnames_array := SMP_EMD_STRING_ARRAY (); l_rule_mcolumns_array := SMP_EMD_STRING_ARRAY (); l_rule_mindexes_array := SMP_EMD_NVPAIR_ARRAY (); mgmt_preferences.create_notification_rule( rule_name_in => l_rule_name, rule_owner_in => l_rule_owner, rule_desc_in => l_rule_desc, rule_public_in => l_rule_public, rule_target_type_in => l_target_type, rule_targets_in => l_rule_targets_array, target_types_in => l_target_types_array, want_tgt_up_in => 0, want_tgt_down_in => 0, want_tgt_unreachable_start_in => 0, want_tgt_unreachable_end_in => 0, want_tgt_metric_err_start_in => 0, want_tgt_metric_err_end_in => 0, want_tgt_blkout_started_in => 0, want_tgt_blkout_ended_in => 0, ignore_rca_in => 0, rule_metric_names_in => l_rule_mnames_array, rule_metric_columns_in => l_rule_mcolumns_array, rule_metric_indexes_in => l_rule_mindexes_array, metric_want_clears_in => l_want_clears, metric_want_critical_alerts_in => l_want_critical_alerts, metric_want_warning_alerts_in => l_want_critical_alerts, want_warning_succeed_in => l_want_warning_succeed, want_warning_problem_in => l_want_warning_problem, want_critical_succeed_in => l_want_critical_succeed, want_critical_problem_in => l_want_critical_problem, policy_names_in => l_rule_policies, want_policy_violation_in => NULL, want_policy_clear_in => NULL, want_policy_job_succeed_in => NULL, want_policy_job_problem_in => NULL, job_names_in => l_rule_job_names, job_owner_in => l_rule_job_owners, job_types_in => l_rule_job_types, want_job_scheduled_in => l_want_job_scheduled, want_job_started_in => l_want_job_started, want_job_suspended_in => l_want_job_suspended, want_job_succeeded_in => l_want_job_succeeded, want_job_problem_in => l_want_job_problem_in, send_email_in => 0, device_names_in => l_rule_device_names, device_types_in => l_rule_device_types, device_not_avail_list_out => l_device_not_avail_list, email_gateway_setup_out => l_email_gateway_setup ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END REGISTER_PAF_STATUS_NOTIF_RULE; PROCEDURE UNREG_PAF_STATUS_NOTIF_RULE IS l_current_user VARCHAR2(128); l_em_repos_owner VARCHAR2 (128); l_rule_name VARCHAR2(128); BEGIN l_rule_name := PAF_STATUS_NOTIF_RULE_NAME; -- Save the currently logged in user l_current_user := mgmt_user.get_current_em_user(); l_em_repos_owner := MGMT_USER.GET_REPOSITORY_OWNER; -- Set em user context to EM_REPOS_USER. The default notification -- rules will be owned by this user. setemusercontext(l_em_repos_owner, MGMT_USER.OP_SET_IDENTIFIER); BEGIN EMD_NOTIFICATION.DELETE_NOTIFICATION_RULE ( v_rule_name => l_rule_name, v_owner => l_em_repos_owner, v_super_user => l_em_repos_owner ); EXCEPTION WHEN OTHERS THEN -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); RAISE; END; -- now reset the user setemusercontext(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END UNREG_PAF_STATUS_NOTIF_RULE; END MGMT_PAF_NOTIF; / show errors; CREATE OR REPLACE PACKAGE BODY MGMT_PAF_JOB_UTIL AS PROCEDURE UPDATE_PAF_JOB ( p_job_status IN SMP_EMD_NVPAIR_ARRAY ) IS l_oms_guid RAW(16); l_job_guid RAW(16); l_job_name VARCHAR2(64); l_job_status NUMBER; l_job_status_bucket NUMBER; l_paf_inst_guid RAW(16); l_paf_state_guid RAW(16); l_result VARCHAR2(20); l_return_status VARCHAR2(20); l_message VARCHAR2(4000); CURSOR l_paf_oms_status_cur IS SELECT oms_guid FROM mgmt_paf_oms_status WHERE STATUS = 'U'; BEGIN BEGIN l_job_guid := p_job_status(1).value; l_job_status := TO_NUMBER(p_job_status(3).value); l_job_status_bucket := TO_NUMBER(p_job_status(4).value); BEGIN select job_name, instance_guid, state_guid into l_job_name, l_paf_inst_guid, l_paf_state_guid from mgmt_paf_states where l_job_guid = job_id; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; BEGIN INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, 'PAF Received notification for job status change.' ); FOR l_oms_guid_rec IN l_paf_oms_status_cur LOOP l_oms_guid := l_oms_guid_rec.oms_guid; BEGIN MGMT_PAF_AQ.EXECUTE_SERVER_CMD ( 'str:oracle.sysman.pp.paf.dal.JobStatusUpdate:'||l_paf_inst_guid||';'||l_job_guid||';'||l_paf_state_guid||';'||l_job_status||';'||l_job_status_bucket, l_oms_guid, l_result, l_return_status ); IF l_return_status = MGMT_PAF_AQ.G_SUCCESS THEN INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, 'Successfully submitted job status change event to the Provisioning Engine.' ); -- If we are here, then there was no exception. -- So, exit out of the loop EXIT; ELSE l_message := 'Failed to submit job status change event to the Provisioning Engine.' || ' Return Status: ' || l_return_status; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, l_message ); END IF; EXCEPTION WHEN OTHERS THEN l_message := 'PAF Failed to submit job status change event to the Provisioning Engine.' || ' Error Code:' || SQLCODE || ' Message: ' || SQLERRM || ' Stack Trace: ' || dbms_utility.format_error_stack ; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( l_job_name, l_paf_inst_guid, l_paf_state_guid, l_job_guid, l_oms_guid, l_job_status, SYSDATE, l_message ); END NOTIFY_PAF_CHK_FOR_EXCEPTION; END LOOP; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN; END; EXCEPTION WHEN OTHERS THEN l_message := 'PAF Error while processing provision job update JOB ID:' || l_job_guid || ' Error Code:' || SQLCODE || ' Message: ' || SQLERRM || ' Stack Trace: ' || dbms_utility.format_error_stack ; INSERT INTO MGMT_PAF_NOTIFICATION_LOG VALUES ( NULL, NULL, NULL, NULL, NULL, NULL, SYSDATE, l_message ); END; END UPDATE_PAF_JOB; END MGMT_PAF_JOB_UTIL; / show errors;