Rem
Rem $Header: util_check_pkgbody.sql 08-jul-2007.04:36:51 smalathe Exp $
Rem
Rem util_check_pkgbody.sql
Rem
Rem Copyright (c) 2004, 2007, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      util_check_pkgbody.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    smalathe    07/02/07 - Bug 5907072: Use object_id in all_arguments_cur
Rem    rmaggarw    07/28/05 - add is_recoverable_error 
Rem    dsahrawa    07/14/05 - roll back to use data_type 
Rem    dsahrawa    07/07/05 - use pls_type instead of data_type because 
Rem                           integers show up as numbers in data_type 
Rem    dsahrawa    07/01/05 - possibly allow null parameter lists in 
Rem                           is_valid_signature 
Rem    jsadras     03/28/05 - check qualified name 
Rem    dcawley     03/09/05 - Use all procedures to avoid problem when logged 
Rem                           in as SYS 
Rem    dcawley     03/09/05 - Fix arguments cursor 
Rem    dcawley     03/08/05 - Check for schema name 
Rem    rpinnama    02/17/05 - Add not null check for date 
Rem    rpinnama    01/06/05 - Add a check for type meta version 
Rem    rpinnama    11/22/04 - Search for proc using case-insensitive search 
Rem    jsadras     10/25/04 - argumentname_10g
Rem    jsadras     10/08/04 - debug_routines
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 BODY em_check
AS
  --
  -- NLS specific initialization of the white space character set strings
  --

  function GET_WHITE_SPACE_CHARS return varchar2;
  function GET_ALPHA_CHARS return varchar2;
  function GET_DIGIT_CHARS return varchar2;

  --
  -- Constants
  --

  White_Space_Chars constant varchar2(256) := GET_WHITE_SPACE_CHARS;
  Alpha_Chars       constant varchar2(256) := GET_ALPHA_CHARS;
  Digit_Chars       constant varchar2(256) := GET_DIGIT_CHARS;


  PROCEDURE check_not_null(p_value      IN VARCHAR2,
                           p_param_name IN VARCHAR2 DEFAULT NULL,
                           p_error_msg  IN VARCHAR2 DEFAULT NULL)
  IS
    l_param_name VARCHAR2(64);
    l_error_msg VARCHAR2(4000);
  BEGIN
    IF (p_value IS NULL) THEN
      l_param_name := NVL(p_param_name, 'parameter');
      l_error_msg := 'Invalid parameters : NULL value passed for ' || l_param_name || '.';
      IF (p_error_msg IS NOT NULL) THEN
        l_error_msg := l_error_msg || ' ' || p_error_msg;
      END IF;

      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END IF;
  END check_not_null;

  PROCEDURE check_not_null(p_value      IN NUMBER,
                           p_param_name IN VARCHAR2 DEFAULT NULL,
                           p_error_msg  IN VARCHAR2 DEFAULT NULL)
  IS
    l_param_name VARCHAR2(64);
    l_error_msg VARCHAR2(4000);
  BEGIN
    IF (p_value IS NULL) THEN
      l_param_name := NVL(p_param_name, 'parameter');
      l_error_msg := 'Invalid parameters : NULL value passed for ' || l_param_name || '.';
      IF (p_error_msg IS NOT NULL) THEN
        l_error_msg := l_error_msg || ' ' || p_error_msg;
      END IF;

      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END IF;
  END check_not_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)
  IS
    l_param_name VARCHAR2(64);
    l_error_msg VARCHAR2(4000);
  BEGIN

    IF ( (p_value < p_min_value) OR (p_value > p_max_value) ) 
    THEN
      l_param_name := NVL(p_param_name, 'parameter');
      l_error_msg := 'The value provided for ' || l_param_name || 'is out of range. ' ||
	 'The valid range for ' || l_param_name || ' is (' || 
	    p_min_value || ' - ' || p_max_value || ').' ;
      IF (p_error_msg IS NOT NULL) THEN
        l_error_msg := l_error_msg || ' ' || p_error_msg;
      END IF;
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END IF;

  END check_range;

  PROCEDURE check_not_null(p_value      IN DATE,
                           p_param_name IN VARCHAR2 DEFAULT NULL,
                           p_error_msg  IN VARCHAR2 DEFAULT NULL)
  IS
    l_param_name VARCHAR2(64);
    l_error_msg VARCHAR2(4000);
  BEGIN 
    IF (p_value IS NULL) THEN
      l_param_name := NVL(p_param_name, 'parameter');
      l_error_msg := 'Invalid parameters : NULL value passed for ' || l_param_name || '.';
      IF (p_error_msg IS NOT NULL) THEN
        l_error_msg := l_error_msg || ' ' || p_error_msg;
      END IF;

      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END IF;
  END check_not_null;

  PROCEDURE check_super_user_priv(p_operation IN VARCHAR2 DEFAULT NULL,
                                  p_error_msg IN VARCHAR2 DEFAULT NULL)
  IS
    l_current_user mgmt_created_users.user_name%TYPE;
  BEGIN
    l_current_user := MGMT_USER.GET_CURRENT_EM_USER;

    IF (MGMT_USER.HAS_PRIV(l_current_user, MGMT_USER.SUPER_USER) = 0) THEN

      raise_application_error(MGMT_GLOBAL.INSUFFICIENT_PRIVILEGES_ERR,
         'Only superusers can perform this operation');

    END IF;

  END check_super_user_priv;

  -- 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)
  IS
    l_dot_idx NUMBER := 0;
    l_maj_ver NUMBER := 0;
    l_min_ver NUMBER := 0;
    l_param_name VARCHAR2(64);
    l_error_msg VARCHAR2(4000);
  BEGIN
    check_not_null(p_type_meta_ver, p_param_name, p_error_msg);

    l_param_name := NVL(p_param_name, 'parameter');

    l_dot_idx := INSTR(p_type_meta_ver, '.');
    IF (l_dot_idx = 0) THEN

      l_error_msg := 'Invalid type meta version parameter : No minor version found in ' 
              || p_type_meta_ver || ' for ' || l_param_name || '.';
      IF (p_error_msg IS NOT NULL) THEN
        l_error_msg := l_error_msg || ' ' || p_error_msg;
      END IF;
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);

    END IF;

    BEGIN
      l_maj_ver := TO_NUMBER( SUBSTR(p_type_meta_ver, 1, l_dot_idx-1));
    EXCEPTION
      WHEN OTHERS THEN
        l_error_msg := 'Invalid type meta version parameter : Non-numeric major version found in ' 
              || p_type_meta_ver || ' for ' || l_param_name || '.';
        IF (p_error_msg IS NOT NULL) THEN
          l_error_msg := l_error_msg || ' ' || p_error_msg;
        END IF;
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END;

    BEGIN
      l_min_ver := TO_NUMBER( SUBSTR(p_type_meta_ver, l_dot_idx + 1));
    EXCEPTION
      WHEN OTHERS THEN
        l_error_msg := 'Invalid type meta version parameter : Non-numeric minor version found in ' 
              || p_type_meta_ver || ' for ' || l_param_name || '.';
        IF (p_error_msg IS NOT NULL) THEN
          l_error_msg := l_error_msg || ' ' || p_error_msg;
        END IF;
        raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR, l_error_msg);
    END;

  END check_type_meta_ver;
  
  --
  --Routines for dumping data 
  -- Has to be called only if debug set
  -- dumps the values in the string array
  PROCEDURE dump_values(p_prefix IN VARCHAR2,
                        p_string_array IN mgmt_short_string_array)
    IS
  BEGIN
    EMDW_LOG.DEBUG(p_prefix||' Parameter Datatypes Start',MODULE_NAME) ;
    IF p_string_array IS NOT NULL AND
       p_string_array.COUNT > 0
    THEN
      FOR i in p_string_array.FIRST..p_string_array.LAST
    LOOP
        EMDW_LOG.DEBUG('    '||p_string_array(i),MODULE_NAME) ;
      END LOOP ;
    END IF ;
    EMDW_LOG.DEBUG(p_prefix||' Parameter Datatypes End',MODULE_NAME) ;
  EXCEPTION WHEN OTHERS THEN NULL ;
  END dump_values;
  --
  -- Check if the signature is valid
  -- Synonyms are not taken care of in this function
  --
  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 
  IS
  l_upper_proc VARCHAR2(64);
  l_proc_name  VARCHAR2(30);
  l_pkg_name   VARCHAR2(30);
  l_schema_name   VARCHAR2(30);
  l_first_dot_position NUMBER := 0;
  l_second_dot_position NUMBER := 0;
  l_found BOOLEAN := FALSE ;
  l_null_arg BOOLEAN := FALSE;
  -- Used to check when p_arg_types is NULL, p_allow_null_args is TRUE
  l_rec_found BOOLEAN := FALSE;
  -- Used to check if an entry exists in all_arguments
  l_object_id NUMBER := 0;

  CURSOR all_arguments_cur(p_object_id IN NUMBER,
                           p_proc_name IN VARCHAR2)
  IS
     SELECT  CAST(MULTISET(SELECT nvl(type_name, data_type)
                             FROM all_arguments aa_in
                           WHERE aa_in.object_id = aa_out.object_id AND
                                 (aa_in.argument_name IS NOT NULL OR
                                  aa_in.position = 0) AND
                                 nvl(aa_in.overload,0)  = nvl(aa_out.overload,0) AND
                                 nvl(aa_in.owner,'X') = nvl(aa_out.owner,'X') AND
                                 nvl(aa_in.package_name,'X') = nvl(aa_out.package_name,'X') AND
                                 nvl(aa_in.object_name,'X') = nvl(aa_out.object_name ,'X')
                           ORDER BY POSITION
                           ) AS mgmt_short_string_array
                 ) args
      FROM all_arguments aa_out
     WHERE object_id = p_object_id AND
           (object_name = p_proc_name) AND
           position = 1
     ORDER BY OBJECT_ID ;


    PROCEDURE raise_error(p_message IN VARCHAR2)
    IS
    BEGIN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
        p_message) ;
    END  raise_error;
  BEGIN

    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('is_valid_signature:Enter '||p_proc_name,MODULE_NAME) ; 
      dump_values('is_valid_signature:Expected ', p_arg_types) ;
    END IF ;

    IF p_proc_name IS NULL
    THEN
      raise_error('Procedure name cannot be null') ;
    END IF ;

    l_upper_proc := UPPER(p_proc_name);

    l_first_dot_position := instr(l_upper_proc,'.') ;
    l_second_dot_position := instr(l_upper_proc,'.', 1, 2) ;

    IF l_second_dot_position > 0
    THEN
      -- This is a name of the form schema.pkg.proc
      l_schema_name  := substr(l_upper_proc, 1, l_first_dot_position-1) ;
      l_pkg_name  := substr(l_upper_proc, l_first_dot_position+1, 
                            l_second_dot_position-l_first_dot_position-1 ) ;
      l_proc_name := substr(l_upper_proc, l_second_dot_position+1) ;
      IF l_schema_name IS NULL or l_pkg_name IS NULL or l_proc_name IS NULL
      THEN
        raise_error('Schema, procedure or package name cannot be null') ;
      END IF ;
    ELSIF l_first_dot_position > 0 
    THEN
      l_schema_name := NULL;
      l_pkg_name  := substr(l_upper_proc, 1, l_first_dot_position-1) ;
      l_proc_name := substr(l_upper_proc, l_first_dot_position+1) ;
      IF l_pkg_name IS NULL or l_proc_name IS NULL
      THEN
        raise_error('Procedure or package name cannot be null') ;
      END IF ;
    ELSE
      l_pkg_name  := NULL ;
      l_proc_name := l_upper_proc ;
    END IF ;

    IF l_second_dot_position > 0
    THEN
      BEGIN
        SELECT object_id INTO l_object_id
          FROM all_objects
          WHERE owner = l_schema_name AND
                object_name = l_pkg_name AND
                object_type in ('PACKAGE');
      EXCEPTION WHEN NO_DATA_FOUND THEN
        l_object_id := 0;
      END;
    ELSE
      BEGIN
        SELECT object_id INTO l_object_id
          FROM all_objects
          WHERE owner = MGMT_USER.GET_REPOSITORY_OWNER AND
                object_name = nvl(l_pkg_name,l_proc_name) AND
                object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION');
      EXCEPTION WHEN NO_DATA_FOUND THEN
        l_object_id := 0;
      END;

       IF l_object_id = 0
       THEN
         -- Check if this was a procedure of the form schema.procedure
         BEGIN
           SELECT object_id INTO l_object_id
             FROM all_objects 
             WHERE owner = l_pkg_name AND
                   object_name = l_proc_name AND
                   object_type in ('PROCEDURE', 'FUNCTION');
         EXCEPTION WHEN NO_DATA_FOUND THEN
           l_object_id := 0;
         END;

         IF l_object_id > 0
         THEN
           l_schema_name := l_pkg_name;
           l_pkg_name := NULL;
         END IF;
       END IF;
    END IF;

    IF l_object_id = 0
    THEN
      raise_error('No such Procedure') ;
    END IF ;

    IF p_arg_types IS NULL or p_arg_types.COUNT =0
    THEN
      IF p_allow_null_args THEN
        l_null_arg := TRUE;
      ELSE
        -- We expect p_allow_null_args to be TRUE to evaluate case of NULL
        -- Parameter list
        raise_error('Parameter list is NULL') ;
      END IF;
    END IF ;

    FOR rec IN all_arguments_cur(l_object_id, l_proc_name)
    LOOP
    BEGIN
      l_rec_found := TRUE;
      IF EMDW_LOG.P_IS_DEBUG_SET 
      THEN
        dump_values('is_valid_signature:Found',rec.args) ;
      END IF ;
      IF rec.args IS NOT NULL AND NOT l_null_arg AND
         rec.args.count = p_arg_types.COUNT
      THEN
        l_found := TRUE ;
        FOR i IN p_arg_types.FIRST..p_arg_types.LAST
        LOOP
          IF UPPER(p_arg_types(i)) != nvl(rec.args(i),'X')
          THEN
            l_found := FALSE ; EXIT ;
          END IF ;
        END LOOP ; 
        EXIT when l_found ;
      ELSIF l_null_arg THEN
        IF rec.args IS NOT NULL AND rec.args.count > 0 THEN
          -- p_arg_types was NULL and p_allow_null_args TRUE, but this
          -- procedure has atleast 1 argument. Probably there could be an
          -- overloaded procedure without any arguments
          l_found := FALSE;
        ELSIF rec.args IS NOT NULL AND rec.args.count = 0 THEN
          l_found := TRUE;
          EXIT;
        END IF;
      END IF ;
    END ;
    END LOOP ;

    -- If package name was provided and NOT l_rec_found, the procedure
    -- does not have entry in all_arguments
    IF l_object_id > 0 AND l_pkg_name IS NOT NULL AND NOT l_rec_found THEN
      raise_error('No such Procedure');
    END IF;

    IF l_null_arg AND (l_found OR (NOT l_rec_found AND l_pkg_name IS NULL))
    THEN
      -- Here we know that such a procedure exists. But if l_null_arg and
      -- this procedure was not part of package, it won't have entry in
      -- all_arguments
      RETURN(TRUE);
    END IF;

    IF NOT l_found
    THEN
      raise_error('Parameter datatypes do not match') ;
    END IF ;
    p_error_message := NULL ;
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('is_valid_signature:Exit normal',MODULE_NAME) ; 
    END IF ;
    RETURN(TRUE) ;
  EXCEPTION
  WHEN OTHERS THEN
    IF EMDW_LOG.P_IS_INFO_SET
    THEN
      EMDW_LOG.INFO('is_valid_signature:Exit exception:'||sqlerrm,MODULE_NAME); 
    END IF ;
    p_error_message := substr(sqlerrm,1,100) ;
    RETURN(FALSE) ;
  END is_valid_signature ;
-- **********************************************************************
-- Copied the following from DBMS_ASSERT code sent by dbronnik. These need
-- to be cutover to use dbms_assert as and when it is backported to 9.2
-- As such the code indentation and local variable naming is different from 
-- rest of code above. Did not want to change any piece of code to make it
-- easier for any bug fixes to dbms_assert to be carried out here.
--***********************************************************************

  --
  -- NLS specific initialization of the white space character set strings
  --

  FUNCTION get_white_space_chars 
  RETURN VARCHAR2 IS
    Value varchar2(256);
    Chr_I char(1);
  BEGIN
    Value := ' ';
    return Value;
  END get_white_space_chars;

  FUNCTION get_alpha_chars 
  RETURN VARCHAR2 IS
  BEGIN
    return 'abcdefghijklmnopqrstuvwxyz' ||
           'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
  END get_alpha_chars;

  FUNCTION get_digit_chars 
  RETURN VARCHAR2 IS
  BEGIN
    return '0123456789';
  END get_digit_chars;

  --
  -- NOOP.
  --
  -- This function returns the value without any checking.
  --
  FUNCTION NOOP(p_sql_name IN VARCHAR2 CHARACTER SET ANY_CS)
  RETURN VARCHAR2 CHARACTER SET p_sql_name%CHARSET
  IS
  BEGIN
    return p_sql_name;
  END noop;

  --
  -- This helper function returns TRUE if the input
  -- is a valid simple SQL name with no spaces around it.
  --

  FUNCTION name_no_spaces_or_quotes(Str varchar2 CHARACTER SET ANY_CS)
  RETURN BOOLEAN IS
    Val char(1);
    Len binary_integer;
  BEGIN
    if (Str is null) then
      return FALSE;
    end if;
    /* lrg1833761: 
       We use the following pattern instead because we assume
       the incoming string is not multiline
     */

    -- return regexp_like(Str, '\A[[:alpha:]]([[:alnum:]#$_])*$');

    if (instr(Alpha_Chars, substr(Str, 1, 1)) <= 0) then
      return FALSE;
    end if;
    Len := length(Str);
    for i in 2..Len loop
      Val := substr(Str, i, 1);
      if (instr(Alpha_Chars, Val) <= 0 AND
          instr(Digit_Chars, Val) <= 0 AND
          Val <> '#' AND
          Val <> '$' AND
          Val <> '_') then
        return FALSE;
      end if;
    end loop;
    return TRUE;

  END name_no_spaces_or_quotes;

  --
  -- This helper function returns TRUE if the input 
  -- is a valid SQL identifier with no white space around it.
  --

  FUNCTION NAME_NO_SPACES(Str varchar2 CHARACTER SET ANY_CS)
  RETURN BOOLEAN IS
    Pos binary_integer;
  BEGIN
    if (substr(Str, 1, 1) = '"') Then
      Pos := instr(Str, '"', 2, 1);
      loop
        -- return TRUE if we found the closing quote in the last position
        if (Pos = length(Str)) then
          return TRUE;
        end if;
        -- return FALSE if no more quotes
        if (Pos < 2) then
          return FALSE;
        end if;
        -- return FALSE if there's a sole quote in the middle of the string
        if (substr(Str, Pos + 1, 1) <> '"') then
          return FALSE;
        end if;
        -- return FALSE if the next quote is in the last position
        if (Pos + 1 = length(Str)) then
          return FALSE;
        end if;
        -- find the next quote
        Pos := instr(Str, '"', Pos + 2, 1);
      end loop;
    else
      -- use the function for simple SQL name (without quotes)
      return NAME_NO_SPACES_OR_QUOTES(Str);
    end if;
  END name_no_spaces;

  --
  -- This helper function returns TRUE if the input 
  -- is a valid SQL identifier possibly with white space around it.
  --

  FUNCTION NAME_SPACES(Str varchar2 CHARACTER SET ANY_CS)
  RETURN BOOLEAN IS
  BEGIN
    return NAME_NO_SPACES(RTRIM(LTRIM(Str, White_Space_Chars),
                                White_Space_Chars));
  END name_spaces;

  --
  -- SIMPLE_SQL_NAME
  --
  -- Verify that the input string is a simple SQL name.
  -- The name must begin with an alphabetic character.
  -- It may contain alphanumeric characters as well as 
  -- the characters _, $, and # in the second and subsequent
  -- character positions.  The length of the name is not
  -- checked by this function.  The input parameter may
  -- have any number of leading and/or trailing white space
  -- characters.  Quoted SQL names are also allowed.
  -- Quoted names must be enclosed in double quotes.
  -- Quoted names allow any characters between the quotes.
  -- Quotes inside the name are represented by two quote
  -- characters in a row, e.g. "a name with "" inside"
  -- is a valid quoted name.
  --

  FUNCTION simple_sql_name(p_sql_name VARCHAR2 CHARACTER SET ANY_CS)
  RETURN VARCHAR2 CHARACTER SET p_sql_name%CHARSET IS
  BEGIN     
    if (NAME_NO_SPACES(RTRIM(LTRIM(p_sql_name, White_Space_Chars),
                             White_Space_Chars))) then
      return p_sql_name;
    else
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'string is not a simple SQL name') ;
    end if;
  END simple_sql_name;

  --
  -- QUALIFIED_SQL_NAME
  --
  -- Verify that the input string is 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
  IS
    Val                  varchar2(32767);
    Len                  binary_integer;
    Pos1                 binary_integer;
    Pos2                 binary_integer;
    In_Quotes            boolean;
    Already_Seen_DB_Link boolean;
    Already_Seen_Conn_Qualifier boolean;
  BEGIN
    -- Skip white space
    Val := p_sql_name;
    Len := length(Val);
    Pos1 := 1;
    while(Pos1 <= Len and
          instr(substr(Val, Pos1, 1), White_Space_Chars) > 0) loop
      Pos1 := Pos1 + 1;
    end loop;
    -- Find name components and examine each component
    Pos2 := Pos1;
    In_Quotes := FALSE;
    Already_Seen_DB_Link := FALSE;
    Already_Seen_Conn_Qualifier := FALSE;
    while (Pos2 <= Len) loop
      if (substr(Val, Pos2, 1) = '"') then
        if (Pos2 < Len and substr(Val, Pos2 + 1, 1) = '"') then
          Pos2 := Pos2 + 1;
        else
          In_Quotes := NOT In_Quotes;
        end if;
      else
        if (substr(Val, Pos2, 1) = '.' and not In_Quotes) then
          if(Already_Seen_Conn_Qualifier) then
            raise MGMT_GLOBAL.invalid_params;
          end if;
          if (not NAME_SPACES(substr(Val, Pos1, Pos2 - Pos1))) then
            raise MGMT_GLOBAL.invalid_params;
          end if;
          Pos1 := Pos2 + 1;
        end if;
        if (substr(Val, Pos2, 1) = '@' and not In_Quotes) then
          if (Already_Seen_DB_Link) then
            if(Already_Seen_Conn_Qualifier) then
              raise MGMT_GLOBAL.invalid_params;
            else
              Already_Seen_Conn_Qualifier := TRUE;
            end if;
          else
            Already_Seen_DB_Link := TRUE;
          end if;
          if (not NAME_SPACES(substr(Val, Pos1, Pos2 - Pos1))) then
            raise MGMT_GLOBAL.invalid_params;
          end if;
          Pos1 := Pos2 + 1;
        end if;
      end if;
      Pos2 := Pos2 + 1;
    end loop;
    -- Check the last component.  It must have length greater than
    -- zero and must not start with white space.
    if (Pos2 > Pos1) then
      if (not NAME_SPACES(substr(Val, Pos1, Pos2 - Pos1))) then
        raise MGMT_GLOBAL.invalid_params;
      end if;
    else
      raise MGMT_GLOBAL.invalid_params;
    end if;
    -- All is fine
    return p_sql_name;
  EXCEPTION
  WHEN MGMT_GLOBAL.invalid_params THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'string is not a qualified SQL name') ;
  WHEN OTHERS THEN
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
     'error validating SQL name:'||sqlerrm) ;
  END qualified_sql_name;


  --
  -- SCHEMA_NAME
  --
  -- This function verifies that the input string is an existing
  -- schema name.
  --

  FUNCTION schema_name(p_schema_name VARCHAR2 CHARACTER SET ANY_CS)
  RETURN VARCHAR2 CHARACTER SET p_schema_name%CHARSET IS
    user_count number;
  BEGIN
    select count(*) into user_count from all_users 
    where username = UPPER(p_schema_name);
    if user_count <> 1 then
      raise_application_error(MGMT_GLOBAL.user_does_not_exist_err,
        MGMT_GLOBAL.user_does_not_exist_err_m) ;
    end if;
    return p_schema_name;
  END schema_name;

  --
  -- SQL_OBJECT_NAME
  --
  -- This function verifies that the input parameter string
  -- is a qualified SQL identifier of an existing SQL object.
  --

  FUNCTION sql_object_name(p_sql_object_name VARCHAR2 CHARACTER SET ANY_CS)
  RETURN VARCHAR2 CHARACTER SET p_sql_object_name%CHARSET IS
    Schema_Name   varchar2(128);
    Part1         varchar2(128);
    Part2         varchar2(128);
    Dblink        varchar2(128);
    Part1_Type    number;
    Object_Number number;
  BEGIN
    for Ctx_Number in 1..3 loop
      begin
        -- Try to resolve the name in the current context
        -- note: we do not support Java in the name resolve
        -- so we only check context 1,2 and 3. See psdicd.c
        -- for details on the context.
        DBMS_UTILITY.name_resolve(p_sql_object_name, Ctx_Number,
                                  Schema_Name, Part1, Part2, Dblink,
                                  Part1_Type, Object_Number);
        -- If the name got resolved in one of the name spaces then
        -- return the input string back
        return p_sql_object_name;
      exception
        -- Ignore errors
        when others then
          null;
      end;
    end loop;
    -- Name resolution failed under all context numbers.  Raise an error
    raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,
      'Invalid object name') ;
  END sql_object_name;

  --
  -- Enquote a string using a given quote character
  --

  FUNCTION enquote_internal(Str   VARCHAR2, 
                            Quote VARCHAR2)
  RETURN VARCHAR2 IS
    already_quoted boolean := substr(Str, 1, 1) = Quote;
    len            binary_integer := length(Str);
    pos            binary_integer;
  BEGIN
    if (already_quoted) then
      pos := 2;
      if (substr(Str, len, 1) <> Quote) then
        raise value_error;
      end if;
    else
      pos := 1;
    end if;
    while (pos <= len) loop
      if (substr(Str, pos, 1) = Quote) then
        if (pos = len) then
          if (not already_quoted) then
            raise value_error;
          end if;
        else
          if (substr(Str, pos + 1, 1) = Quote) then
            pos := pos + 1;
          else
            raise value_error;
          end if;
        end if;
      end if;
      pos := pos + 1;
    end loop;
    if (already_quoted) then
      return Str;
    else
      return Quote || Str || Quote;
    end if;
  END enquote_internal;

  --
  -- 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.  Alphabetic characters of a name which was not
  -- in quotes are translated to upper case.
  --

  FUNCTION enquote_name(p_name       VARCHAR2, 
                        p_capitalize BOOLEAN DEFAULT TRUE)
  RETURN VARCHAR2 IS
  BEGIN
    if ((substr(p_name, 1, 1) = '"') or (p_capitalize = FALSE)) then
      return ENQUOTE_INTERNAL(p_name, '"');
    else
      return ENQUOTE_INTERNAL(upper(p_name), '"');
    end if;
  END enquote_name;

  --
  -- 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
  BEGIN
    return ENQUOTE_INTERNAL(p_name, '''');
  END enquote_literal;


  --
  -- 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 IS
  err_cnt INTEGER;
  BEGIN
    SELECT count(*) INTO err_cnt
    FROM mgmt_error_master
    WHERE (error_start <= p_errcode AND error_end >= p_errcode)
    AND is_recoverable = 0;

    IF (err_cnt = 0) THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
  END is_recoverable_error;

  --
  -- 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
  IS
  BEGIN
     DELETE FROM MGMT_ERROR_MASTER; 

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-20999, -20000); -- application errors

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-12899, -12899); -- 10g - inserted value too large for column

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-6533, -6533); -- subscript_beyond_count

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-6531, -6531); -- collection_is_null

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-6502, -6502); -- VALUE_ERROR

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1422, -1422); -- too many rows

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1407, -1407); -- cannot update to NULL

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1403, -1403); -- no data found

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1401, -1401); --inserted value too large for column

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1400, -1400); -- cannot insert NULL

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-999, -900); -- SQL syntax errors

     INSERT INTO MGMT_ERROR_MASTER (error_start, error_end)
     VALUES (-1, -1); -- DUP VAL ON INDEX

     COMMIT;

  END load_error_master;

END em_check;
/

show errors