Rem Rem $Header: util_check_pkgdef.sql 02-aug-2005.20:04:30 rmaggarw Exp $ Rem Rem util_check_pkgdef.sql Rem Rem Copyright (c) 2004, 2005, Oracle. All rights reserved. Rem Rem NAME Rem util_check_pkgdef.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem rmaggarw 07/28/05 - add recoverable_error Rem dsahrawa 07/01/05 - possibly allow null parameter lists in Rem is_valid_signature Rem jsadras 03/27/05 - security functions Rem rpinnama 02/17/05 - Add not null check for date Rem rpinnama 01/06/05 - Add a check for type meta version Rem jsadras 10/08/04 - module_name Rem jsadras 10/08/04 - check_signature Rem rpinnama 07/30/04 - rpinnama_add_metric_policy_apis_2 Rem rpinnama 07/28/04 - Created Rem CREATE OR REPLACE PACKAGE em_check AS MODULE_NAME CONSTANT VARCHAR2(10) := 'EM_CHECK' ; -- Check for varchar2 data types PROCEDURE check_not_null(p_value IN VARCHAR2, p_param_name IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); -- Checks for numeric data types PROCEDURE check_not_null(p_value IN NUMBER, p_param_name IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); PROCEDURE check_range(p_value IN NUMBER, p_min_value IN NUMBER DEFAULT 0, p_max_value IN NUMBER DEFAULT 0, p_param_name IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); -- Checks for Date data types PROCEDURE check_not_null(p_value IN DATE, p_param_name IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); -- Check for user privieleges privileged PROCEDURE check_super_user_priv(p_operation IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); -- Checks the given type meta version PROCEDURE check_type_meta_ver(p_type_meta_ver IN VARCHAR2, p_param_name IN VARCHAR2 DEFAULT NULL, p_error_msg IN VARCHAR2 DEFAULT NULL); -- -- Function to check if the signature of the given procedure -- matches the expected signature -- -- p_arg_types contains a array of datatypes -- p_error_message will contain the error message if any -- Returns TRUE if the procedure or package has signature -- as defined by p_arg_types else FALSE -- For example: -- If you want to test the signature of PL/SQL metric evaluation procedure -- The metric evaluation procedure should have the signature -- (p_target_guid IN RAW, -- p_metric_guid IN RAW, -- p_collection_name IN VARCHAR2, -- p_results OUT mgmt_metric_value_array ) -- Then the p_arg_types_array should contain -- mgmt_short_string_array('RAW','RAW','VARCHAR2','MGMT_METRIC_VALUE_ARRAY') -- Note: Currently it tests only data types, it does not check mode of parameters -- TBD. FUNCTION is_valid_signature(p_proc_name IN VARCHAR2, p_arg_types IN mgmt_short_string_array, p_error_message OUT VARCHAR2, p_allow_null_args IN BOOLEAN DEFAULT FALSE) RETURN BOOLEAN ; -- -- ************************************************************* -- Following functions are created here to serve as place holders -- till dbms_assert is backported to 9.2. The implementation of these -- procedures will be cutover to use dbms_assert later on. These -- procedure implementation will be cutover to use dbms_assert when it -- becomes available in least supported db version ( currently 9.2). -- Once dbms_assert becomes available in 9.2 it should be called directly -- and these functions should not be used -- ************************************************************** -- -- NOOP. -- -- This function returns the value without any checking. -- To be used when you are absolutely sure that the input string -- is a valid sql object name -- FUNCTION noop(p_sql_name IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET p_sql_name%CHARSET; -- -- SIMPLE_SQL_NAME -- -- Verify that the input string is a simple SQL name: -- 1. The name must begin with an alphabetic character. -- 2. It may contain alphanumeric characters as well as -- the characters _, $, and # in the second and subsequent -- character positions. -- 3. Quoted SQL names are also allowed. -- 4. Quoted names must be enclosed in double quotes. -- 5. Quoted names allow any characters between the quotes. -- 6. Quotes inside the name are represented by two quote -- characters in a row, e.g. "a name with "" inside" -- is a valid quoted name. -- 7. The input parameter may have any number of leading -- and/or trailing white space characters. -- -- Note: The length of the name is not checked. -- -- EXCEPTIONS: -- MGMT_GLOBAL.INVALID_PARAMS_ERR: string is not a simple SQL name FUNCTION simple_sql_name(p_sql_name varchar2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET p_sql_name%CHARSET; -- -- QUALIFIED_SQL_NAME -- -- Verify that the input string is a qualified SQL name. -- A qualified SQL name can be expressed by the -- following grammar: -- -- ::= {'.' } -- ::= ['@' ] -- ::= -- ::= ['@' ] -- -- EXCEPTIONS: -- MGMT_GLOBAL.INVALID_PARAMS_ERR: string is not a qualified SQL name FUNCTION qualified_sql_name(p_sql_name IN VARCHAR2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET p_sql_name%CHARSET; -- -- SCHEMA_NAME -- -- This function verifies that the input string is an existing -- schema name. -- Note: -- Please be aware that by definition, a schema name need not -- be just a simple sql name. For example, "FIRST LAST" is a valid -- schema name. As a consequence, care must be taken to quote the -- output of schema name before concatenating it with SQL text. -- -- EXCEPTIONS: -- MGMT_GLOBAL.USER_DOES_NOT_EXIST:User does not exist FUNCTION schema_name(p_schema_name varchar2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET p_schema_name%CHARSET; -- -- SQL_OBJECT_NAME -- -- This function verifies that the input parameter string -- is a qualified SQL identifier of an existing SQL object. -- -- EXCEPTIONS: -- MGMT_GLOBAL.INVALID_PARAMS_ERR: Invalid object name FUNCTION sql_object_name(p_sql_object_name varchar2 CHARACTER SET ANY_CS) RETURN VARCHAR2 CHARACTER SET p_sql_object_name%CHARSET; -- -- ENQUOTE_NAME -- -- This function encloses a name in double quotes. No additional -- quotes are added if the name was already in quotes. Verify -- that all other double quotes in the string are adjacent pairs -- of double quotes. -- p_name (IN) - string to enquote -- p_capitalize (IN) - if true or defaulted, alphabetic characters of -- Str which was not in quotes are translated to -- upper case. FUNCTION ENQUOTE_NAME(p_name VARCHAR2, p_capitalize BOOLEAN DEFAULT TRUE) RETURN VARCHAR2; -- -- ENQUOTE_LITERAL -- -- Enquote a string literal. Add leading and trailing single quotes -- to a string literal. Verify that all single quotes except leading -- and trailing characters are paired with adjacent single quotes. FUNCTION enquote_literal(p_name VARCHAR2) RETURN VARCHAR2; -- -- IS_RECOVERABLE_ERROR -- -- Returns true if error code is not in the list of unrecoverable -- errors stored in MGMT_ERROR_MASTER -- FUNCTION is_recoverable_error(p_errcode NUMBER) RETURN BOOLEAN; -- -- LOAD_ERROR_MASTER -- -- Deletes and then reloads entries in MGMT_ERROR_MASTER -- -- This should be only called during repository creation/upgrade -- PROCEDURE load_error_master; END em_check; / show errors