Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_repos_procs.sql /main/1 2025/10/06 10:10:53 spramani Exp $
Rem
Rem admin_repos_procs.sql
Rem
Rem Copyright (c) 2008, 2025, Oracle and/or its affiliates.
Rem All rights reserved. 

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_repos_procs.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/admin/admin_repos_procs.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      admin_repos_procs.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    spramani    09/26/25 - Created
Rem
DEFINE MY_REPOS_USER = "&1"
DEFINE MY_DBA_USER = "&2"

ALTER SESSION SET CURRENT_SCHEMA=&MY_REPOS_USER;
-- -------------------------
-- IMPORTANT NOTE:
-- -------------------------
-- This is an independent function is created for ATP-s and to be used in other parts of EM for random strings 
-- -------------------------
declare 
l_sql VARCHAR2(32700);

BEGIN
    l_sql :=q'[CREATE OR REPLACE FUNCTION em_repos_func_random_password(seed_in IN VARCHAR2) 
        RETURN VARCHAR2
        IS
        l_repos_type VARCHAR2(256);
        l_pwd_length NUMBER;
        l_view_pwd VARCHAR2(256);
        l_no_of_digits CONSTANT NUMBER := 3;
        l_no_of_spl_chars CONSTANT NUMBER := 5;
        l_no_of_lower CONSTANT NUMBER := 4;
        l_no_of_upper CONSTANT NUMBER := 4;
        l_pwd_part VARCHAR2(30);
        l_digits   CONSTANT VARCHAR2(10) := '0123456789';
        l_lower    CONSTANT VARCHAR2(26) := 'abcdefghijklmnopqrstuvwxyz';
        l_upper    CONSTANT VARCHAR2(26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        l_spl_chars  CONSTANT VARCHAR2(32) := '_#$';
        l_input      RAW(256);
        l_md5_hash   RAW(16);
        BEGIN
           l_input := SYS.UTL_RAW.cast_to_raw( seed_in||RAWTOHEX(sys_guid())); 
           select STANDARD_HASH(l_input, 'MD5') into l_md5_hash from dual;
           l_view_pwd := (SUBSTR(RAWTOHEX(l_md5_hash), 1, 29)) ;
           -- generate part of password text in 
           -- combination of numbers, special chars, upper and lower chars.
           SELECT LISTAGG(one_char, NULL) WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE)
           INTO l_pwd_part
           FROM
           (
                SELECT SUBSTR(l_digits, FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(l_digits) + 1)),1) AS one_char
                FROM DUAL CONNECT BY LEVEL <= l_no_of_digits
                UNION ALL
                SELECT SUBSTR(l_lower, FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(l_lower) + 1)),1) AS one_char
                FROM DUAL CONNECT BY LEVEL <= l_no_of_lower
                UNION ALL
                SELECT SUBSTR(l_upper, FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(l_upper) + 1)),1) AS one_char
                FROM DUAL CONNECT BY LEVEL <= l_no_of_upper
                UNION ALL
                SELECT SUBSTR(l_spl_chars,FLOOR(DBMS_RANDOM.VALUE(1, LENGTH(l_spl_chars) + 1)),1) AS one_char
                FROM DUAL CONNECT BY LEVEL <= l_no_of_spl_chars
            );

            l_pwd_length := NVL(LENGTH(l_view_pwd),0);
            -- 13 chars from sysguid, 16 chars from combinations and 1 char (A) = 30 chars
            IF l_pwd_length >= 13 THEN
               l_view_pwd := SUBSTR(l_view_pwd, 1, 13);
            END IF;
            l_view_pwd := 'A' || l_view_pwd || l_pwd_part;

       RETURN l_view_pwd;

    END;]';
    execute immediate l_sql;
EXCEPTION 
    WHEN OTHERS THEN 
        RAISE;
END;
/

ALTER SESSION SET CURRENT_SCHEMA=&MY_DBA_USER;
