Rem drv:
Rem
Rem $Header: rca_pkgdef.sql 01-sep-2007.07:05:08 denath Exp $
Rem
Rem basic_rca_pkgdef.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.
Rem
Rem NAME
Rem basic_rca_pkgdef.sql -
Rem
Rem DESCRIPTION
Rem
Rem
Rem NOTES
Rem
Rem
Rem MODIFIED (MM/DD/YY)
Rem denath 07/25/07 - fix 6219000. added pragma NO_NOTIF_AQ_MSG.
Rem denath 09/01/07 - Backport denath_bug-6219000 from main
Rem jriel 08/25/05 - fix NLS column label problem in comp tests
Rem scgrover 07/07/05 - add extended sql trace
Rem gsbhatia 07/01/05 - New repmgr header impl
Rem jriel 06/27/05 - add is test avail func
Rem jriel 06/08/05 - change get met detail
Rem jriel 06/02/05 - add delete task
Rem jriel 05/27/05 - const for rca status
Rem jriel 05/12/05 - no event result duplication
Rem jriel 05/05/05 -
Rem jriel 03/21/05 - fix svc affect
Rem chyu 03/18/05 - removing the echo off
Rem jriel 03/02/05 - add procedures for datagen
Rem jriel 01/19/05 - add del callbacks
Rem jriel 01/05/05 - fix LOV
Rem jriel 12/02/04 - add svc impacted
Rem jriel 11/22/04 - add types for results
Rem jriel 11/10/04 - add peek methods
Rem jriel 11/03/04 - fix notif content
Rem pmaddi 10/18/04 - Adding procedure to get the services impacted.
Rem pmaddi 10/15/04 -
Rem pmaddi 10/13/04 - Adding one mroe parameter to get_availabilty
Rem procedure for getting the severity guid.
Rem jriel 10/13/04 - add set interactive proc
Rem pmaddi 09/26/04 - Adding functionality to check for the target
Rem availabilty.
Rem pmaddi 09/25/04 - Checking if the target is a service type.
Rem jriel 08/30/04 - add cleanup procedures
Rem jriel 10/06/04 - fill in remove callbacks
Rem jriel 09/29/04 - get details procedure
Rem pmaddi 08/26/04 -
Rem jriel 08/30/04 - add cleanup procedures
Rem pmaddi 08/19/04 - pmaddi_rca_metrix
Rem pmaddi 07/29/04 - Adding more functions to rca.
Rem pmaddi 07/27/04 - Created
Rem
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100
CREATE OR REPLACE PACKAGE mgmt_rca AS
NO_NOTIF_AQ_MSG exception;
pragma exception_init(NO_NOTIF_AQ_MSG, -25263);
-- Package level Type Definition
TYPE cursorType IS REF CURSOR;
STATUS_SEVERITY CONSTANT NUMBER(1) := 0;
STATUS_TEST CONSTANT NUMBER(1) := 1;
METRIC_SEVERITY CONSTANT NUMBER(1) := 2;
METRIC_TEST CONSTANT NUMBER(1) := 3;
ROOT_CAUSE_TYPE CONSTANT NUMBER(1) := 1;
SERVICE_TYPE CONSTANT NUMBER(1) := 1;
SCOPE_SERVICE CONSTANT NUMBER(1) := 0;
SCOPE_COMPONENT CONSTANT NUMBER(1) := 1;
RCA_STATUS_CLOSED CONSTANT NUMBER(1) := 0;
RCA_STATUS_OPEN CONSTANT NUMBER(1) := 1;
EST_RCA_NAME CONSTANT VARCHAR(32) := 'EST_RCA';
PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN);
PROCEDURE purge_rca_results;
PROCEDURE store_rca_trace(event_guid_in IN RAW,
update_id_in IN NUMBER,
trace_doc_in IN VARCHAR2);
PROCEDURE get_alert_details(severity_guid_in IN RAW,
target_type_in IN VARCHAR2,
root_cause_only_in IN NUMBER,
is_service_type_out OUT NUMBER,
is_status_metric_out OUT NUMBER,
rca_dep_alert_details_out OUT cursorType,
rca_causes_alert_details_out OUT cursorType);
--
--Gets the RCA metric details
PROCEDURE get_affected_services_bymetric(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
metric_key_value_in IN VARCHAR2,
has_priv_out OUT NUMBER,
page_type_in IN VARCHAR2,
root_cause_only_in IN NUMBER,
start_date_in IN DATE,
end_date_in IN DATE,
rca_metric_test_results_out OUT cursorType);
PROCEDURE get_availability(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
root_cause_only_in IN NUMBER,
severity_guid_out OUT VARCHAR2,
is_service_down OUT NUMBER,
is_service_type_out OUT NUMBER,
rca_status_out OUT NUMBER,
rca_no_of_causes_out OUT NUMBER,
rca_no_of_serv_affected_out OUT NUMBER);
PROCEDURE get_failure_causes(severity_guid_in IN RAW,
update_id_in IN NUMBER,
rca_causeoffailure_cur_out OUT cursorType);
PROCEDURE get_affected_services_bysev(severity_guid_in IN RAW,
root_cause_only_in IN NUMBER,
rca_affected_services_cur_out OUT cursorType);
PROCEDURE get_affected_services_byname(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
num_days_in IN NUMBER,
root_cause_only_in IN NUMBER,
rca_affected_services_cur_out OUT cursorType);
PROCEDURE get_service_availability(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
current_status_out OUT NUMBER);
PROCEDURE check_target_privielges(service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
service_priv_out OUT NUMBER,
target_priv_out OUT NUMBER);
PROCEDURE get_fault_tests(service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
test_scope_in IN NUMBER,
fault_tests_cur_out OUT cursorType,
status_metric_cur_out OUT cursorType,
comp_host_test_cur_out OUT cursorType);
PROCEDURE get_metric_types(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_types_cur_out OUT cursorType);
PROCEDURE get_metrics_for_type(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_type_in IN NUMBER,
test_scope_in IN NUMBER,
metrics_cur_out OUT cursorType,
host_name_out OUT VARCHAR2);
PROCEDURE get_metric_detail(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
metric_guid_in IN RAW,
test_scope_in IN NUMBER,
trans_key_in IN VARCHAR2,
operator_out OUT NUMBER,
metric_detail_cur_out OUT cursorType,
host_name_out OUT VARCHAR2,
no_key_threshold_out OUT VARCHAR2,
metric_info_cur_out OUT cursorType);
PROCEDURE get_metric_detail_for_edit(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_test_guid_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
test_scope_in IN NUMBER,
num_keys_out OUT NUMBER,
metric_info_out OUT cursorType,
metric_detail_cur_out OUT cursorType,
host_name_out OUT VARCHAR2);
PROCEDURE create_fault_test(service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_guid_in IN RAW,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
threshold_in IN VARCHAR2,
operator_in IN NUMBER,
use_test_threshold_in IN NUMBER,
edit_type_in IN NUMBER,
metric_keys_array_in IN mgmt_medium_string_array,
test_scope_in IN NUMBER,
status_out OUT NUMBER);
PROCEDURE delete_rca_metric(metric_test_id_in IN VARCHAR2,
status_out OUT NUMBER);
PROCEDURE check_threshold_availabilty(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
key_value_in IN VARCHAR2,
test_scope_in IN NUMBER,
avail_status_out OUT NUMBER,
threshold_out OUT VARCHAR2);
FUNCTION get_threshold(target_guid_in IN RAW,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
key_value_in IN VARCHAR2,
prop_array IN mgmt_category_prop_array) return VARCHAR2;
FUNCTION concat_keys(num_keys IN NUMBER,
key_part1_value IN VARCHAR2,
key_part2_value IN VARCHAR2,
key_part3_value IN VARCHAR2,
key_part4_value IN VARCHAR2,
key_part5_value IN VARCHAR2) return VARCHAR2;
PROCEDURE get_all_metric_keys(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
key_part_in IN NUMBER,
filter_value_in IN VARCHAR2,
test_scope_in IN NUMBER,
metric_all_keys_out OUT cursorType,
host_name_out OUT VARCHAR2);
FUNCTION get_composite_keys(num_keys IN NUMBER,
key_part1_value IN VARCHAR2,
key_part2_value IN VARCHAR2,
key_part3_value IN VARCHAR2,
key_part4_value IN VARCHAR2,
key_part5_value IN VARCHAR2) return mgmt_medium_string_array;
PROCEDURE check_keys_availability(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
num_keys_in IN NUMBER,
metric_keys_array_in IN mgmt_medium_string_array,
test_scope_in IN NUMBER,
keys_status_out OUT VARCHAR2);
PROCEDURE check_user_permissions(service_guid_in IN RAW,
target_guid_in IN RAW,
permissions_out OUT NUMBER);
PROCEDURE delete_target_rca(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
target_guid_in IN RAW);
PROCEDURE delete_severity_rca(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
severity_guid_in IN RAW,
hasPriv OUT NUMBER);
PROCEDURE delete_rca_for_severity(severity_guid_in IN RAW, is_service IN NUMBER);
PROCEDURE delete_target_assoc_rca(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2);
PROCEDURE delete_fault_test(service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
metric_test_guid_in IN RAW,
hasPriv OUT NUMBER);
PROCEDURE get_rca_details(service_guid_in IN RAW,
severity_guid_in IN RAW,
update_id_in IN NUMBER,
last_id_in IN NUMBER,
root_cause_only_in IN NUMBER,
user_privilege_out OUT NUMBER,
last_id_out OUT NUMBER,
alert_summary_out OUT cursorType,
run_summary_out OUT cursorType,
rca_causes_out OUT cursorType,
rca_impacts_out OUT cursorType,
rca_trace_doc_out OUT CLOB);
PROCEDURE get_rca_history(service_guid_in IN RAW,
severity_guid_in IN RAW,
user_privilege_out OUT NUMBER,
alert_summary_out OUT cursorType,
rca_analysis_summary_out OUT cursorType,
rca_history_out OUT cursorType);
PROCEDURE get_alert_summary(severity_guid_in IN RAW,
alert_summary_out OUT cursorType);
PROCEDURE add_rca_event (event_source_guid_in IN RAW,
event_source_type_in IN NUMBER,
event_target_guid_in IN RAW,
collection_time_in IN DATE,
event_guid_out OUT RAW);
PROCEDURE associate_event_with_severity (event_guid_in IN RAW,
severity_guid_in IN RAW,
is_leaf_event_in IN NUMBER);
PROCEDURE set_rca_fault_test(
service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
metric_keys_array_in IN mgmt_medium_string_array,
override_sev_threshold_in IN NUMBER,
threshold_in IN VARCHAR2,
new_test_in IN NUMBER,
hasPriv OUT NUMBER);
PROCEDURE delete_rca_fault_test(
service_name_in IN VARCHAR2,
service_type_in IN VARCHAR2,
target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metric_name_in IN VARCHAR2,
metric_column_in IN VARCHAR2,
metric_keys_array_in IN mgmt_medium_string_array,
hasPriv OUT NUMBER);
PROCEDURE set_interactive_test(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
interactive_rca_in IN NUMBER,
hasPriv OUT NUMBER);
PROCEDURE get_interactive_test(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
interactive_rca_out OUT NUMBER);
FUNCTION get_scope_guid(service_guid_in IN RAW,
target_guid_in IN RAW,
test_scope_in IN NUMBER) RETURN RAW;
FUNCTION get_test_guid(target_guid_in IN RAW,
metric_guid_in IN RAW,
metric_keys_array_in IN mgmt_medium_string_array,
service_guid_in IN RAW,
test_scope_in IN NUMBER) RETURN RAW;
PROCEDURE get_possible_causes_detail(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
update_id_in IN NUMBER,
interactive_rca_out OUT NUMBER,
severity_guid_out OUT RAW,
rca_causeoffailure_cur_out OUT cursorType,
rca_info_cur_out OUT cursorType);
FUNCTION get_severity(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2) return raw;
PROCEDURE get_svcs_aff_count_byname(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
root_cause_only_in IN NUMBER,
svcs_affected_out OUT NUMBER);
PROCEDURE get_svcs_aff_count_bysev(severity_guid_in IN RAW,
root_cause_only_in IN NUMBER,
svcs_affected_out OUT NUMBER);
FUNCTION get_interactive_flag(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2)
RETURN NUMBER;
PROCEDURE get_sev_summary(service_guid_in IN RAW,
severity_guid_in IN RAW,
root_cause_only_in IN NUMBER,
hasPriv OUT NUMBER,
alert_summary_out OUT cursorType,
rca_impacts_out OUT cursorType);
PROCEDURE get_rca_notif_content(severity_guid_in IN RAW,
rca_causeoffailure_cur_out OUT cursorType,
target_timezone_out OUT VARCHAR2,
severity_timestamp_out OUT TIMESTAMP);
PROCEDURE queue_rca_task(target_guid_in IN RAW,
violation_guid_in IN RAW);
FUNCTION check_message(qname_in IN VARCHAR2, target_guid_in IN RAW, violation_guid_in IN RAW) RETURN NUMBER;
PROCEDURE remove_task(qname_in IN VARCHAR2, msgid_in IN RAW);
PROCEDURE remove_task_by_target(target_name_in IN VARCHAR2, target_type_in IN VARCHAR2, target_guid_in IN RAW);
PROCEDURE peek_message(qname_in IN VARCHAR2, notif_que_out OUT NOTIF_QUEUE_MSGS);
PROCEDURE get_rca_result_set(severity_array_in IN SEVERITY_GUID_ARRAY, results_out OUT RCA_RESULTS_ARRAY);
PROCEDURE handle_target_delete(p_target_name VARCHAR2,
p_target_type VARCHAR2,
p_target_guid RAW);
PROCEDURE queue_clear_cache(target_guid_in IN RAW);
PROCEDURE handle_assoc_delete(p_assoc_def_name IN VARCHAR2,
p_source_target_name IN VARCHAR2,
p_source_target_type IN VARCHAR2,
p_assoc_target_name IN VARCHAR2,
p_assoc_target_type IN VARCHAR2,
p_scope_target_name IN VARCHAR2,
p_scope_target_type IN VARCHAR2);
PROCEDURE validate_target_avail_metric( target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
p_type_meta_ver IN VARCHAR2,
p_category_prop_1 IN VARCHAR2,
p_category_prop_2 IN VARCHAR2,
p_category_prop_3 IN VARCHAR2,
p_category_prop_4 IN VARCHAR2,
p_category_prop_5 IN VARCHAR2);
PROCEDURE get_all_metrics(target_name_in IN VARCHAR2,
target_type_in IN VARCHAR2,
metrics_cur_out OUT cursorType);
FUNCTION IS_TEST_BASED_AVAIL ( p_service_target_name IN VARCHAR2,
p_service_target_type IN VARCHAR2)
RETURN NUMBER;
end mgmt_rca;
/