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;
/