XX_COMMON_PCK

create or replace package xx_common_pck is

  g_key     RAW(32)  := UTL_RAW.cast_to_raw('12345678');

  g_pad_chr VARCHAR2(1) := '~';

  PROCEDURE padstring (p_text  IN OUT  VARCHAR2);

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN VARCHAR2;

  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2;

  function get_conf(p_rco_name varchar2, p_date date) return varchar2;

  function str_to_table(p_str in varchar2, p_delim in varchar2 default ',')

    return mytabletype;

  FUNCTION split_text(p_text IN CLOB, p_delimeter IN VARCHAR2 DEFAULT ',')

    RETURN mytabletype;

end;

/

create or replace package body xx_common_pck is

  -- --------------------------------------------------

  PROCEDURE padstring (p_text  IN OUT  VARCHAR2) IS

  -- --------------------------------------------------

    l_units  NUMBER;

  BEGIN

    IF LENGTH(p_text) MOD 8 > 0 THEN

      l_units := TRUNC(LENGTH(p_text)/8) + 1;

      p_text  := RPAD(p_text, l_units * 8, g_pad_chr);

    END IF;

  END;

  -- --------------------------------------------------

  FUNCTION encrypt (p_text  IN  VARCHAR2) RETURN VARCHAR2 IS

  -- --------------------------------------------------

    l_text       VARCHAR2(32767) := p_text;

    l_encrypted  RAW(32767);

  BEGIN

    padstring(l_text);

    DBMS_OBFUSCATION_TOOLKIT.desencrypt(input          => UTL_RAW.cast_to_raw(l_text),

                                        key            => g_key,

                                        encrypted_data => l_encrypted);

    RETURN utl_raw.cast_to_varchar2(l_encrypted);

  END;

  -- --------------------------------------------------

  -- --------------------------------------------------

  FUNCTION decrypt (p_raw  IN  RAW) RETURN VARCHAR2 IS

  -- --------------------------------------------------

    l_decrypted  VARCHAR2(32767);

  BEGIN

    DBMS_OBFUSCATION_TOOLKIT.desdecrypt(input => p_raw,

                                        key   => g_key,

                                        decrypted_data => l_decrypted);

    RETURN RTrim(UTL_RAW.cast_to_varchar2(l_decrypted), g_pad_chr);

  END;

  -- --------------------------------------------------

  function get_conf(p_rco_name varchar2, p_date date) return varchar2 as

    v_return varchar2(250);

  begin

    select f.rco_value

    into v_return

    from admin.xx_config f

    where f.rco_name=p_rco_name

    and p_date between nvl(f.from_date,p_date) and nvl(f.to_date,p_date)

    and nvl(f.flag_status,'Y')='Y';

    return v_return;

  exception

    when others then

      return null;

  end;

  function str_to_table(p_str in varchar2, p_delim in varchar2 default ',')

    return mytabletype as

    v_str    clob;

    v_fields pls_integer;

    v_substr clob; --varchar2(32767);

    v_return mytabletype := mytabletype();

  begin

    v_str    := p_delim || trim(p_delim from p_str) || p_delim;

    v_fields := length(v_str) - length(replace(v_str, p_delim, '')) - 1;

    v_return.extend(v_fields);

    for i in 1 .. v_fields loop

      v_substr := substr(v_str,

                         instr(v_str, p_delim, 1, i) + 1,

                         instr(v_str, p_delim, 1, i + 1) -

                         instr(v_str, p_delim, 1, i) - 1);

      v_return(i) := v_substr;

    end loop;

    return v_return;

  end;

  FUNCTION split_text(p_text IN CLOB, p_delimeter IN VARCHAR2 DEFAULT ',')

    RETURN mytabletype IS

    -- ----------------------------------------------------------------------------

    l_array mytabletype := mytabletype();

    l_text  CLOB := p_text;

    l_idx   NUMBER;

  BEGIN

    l_array.delete;

    IF l_text IS NULL THEN

      RAISE_APPLICATION_ERROR(-20000, 'P_TEXT parameter cannot be NULL');

    END IF;

    WHILE l_text IS NOT NULL LOOP

      l_idx := INSTR(l_text, p_delimeter);

      l_array.extend;

      IF l_idx > 0 THEN

        l_array(l_array.last) := SUBSTR(l_text, 1, l_idx - 1);

        l_text := SUBSTR(l_text, l_idx + LENGTH(p_delimeter));

        --dbms_output.put_line(l_text);

      ELSE

        l_array(l_array.last) := l_text;

        l_text := NULL;

      END IF;

    END LOOP;

    RETURN l_array;

  END split_text;

end;

/