Rem Rem $Header: sdk_config_std_pkgbody.sql 13-oct-2006.12:11:03 groyal Exp $ Rem Rem sdk_config_std_pkgbody.sql Rem Rem Copyright (c) 2006, Oracle. All rights reserved. Rem Rem NAME Rem sdk_config_std_pkgbody.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem groyal 10/13/06 - Fix reference to uninitialized object Rem groyal 08/27/06 - Update mapping of between importance level and severity Rem rreilly 07/14/06 - Rem aragarwa 06/28/06 - finding severity value from rule importance Rem for event settings. Rem rreilly 06/20/06 - call to log_cs_evaluation_results needs Rem rqs_guid and cs_guid Rem aragarwa 06/01/06 - Adding standard export functionality. Rem aragarwa 05/17/06 - upcasing author. Rem aragarwa 05/01/06 - Adding support for reusable queries and cs Rem niramach 05/03/06 - 10.3 and 11g table unification related changes. Rem aragarwa 05/01/06 - Adding support for reusable queries and cs Rem inclusion. Rem niramach 04/11/06 - Add cs delete eval results api. Rem aragarwa 04/06/06 - Adding keyword list as parameter to create Rem standard. Rem groyal 03/31/06 - Add job related procedures Rem aragarwa 02/28/06 - reflecting XSD and table changes. Rem niramach 02/28/06 - Add context type for logging Rem kchiasso 02/22/06 - change owner to author Rem niramach 01/31/06 - Add the cs evaluation implementation. Rem aragarwa 01/27/06 - Adding methods for creating compliance repos. Rem niramach 01/13/06 - mgmt_config_std package body definition. Rem niramach 01/13/06 - Created Rem Rem **************************************************************************** Rem MGMT_CONFIG_STD package body Rem **************************************************************************** CREATE OR REPLACE PACKAGE BODY mgmt_config_std AS -- Generates the config standard guid FUNCTION generate_cs_guid( p_cs_author VARCHAR2, p_cs_iname VARCHAR2, p_cs_version VARCHAR2 ) RETURN RAW IS l_cs_guid MGMT_CS_CONFIG_STANDARD.cs_guid%TYPE; BEGIN l_cs_guid := DBMS_OBFUSCATION_TOOLKIT.md5( input => UTL_RAW.cast_to_raw(p_cs_iname || ';' || p_cs_author || ';'|| p_cs_version)); RETURN l_cs_guid; END generate_cs_guid; -- Looks up the config standard guid from the config standard table FUNCTION get_config_std_guid( p_config_std_name VARCHAR2, p_config_std_author VARCHAR2, p_version NUMBER ) RETURN RAW IS l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; BEGIN BEGIN SELECT cs_guid INTO l_cs_guid FROM mgmt_cs_config_standard WHERE author = p_config_std_author AND cs_iname = p_config_std_name AND version = p_version; EXCEPTION WHEN NO_DATA_FOUND THEN raise_application_error(MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR, 'Configuration Standard not found: ' || p_config_std_author || '.' || p_config_std_name || '.' || p_version); END; RETURN l_cs_guid; END get_config_std_guid; -- Create a configuration standard FUNCTION create_config_standard( p_cs_iname VARCHAR2, p_cs_dname VARCHAR2, p_version NUMBER, p_target_type VARCHAR2, p_author VARCHAR2 DEFAULT 'ORACLE', p_lifecycle_status NUMBER DEFAULT MGMT_CONFIG_STD.G_LIFECYCLE_PRODUCTION, p_description VARCHAR2 DEFAULT NULL, p_reference_url VARCHAR2 DEFAULT NULL, p_front_matter VARCHAR2 DEFAULT NULL, p_rear_matter VARCHAR2 DEFAULT NULL, p_notice VARCHAR2 DEFAULT NULL, p_is_hidden NUMBER DEFAULT MGMT_GLOBAL.G_FALSE, p_is_locked NUMBER DEFAULT MGMT_GLOBAL.G_FALSE, p_usage_type NUMBER DEFAULT MGMT_CONFIG_STD.G_USAGE_TYPE_CON, p_keyword_list MGMT_SHORT_STRING_TABLE DEFAULT NULL, p_param_list MGMT_CS_PARAM_ARRAY DEFAULT NULL, p_reusable_query_list MGMT_CS_REUSABLE_QUERY_ARRAY DEFAULT NULL /* p_access_list MGMT_CS_ACCESS_ARRAY DEFAULT NULL */ ) RETURN RAW IS l_cs_found NUMBER := 0; l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; l_current_user mgmt_created_users.user_name%TYPE; l_owner mgmt_cs_config_standard.owner%TYPE; l_author VARCHAR2(256); l_keyword VARCHAR2(64); l_locked_by mgmt_created_users.user_name%TYPE; l_locked_time DATE; l_param MGMT_CS_PARAM_DEF; l_param_type NUMBER; l_reusable_query MGMT_CS_REUSABLE_QUERY_DEF; /* l_access MGMT_CS_ACCESS_DEF; */ BEGIN -- Check for NULLs EM_CHECK.check_not_null(p_cs_iname, 'p_cs_iname'); EM_CHECK.check_not_null(p_target_type, 'p_target_type'); -- upcasing author and saving it to repository. l_author := upper(p_author); l_current_user := MGMT_USER.GET_CURRENT_EM_USER; l_owner := l_current_user; IF (l_author = MGMT_GLOBAL.G_AUTHOR_ORACLE) THEN l_owner := ''; END IF; IF ( p_is_locked = MGMT_GLOBAL.G_TRUE ) THEN l_locked_by := l_current_user ; l_locked_time := SYSDATE ; END IF; -- Check if the configuration standard already exist BEGIN SELECT COUNT(1) INTO l_cs_found FROM mgmt_cs_config_standard WHERE cs_iname = p_cs_iname AND author = l_author AND version = p_version; EXCEPTION WHEN NO_DATA_FOUND THEN l_cs_found := 0; END; IF (l_cs_found > 0) THEN raise_application_error(MGMT_GLOBAL.CS_ALREADY_EXISTS_ERR, 'Configuration Standard: ' || l_author || '.' || p_cs_iname || '.' || p_version || ' already exists.'); END IF; -- Generate config standard guid l_cs_guid := generate_cs_guid( p_cs_author => l_author, p_cs_iname => p_cs_iname, p_cs_version => p_version); -- Insert config standard row INSERT INTO mgmt_cs_config_standard (cs_guid, cs_iname, cs_dname, target_type, last_updated_by, author, owner, version, lifecycle_status,usage_type, is_locked, locked_by, locked_time, is_hidden, description, reference_url, front_matter, rear_matter, notice) VALUES (l_cs_guid, p_cs_iname, p_cs_dname, p_target_type, l_owner, l_author, l_owner, p_version, p_lifecycle_status, p_usage_type, p_is_locked, l_locked_by, l_locked_time, p_is_hidden, p_description, p_reference_url, p_front_matter, p_rear_matter, p_notice); /* BEGIN -- Enter super-user mode. This is necessary because this operation -- involves making calls to the security system that only super-users -- are allowed to make SETEMUSERCONTEXT(MGMT_USER.get_repository_owner, MGMT_USER.OP_SET_IDENTIFIER); -- Grant full access to owner MGMT_USER.grant_priv( grantee_in => l_current_user, priv_name_in => MGMT_USER.FULL_CONFIG_STD, guid_in => l_cs_guid); -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN IF l_current_user IS NOT NULL THEN SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END IF; RAISE; END; IF ((p_access_list IS NOT NULL) AND (p_access_list.COUNT > 0)) THEN FOR acc_ctr IN p_access_list.FIRST..p_access_list.LAST LOOP l_access := p_access_list(acc_ctr); MGMT_USER.grant_priv( grantee_in => l_access.user_name, priv_name_in => l_access.privilege, guid_in => l_cs_guid); END LOOP; END IF; */ -- Insert into keywords IF ((p_keyword_list IS NOT NULL) AND (p_keyword_list.COUNT > 0)) THEN FOR keyword_ctr IN p_keyword_list.FIRST..p_keyword_list.LAST LOOP l_keyword := p_keyword_list(keyword_ctr); EM_CONFIG_STD.add_keyword( p_cs_guid => l_cs_guid, p_keyword => l_keyword); END LOOP; END IF; -- Insert into parameters IF ((p_param_list IS NOT NULL) AND (p_param_list.COUNT > 0)) THEN FOR param_ctr IN p_param_list.FIRST..p_param_list.LAST LOOP l_param := p_param_list(param_ctr); EM_CONFIG_STD.add_parameter( p_cs_guid => l_cs_guid, p_param_iname => l_param.param_name, p_description => l_param.description, p_param_type => l_param.param_type, p_param_value => l_param.value, p_prohibit_changes => l_param.prohibit_changes, p_lower_bound => l_param.lower_bound, p_upper_bound => l_param.upper_bound, p_choice_list => l_param.choice_list, p_must_match => l_param.must_match); END LOOP; END IF; -- Insert into reusable queries IF ((p_reusable_query_list IS NOT NULL) AND (p_reusable_query_list.COUNT > 0)) THEN FOR reusable_query_ctr IN p_reusable_query_list.FIRST..p_reusable_query_list.LAST LOOP l_reusable_query := p_reusable_query_list(reusable_query_ctr); EM_CONFIG_STD.add_reusable_query( p_cs_guid => l_cs_guid, p_query_iname => l_reusable_query.query_name, p_description => l_reusable_query.description, p_query => l_reusable_query.query); END LOOP; END IF; RETURN l_cs_guid ; END create_config_standard; -- Add a rule folder to a configuration standard. FUNCTION add_rulefolder( p_cs_guid VARCHAR2, p_rulefolder_iname VARCHAR2, p_rulefolder_dname VARCHAR2, p_parent_guid VARCHAR2, p_child_position NUMBER, p_description VARCHAR2 DEFAULT NULL, p_reference_url VARCHAR2 DEFAULT NULL ) RETURN RAW IS l_rulefolder_guid mgmt_cs_rulefolder.rulefolder_guid%TYPE; BEGIN l_rulefolder_guid := EM_CONFIG_STD.add_rulefolder( p_cs_guid => p_cs_guid, p_rulefolder_iname => p_rulefolder_iname, p_parent_guid => p_parent_guid, p_child_position => p_child_position, p_rulefolder_dname => p_rulefolder_dname, p_description => p_description, p_reference_url => p_reference_url); RETURN l_rulefolder_guid; END add_rulefolder; -- Add a rule to a configuration standard. FUNCTION add_rule( p_cs_guid VARCHAR2, p_rule_iname VARCHAR2, p_rule_dname VARCHAR2, p_parent_guid VARCHAR2, p_child_position NUMBER, p_test VARCHAR2 DEFAULT NULL, p_importance_level NUMBER DEFAULT MGMT_CONFIG_STD.G_IMPORTANCE_NORMAL, p_test_type NUMBER DEFAULT NULL, p_reference_url VARCHAR2 DEFAULT NULL, p_description VARCHAR2 DEFAULT NULL, p_rationale VARCHAR2 DEFAULT NULL, p_fixtext VARCHAR2 DEFAULT NULL, p_warning VARCHAR2 DEFAULT NULL, p_message VARCHAR2 DEFAULT NULL, p_clear_message VARCHAR2 DEFAULT NULL, p_violctx_list MGMT_CS_VIOL_COLUMN_ARRAY DEFAULT NULL, p_fixlink_list MGMT_CS_FIX_LINK_ARRAY DEFAULT NULL, p_change_advisor_tag MGMT_CS_CHANGE_ADVISOR_ARRAY DEFAULT NULL, p_simple_test_list MGMT_CS_SIMPLE_TEST_ARRAY DEFAULT NULL, p_simple_test_condition NUMBER DEFAULT NULL ) RETURN RAW IS l_rule_guid mgmt_cs_rule.rule_guid%TYPE ; l_policy_guid RAW(16) := NULL; l_cs_guid RAW(16); l_cs_iname VARCHAR2(128); l_cs_author VARCHAR2(256); l_cs_version NUMBER; l_violctx MGMT_CS_VIOL_COLUMN_DEF; l_fixlink MGMT_CS_FIX_LINK_DEF; l_change_advisor_def MGMT_CS_CHANGE_ADVISOR_DEF; l_simple_test_def MGMT_CS_SIMPLE_TEST_DEF; l_severity NUMBER(1); l_cs_target_type VARCHAR2(128); BEGIN l_cs_guid := HEXTORAW(p_cs_guid); IF ((p_change_advisor_tag IS NOT NULL) AND (p_change_advisor_tag.COUNT > 0)) THEN l_change_advisor_def := p_change_advisor_tag(1); END IF; IF (p_test_type = MGMT_CONFIG_STD.G_TEST_TYPE_POLICY) THEN BEGIN --create a new cs policy which is copy of the original policy. --policy guid is OUT parameter, so l_policy_guid will have the --new policy guid after the procedure call. SELECT cs_iname, author, version, target_type INTO l_cs_iname, l_cs_author, l_cs_version, l_cs_target_type FROM mgmt_cs_config_standard WHERE cs_guid = p_cs_guid; EM_POLICY.CREATE_CS_POLICY(p_policy_name => p_test, p_target_type => l_cs_target_type, p_config_std_name => l_cs_iname, p_rule_name => p_rule_iname, p_author => l_cs_author, p_version => l_cs_version, p_cs_policy_guid => l_policy_guid); EXCEPTION --TODO:If required handle exception WHEN NO_DATA_FOUND THEN NULL; END; END IF ; -- A 11g type rule may not have IF ((p_test_type = MGMT_CONFIG_STD.G_TEST_TYPE_POLICY) AND ((p_change_advisor_tag IS NOT NULL) AND (p_change_advisor_tag.COUNT > 0)) OR ((p_fixlink_list IS NOT NULL) AND (p_fixlink_list.COUNT > 0)) OR ((p_simple_test_list IS NOT NULL) AND (p_simple_test_list.COUNT > 0)) OR ((p_violctx_list IS NOT NULL) AND (p_violctx_list.COUNT > 0))) THEN raise_application_error(MGMT_GLOBAL.CS_POLICY_RULE_TAG_ERR, 'Configuration Standard: ' || l_cs_author || '.' || l_cs_iname || '.' || l_cs_version || MGMT_GLOBAL.CS_POLICY_RULE_TAG_ERR_M); END IF; -- finding severity value based on rule importance. l_severity := CASE p_importance_level WHEN MGMT_CONFIG_STD.G_IMPORTANCE_NOT_SCORED THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO WHEN MGMT_CONFIG_STD.G_IMPORTANCE_EXTREMELY_LOW THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO WHEN MGMT_CONFIG_STD.G_IMPORTANCE_LOW THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO WHEN MGMT_CONFIG_STD.G_IMPORTANCE_NORMAL THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_WARNING WHEN MGMT_CONFIG_STD.G_IMPORTANCE_HIGH THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_CRITICAL WHEN MGMT_CONFIG_STD.G_IMPORTANCE_EXTREMELY_HIGH THEN MGMT_CONFIG_STD.G_VIOL_SEVERITY_CRITICAL ELSE MGMT_CONFIG_STD.G_VIOL_SEVERITY_INFO END; l_rule_guid := EM_CONFIG_STD.add_rule( p_cs_guid => p_cs_guid, p_rule_iname => p_rule_iname, p_rule_dname => p_rule_dname, p_parent_guid => p_parent_guid, p_child_position => p_child_position, p_test => p_test, p_importance_level => p_importance_level, p_test_type => p_test_type, p_reference_url => p_reference_url, p_description => p_description, p_rationale => p_rationale, p_fixtext => p_fixtext, p_warning => p_warning, p_message => p_message, p_clear_message => p_clear_message, p_severity => l_severity, p_policy_guid => l_policy_guid, p_change_advisor_tag => l_change_advisor_def, p_simple_test_condition => p_simple_test_condition); -- Insert into simple test table. IF ((p_simple_test_list IS NOT NULL) AND (p_simple_test_list.COUNT > 0)) THEN FOR simple_test_ctr IN p_simple_test_list.FIRST..p_simple_test_list.LAST LOOP l_simple_test_def := p_simple_test_list(simple_test_ctr); EM_CONFIG_STD.add_simple_test( p_rule_guid => l_rule_guid, p_property => l_simple_test_def.property, p_operator => l_simple_test_def.operator, p_value => l_simple_test_def.value, p_position => l_simple_test_def.position); END LOOP; END IF; -- Insert into fix links IF ((p_fixlink_list IS NOT NULL) AND (p_fixlink_list.COUNT > 0)) THEN FOR fixlink_ctr IN p_fixlink_list.FIRST..p_fixlink_list.LAST LOOP l_fixlink := p_fixlink_list(fixlink_ctr); EM_CONFIG_STD.add_fixlink( p_rule_guid => l_rule_guid, p_display_label => l_fixlink.display_label, p_link_template => l_fixlink.link_template, p_link_encode => l_fixlink.link_encode, p_is_link_empage => l_fixlink.is_link_em_page); END LOOP; END IF; -- Insert into violation context IF ((p_violctx_list IS NOT NULL) AND (p_violctx_list.COUNT > 0)) THEN FOR violctx_ctr IN p_violctx_list.FIRST..p_violctx_list.LAST LOOP l_violctx := p_violctx_list(violctx_ctr); EM_CONFIG_STD.add_violation_context( p_rule_guid => l_rule_guid, p_column_iname => l_violctx.column_iname, p_column_dname => l_violctx.column_dname, p_column_type => 2, p_column_position => violctx_ctr, p_is_key => l_violctx.is_key, p_is_hidden => l_violctx.is_hidden, p_link_template => l_violctx.link_value, p_link_encode => l_violctx.link_encode, p_is_empage => l_violctx.is_empage); END LOOP; END IF; RETURN l_rule_guid; END add_rule; -- Add an inclusion to a configuration standard. PROCEDURE add_include_std( p_cs_guid VARCHAR2, p_cs_iname VARCHAR2, p_cs_author VARCHAR2, p_cs_version NUMBER, p_context_query VARCHAR2 DEFAULT NULL, p_override_params MGMT_CS_PARAM_OVER_ARRAY DEFAULT NULL, p_parent_guid VARCHAR2, p_child_position NUMBER ) IS l_override_param MGMT_CS_PARAM_OVER_DEF; l_oparam_ctr NUMBER; l_cs_inclusion_guid RAW(16); l_cs_guid RAW(16); l_parent_guid RAW(16); l_cs_author VARCHAR2(256); l_param_value VARCHAR2(1024); BEGIN l_cs_guid := HEXTORAW(p_cs_guid); l_parent_guid := HEXTORAW(p_parent_guid); l_cs_author := upper(p_cs_author); INSERT INTO mgmt_cs_inclusion ( parent_cs_guid, included_cs_iname,included_cs_author, included_cs_version,context_query) VALUES ( l_cs_guid,p_cs_iname,l_cs_author,p_cs_version, p_context_query ) RETURNING cs_inclusion_guid INTO l_cs_inclusion_guid ; INSERT INTO mgmt_cs_hierarchy (parent_guid, child_guid, child_type, child_position) VALUES (l_parent_guid, l_cs_inclusion_guid, MGMT_CONFIG_STD.G_ELEMENT_INCLUSION, p_child_position); -- Insert into Overridden Parameters. IF ((p_override_params IS NOT NULL) AND (p_override_params.COUNT > 0)) THEN FOR l_oparam_ctr IN p_override_params.FIRST..p_override_params.LAST LOOP l_override_param := p_override_params(l_oparam_ctr); l_param_value := l_override_param.parameter_value ; IF ( l_override_param.parameter_reference IS NOT NULL) THEN BEGIN SELECT param_value INTO l_param_value FROM mgmt_cs_parameter WHERE param_iname = l_override_param.parameter_reference AND cs_guid = l_cs_guid ; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; END IF; -- mgmt_log.log_error( -- MGMT_USER.USERMODEL_MODULE_NAME, -- MGMT_GLOBAL.CALLBACK_FAILED_ERR,'ArunAgarwal : ' || l_param_value ); EM_CONFIG_STD.add_overridden_parameter( p_cs_inclusion_guid => l_cs_inclusion_guid, p_name => l_override_param.parameter_name, p_value => l_param_value, p_reference => l_override_param.parameter_reference); END LOOP; END IF; END add_include_std; -- Delete a configuration standard. PROCEDURE delete_config_standard( p_cs_iname VARCHAR2, p_version NUMBER, p_author VARCHAR2 DEFAULT 'ORACLE') IS l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; l_current_user mgmt_created_users.user_name%TYPE; BEGIN l_current_user := MGMT_USER.GET_CURRENT_EM_USER; -- Check for NULLs EM_CHECK.check_not_null(p_author, 'p_author'); EM_CHECK.check_not_null(p_cs_iname, 'p_cs_iname'); l_cs_guid := get_config_std_guid( p_config_std_name => p_cs_iname, p_config_std_author => p_author, p_version => p_version); /* -- Only users with FULL CONFIG_STD privilege can delete configuration standards IF (MGMT_USER.has_priv(l_current_user, MGMT_USER.FULL_CONFIG_STD, l_cs_guid) = MGMT_USER.USER_DOES_NOT_HAVE_PRIV) THEN raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR, 'Only users with FULL CONFIGURATION STANDARD privilege can delete configuration standards.'); END IF; */ -- Delete configuration standard EM_CONFIG_STD.delete_cs_data(l_cs_guid); -- Delete configuration standard EM_CONFIG_STD.delete_cs_metadata(l_cs_guid); END delete_config_standard; -- -- PURPOSE -- -- To evaluate the given configuration standard for bunch of targets. -- -- Parameters -- p_config_std_namee : Configuration Standard Name -- p_config_std_author : Configuration Standard Author -- p_version : Version of the configuration standard -- p_targets_list : MGMT_TARGET_ARRAY having the list of MGMT_TARGET_OBJ -- PROCEDURE evaluate_config_std( p_config_std_name IN VARCHAR2, p_config_std_author IN VARCHAR2, p_version IN NUMBER DEFAULT 1, p_targets_list IN MGMT_TARGET_ARRAY) IS l_metrics MGMT_SHORT_STRING_TABLE := MGMT_SHORT_STRING_TABLE(); l_metric mgmt_metrics.metric_name%TYPE; l_start_time mgmt_cs_eval_summ_rqs.last_evaluation_date%TYPE := SYSDATE; l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; l_target_type mgmt_cs_config_standard.target_type%TYPE; l_context_type emdw_trace_config.context_type%TYPE; l_repo_timing_enabled mgmt_cs_config_standard.repo_timing_enabled%TYPE; BEGIN --Hold the current log context type l_context_type := EMDW_LOG.get_context_type(); --set the log context EMDW_LOG.set_context(v_context_type=>G_EMDW_LOG_CTX); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('evaluate_config_std:Enter',G_MODULE_NAME) ; EMDW_LOG.DEBUG('evaluate_config_std:Config Std Name='||p_config_std_name|| ' Config Std Author='||p_config_std_author || ' Version=' || p_version,G_MODULE_NAME) ; END IF ; --validate parameters EM_CHECK.check_not_null(p_config_std_name, 'p_config_std_name'); EM_CHECK.check_not_null(p_config_std_author, 'p_config_std_author'); --get the config guid for the given config std name,author and version BEGIN SELECT cs_guid,target_type,repo_timing_enabled INTO l_cs_guid,l_target_type,l_repo_timing_enabled FROM mgmt_cs_config_standard WHERE cs_iname = p_config_std_name AND author = p_config_std_author AND version = p_version; EXCEPTION WHEN NO_DATA_FOUND THEN IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_config_std:Exit Exception : '|| MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR_M||'Config Std Name='|| p_config_std_name || ' author=' || p_config_std_author || ' version='|| p_version,G_MODULE_NAME) ; END IF ; raise_application_error(MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR, MGMT_GLOBAL.CS_DOES_NOT_EXIST_ERR_M||'Config Std Name='|| p_config_std_name || ' author=' || p_config_std_author || ' version='|| p_version); END; --Determine list of distinct metrics select distinct(m.metric_name) bulk collect into l_metrics from mgmt_cs_rule r, mgmt_policies p, mgmt_metrics m where r.cs_guid = l_cs_guid and p.policy_guid = r.policy_guid and m.metric_guid = p.metric_guid; IF ((l_metrics IS NOT NULL) AND (l_metrics.COUNT > 0)) THEN FOR metrics_ctr IN l_metrics.FIRST..l_metrics.LAST LOOP l_metric := l_metrics(metrics_ctr); --Run collection for each metric --This procedure in turn evaluates the corresponding CS policies --and logs the evaluation results to policy evaluation related tables. MGMT_COLLECTION.RUN_COLLECTION( p_targets_list => p_targets_list, p_metric_name => l_metric, p_coll_name => l_metric, p_store_metric => MGMT_GLOBAL.G_FALSE, p_config_std_name => p_config_std_name, p_config_std_author => p_config_std_author, p_config_std_version => p_version); END LOOP; ELSE IF EMDW_LOG.P_IS_ERROR_SET THEN EMDW_LOG.ERROR('evaluate_config_std:Exit Exception : '|| MGMT_GLOBAL.NO_METRICS_FOUND_ERR_M||'Config Std Name='|| p_config_std_name || ' author=' || p_config_std_author || ' version='|| p_version, G_MODULE_NAME) ; raise_application_error(MGMT_GLOBAL.NO_METRICS_FOUND_ERR, MGMT_GLOBAL.NO_METRICS_FOUND_ERR_M||'Config Std Name='|| p_config_std_name || ' author=' || p_config_std_author || ' version='|| p_version); END IF; END IF; --Invoke em_config_std procedure to compute compliance score and log cs --evaluation results to cs evaluation summary tables. EM_CONFIG_STD.LOG_CS_EVALUATION_RESULTS ( p_config_std_name => p_config_std_name, p_rqs_guid => l_cs_guid, p_cs_guid => l_cs_guid, p_target_type => l_target_type, p_targets_list => p_targets_list, p_repo_timing_enabled => l_repo_timing_enabled, p_start_time => l_start_time); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('evaluate_config_std:Exit Normal',G_MODULE_NAME) ; END IF ; --reset the log context back to previous context. EMDW_LOG.set_context(v_context_type=>l_context_type); END evaluate_config_std; -- Submit the evaluation of a configuration standard. PROCEDURE submit_job( p_cs_guid IN RAW, p_eval_name IN VARCHAR2, p_description IN VARCHAR2, p_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) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_target_lists MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); BEGIN BEGIN -- Enter super-user mode. This is necessary because this operation -- involves making calls to the security system that only super-users -- are allowed to make SETEMUSERCONTEXT(MGMT_USER.get_repository_owner, MGMT_USER.OP_SET_IDENTIFIER); MGMT_JOBS.submit_job(p_job_name => p_eval_name, p_description => p_description, p_job_type => MGMT_CONFIG_STD.G_COMPLIANCE_JOBTYPE, p_job_targets => l_target_lists, p_job_params => p_params, p_schedule => p_schedule, p_job_id_out => p_job_id_out, p_execution_id_out => p_execution_id_out, p_owner => null, p_system_job => MGMT_JOB_ENGINE.SYSTEM_JOB_RETRY, p_job_creds => null, p_job_target_type => null, p_job_notify_states => null); -- Grant full access to current user MGMT_USER.grant_priv(grantee_in => l_current_user, priv_name_in => MGMT_USER.FULL_JOB, guid_in => p_job_id_out); INSERT INTO mgmt_cs_scheduled_eval ( cs_guid, job_id, eval_name, eval_owner, eval_description) VALUES ( p_cs_guid, p_job_id_out, upper(p_eval_name), l_current_user, p_description); -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN IF l_current_user IS NOT NULL THEN SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END IF; RAISE; END; END submit_job; -- Retrieve settings of a scheduled evaluation. PROCEDURE get_job_info( p_job_id IN VARCHAR2, p_eval_name_out OUT VARCHAR2, p_description_out OUT VARCHAR2, p_params_out OUT MGMT_JOB_PARAM_LIST, p_schedule_out OUT MGMT_JOB_SCHEDULE_RECORD) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_job_owner MGMT_JOB.job_owner%TYPE := MGMT_USER.get_repository_owner; BEGIN BEGIN -- Enter super-user mode. This is necessary because this operation -- involves making calls to the security system that only super-users -- are allowed to make SETEMUSERCONTEXT(MGMT_USER.get_repository_owner, MGMT_USER.OP_SET_IDENTIFIER); SELECT job_name, job_description INTO p_eval_name_out, p_description_out FROM MGMT_JOB WHERE job_id = p_job_id; -- Parameter information SELECT MGMT_JOB_PARAM_RECORD(parameter_name, parameter_type, MGMT_JOB_ENGINE.decrypt_scalar(encrypted, scalar_value), MGMT_JOB_ENGINE.decrypt_vector(encrypted, vector_value)) BULK COLLECT INTO p_params_out FROM MGMT_JOB_PARAMETER WHERE job_id = p_job_id AND execution_id = MGMT_JOB_ENGINE.NO_EXECUTION; -- Schedule 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 p_schedule_out FROM MGMT_JOB_SCHEDULE s, MGMT_JOB j WHERE s.schedule_id = j.schedule_id AND j.job_id = p_job_id; -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN IF l_current_user IS NOT NULL THEN SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END IF; RAISE; END; END get_job_info; -- Update settings of a scheduled evaluation. PROCEDURE edit_job( p_job_id IN VARCHAR2, p_description IN VARCHAR2, p_params IN MGMT_JOB_PARAM_LIST, p_schedule IN MGMT_JOB_SCHEDULE_RECORD) IS l_current_user VARCHAR2(256) := MGMT_USER.get_current_em_user(); l_job_owner MGMT_JOB.job_owner%TYPE := MGMT_USER.get_repository_owner; l_target_lists MGMT_JOB_TARGET_LIST := MGMT_JOB_TARGET_LIST(); BEGIN BEGIN -- Enter super-user mode. This is necessary because this operation -- involves making calls to the security system that only super-users -- are allowed to make SETEMUSERCONTEXT(MGMT_USER.get_repository_owner, MGMT_USER.OP_SET_IDENTIFIER); MGMT_JOB_ENGINE.edit_job(p_job_id => p_job_id, p_description => p_description, p_params => p_params, p_targets => l_target_lists, p_schedule => p_schedule); UPDATE mgmt_cs_scheduled_eval SET eval_description = p_description WHERE job_id = p_job_id; -- Revert back to being the same user as we entered SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); EXCEPTION WHEN OTHERS THEN IF l_current_user IS NOT NULL THEN SETEMUSERCONTEXT(l_current_user, MGMT_USER.OP_SET_IDENTIFIER); END IF; RAISE; END; END edit_job; -- Delete a scheduled evaluation. PROCEDURE delete_job(p_job_id IN VARCHAR2) IS BEGIN MGMT_JOB_ENGINE.stop_all_executions_with_id(p_job_id => p_job_id); END delete_job; -- Callback procedure for cleaning up of the saved schedule evaluations associated with -- the evaluation job being deleted. PROCEDURE on_comp_job_delete_callback( p_reason NUMBER, p_status NUMBER, p_job_id RAW, p_execution_id RAW) IS BEGIN DELETE FROM mgmt_cs_scheduled_eval WHERE job_id = p_job_id; COMMIT; END on_comp_job_delete_callback; -- -- PURPOSE -- -- To delete the evaluation results of the given configuration standard for all -- the targets for which the user has the operator target privileges. -- -- Parameters -- p_config_std_namee : Configuration Standard Name -- p_config_std_owner : Configuration Standard Owner -- p_version : Version of the configuration standard -- PROCEDURE delete_cs_eval_results ( p_config_std_name VARCHAR2, p_config_std_author VARCHAR2, p_config_std_version NUMBER) IS l_cs_guid mgmt_cs_config_standard.cs_guid%TYPE; l_context_type emdw_trace_config.context_type%TYPE; BEGIN --Hold the current log context type l_context_type := EMDW_LOG.get_context_type(); --set the log context EMDW_LOG.set_context(v_context_type=>G_EMDW_LOG_CTX); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('delete_cs_eval_results:Enter',G_MODULE_NAME) ; EMDW_LOG.DEBUG('delete_cs_eval_results:Config Std Name='||p_config_std_name|| ' Config Std Author='||p_config_std_author || ' Version=' || p_config_std_version ,G_MODULE_NAME) ; END IF ; -- Check for NULLs EM_CHECK.check_not_null(p_config_std_name, 'p_config_std_name'); EM_CHECK.check_not_null(p_config_std_author, 'p_config_std_author'); EM_CHECK.check_not_null(p_config_std_version, 'p_config_std_version'); l_cs_guid := get_config_std_guid( p_config_std_name => p_config_std_name, p_config_std_author => p_config_std_author, p_version => p_config_std_version); EM_CONFIG_STD.delete_cs_eval_results(p_config_std_guid => l_cs_guid); END delete_cs_eval_results; -- -- PURPOSE -- -- To delete the evaluation results of the given configuration standard for all targets -- for which the user has operator target privileges. -- -- Parameters -- p_config_std_guid : Configuration Standard GUID -- PROCEDURE delete_cs_eval_results (p_config_std_guid RAW) IS l_context_type emdw_trace_config.context_type%TYPE; BEGIN --Hold the current log context type l_context_type := EMDW_LOG.get_context_type(); --set the log context EMDW_LOG.set_context(v_context_type=>G_EMDW_LOG_CTX); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('delete_cs_eval_results:Enter',G_MODULE_NAME) ; EMDW_LOG.DEBUG('delete_cs_eval_results:Config Std Id='||p_config_std_guid,G_MODULE_NAME) ; END IF ; -- Check for NULLs EM_CHECK.check_not_null(p_config_std_guid, 'p_config_std_guid'); EM_CONFIG_STD.delete_cs_eval_results (p_config_std_guid => p_config_std_guid); IF EMDW_LOG.P_IS_DEBUG_SET THEN EMDW_LOG.DEBUG('delete_cs_eval_results:Exit Normal',G_MODULE_NAME) ; END IF ; --reset the log context back to previous context. EMDW_LOG.set_context(v_context_type=>l_context_type); END delete_cs_eval_results; -- -- PURPOSE -- -- To export the standard specified by standard name, author and version -- returns an object of standard type and this will be read in java to -- create xml file out of this. -- -- Parameters -- p_cs_iname : Configuration Standard Name -- p_cs_author : Configuration Standard Author -- p_cs_version : Configuration Standard Version -- FUNCTION export_config_standard( p_cs_iname VARCHAR2, p_cs_author VARCHAR2, p_cs_version VARCHAR2 ) RETURN MGMT_CS_STANDARD_DEF IS l_cs_guid MGMT_CS_CONFIG_STANDARD.cs_guid%TYPE; l_param_guids MGMT_CS_GUID_ARRAY; l_std MGMT_CS_STANDARD_DEF; l_keyword_list MGMT_SHORT_STRING_TABLE; l_param MGMT_CS_PARAM_DEF; l_param_list MGMT_CS_PARAM_ARRAY; l_choice_list MGMT_CS_CHOICE_LIST_ARRAY; l_reusable_query_list MGMT_CS_REUSABLE_QUERY_ARRAY; l_param_guid RAW(16); l_param_ctr NUMBER := 0; BEGIN -- Generate config standard guid l_cs_guid := generate_cs_guid( p_cs_author => p_cs_author, p_cs_iname => p_cs_iname, p_cs_version => p_cs_version); -- Initializing various arrays/lists. l_param_list := MGMT_CS_PARAM_ARRAY(); /* Debugging code.... mgmt_log.log_error( MGMT_USER.USERMODEL_MODULE_NAME, MGMT_GLOBAL.CALLBACK_FAILED_ERR,'ArunAgarwal : ' || l_cs_guid ); */ -- aragarwa : get keywords, parameters and reusable queries for this standard. -- Keywords SELECT keyword BULK COLLECT INTO l_keyword_list FROM mgmt_cs_keyword WHERE cs_guid = l_cs_guid; -- Parameters -- first collect all guids SELECT param_guid BULK COLLECT INTO l_param_guids FROM MGMT_CS_PARAMETER WHERE cs_guid = l_cs_guid; -- use these guids for getting relevant information. IF ((l_param_guids IS NOT NULL) AND (l_param_guids.COUNT > 0)) THEN FOR l_param_ctr IN l_param_guids.FIRST..l_param_guids.LAST LOOP l_param_guid := l_param_guids(l_param_ctr); mgmt_log.log_error( MGMT_USER.USERMODEL_MODULE_NAME, MGMT_GLOBAL.CALLBACK_FAILED_ERR,'ArunAgarwal : ' || l_param_guid ); SELECT choice_value BULK COLLECT INTO l_choice_list FROM mgmt_cs_parameter_choices WHERE param_guid = l_param_guid; BEGIN SELECT MGMT_CS_PARAM_DEF( param_iname,description,param_type,param_value, prohibit_changes,lower_bound,upper_bound, l_choice_list, must_match) INTO l_param FROM mgmt_cs_parameter WHERE param_guid = l_param_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; l_param_list.EXTEND ; l_param_list(l_param_ctr) := l_param ; END LOOP; END IF; -- Reusable queries BEGIN SELECT MGMT_CS_REUSABLE_QUERY_DEF(reusable_query_iname, description, reusable_query) BULK COLLECT INTO l_reusable_query_list FROM mgmt_cs_reusable_query WHERE cs_guid = l_cs_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- get the cs data in CS def object. SELECT MGMT_CS_STANDARD_DEF(cs_iname, cs_dname, target_type, author, version,is_locked, lifecycle_status, usage_type,is_hidden, description,l_keyword_list, reference_url, front_matter, rear_matter, notice, l_param_list, l_reusable_query_list, l_cs_guid) INTO l_std FROM mgmt_cs_config_standard WHERE cs_guid = l_cs_guid ; commit; RETURN l_std ; END export_config_standard; -- -- PURPOSE -- -- To export the standard rules specified by standard guid -- returns an object of rule type and this will be read in java to -- create xml file out of this. -- -- Parameters -- p_cs_guid : Configuration Standard Guid -- FUNCTION export_cs_rules( p_cs_guid RAW ) RETURN MGMT_CS_RULE_ARRAY IS l_rule_guids MGMT_CS_GUID_ARRAY; l_rule_def MGMT_CS_RULE_DEF; l_rules MGMT_CS_RULE_ARRAY; l_rule_viol_ctx_list MGMT_CS_VIOL_COLUMN_ARRAY; l_rule_advisor_list MGMT_CS_CHANGE_ADVISOR_ARRAY; l_rule_fix_link_list MGMT_CS_FIX_LINK_ARRAY; l_rule_simple_tests MGMT_CS_SIMPLE_TEST_ARRAY; l_rule_guid RAW(16); l_rule_obj_ctr NUMBER := 0; BEGIN -- Initializing various arrays/lists. l_rules := MGMT_CS_RULE_ARRAY(); -- COLLECT RULES -- ============= -- first collect all guids SELECT rule_guid BULK COLLECT INTO l_rule_guids FROM MGMT_CS_RULE WHERE cs_guid = p_cs_guid; -- use these guids for getting relevant information. IF ((l_rule_guids IS NOT NULL) AND (l_rule_guids.COUNT > 0)) THEN FOR l_rule_obj_ctr IN l_rule_guids.FIRST..l_rule_guids.LAST LOOP l_rule_guid := l_rule_guids(l_rule_obj_ctr); -- collecting viol ctx information.. BEGIN SELECT MGMT_CS_VIOL_COLUMN_DEF(column_iname, column_dname, is_hidden, is_key, link_template, link_encode, is_link_em_page) BULK COLLECT INTO l_rule_viol_ctx_list FROM mgmt_cs_rule_viol_ctx WHERE rule_guid = l_rule_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- collecting fix link information BEGIN SELECT MGMT_CS_FIX_LINK_DEF(display_label, link_template, link_encode, is_link_em_page) BULK COLLECT INTO l_rule_fix_link_list FROM mgmt_cs_rule_fix_link WHERE rule_guid = l_rule_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- collecting change advisor information BEGIN SELECT MGMT_CS_CHANGE_ADVISOR_DEF(change_advisor_type, change_advisor_subtype, change_advisor_reason ) BULK COLLECT INTO l_rule_advisor_list FROM mgmt_cs_rule WHERE rule_guid = l_rule_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- collecting simple tests information BEGIN SELECT MGMT_CS_SIMPLE_TEST_DEF(property, operator, value, position) BULK COLLECT INTO l_rule_simple_tests FROM mgmt_cs_rule_simple_test WHERE rule_guid = l_rule_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; --Build rule object with above information... BEGIN SELECT MGMT_CS_RULE_DEF(rule_guid, rule_iname, rule_dname, description, reference_url, importance_level, rationale, fixtext, warning, l_rule_fix_link_list, l_rule_advisor_list, l_rule_viol_ctx_list, message, clear_message, test_type, test, simple_test_condition, l_rule_simple_tests) INTO l_rule_def FROM mgmt_cs_rule WHERE rule_guid = l_rule_guid; EXCEPTION WHEN NO_DATA_FOUND THEN NULL; END; -- now insert this rule object in rules list(array). l_rules.EXTEND; l_rules(l_rule_obj_ctr) := l_rule_def; END LOOP; END IF; RETURN l_rules ; END export_cs_rules; -- -- PURPOSE -- -- To export the standard rulefolder specified by standard guid -- returns an object of standard rulefolder type and this will be read in java to -- create xml file out of this. -- -- Parameters -- p_cs_guid : Configuration Standard Guid -- FUNCTION export_cs_rulefolders( p_cs_guid RAW ) RETURN MGMT_CS_RULEFOLDER_ARRAY IS l_rulefolder_guids MGMT_CS_GUID_ARRAY; l_rulefolder_def MGMT_CS_RULEFOLDER_DEF; l_rulefolders MGMT_CS_RULEFOLDER_ARRAY; l_rulefolder_guid RAW(16); l_rulefolder_obj_ctr NUMBER := 0; BEGIN -- Initializing various arrays/lists. l_rulefolders := MGMT_CS_RULEFOLDER_ARRAY(); -- COLLECT RULEFOLDER -- ================== -- first collect all guids SELECT rulefolder_guid BULK COLLECT INTO l_rulefolder_guids FROM MGMT_CS_RULEFOLDER WHERE cs_guid = p_cs_guid; -- use these guids for getting relevant information. IF ((l_rulefolder_guids IS NOT NULL) AND (l_rulefolder_guids.COUNT > 0)) THEN FOR l_rulefolder_obj_ctr IN l_rulefolder_guids.FIRST..l_rulefolder_guids.LAST LOOP l_rulefolder_guid := l_rulefolder_guids(l_rulefolder_obj_ctr); SELECT MGMT_CS_RULEFOLDER_DEF(rulefolder_guid, rulefolder_iname, rulefolder_dname, description, reference_url) INTO l_rulefolder_def FROM mgmt_cs_rulefolder WHERE rulefolder_guid = l_rulefolder_guid; -- now insert this rule object in rules list(array). l_rulefolders.EXTEND; l_rulefolders(l_rulefolder_obj_ctr) := l_rulefolder_def; END LOOP; END IF; RETURN l_rulefolders ; END export_cs_rulefolders; -- -- PURPOSE -- -- To export the standard inclusions specified by standard guid -- returns an object of standard inclusions type and this will be read in java to -- create xml file out of this. -- -- Parameters -- p_cs_guid : Configuration Standard guid -- FUNCTION export_cs_inclusions( p_cs_guid RAW ) RETURN MGMT_CS_INCLUSION_ARRAY IS l_inclusion_guids MGMT_CS_GUID_ARRAY; l_inclusion_def MGMT_CS_INCLUSION_DEF; l_inclusions MGMT_CS_INCLUSION_ARRAY; l_over_param_list MGMT_CS_PARAM_OVER_ARRAY; l_inclusion_guid RAW(16); l_inclusion_obj_ctr NUMBER := 0; BEGIN -- Initializing various arrays/lists. l_inclusions := MGMT_CS_INCLUSION_ARRAY(); -- COLLECT INCLUSIONS -- ================== -- first collect all guids SELECT cs_inclusion_guid BULK COLLECT INTO l_inclusion_guids FROM MGMT_CS_INCLUSION WHERE parent_cs_guid = p_cs_guid; -- use these guids for getting relevant information. IF ((l_inclusion_guids IS NOT NULL) AND (l_inclusion_guids.COUNT > 0)) THEN FOR l_inclusion_obj_ctr IN l_inclusion_guids.FIRST..l_inclusion_guids.LAST LOOP l_inclusion_guid := l_inclusion_guids(l_inclusion_obj_ctr); SELECT MGMT_CS_PARAM_OVER_DEF(param_iname, param_override_value, ref_param_iname) BULK COLLECT INTO l_over_param_list FROM mgmt_cs_inclusion_parameter WHERE cs_inclusion_guid = l_inclusion_guid; SELECT MGMT_CS_INCLUSION_DEF(cs_inclusion_guid, included_cs_iname, included_cs_author, included_cs_version, context_query, l_over_param_list) INTO l_inclusion_def FROM MGMT_CS_INCLUSION WHERE cs_inclusion_guid = l_inclusion_guid; -- now insert this rule object in rules list(array). l_inclusions.EXTEND; l_inclusions(l_inclusion_obj_ctr) := l_inclusion_def; END LOOP; END IF; RETURN l_inclusions ; END export_cs_inclusions; -- PURPOSE -- -- To export the standard hierarchy specified by standard guid -- returns an object of standard hierarchy type and this will be read in java to -- create xml file out of this. -- -- Parameters -- p_cs_guid : Configuration Standard guid -- FUNCTION export_cs_hierarchy( p_cs_guid RAW ) RETURN MGMT_CS_HIERARCHY_ARRAY IS l_cs_hierarchy MGMT_CS_HIERARCHY_ARRAY; BEGIN -- Initializing various arrays/lists. l_cs_hierarchy := MGMT_CS_HIERARCHY_ARRAY(); SELECT mgmt_cs_hierarchy_def(parent_guid, child_guid, child_type, child_position) BULK COLLECT INTO l_cs_hierarchy FROM mgmt_cs_hierarchy start with parent_guid = p_cs_guid connect by prior child_guid = parent_guid order siblings by child_position ; RETURN l_cs_hierarchy ; END export_cs_hierarchy; END mgmt_config_std; / show errors;