723 lines
28 KiB
MySQL
723 lines
28 KiB
MySQL
|
--------------------------------------------------------
|
||
|
|
-- DDL for Package Body PKG_SICD_COMMONS
|
||
|
|
--------------------------------------------------------
|
||
|
|
|
||
|
|
CREATE OR REPLACE EDITIONABLE PACKAGE BODY "ENVX_OPER"."PKG_SICD_COMMONS" AS
|
||
|
|
|
||
|
|
/*--------------- FNC01-------------------*/
|
||
|
|
|
||
|
|
FUNCTION f_rest_call(
|
||
|
|
p_body IN VARCHAR2,
|
||
|
|
p_url IN VARCHAR2,
|
||
|
|
p_http_verb IN VARCHAR2,
|
||
|
|
p_http_version IN VARCHAR2,
|
||
|
|
p_http_content_type IN VARCHAR2 DEFAULT NULL,
|
||
|
|
p_http_accept IN VARCHAR2,
|
||
|
|
p_one_way IN BOOLEAN DEFAULT false,
|
||
|
|
p_response_text OUT CLOB,
|
||
|
|
p_reason_phrase OUT VARCHAR2
|
||
|
|
) RETURN NUMBER AS
|
||
|
|
t_http_req utl_http.req;
|
||
|
|
t_http_resp utl_http.resp;
|
||
|
|
p_http_response_code NUMBER;
|
||
|
|
v_url VARCHAR2(2000);
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.f_rest_call';
|
||
|
|
BEGIN
|
||
|
|
p_response_text := '';
|
||
|
|
--Seteamos la URL, el verbo y la version de HTTP
|
||
|
|
t_http_req := utl_http.begin_request(p_url, p_http_verb, p_http_version);
|
||
|
|
utl_http.set_transfer_timeout(60);
|
||
|
|
--Seteamos el tipo de contenido del request y del response
|
||
|
|
utl_http.set_header(t_http_req, 'accept-encoding', 'gzip, deflate');
|
||
|
|
utl_http.set_header(t_http_req, 'Accept', p_http_accept);
|
||
|
|
|
||
|
|
--Definimos el tama?o del contenido
|
||
|
|
utl_http.set_header(t_http_req, 'Content-Length', length(p_body));
|
||
|
|
|
||
|
|
--Grabamos el body en el request
|
||
|
|
utl_http.write_text(t_http_req, p_body);
|
||
|
|
|
||
|
|
--Invocamos el Web Service
|
||
|
|
t_http_resp := utl_http.get_response(t_http_req);
|
||
|
|
dbms_output.put_line('Codigo http retornado = ' || t_http_resp.status_code);
|
||
|
|
IF (p_one_way = false) THEN
|
||
|
|
utl_http.read_text(t_http_resp, p_response_text);
|
||
|
|
--p_response_text := (CONVERT(p_response_text, 'WE8ISO8859P1', 'utf8'));
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
p_http_response_code := t_http_resp.status_code;
|
||
|
|
p_reason_phrase := t_http_resp.reason_phrase;
|
||
|
|
dbms_output.put_line('p_reason_phrase = ' || p_reason_phrase);
|
||
|
|
-- Cerramos la comunicacion request/response
|
||
|
|
utl_http.end_response(t_http_resp);
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => 'VARIABLES p_http_response_code: ' || p_http_response_code
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN p_http_response_code;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
p_response_text := 'Error al ejecutar '
|
||
|
|
|| p_url
|
||
|
|
|| sqlerrm
|
||
|
|
|| '-'
|
||
|
|
|| dbms_utility.format_error_backtrace;
|
||
|
|
|
||
|
|
p_http_response_code := sqlcode;
|
||
|
|
p_reason_phrase := 'error';
|
||
|
|
RETURN p_http_response_code;
|
||
|
|
END f_rest_call;
|
||
|
|
|
||
|
|
/*--------------- FNC02-------------------*/
|
||
|
|
|
||
|
|
FUNCTION f_system_message(
|
||
|
|
pi_id_message NUMBER,
|
||
|
|
pi_message_class VARCHAR2,
|
||
|
|
pi_return_type NUMBER DEFAULT 1
|
||
|
|
-- 0- SIMPLE / 1- COMPLETE / 2- ADDITIONAL INFO
|
||
|
|
) RETURN VARCHAR2 AS
|
||
|
|
v_message VARCHAR2(1000);
|
||
|
|
v_additional_info VARCHAR2(1000);
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.f_system_message';
|
||
|
|
BEGIN
|
||
|
|
SELECT m.message_text,
|
||
|
|
m.additional_info
|
||
|
|
INTO v_message,
|
||
|
|
v_additional_info
|
||
|
|
FROM system_message m
|
||
|
|
WHERE m.id_message = pi_id_message
|
||
|
|
AND m.id_message_class = pi_message_class;
|
||
|
|
|
||
|
|
CASE pi_return_type
|
||
|
|
WHEN 1 THEN
|
||
|
|
RETURN v_message;
|
||
|
|
WHEN 2 THEN
|
||
|
|
RETURN pi_message_class
|
||
|
|
|| '-'
|
||
|
|
|| lpad(pi_id_message, 5, '0')
|
||
|
|
|| ';'
|
||
|
|
|| v_message;
|
||
|
|
WHEN 3 THEN
|
||
|
|
RETURN pi_message_class
|
||
|
|
|| '-'
|
||
|
|
|| lpad(pi_id_message, 5, '0')
|
||
|
|
|| ';'
|
||
|
|
|| v_additional_info;
|
||
|
|
END CASE;
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => 'VARIABLES v_message,v_additional_info: '
|
||
|
|
|| v_message
|
||
|
|
|| ','
|
||
|
|
|| v_additional_info
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
--- WTF error ---
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN 'WTF-0000: Error desconocido, contacte con el administrador del sistema';
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC03-------------------*/
|
||
|
|
|
||
|
|
FUNCTION get_parameter_value(
|
||
|
|
param_name VARCHAR2
|
||
|
|
) RETURN VARCHAR2 AS
|
||
|
|
p_value VARCHAR2(4000);
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_value';
|
||
|
|
BEGIN
|
||
|
|
SELECT parameter_value
|
||
|
|
INTO p_value
|
||
|
|
FROM system_parameter
|
||
|
|
WHERE parameter_name = param_name
|
||
|
|
AND unique_param_value = 1;
|
||
|
|
apex_debug.info('pkg_sicd_commons.get_parameter_value - p_value: %s', p_value);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => 'VARIABLES p_value: ' || p_value
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN p_value;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: '
|
||
|
|
|| sqlerrm
|
||
|
|
|| ' - Parámetro consultado: '
|
||
|
|
|| param_name
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN NULL;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC04-------------------*/
|
||
|
|
FUNCTION get_parameter_list(
|
||
|
|
param_name VARCHAR2
|
||
|
|
) RETURN SYS_REFCURSOR AS
|
||
|
|
v_values SYS_REFCURSOR;
|
||
|
|
v_list CLOB;
|
||
|
|
v_query CLOB;
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_list';
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
SELECT parameter_value
|
||
|
|
INTO v_list
|
||
|
|
FROM system_parameter sp
|
||
|
|
WHERE parameter_name = param_name
|
||
|
|
AND id_status_parameter = 1
|
||
|
|
AND id_parameter_type = 4;
|
||
|
|
|
||
|
|
v_query := q'~SELECT regexp_substr(valores, '[^,]+', 1, level) AS valor
|
||
|
|
FROM (select '~'||v_list||q'~' as valores from dual)
|
||
|
|
CONNECT BY level <= regexp_count(valores, ',') + 1~';
|
||
|
|
|
||
|
|
OPEN v_values FOR v_query;
|
||
|
|
|
||
|
|
RETURN v_values;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN NULL;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC05-------------------*/
|
||
|
|
FUNCTION get_parameter_list_q(
|
||
|
|
param_name VARCHAR2
|
||
|
|
) RETURN VARCHAR2 AS
|
||
|
|
v_list CLOB;
|
||
|
|
v_query CLOB;
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.get_parameter_list_q';
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
SELECT parameter_value
|
||
|
|
INTO v_list
|
||
|
|
FROM system_parameter sp
|
||
|
|
WHERE parameter_name = param_name
|
||
|
|
AND id_status_parameter = 1
|
||
|
|
AND id_parameter_type = 4;
|
||
|
|
|
||
|
|
v_query := q'~SELECT regexp_substr(valores, '[^,]+', 1, level) AS valor
|
||
|
|
FROM (select '~'||v_list||q'~' as valores from dual)
|
||
|
|
CONNECT BY level <= regexp_count(valores, ',') + 1~';
|
||
|
|
|
||
|
|
apex_debug.info('pkg_sicd_commons.get_parameter_value - v_query: %s', v_query);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => 'VARIABLES v_query: ' || v_query
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN v_query;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN no_data_found THEN
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
RETURN NULL;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC06-------------------*/
|
||
|
|
|
||
|
|
FUNCTION fnc_ldap_auth(
|
||
|
|
p_username IN VARCHAR2,
|
||
|
|
p_password IN VARCHAR2
|
||
|
|
) RETURN BOOLEAN IS
|
||
|
|
|
||
|
|
l_ldap_host VARCHAR2(250) := 'ldapsvr.caja.com';
|
||
|
|
l_ldap_port VARCHAR2(250) := '389';
|
||
|
|
l_ldap_base VARCHAR2(250) := 'o=Cas';
|
||
|
|
l_dn_user VARCHAR2(250);
|
||
|
|
l_retval PLS_INTEGER;
|
||
|
|
l_session dbms_ldap.session;
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.fnc_ldap_auth';
|
||
|
|
BEGIN
|
||
|
|
IF p_username IS NULL OR p_password IS NULL THEN
|
||
|
|
raise_application_error(-20000, 'Debe especificar sus credenciales.');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
dbms_ldap.use_exception := true;
|
||
|
|
|
||
|
|
-- Selecciono de LDAP el DN del usuario que se intenta loguear y verifico que exista en el grupo GRP_SED
|
||
|
|
|
||
|
|
IF upper(p_username) = 'ADMIN' THEN
|
||
|
|
RETURN true;
|
||
|
|
ELSE
|
||
|
|
SELECT dn
|
||
|
|
INTO l_dn_user
|
||
|
|
FROM TABLE (apex_ldap.search(
|
||
|
|
p_username => 'cn=LdapSed,ou=CuentasdeServicio,o=Cas',
|
||
|
|
p_pass => 'LDAPSEDsistemas20caracteres',
|
||
|
|
p_host => 'ldapsvr.caja.com',
|
||
|
|
p_search_base => 'o=Cas',
|
||
|
|
p_search_filter => '&(objectClass=person)(uid='
|
||
|
|
|| p_username
|
||
|
|
|| ')(groupMembership=cn=GRP_SED,ou=Grupos,ou=Menu,ou=LOGICA,o=Cas)',
|
||
|
|
p_attribute_names => 'cn'
|
||
|
|
));
|
||
|
|
|
||
|
|
IF length(l_dn_user) > 1 THEN
|
||
|
|
-- Conexion con el servidor de LDAP.
|
||
|
|
l_session := dbms_ldap.init(
|
||
|
|
hostname => l_ldap_host,
|
||
|
|
portnum => l_ldap_port
|
||
|
|
);
|
||
|
|
l_retval := dbms_ldap.simple_bind_s(
|
||
|
|
ld => l_session,
|
||
|
|
dn => l_dn_user,
|
||
|
|
passwd => p_password
|
||
|
|
);
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
apex_debug.warn('pkg_sicd_commons.fnc_ldap_auth %s usuario: %s clave %s', 'Ingreso correctamente', l_dn_user, p_password);
|
||
|
|
RETURN true;
|
||
|
|
END IF;
|
||
|
|
RETURN false;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos');
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
RETURN false;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC07-------------------*/
|
||
|
|
|
||
|
|
FUNCTION f_access_token_sicddb RETURN VARCHAR2 AS
|
||
|
|
v_url VARCHAR2(500);
|
||
|
|
v_credentials VARCHAR2(1000);
|
||
|
|
v_http_method VARCHAR2(100) := 'POST';
|
||
|
|
v_http_code NUMBER := 0;
|
||
|
|
v_access_token CLOB := '0';
|
||
|
|
v_body CLOB;
|
||
|
|
log_program_name VARCHAR2(250) := 'f_access_token_sicddb';
|
||
|
|
BEGIN
|
||
|
|
-- Busco la URL y las credenciales para solicitar token
|
||
|
|
v_url := pkg_sicd_commons.get_parameter_value('WSO2_APIM_URL');
|
||
|
|
v_credentials := pkg_sicd_commons.get_parameter_value('SICD_DB_APP_CREDENTIALS');
|
||
|
|
|
||
|
|
-- Configuro los HTTP headers para invocar al APIM
|
||
|
|
apex_web_service.g_request_headers.DELETE();
|
||
|
|
apex_web_service.g_request_headers(1).name := 'Authorization';
|
||
|
|
apex_web_service.g_request_headers(1).value := 'Basic ' || v_credentials;
|
||
|
|
apex_web_service.g_request_headers(2).name := 'Content-Type';
|
||
|
|
apex_web_service.g_request_headers(2).value := 'application/json';
|
||
|
|
|
||
|
|
-- Realizo la invocación al APIM para solicitar token
|
||
|
|
v_body := apex_web_service.make_rest_request(
|
||
|
|
p_url => v_url,
|
||
|
|
p_http_method => v_http_method,
|
||
|
|
p_body => '{"grant_type" : "client_credentials"}'
|
||
|
|
);
|
||
|
|
v_http_code := apex_web_service.g_status_code;
|
||
|
|
|
||
|
|
-- Analizo si fue exitoso el pedido de token o no
|
||
|
|
IF v_http_code = 200 THEN
|
||
|
|
-- Si fue exitoso, guardo el access_token recibido en la tabla de parámetros
|
||
|
|
SELECT JSON_VALUE(v_body, '$.access_token' returning clob)
|
||
|
|
INTO v_access_token
|
||
|
|
FROM dual;
|
||
|
|
|
||
|
|
UPDATE system_parameter
|
||
|
|
SET parameter_value = v_access_token
|
||
|
|
WHERE parameter_name = 'SICD_DB_APP_ACCESS_TOKEN';
|
||
|
|
|
||
|
|
IF SQL%rowcount != 1 THEN
|
||
|
|
INSERT INTO system_parameter (
|
||
|
|
parameter_name,
|
||
|
|
parameter_value,
|
||
|
|
id_status_parameter,
|
||
|
|
creation_date,
|
||
|
|
unique_param_value,
|
||
|
|
paramater_func_name
|
||
|
|
)
|
||
|
|
VALUES (
|
||
|
|
'SICD_DB_APP_ACCESS_TOKEN',
|
||
|
|
v_access_token,
|
||
|
|
1,
|
||
|
|
sysdate,
|
||
|
|
1,
|
||
|
|
'Access Token para peticiones en API Manager'
|
||
|
|
);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => substr(
|
||
|
|
'Se obtuvo el token - HTTP code recibido: '
|
||
|
|
|| v_http_code
|
||
|
|
|| ' - response: '
|
||
|
|
|| v_body,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
ELSE
|
||
|
|
-- Si no fue exitoso, escribo log
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 1, --- ERROR
|
||
|
|
pi_log_message => substr(
|
||
|
|
'Error - Se obtuvo un HTTP code distinto de 200 - HTTP code recibido: '
|
||
|
|
|| v_http_code
|
||
|
|
|| ' - response: '
|
||
|
|
|| v_body
|
||
|
|
|| ' - Detalle del error: '
|
||
|
|
|| sqlerrm,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Retorno el access_token obtenido o un '0' para indicar que hubo algún problema en la solicitud del token
|
||
|
|
RETURN v_access_token;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
-- En caso de algún error escribo log
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 1, --- ERROR
|
||
|
|
pi_log_message => substr(
|
||
|
|
'Error EXCEPTION WHEN OTHERS - Detalle del error: '
|
||
|
|
|| sqlerrm
|
||
|
|
|| ' - Ubicación: '
|
||
|
|
|| dbms_utility.format_error_backtrace,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
RETURN v_access_token;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC08-------------------*/
|
||
|
|
FUNCTION fnc_ad_auth(
|
||
|
|
p_username IN VARCHAR2,
|
||
|
|
p_password IN VARCHAR2
|
||
|
|
) RETURN BOOLEAN IS
|
||
|
|
|
||
|
|
l_ldap_host VARCHAR2(500) := 'ldaplc.lc.gc.local';
|
||
|
|
l_ldap_port VARCHAR2(500) := '389';
|
||
|
|
l_ldap_base VARCHAR2(500) := 'DC=lc,DC=gc,DC=local';
|
||
|
|
l_dn_user VARCHAR2(500);
|
||
|
|
l_retval PLS_INTEGER;
|
||
|
|
l_session dbms_ldap.session;
|
||
|
|
v_boolean BOOLEAN;
|
||
|
|
log_program_name VARCHAR2(50) := 'pkg_sicd_commons.fnc_ad_auth';
|
||
|
|
BEGIN
|
||
|
|
IF p_username IS NULL OR p_password IS NULL THEN
|
||
|
|
raise_application_error(-20000, 'Debe especificar sus credenciales.');
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
dbms_ldap.use_exception := true;
|
||
|
|
SELECT dn
|
||
|
|
INTO l_dn_user
|
||
|
|
FROM TABLE (apex_ldap.search(
|
||
|
|
p_username => 'CN=LCU SICD LDAP,OU=Usuarios de Servicios,DC=lc,DC=gc,DC=local',
|
||
|
|
p_pass => 'martesSICDsegura10',
|
||
|
|
p_host => 'ldaplc.lc.gc.local',
|
||
|
|
p_search_base => 'DC=lc,DC=gc,DC=local',
|
||
|
|
p_search_filter => '&(objectClass=person)(userPrincipalName='
|
||
|
|
|| p_username
|
||
|
|
|| '@lc.gc.local)(memberOf=CN=LCGU_SICD_Users,OU=Grupos,DC=lc,DC=gc,DC=local)',
|
||
|
|
p_attribute_names => 'CN'
|
||
|
|
));
|
||
|
|
|
||
|
|
IF length(l_dn_user) > 1 THEN
|
||
|
|
l_session := dbms_ldap.init(l_ldap_host, l_ldap_port);
|
||
|
|
l_retval := dbms_ldap.simple_bind_s(
|
||
|
|
ld => l_session,
|
||
|
|
dn => l_dn_user,
|
||
|
|
passwd => p_password
|
||
|
|
);
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
v_boolean := true;
|
||
|
|
RETURN v_boolean;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
v_boolean := false;
|
||
|
|
RETURN v_boolean;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
apex_util.set_custom_auth_status(p_status => 'Usuario o Contraseña incorrectos');
|
||
|
|
IF length(l_session) > 1 THEN
|
||
|
|
l_retval := dbms_ldap.unbind_s(ld => l_session);
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
------------------------------------------------------------------
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 2, --- WARNING
|
||
|
|
pi_log_message => 'ERROR: ' || sqlerrm
|
||
|
|
);
|
||
|
|
------------------------------------------------------------------
|
||
|
|
|
||
|
|
v_boolean := false;
|
||
|
|
RETURN v_boolean;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC09-------------------*/
|
||
|
|
FUNCTION fnc_auth_groups(
|
||
|
|
p_js_parameters json DEFAULT NULL
|
||
|
|
) RETURN BOOLEAN IS
|
||
|
|
BEGIN
|
||
|
|
RETURN true;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- FNC10-------------------*/
|
||
|
|
FUNCTION fnc_parameter_validator(pi_value IN CLOB, pi_type IN NUMBER)
|
||
|
|
RETURN BOOLEAN
|
||
|
|
IS
|
||
|
|
v_num NUMBER;
|
||
|
|
v_date DATE;
|
||
|
|
v_json json_object_t;
|
||
|
|
v_format VARCHAR2(100);
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
SELECT parameter_type_format
|
||
|
|
INTO v_format
|
||
|
|
FROM system_parameter_type spt
|
||
|
|
WHERE id_parameter_type = pi_type;
|
||
|
|
|
||
|
|
-- Intenta convertir el valor a un número
|
||
|
|
CASE pi_type
|
||
|
|
WHEN 1 THEN
|
||
|
|
--- NUMERO ---
|
||
|
|
BEGIN
|
||
|
|
v_num := TO_NUMBER(pi_value);
|
||
|
|
RETURN true;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN false;
|
||
|
|
END;
|
||
|
|
WHEN 2 THEN
|
||
|
|
--- FECHA Y HORA ---
|
||
|
|
BEGIN
|
||
|
|
v_date := TO_DATE(pi_value, 'YYYY-MM-DD');
|
||
|
|
RETURN true;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN false;
|
||
|
|
END;
|
||
|
|
WHEN 3 THEN
|
||
|
|
--- JSON ---
|
||
|
|
BEGIN
|
||
|
|
v_json := json_object_t.parse(pi_value);
|
||
|
|
RETURN true;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN false;
|
||
|
|
END;
|
||
|
|
WHEN 4 THEN
|
||
|
|
--- LISTA DE VALORES ---
|
||
|
|
IF regexp_like(pi_value, '^''[[:alnum:]]+''(,''[[:alnum:]]+'')*$') THEN
|
||
|
|
RETURN true;
|
||
|
|
ELSE
|
||
|
|
RETURN false;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
WHEN 0 THEN
|
||
|
|
--- TEXTO SIN FORMATO ---
|
||
|
|
RETURN true;
|
||
|
|
END CASE;
|
||
|
|
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
RETURN false;
|
||
|
|
END;
|
||
|
|
|
||
|
|
/*--------------- PR01-------------------*/
|
||
|
|
|
||
|
|
PROCEDURE p_rest_call_apim(
|
||
|
|
pi_url IN VARCHAR2,
|
||
|
|
pi_http_method IN VARCHAR2 DEFAULT 'GET',
|
||
|
|
pi_body IN CLOB DEFAULT empty_clob(),
|
||
|
|
po_http_status OUT VARCHAR2,
|
||
|
|
po_http_headers OUT http_headers_tab,
|
||
|
|
po_response OUT CLOB,
|
||
|
|
po_response_code OUT VARCHAR2,
|
||
|
|
po_error_message OUT VARCHAR2
|
||
|
|
) AS
|
||
|
|
v_response CLOB;
|
||
|
|
v_credentials CLOB;
|
||
|
|
log_program_name VARCHAR2(250) := 'p_rest_call_apim';
|
||
|
|
BEGIN
|
||
|
|
po_response_code := 'OK';
|
||
|
|
-- Busco el access_token para invocar al APIM
|
||
|
|
--v_credentials := pkg_sicd_commons.get_parameter_value('SICD_DB_APP_ACCESS_TOKEN');
|
||
|
|
v_credentials := pkg_sicd_commons.get_parameter_value('ENVX_APIKEY');
|
||
|
|
|
||
|
|
-- Configuro HTTP headers para invocar al APIM
|
||
|
|
apex_web_service.g_request_headers.DELETE();
|
||
|
|
-- apex_web_service.g_request_headers(1).name := 'Authorization'; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta
|
||
|
|
-- apex_web_service.g_request_headers(1).value := 'Bearer ' || v_credentials; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta
|
||
|
|
|
||
|
|
apex_web_service.g_request_headers(1).name := 'apikey';
|
||
|
|
apex_web_service.g_request_headers(1).value := v_credentials;
|
||
|
|
|
||
|
|
-- Realizo la invocación al APIM
|
||
|
|
po_response := apex_web_service.make_rest_request(
|
||
|
|
p_url => pi_url,
|
||
|
|
p_http_method => pi_http_method,
|
||
|
|
p_body => pi_body
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Obtengo el cófigo HTTP de respuesta y lo escribo en el log
|
||
|
|
po_http_status := apex_web_service.g_status_code;
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => substr(
|
||
|
|
'Se ejecuta request hacia AM - HTTP code recibido: '
|
||
|
|
|| po_http_status
|
||
|
|
|| ' - response: '
|
||
|
|
|| po_response,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Analizo si el access_token utilizado esta vencido o no
|
||
|
|
IF po_http_status = 401 THEN
|
||
|
|
-- El access_token utilizado esta vencido
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 3, --- INFO
|
||
|
|
pi_log_message => 'Error invalid_credentials - HTTP code recibido: ' || po_http_status
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Solicito un nuevo access_token
|
||
|
|
v_credentials := f_access_token_sicddb();
|
||
|
|
|
||
|
|
-- Si NO se pudo obtener un nuevo access_token
|
||
|
|
IF v_credentials = '0' THEN
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 1, --- ERROR
|
||
|
|
pi_log_message => 'Error al solicitar token mediante invocación de rutina f_access_token_sicddb()'
|
||
|
|
);
|
||
|
|
po_response_code := 'ERROR';
|
||
|
|
po_error_message := 'Error al solicitar token - Consulte con el administrador del sistema.';
|
||
|
|
ELSE
|
||
|
|
-- Si obtuvimos un nuevo access_token, configuro HTTP headers y realizo la invocación del APIM de nuevo
|
||
|
|
apex_web_service.g_request_headers.DELETE();
|
||
|
|
--apex_web_service.g_request_headers(1).name := 'Authorization';
|
||
|
|
--apex_web_service.g_request_headers(1).value := 'Bearer ' || v_credentials; -- OJO, Tener en cuenta para ejecutar la llamada al APIM de manera correcta
|
||
|
|
--apex_web_service.g_request_headers(1).value := 'Basic dmF0cm94OjExYjczNDVjODYxYTQzMWIxNTM1ZjQxYWU2NzExY2FkZWY=';
|
||
|
|
apex_web_service.g_request_headers(1).name := 'apikey';
|
||
|
|
apex_web_service.g_request_headers(1).value := v_credentials;
|
||
|
|
po_response := apex_web_service.make_rest_request(
|
||
|
|
p_url => pi_url,
|
||
|
|
p_http_method => pi_http_method,
|
||
|
|
p_body => pi_body
|
||
|
|
);
|
||
|
|
po_http_status := apex_web_service.g_status_code;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
-- Capturo los headers HTTP retornados y los almaceno en la variable de salida po_http_headers
|
||
|
|
po_http_headers := http_headers_tab();
|
||
|
|
FOR i IN 1..apex_web_service.g_headers.count
|
||
|
|
LOOP
|
||
|
|
po_http_headers.extend(1);
|
||
|
|
po_http_headers(po_http_headers.last) := http_headers_obj(apex_web_service.g_headers(i).name, apex_web_service.g_headers(i).value);
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 4, --- DEBUG
|
||
|
|
pi_log_message => substr(
|
||
|
|
'HTTP header OUT - '
|
||
|
|
|| po_http_headers(i).campo
|
||
|
|
|| ': '
|
||
|
|
|| po_http_headers(i).valor,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
END LOOP;
|
||
|
|
EXCEPTION
|
||
|
|
WHEN OTHERS THEN
|
||
|
|
-- En caso de algún error, escribo log y cargo las variables de error de salida
|
||
|
|
log_collector(
|
||
|
|
pi_program_name => log_program_name,
|
||
|
|
pi_log_level => 1, --- ERROR
|
||
|
|
pi_log_message => substr(
|
||
|
|
'Error EXCEPTION WHEN OTHERS - Detalle del error: '
|
||
|
|
|| sqlerrm
|
||
|
|
|| ' - Ubicación: '
|
||
|
|
|| dbms_utility.format_error_backtrace
|
||
|
|
|| ' - HTTP code recibido: '
|
||
|
|
|| po_http_status
|
||
|
|
|| ' - response: '
|
||
|
|
|| po_response,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
)
|
||
|
|
);
|
||
|
|
po_response_code := 'ERROR';
|
||
|
|
po_error_message := substr(
|
||
|
|
'Error: '
|
||
|
|
|| sqlerrm
|
||
|
|
|| ' - HTTP code recibido: '
|
||
|
|
|| po_http_status
|
||
|
|
|| ' - response: '
|
||
|
|
|| po_response,
|
||
|
|
1,
|
||
|
|
3900
|
||
|
|
);
|
||
|
|
END;
|
||
|
|
|
||
|
|
-------------------------------------------
|
||
|
|
|
||
|
|
END pkg_sicd_commons;
|
||
|
|
|
||
|
|
/
|
||
|
|
|
||
|
|
GRANT EXECUTE ON "ENVX_OPER"."PKG_SICD_COMMONS" TO "APEX_REST_PUBLIC_USER";
|
||
|
|
GRANT DEBUG ON "ENVX_OPER"."PKG_SICD_COMMONS" TO "APEX_REST_PUBLIC_USER";
|